MySQL Bugs: #112063: Uses too much memory

Bug #112063	Uses too much memory
Submitted:	14 Aug 2023 9:50	Modified:	3 Jan 2024 23:31
Reporter:	Boy Zhang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	handler, histogram, random_number_engine

Description:
The memory used by the MySQL instance is too high. The analysis found that the m_random_number_engine object on the handler occupies too much memory (5000 bytes). When there are many concurrent connections, this part of the memory overhead is very large. m_random_number_engine is only used in the histogram scenario, but this part of the memory is allocated on the handler in advance, regardless of whether the histogram is used or not. In fact, this part of the space is wasted.

How to repeat:
None.

Suggested fix:
m_random_number_engine can exist as a pointer, and maintain a pointer on THD at the same time. When using histogram, allocate memory from THD, and then all handlers in this connection can use this memory to allocate space for m_random_number_engine.
For example:
table->file->m_random_number_engine = new (current_thd->random_number_engine) std::mt19937;

Hi Mr. Zhang,

Thank you for your bug report.

However, it is our opinion that this is not a bug.

First , our random number generator is not a storage engine.

Next, 5 KBytes used per connection is insignificant compared to megabytes and megabytes used for local buffers on each of the connection. We are using the most appropriate algorithm for the random numbers and we can not change it without performance penalties and even breaking compatibility.

However, if you send us your patch for the replacement that will maintain performance and compatibility, while using MUCH less memory, we shall definitely examine it ....

Not a bug.

Hi Mr. Zhang,

Thank you for your bug report.

We decided that this is a good feature request, since those 5K bytes do not need to be allocated always.

This is now a verified feature request.

Documented fix as follows in the MySQL 8.3.0 changelog:

    Improved handler state resource allocation.

Closed.

Thank you, Jon.