Mysql database problem

I would assume that some other process is running on the same host as the slow slave and it is holding up resources.

Try running "from above" (or using Nagios or Cactus or whatever) to monitor system performance on the three slave database nodes and see if there are any trends you can observe. CPU utilization is associated with a process other than mysqld, or persistent I / O saturation, something like that.

update: Read the following two articles by MySQL performance expert Peter Zaitsev:

• Managing Slave Latency with MySQL Replication
• Fighting MySQL Replication Replication

The author points out that the replication slave is single-threaded, and the slave is executing requests sequentially rather than in parallel, since they were executed on the master. Therefore, if you have multiple replicated requests that take a very long time to complete, they may "delay the queue".

He suggests fixing this to make lengthy SQL queries easier to run faster. For instance:

• If you have an UPDATE that affects millions of rows, split it into multiple UPDATEs that act on a subset of the rows.

• If you have complex SELECT statements included in your UPDATE or INSERT queries, separate the SELECT into your own statement, generate a set of literal values ​​in your application code, and then run an UPDATE or INSERT. Of course the SELECT will not replicate, the slave will only see UPDATE / INSERT with literal values.

• If you have a long running batch job, this may block other updates from being performed on the slave. You can put some hibernation in a batch job, or even write a batch job to check the replication latency at intervals and sleep when needed.