mysql slave database problem

Question

Currently we have 3 slave databases,

but almost always there is one of them extremly slow than others(can be an hour after master database)

Has any one met similar problem?What may be the cause?

Answer 1

I'd guess some other process is running on the same host as the slow slave database, and it's hogging resources.

Try running "top" (or use Nagios or Cactus or something) to monitor system performance on the three slave database hosts and see if there are any trends you can observe. CPU utilization pegged by another process besides mysqld, or I/O constantly saturated, that sort of thing.

---

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

• Managing Slave Lag with MySQL Replication
• Fighting MySQL Replication Lag

The author points out that the replication slave is single-threaded, and the slave executes queries sequentially, instead of in parallel as they were executed on the master. So if you have a few replicated queries that are very long-running, they can "hold up the queue."

He suggests the remedy is to simplify long-running SQL queries so they run more quickly. For example:

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

• If you have complex SELECT statements incorporated into your UPDATE or INSERT queries, separate the SELECT into its own statement, generate a set of literal values in application code, and then run your UPDATE or INSERT on these. Of course the SELECT won't be replicated, the slave will only see the UPDATE/INSERT with literal values.

• If you have a long-running batch job running, it could be blocking other updates from executing on the slave. You can put some sleeps into the batch job, or even write the batch job to check the replication lag at intervals and sleep if needed.

Answer 2

Are all the slave servers located in the same location? In my case, one of the slave server was located in another location and it was a network issue.