MySQL Bugs: #100549: InnoDB allocates too much memory during create index

Bug #100549	InnoDB allocates too much memory during create index
Submitted:	17 Aug 2020 18:04	Modified:	18 Aug 2020 13:06
Reporter:	Mark Callaghan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.0.18, 8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The docs state there are 3 innodb_sort_buffer_size allocations per create index. AFAIK that is correct. This request is open because:
* external sort should only need one large sort buffer per create index. Using 3 is too much.
* the true overhead on mysqld VSZ and RSS is more than 6X the value of innodb_sort_buffer_size rather than the 3X listed in the docs.

I have learned this because my tests are getting OOM when I have 8 concurrent clients doing 'alter table ... add index x1 ..., add index x2 ..., add index x3 ...

So that is 8 clients X 3 indexes/client == 24 concurrent create index. With innodb_sort_buffer_size=64M then 24 * 3 * 64M ~=  4.6G but VSZ and RSS increase by ~12G in my tests.

Docs are:
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_sort_buffer_s...

JFG explained what I was seeing in:
https://jfg-mysql.blogspot.com/2016/07/understanding-bulk-index-creation-in-innodb.html

How to repeat:
On the way

Suggested fix:
1) Update the docs and 2) update InnoDB to only use one large sort buffer per create index

Here is an example of what I see when sort is in progress via the PS. This allocation is the source of my OOM. For this test innodb_page_size=8192 and the results below appear to be ~8KB allocations

                  EVENT_NAME: memory/innodb/memory
                 COUNT_ALLOC: 74418223
                  COUNT_FREE: 73863301
   SUM_NUMBER_OF_BYTES_ALLOC: 130435380648
    SUM_NUMBER_OF_BYTES_FREE: 125882388224
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 554922
             HIGH_COUNT_USED: 819040
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 4552992424
   HIGH_NUMBER_OF_BYTES_USED: 6723130240

FWIW, the design and code are in need of lots of love. I have a WIP branch which will fix this and other issues.  The control flow is just too convoluted for my taste.

I worked on the repro script for a few hours then ran out of time. But code review should be able to confirm that InnoDB does 3 allocations of size innodb_sort_buffer_size and background reading on external sort will confirm that only one large allocation is needed. The merge phase only needs a small buffer per sorted run and a small output buffer.

I won't suggest more because I worked on the RDBMS sort at Oracle.

Hello Mark,

Thank you for feedback and reasonable feature request!
Observed this with 8.0.21 release build.

Thanks,
Umesh