Fix Mysql “128 rollback segment(s) are active” and “Waiting for purge to start”

The main problem is not the fact that you are receiving the error when issuing service mysql status, but the fact that the database is stuck and responding very slowly.

You can get the specified error after updating Mysql server to 10.0.x and this happens due to innodb storage formats. Probably the previous Mysql server version used Barracuda file format and after the update you use the Antelope storage format.

What you want to do is change the format to Barracuda and make this setting global.

The first thing to do is to backup all your databases !!

Edit your Mysql configuration file (/etc/mysql/my.cnf or other- depending on distro) and add:

innodb-rollback-segments=256
innodb_file_format=Barracuda

Save and restart mysql process.

The login to the Mysql server and issue :

mysql -u admin -p`cat /etc/psa/.psa.shadow`         //for Plesk

mysql -u admin -p         // without Plesk

show variables like “%innodb_file%”;

If you see Antelope, then do this:

SET GLOBAL innodb_file_per_table=1;

SET GLOBAL innodb_file_format=Barracuda;

GRANT ALL PRIVILEGES ON yourdb.* TO ‘youruser’@’localhost’;

FLUSH PRIVILEGES;

Restart mysql (and do a reboot?).

In the end you should have something like this:

If this doesn’t work, you might want to try THIS first and restart the process described above.