MySQL OOM Series 3: Get rid of the bad luck of MySQL being killed_MySQL

In the previous two chapters, we analyzed the Linux memory allocation strategy and Linux solved the risk caused by "overbooking" by using the OOM_Killer mechanism. MySQL, like other applications, can also be overbooked within the scope allowed by the operating system. , Most people understand that Innodb_buffer_pool must be smaller than the actual physical memory, otherwise MySQL will fail to start. In fact, this is a misunderstanding. This is not controlled by the MySQL layer. This is controlled by the operating system (OS) layer. The aforementioned /proc/sys/overcommit_memory controls whether the OS allows "overbooking". If "overbooking" is allowed, Innodb_buffer_pool can far exceed the actual memory space size, but this part of the space is not used. We can do a small experiment, see the picture below:

MySQL's Innodb_buffer_pool is set to 5G, but the actual memory is only 3G.
Having said so much, let’s get back to the topic and return to the issue we first mentioned about the RDS instance being killed by the OS. We also mentioned earlier that once the available memory of the instance is insufficient, MySQL will generally become the first choice of OOM_Killer. There are two issues involved here:

1. Why is there insufficient memory?
2. How to get MySQL out of the bad luck of being killed?
First let's look at the first question. There are many reasons for the problem of insufficient memory, but there are mainly two aspects. The first is that there is a problem with MySQL's own memory planning. The second is that servers that generally deploy MySQL will deploy a lot of monitoring or scheduled task scripts, and these scripts often lack necessary memory restrictions, resulting in occupying a large amount of memory during peak periods, triggering the Linux OOM_Killer mechanism, and MySQL is innocent sacrificed.
So how can we get MySQL out of the bad luck of being killed? The root cause of MySQL being killed lies in the overbooked memory allocation mechanism of Linux. As mentioned earlier, as long as this overbooked mechanism exists, it is impossible to completely avoid the risk of a certain application being killed. To prevent MySQL from being killed, the operating system can only be prohibited from allocating memory beyond the actual memory space. But as we mentioned before, we do not recommend this for servers where MySQL is deployed, because a lot of MySQL’s memory has just been applied for and is not used immediately. Once the OS prohibits overbooking, this will not only affect MySQL’s own memory The planning puts forward more stringent requirements, and there is also the problem of insufficient memory utilization. At the same time, the private memory of each MySQL connection is dynamically allocated. If it is not allocated, it will directly cause the server to crash, which will also increase the risk of MySQL crash.
Since it is limited by the operating system and cannot completely avoid being killed, we can only try to reduce the probability of MySQL being killed. I think we can do at least the following 3 things:

1) Reasonably plan the memory usage of MySQL.
2) Adjust the OOM_adj parameter to lower the priority of MySQL being locked by OOM_Killer.
3) Strengthen memory monitoring and alarming. Once alarmed, the DBA should quickly intervene and kill some connections that occupy more memory.