Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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

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

  1. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    694
    Likes Received:
    15
    Trophy Points:
    68
    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?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,802
    Likes Received:
    1,895
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. wonker

    wonker Active Member

    Joined:
    Dec 5, 2007
    Messages:
    29
    Likes Received:
    2
    Trophy Points:
    53
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,802
    Likes Received:
    1,895
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    I am happy to see you were able to find a solution to the issue you were facing. Thank you for updating us with the outcome.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. wonker

    wonker Active Member

    Joined:
    Dec 5, 2007
    Messages:
    29
    Likes Received:
    2
    Trophy Points:
    53
    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...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice