Replication - High Performance MySQL - page 507

The problem with this method is that you need an external way to generate key values,
because AUTO_INCREMENT can't do it for you. Don't use @@server_id in place of the con-
stant value 15 in the INSERT , because you'll get a different result on the replica.
You can also turn to pseudorandom values using a function such as MD5() or UUID() ,
but these can be bad for performance—they're big, and they're essentially random,
which is bad for InnoDB in particular. (Don't use UUID() unless you generate the values
in the application, because UUID() doesn't replicate correctly with statement-based
replication.)
It's a hard problem to solve, and we usually recommend redesigning your application
so that you have only one writable master instead. Who'd have guessed it?
Excessive Replication Lag
Replication lag is a frequent problem. No matter what, it's a good idea to design your
applications to tolerate some lag on the replicas. If the system can't function with lag-
ging replicas, replication might not be the correct architecture for your application.
However, there are some steps you can take to help replicas keep up with the master.
The single-threaded nature of MySQL replication means it's relatively inefficient on
the replica. Even a fast replica with lots of disks, CPUs, and memory can easily fall
behind a master, because the replica's single thread usually uses only one CPU and disk
efficiently. In fact, each replica typically needs to be at least as powerful as the master.
Locking on the replicas is also a problem. Other queries running on a replica might
acquire locks that block the replication thread. Because replication is single-threaded,
the replication thread won't be able to do other work while it waits.
Replication tends to fall behind in two ways: spikes of lag followed by catching up, or
staying steadily behind. The former pattern is usually caused by single queries that run
for a long time, but the latter can crop up even when there are no long queries.
Unfortunately, at present it's not as easy as we'd like to find out whether a replica is
close to its capacity, as discussed earlier in this chapter. If your load were perfectly
uniform at all times, your replicas would perform nearly as well at 99% capacity as at
10% capacity and when they reached 100% capacity they'd abruptly begin to fall be-
hind. In reality, the load is unlikely to be steady, so when a replica is close to its write
capacity you'll probably see increased replication lag during times of peak load.
Logging queries on a replica and using a log analysis tool to see what's really slow is
one of the best things to do when replicas can't keep up. Don't rely on your instincts
about what's slow, and don't base your opinion on how queries perform on the master,
because replicas and masters have very different performance profiles. The best way to
do this analysis is to enable the slow query log on a replica for a while, and then analyze
it with pt-query-digest as discussed in Chapter 3 . The standard MySQL slow query log
can log queries the replication thread executes in MySQL 5.1 and newer, if you enable