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

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.

 

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