Bug #112063 Uses too much memory
Submitted: 14 Aug 2023 9:50 Modified: 3 Jan 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

[14 Aug 2023 9:50] Boy Zhang
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;
[14 Aug 2023 12:28] MySQL Verification Team
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.
[16 Aug 2023 13:31] MySQL Verification Team
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.
[3 Jan 23:31] Jon Stephens
Documented fix as follows in the MySQL 8.3.0 changelog:

    Improved handler state resource allocation.

Closed.
[8 Jan 10:41] MySQL Verification Team
Thank you, Jon.