Memory drain issues on MySQL 5.6 ?

by | Jul 24, 2014 | cPanel/WHM, MySQL | 22 comments

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.

 

22 Comments

  1. Robert
    Robert on October 16, 2018 at 3:19 am

    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.

    Reply
    • Joel Abraham
      Joel Abraham on October 16, 2018 at 5:21 am

      Thank you Robert for the heads up !

      Reply
  2. Robert
    Robert on November 15, 2014 at 10:01 am

    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.

    Reply
    • Joel Abraham
      Joel Abraham on November 15, 2014 at 7:10 pm

      Thank you Robert for the heads up !

      Reply
  3. Style
    Style on October 16, 2018 at 3:19 am

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

    Reply
    • joelta
      joelta on October 16, 2018 at 5:21 am

      Hey,

      Yes, this really was a headache !

      Reply
  4. Style
    Style on April 6, 2015 at 8:51 pm

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

    Reply
    • joelta
      joelta on April 7, 2015 at 5:44 pm

      Hey,

      Yes, this really was a headache !

      Reply
  5. Renato Doe
    Renato Doe on October 16, 2018 at 3:19 am

    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!

    Reply
  6. Renato Doe
    Renato Doe on June 8, 2015 at 3:04 pm

    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!

    Reply
  7. danket
    danket on October 16, 2018 at 3:19 am

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

    Reply
    • joelta
      joelta on October 16, 2018 at 3:19 am

      (y) cool!

      Reply
  8. danket
    danket on July 19, 2015 at 3:17 am

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

    Reply
  9. macadanet
    macadanet on October 16, 2018 at 3:19 am

    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?

    Reply
  10. macadanet
    macadanet on May 19, 2016 at 5:50 pm

    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?

    Reply

Submit a Comment