Recently, I often receive alarm messages similar to the lack of memory in the mysql instance.Log in to the server and see that mysql eats 99%of the memory, god!
Sometimes it is not handled in time,The kernel will help us restart mysql, 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 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)
Triggering scenario:The slave will experience a periodic spike in memory regardless of whether other links come in.Trigger kernel oom-killer
It is said that this problem has been appearing for more than a year. Since I just came here,The boss asked me to check again for any clues.Then start checking this question slightly:
1. I suspect that the memory allocated to mysql is unreasonable,Then I went to check the size of innodb_buffer_pool and the size of physical memory.It is found that the size allocated to bp accounts for about 60%of the physical memory, then this is not the reason, Excluded, if this problem, they should have found out long ago ~
2. Check the parameters of the operating system.[vm.swappiness=1;/proc/sys/vm/overcommit_memory;oom_adj] You can temporarily set the adj parameter to a -15 or directly -17 before troubleshooting the problem, so that the kernel will never kill mysql, But this does not solve the problem at all, And there are certain risks, Will it cause mysql to need memory and fail to allocate it and hang?Think about it this way.
3. Well, the mysql initialization parameters and operating system parameters do not seem to be configured incorrectly.Then let's look for mysql itself!
Since mysql memory has been soaring,So, is it caused by the time of memory allocation?Then according to a bug reported online by mysql memory allocation, I also came to operate one in my environment.Take a look at it:1. Record the amount of memory occupied 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 these two results,Mainly to see if there is a significant change in the memory allocated by mysql before the flush table and after the flush table. Well, this bug looks like it's not here anymore.
Take a look at this version. There is an innodb_buffer_pool_instances parameter. There are also innodb_buffer_pool_instances and innodb_buffer_pool_size settings on the official website, which cause a bug in mysql oom. It probably means that we can set the innodb_buffer_pool_size to be larger than our actual physical memory.For example, our physical memory is:64gb, and we set innodb_buffer_pool_size=300gb, and set innodb_buffer_pool_instances>5, we can still pull mysql up. But, this way mysql is easy to oom. Details:Look here.
There is another situation,Also reported a bug, that is, when the slave sets filtering,Will also trigger oom, but these instances of me are not set, So just ignore that.
Since it is not caused by mysql memory oversold,Nor is it caused by the handle to open the table.So why else?
Let's think again,This phenomenon occurs in the slave. The master and slave configurations are the same. It ’s just that the master ran the production business,Some instances on the slave ran the query business,Some instances don't run any tasks at all,But it will still start oom, then this situation is likely to be caused by slave.
Then I found an example and tried it. Do n’t try and do n’t know, Give it a try.Go up and execute it:stop slave;start slave;this command stuck for about 3 minutes, and then look at the memory usage,All of a sudden released 20gb +. This is basically the problem.But slave we all know there are two threads,Is it caused by sql thread or io thread?This is awaiting further investigation the next time it is about to happen.
Monitoring information of the tag 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 a little more specific here:if you can't access, you can access (/go.php?id=88729&s=a)
Finally, a little summary:
Temporary solution:restart the slave
Long-term solution:minor version upgrade mysql server
For a more systematic look, please see what General Guo wrote:
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- android studio - unresolved reference comes out in kotlin
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- python - importerror: cannot import name md5 error cannot be resolved