MySQL OOM Series 3 get rid of the bad luck of MySQL being killed, oommysql

MySQL OOM Series 3 get rid of the bad luck of MySQL being killed, oommysql

In the previous two chapters, we analyzed the Linux memory allocation policy and the Linux OOM_Killer mechanism to solve the risks caused by "oversales". MySQL is the same as other applications, the Innodb_buffer_pool must be smaller than the actual physical memory. Otherwise, MySQL may fail to start. In fact, this is a misunderstanding. This is not controlled by the MySQL layer. It is controlled by the operating system (OS) layer, that is, whether the/proc/sys/overcommit_memory control OS allows "oversales ". If "oversold" is allowed, Innodb_buffer_pool can be much larger than the actual memory size, but this part of space is not used. For a small experiment, see:

MySQL Innodb_buffer_pool has 5 GB open, but the actual memory is only 3 GB.
After talking so much about it, let's get back to the problem of the first apsaradb for RDS instance being killed by the OS. As we mentioned above, once the instance has insufficient memory, mySQL is usually the preferred target of OOM_Killer. Two problems are involved:

1. Why is the memory insufficient?
2. How can I free MySQL from the bad luck of being killed?
First, let's take a look at the first question. There are many reasons for this problem due to insufficient memory, but there are two main reasons. The first one is that there is a problem with MySQL memory planning. The second is to deploy a lot of monitoring or scheduled task scripts on the MySQL server. These scripts often lack the necessary memory restrictions, resulting in occupying a large amount of memory during peak hours, as a result, the Linux OOM_Killer mechanism is triggered, and MySQL will be sacrificed.
So how can we free MySQL from the bad luck of being killed? The root cause of MySQL Kill is the oversold Memory Allocation Mechanism in Linux. As mentioned above, as long as this oversold mechanism exists, it is impossible to completely avoid the risk of Kill an application. So that MySQL will not be killed, and the operating system can only be prohibited from allocating memory that exceeds the actual memory space. However, we also mentioned earlier that we do not recommend this for the MySQL server, because many of MySQL's memory has just been applied for and is not immediately used, once the OS is not oversold, this not only puts more stringent requirements on MySQL memory planning, but also has the problem that memory cannot be fully utilized. At the same time, the private memory of each MySQL connection is dynamically allocated. If the allocation fails, the server Crash will be directly caused, which will also increase the risk of MySQL Crash.
Since the operating system is limited and cannot be completely prevented from being killed, we can only minimize the chance of MySQL being killed. I think at least three things can be done:

1) reasonably plan MySQL memory usage.
2) Adjust the OOM_adj parameter to lower the priority of MySQL locked by OOM_Killer.
3) strengthen memory monitoring and alarm. Once an alarm is triggered, DBA should intervene quickly to Kill connections that occupy a large amount of memory.