MySQL Slave trigger Oom-killer solution _mysql

Recently often received MySQL instances similar to the lack of memory alarm information, landing to the server on a look found that MySQL ate 99% of memory, God!

Sometimes the kernel will help us restart MySQL on its own, and then we can see that the DMESG information has the following records:

Mar 9 11:29:16 xxxxxx kernel:mysqld invoked oom-killer:gfp_mask=0x201da, order=0, oom_adj=0, oom_score_adj=0
Mar 9 11:29:16 xxxxxx kernel:mysqld cpuset=/mems_allowed=0
Mar 9 11:29:16 xxxxxx kernel:pid:99275, comm:mysqld not tainted 2.6.32-431.el6.x86_64 #1
Mar 9 11:29:16 xxxxxx kernel:call Trace:

Now describe the specific scene:

Premise: Operating system and MySQL version:

Os:centos Release 6.5 (Final) kernel:2.6.32-431.el6.x86_64 (physical machine)
Mysql:percona 5.6.23-72.1-log (Single instance)

Trigger scene: Slave Whether or not there are other links in the memory cycle will occur, triggering the kernel Oom-killer

It is said that the problem has appeared for more than 1 years, because just come over, the boss let me check to find out what clues, then start check this question slightly:

1. Suspect that the memory allocated to MySQL is not reasonable, then I went to check the size of the Innodb_buffer_pool and physical memory size, found that the size of the allocation of BP is about 60% of the physical memory, then not this reason, excluded, If this is the problem, they should have found it already.
2. Check the operating system parameters configuration. [vm.swappiness = 1;/proc/sys/vm/overcommit_memory Oom_adj] You can temporarily set the adj parameter to a-15 or direct-17 Before you troubleshoot the problem, so the kernel will never kill Mys QL, but this does not solve the problem at all, and there is a certain risk, will not lead to MySQL need to allocate memory and hang live it? Let's just think about this method.
3. OK, MySQL initialization parameters, operating system parameters do not seem to have the configuration of the appropriate place. So let's look for MySQL itself!

Now that MySQL memory is in a state of soaring, so, will it be due to the memory allocation of time, then according to the Internet reported a MySQL memory allocation caused by a bug, I also came to operate in my environment, a look at exactly: 1. Record the amount of memory consumed by the current MySQL process 2. Record show engine InnoDB status; 3. Execute flush tables; 4. Record show engine InnoDB status; 5. Record the size of the MySQL process; 6 Compare the two results to see if there is any noticeable change in the memory allocated by MySQL before executing Flush table and Flush table. Well, this bug looks like I'm not here anymore.

Look at this version has a innodb_buffer_pool_instances parameter, official Internet also has about innodb_buffer_pool_instances and innodb_buffer_pool_size set improper The bug that causes MySQL OOM, presumably means: we can set the innodb_buffer_pool_size than our actual physical memory, such as our physical memory is: 64GB, and we set Innodb_buffer_pool_ SIZE=300GB, and Innodb_buffer_pool_instances > 5, we can still pull MySQL up. But it's easy to oom MySQL. Detailed information: http://bugs.mysql.com/bug.php?id=79850 here look over here.

There is also a situation, also reported a bug, that is, slave settings filter, will trigger Oom, but I Instance not set, so ignore this.

Since it is not a MySQL memory sale, it is not the result of opening the table handle. So what's the reason?

We think again, this phenomenon appears in Slave,master and Slave configuration, just as Master ran the production business, Slave Some instance ran query business, some instance did not run any task, but still will start Oom, Then this situation is likely to be caused by Slave 囖.

Then I found an example to try a, not to try not to know Ah, a try to frighten. Go up to perform a bit: Stop slave;start slave; This command card for about 3 minutes, and then a look at memory usage, and suddenly released the 20gb+. This is basically where the problem lies, but slave we all know that there are two threads, is it due to SQL thread or IO thread? This is still waiting for the next time it will be further investigation.

The monitoring information of the sticking point memory:

12:00:01 PM kbmemfree kbmemused%memused kbbuffers kbcached kbcommit%commit
02:40:01 PM 566744 131479292 99.57 88744 618612 132384348 89.19
02:50:01 PM 553252 131492784 99.58 83216 615068 132406792 89.20
03:00:01 PM 39302700 92743336 70.24 95908 925860 132413308 89.21
03:10:01 PM 38906360 93139676 70.54 109264 1292908 132407836 89.21
03:20:01 PM 38639536 93406500 70.74 120676 1528272 132413136 89.21

I recorded something slightly more specific: https://bugs.launchpad.net/percona-server/+bug/1560304 If you can't access it (http://www.jb51.net/article/88729.htm)

Finally, a little summary:

Phenomenon: Slave OOM
Interim Solution: Restart slave
Long-term solution: Small version upgrade MySQL Server

More systematic point please see Guo always wrote:
Http://www.jb51.net/article/88726.htm
Http://www.jb51.net/article/88727.htm