MySQL Bugs: #110415: Unrestricted memory allocation to query causes OOM kill

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

Description:
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 
Internal_tmp_mem_storage_engine=MEMORY
•	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.

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 .......

We have created a special test case with limited system RAM to simulate the OOM kill issue.

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.

Could you provide a reference to the MySQL reference manual on this OOM Kill issue? Thanks.

Hi,

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.

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.

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.

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.

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.

Hi,

Sure , there it is:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

That link provides many other links that should be all read in order to understand how is MySQL memory utilisation configured.

Hi,

One other, very  small remark.

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