MySQL Bugs: #112223: Adaptive Hash Index(AHI) use 1/64 memory, even it never be switch on

Bug #112223	Adaptive Hash Index(AHI) use 1/64 memory, even it never be switch on
Submitted:	30 Aug 2023 20:44	Modified:	15 Sep 2023 5:30
Reporter:	Zongzhi Chen (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	all version	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ahi, innodb

Description:
In our production environment, customer want to know the detail usage of memory.

It is an 32Core 128G instance, We find that even we don't use Adaptive Hash Index(AHI), the default value is switch off when the instance is started. the AHI still use about 2G memory, it is about 1/64 of the buffer pool memory.

As the upstream suggest to switch off the AHI by default, I suggest to disable the memory allocate by AHI module by default, only allocate the memory when user switch on.

The memory allocate code is here
in buf_pool_init() =>
btr_search_sys_create(buf_pool_get_curr_size() / sizeof(void *) / 64)

it will initial an hash_table size equal to = buf_pool_get_curr_size() / sizeof(void *) / 64 = buf_pool_get_curr_size() / 512

each hash_cell in the hash table use 8 byte pointer, then the hash_table will use (buf_pool_get_curr_size() / 512) * 8 = buf_pool_get_curr_size() / 64 size memory.

So if use a large memory instance, the AHI will waste too many memory.

How to repeat:
read the code and start an instance.

Hello chen zongzhi,

Thank you for the report and feedback.

regards,
Umesh

The problem is not only that memory gets allocated even if AHI is disabled. That memory is also zero-initialized leading to physical memory pages to be allocated and linked from the process page table.

Another problem is that the allocated memory is not visible in Performance Schema, which has been reported as bug #117778.

Also, zero-initializing (possibly unused) AHI memory blocks the server startup by a few seconds on instances with big buffer pools.