5.5.25a server very slow in replication

I have setup MySQL replication with Percona 5.5.25a server as a slave for MySQL 5.0.95 master. Slave is very slow in catching up with master. It is lagging behind master for around 2 days now. Both the master and slave runs on VM nodes. Physical configuration of both the nodes are same. MySQL configuration is also same on both.

When I change the slave to 5.0.95, slave immediately catches up and never lags behind master.

I am using Innodb as the engine. Innodb status on the slave showed that there were locks on a table during replication. Every time an update runs on this table, the locks were held for around 15 to 20 mins. I have ignored this table in replication. After this, there are no locks observed in the slave. But the QPS on slave is only 4. With this rate, the slave is never to going to catch up with the master.

Any suggestions on how to resolve this will be much appreciated.

Hi,

I’m bit confused here as innodb should not lock the tables unless you are running ALTER TABLE or LOCK TABLE. so whatever you are seeing on slave should be something else. Can you provide “SHOW ENGINE INNODB STATUS” and “SHOW PROCESSLIST” of slave? also update statement and table structure if possible.

I’m assuming that all tables are InnoDB in your master-slave servers. It would be also helpful if you can provide my.cnf configurations and global variables of both the servers. I would also like to suggest you to check/upgrade latest Percona server version(PS 5.5.28-29.2) and try to regenerate above issue.

Thanks Niljoshi for your response.

I stopped the slave and just ran the update query on the slave that was causing issues. This query has been running for more than an hour now. There is no other query running on the DB now. I have attached all the details from the slave node - innodb status, process list, table details, my.cnf, details from innotop.

I am not sure whether I need to change any mysql configuration.

Thanks for all your help.

Regards,
Vijay