Reason analysis of MySQL Slave triggering Oom-killer

Recently, I have received a MySQL instance like out-of-memory alarm information, log on to the server to see that MySQL ate 99% of the memory, God!

Sometimes not in time, the kernel will help us restart MySQL, and then we can see 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 scenario:

Major 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 Scenario: Slave There is a recurring spike in memory, whether or not there are other links, triggering the kernel Oom-killer

It is said that this problem has appeared for more than 1 years, because just came over, the eldest brother let me check to see if I can find any clues, then start check this question:

1. Suspect that the memory allocated to MySQL is unreasonable, then I went to check the size of the Innodb_buffer_pool and physical memory size, found that the size of the allocation to BP accounted for about 60% of the physical memory, then this is not the reason, ruled out, 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 15 or direct 17 before troubleshooting, so the kernel will never kill my SQL, 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 not out of the hang it? This is the way to think about it.

3. Well, MySQL initialization parameters, operating system parameters do not seem to be configured with inappropriate places. Then let's find MySQL itself!

Since the MySQL memory has been in the soaring state, then, will not be due to the memory allocation of the time, then according to the Internet reported a MySQL memory allocation caused by a bug, I also come in my environment to operate a, a look at exactly: 1. Record the current amount of memory occupied by the MySQL process ; 2. Record show engine InnoDB status; 3. Implementation of the flush tables; 4. Record show engine InnoDB status; 5. Record the MySQL process footprint, 6 compare these two results to see if there is any noticeable change in the memory allocated by MySQL before the Flush table and flush table. Well, this bug doesn't seem to be my place anymore.

Look at this version has a innodb_buffer_pool_instances parameter, the official network also has about innodb_buffer_pool_instances and Innodb_buffer_pool_size improper setup The bug that causes the MySQL OOM, probably means: we can set the innodb_buffer_pool_size to be larger 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, this way, MySQL is easy to oom. More information: http://bugs.mysql.com/bug.php?id=79850 look over here.

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

Since it is not the MySQL memory oversold cause, it is not the handle of the open table. So what's the reason?

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

Then I found an example to try a, don't try not to know Ah, a try to scare a jump. Went up to execute a bit: Stop slave;start slave; This command card for about 3 minutes, and then a look at memory usage, all of a sudden released 20gb+. This is basically where the problem is, but slave we all know that there are two threads that are caused by SQL thread or IO thread? This is still waiting for the next time to happen when the further investigation.

The monitoring information of the post-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've recorded something a little bit more specific: https://bugs.launchpad.net/percona-server/+bug/1560304

Finally, a little summary:

Phenomenon: Slave OOM

Interim Workaround: Restart slave

Long-term solution: Minor version upgrade MySQL Server

More systematic point of see Guo always wrote: http://backend.blog.163.com/blog/static/202294126201371651930522/

http://backend.blog.163.com/blog/static/20229412620137195534755/

Reason analysis of MySQL Slave triggering Oom-killer