Bug #110415 Unrestricted memory allocation to query causes OOM kill
Submitted: 17 Mar 14:38 Modified: 28 Mar 14:12
Reporter: Xing Zhuge Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:8.0.30, 8.0.31, 8.0.32 OS:Linux
Assigned to: CPU Architecture:x86

[17 Mar 14:38] Xing Zhuge
We observed in a customer production database that under intense workloads where multiple sessions are spun, the database memory becomes exhaustive. The available memory on the operating system dropped below 100MB. As a result the mysqld process was terminated by the operating system. Thereby running the MySQL database out of memory and shutting and restarting the instance. A similar issue was reported https://bugs.mysql.com/bug.php?id=33250

How to repeat:
Tests were executed in MySQL community edition version 8.0.30/8.0.31/8.0.32.
The following parameters were set;
•	memory connections = 1000 
•	Produce a workload of 1000 concurrent connections within a couple of minutes with each connection running a query with a large sort operation. 
•	The database instance crashes within a few minutes.

Suggested fix:
Enhance MySQL memory management to restrict memory usage on certain memory related operations.
MySQL database should monitor the free memory on the operating system so that it doesn’t get killed when there’s memory exhaustion.
[17 Mar 14:53] MySQL Verification Team
Hi Mr. Zhugg,

Thank you very much for your bug report.

We tried to repeat the problem, but with no success .......

You simply have to reconfigure your MySQL server.

You will find all the necessary instructions in our Reference Manual, which is available online, on the WWW site dev.mysql.com.

Can't repeat .......
[27 Mar 16:00] Xing Zhuge
We have created a special test case with limited system RAM to simulate the OOM kill issue.
[28 Mar 12:15] MySQL Verification Team
Hi Mr. Zhuge,

We have ran your test case and, indeed, Operating System kernel does kill a process.

That is exactly expected behaviour, as described in our Reference Manual.

Not a bug.
[28 Mar 14:12] Xing Zhuge
Could you provide a reference to the MySQL reference manual on this OOM Kill issue? Thanks.
[29 Mar 11:49] MySQL Verification Team

This is described in the User's Manual of the OS that you are using, in your case, search in Linux User's Manual.

OOM is not a functionality of MySQL, but of the host OS.
[31 Mar 13:46] Philip Gladstone
You made the comment:

| That is exactly expected behaviour, as described in our Reference Manual.

Can you please point me to the section in the MySQL docs where it describes why the mysql process exceeds its memory configuration and therefore gets killed by the operating system?

Alternatively, is there a way to limit the memory consumption of MySQL so that it *never* exceeds a certain amount of memory? Or is there a formula (presumably based on the configuration settings) that gives the maximum amount of memory that can be consumed by MySQL? I wish to configure my environment so that the MySQL process *never* runs out of memory.
[31 Mar 13:55] MySQL Verification Team
Hi Mr. Gladstone,

Thank you for your question.

MySQL does not work in that fashion.  MySQL does not have a top amount of memory that it can not run over. Actually, most of the RDBMS on the market simply use all the memory that is available.

However, 95 % of memory allocated by MySQL is actually configurable, so you can tune MySQL to perfection. There are some local / session / thread buffers, which are all configurable. That means that total memory used by MySQL will be N times that total amount. If you have 1.000 concurrent connections and your total , for example 64 Mb, then MySQL will use 64 Gb just for local, per-thread buffers.

Configuring MySQL is an art in itself and can be fine tuned from query to query.

However, this is a forum ONLY for reports that prove that MySQL does not follow its functionality as described in our Reference Manual.

This is NOT a forum for asking any questions, including the one that you asked here.

So, please read the next comment very carefully.
[31 Mar 13:56] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.
[31 Mar 14:04] Philip Gladstone
This is the result of a bug -- that MySQL can consume memory without limit. You said that there was a description of this behavior in *your* reference manual. I am simply asking you to identify this section in *your* reference manual so that I may learn why this behavior is not a bug.
[31 Mar 14:09] MySQL Verification Team

Sure , there it is:


That link provides many other links that should be all read in order to understand how is MySQL memory utilisation configured.
[31 Mar 14:13] MySQL Verification Team

One other, very  small remark.

If you are using MySQL database system, you are supposed to read the entire Reference Manual.