Memory drain issues on MySQL 5.6 ?

With the recent upgrade of WHM/cPanel, the users get the ability to upgrade their MySQL server to 5.6.x ( x > 6 ).

However, when this upgrade is done, lot of server owners are seeing memory drainage issues. An idle MySQL server tends to consume around 50% of your RAM, which is a very serious concern.  I had to upgrade a personal server of mine and faced serious issues with memory drainage. A more dig on this issue, highlighted a change brought around in the latest versions of MySQL with the parameter ‘performance_schema‘.

Starting from MySQL 5.6.6, this parameter performance_schema is enabled by default and it consumes the server memory even at an idle state. Performance Schema automatically sizes the values of several of its parameters at server start-up if they are not set explicitly, which causes the memory usage to spike up.

The workaround for this issue is to disable performance_schema. This can be done by adding the following value to the configuration file – my.cnf

performance_schema = 0

Add this line and restart MySQL server. Things should be fine from now 🙂

Note : When you try to upgrade MySQL to 5.6.x, from a VPS with 1GB of RAM provisioned you will need to edit the config file and pass the keyword to disable performance_schema ( Yes, before the upgrade ). If not, there are chances for your upgrade to fail partly, due to MySQL upgrade script installing MySQL server components each and it gets killed due to over-usage of RAM as performance_schema is enabled by default.

 

  • Robert

    If you do not want to disable performance_schema completely, you can adjust the settings of table_definition_cache to 400 and table_open_cache to 2000 or lower. This will cause performance_schema to use much less memory (in my case only 10% compared to table_definition_cache=1400)
    You have to restart the server to see the changes in memory consumption.

  • Style

    Wow thank you very much!!!! I was on 85% now I’m at 35% of Memory Used. This upgrade was a headache :/

    • Hey,

      Yes, this really was a headache !

  • Renato Medina

    Thanks mate. I have an EC2 small instance and it was the solution for my lack of memory since I’ve updated to MySQL 5.6. Cheers!

  • danket

    Simple solution that made my life way less stressful… Thx a bunch man!

  • macadanet

    Hi all,
    We are desperate because mysql 5.7 in our servers is eating too much RAM and it restarts every 10min because kernel kills mysqld proccess.
    We did everything: performance_schema OFF, variables with lower amounts.
    Server has 32GB RAM in a ubuntu 16.04 64bits – 4.4.0-21-generic.
    Any help?