MySQL replication is a great feature: it's easy to setup and
relatively easy to manage, it can provide scalabilty and
availability, a master can serve dozens of slaves, etc. However,
sometimes people have the problem that the slaves can't keep up;
they get further and further behind, which is a major problem. So
this post talks about why that happens (sometimes) and what you
can do about it.
Causes:
- The most common reason that a slave cannot keep up with the master is the SQL Thread. There are 2 threads on the slave that handle replication: the IO Thread which connects to the master and pulls down the binary log events, and the SQL Thread which executes these events on the slave. But the master might have dozens of concurrent threads executing inserts/updates/deletes. So when they get to the slave, they are run in a single thread which may not be able to do the same throughput. There's a good reason for the …