mysql 5.7.16 memory consumption problem

04-20-2017, 09:18 AM

Hi,
I am stuck between lot of configurations and trying to understand and overcome memory consumption issue but till now it is difficult to find a root cause. MySQL server version 5.7.16 is running on Azure VM with 112GB Ram and 16 cores processors. Enough Harddisk (ssd) is attached so nothing to concern about storage as particularly it is a problem relevant to memory / wrong configuration which is causing high consumption and memory leakage. I believe that issue can be resolved and it is not a bug but till now not succeded to find where is the root cause.
I need you people to help me out from that issue.
Basically issue happens when mysql service start and it is consuming memory faster.
My OS is CentOS 6.6 (64-bit) and mysql version is 5.7.16 specs already mentioned in above paragraph. Total DBs (user based - > 11) in which 4 dbs are specifically assigned for blob based objects and the other remaining 7 db which are mounted on server are innodb based.
Facing a problem with several auto restarts (OOM Killer) because ram goes full and service auto restart. Also tried to fix this with setting of ranking with -530 value but still facing an auto restart proble. I also tried to put manual parameters and manually stop and start service but it is something bad going on. I am attaching current snapshot of server top command and my.inf file for reference. If you guys were face that kind of problem than please help out.

I note that your innodb_buffer_pool_size=30GB on 112GB RAM, yet mysqld is using +66GB so you have considerable consumption outside of the Buffer Pool. One suggestion I have is to consider deploying Percona Monitoring and Management (PMM) as we have a graph designed specifically for display of the MySQL Internal Memory Overview. You could deploy PMM via RPM on your server, and run VirtualBox instance on your laptop, in order to perform this initial investigation - no need to set up servers (yet, unless you really like the tool!). See installation section of our Documentation.

You might also consider:

Add swap partition to your server. You have zero right now, and therefore you allow no space for mysqld to overflow RAM to disk, which is likely causing you to OOM when memory pressure gets too high.

You can look at configuring the OOM killer, but frankly your server is a single-service machine (good) so mysqld will always be the process chosen for kill. You might only be able to tune OOM to delay killing mysqld a little while longer.