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

[postcd]

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?

 

[cPanelMichael]

Hello

This option is showing you the output from:

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.

 

[wonker]

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 :

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).

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 :

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.

 

[cPanelMichael]

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.

 

[wonker]

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 :

if [-f'/etc/munin/plugins/mysql_innodb']; then

        /bin/rm -f /etc/munin/plugins/mysql_innodb
        /sbin/service munin-node restart
fi