The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Long queries: SELECT data_free FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1

Discussion in 'Database Discussions' started by postcd, Jun 2, 2014.

  1. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    620
    Likes Received:
    6
    Trophy Points:
    18
    On Show MySQL Processes page in WHM i see:

    "Opening tables" queries that are running somewhere between 300-3000 seconds, there are 12 of them..
    SELECT data_free FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1

    any idea please?
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    This option is showing you the output from:

    Code:
    mysqladmin processlist
    You may need to review your MySQL configuration or the MySQL scripts to determine why those processes are not ending as quickly as you would prefer them to. This would be more of an issue with your MySQL configuration as opposed to an issue with the cPanel/WHM software.

    Thank you.
     
  3. wonker

    wonker Active Member

    Joined:
    Dec 5, 2007
    Messages:
    29
    Likes Received:
    2
    Trophy Points:
    3
    Hello,

    Sorry for reopening a year old thread, we've just had this issue and this thread was the first result on Google so I thought I would share how I fixed it to help others.

    This is not a mysql configuration issue, but an issue with the default Munin configuration that comes with cPanel's Munin plugin.

    Our symptoms were that mysql was fast after a restart and became slower every hour that passed after the restart. The CPU usage, disk io wait, memory usage were low but the number of mysql threads kept increasing until mysql came to a halt.

    MySQL is supposed to be slow after a restart and become faster once the cache is warm. We had the opposite, mysql was fast after a restart then became slow over time. We couldn't find any reason why this would happen until we noticed multiple instances of the following query in mysql's processlist :

    Code:
    SELECT data_free FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1
    Our servers have about 30GB of small databases and the same amount of innodb cache. During backups, the server didn't manage to run these queries within munin's check interval so processes started overlapping and mysql became slower every new process that was added.

    If you have the following in /etc/my.cnf (you should have it if you don't).

    Code:
    innodb_file_per_table=1
    When this is set the innodb free space check isn't so important and you can safely disable munin's innodb plugin :

    Code:
    rm /etc/munin/plugins/mysql_innodb
    service munin-node restart
    /etc/munin/plugins/mysql_innodb is a symlink, not the actual plugin so it can easily be recreated later if you need it.

    Doing this has made all websites much faster, reduced the backup interval and so far has stopped mysql from crashing.
     
    stevedaily and postcd like this.
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  5. wonker

    wonker Active Member

    Joined:
    Dec 5, 2007
    Messages:
    29
    Likes Received:
    2
    Trophy Points:
    3
    Hello,

    Just a quick note for those trying this, the symlink will be automaticly created every time munin is updated.

    While not supported by cPanel, they suggested as a work around to add the following to /scripts/postupcp :
    Code:
    if [-f'/etc/munin/plugins/mysql_innodb']; then
    
            /bin/rm -f /etc/munin/plugins/mysql_innodb
            /sbin/service munin-node restart
    fi
     
Loading...
Similar Threads - Long queries SELECT
  1. mahdy_sharifi
    Replies:
    5
    Views:
    571

Share This Page