Databases Reference
In-Depth Information
3% of its capacity. This doesn't mean it won't have occasional spikes of lag—if the
master executes a change that takes 10 minutes to complete, it's likely that the replica
will lag by about the same amount of time while applying the change—but it's a good
indication that the replica will be able to recover from any spikes it experiences.
Plan to Underutilize
Intentionally underutilizing your servers can be a smart and cost-effective way to build
a large application, especially when you use replication. Servers that have spare
capacity can tolerate surges better, have more power to handle slow queries and main-
tenance jobs (such as
OPTIMIZE TABLE
operations), and will be better able to keep up in
replication.
Trying to reduce the replication penalty a little by writing to both nodes in a master-
master topology is typically a false economy. You should usually load the master-master
pair less than 50% with reads, because if you add more load, there won't be enough
capacity if one of the servers fails. If both servers can handle the load by themselves,
you probably won't need to worry much about the single-threaded replication penalty.
Building in excess capacity is also one of the best ways to achieve high availability,
although there are other ways, such as running your application in “degraded” mode
when there's a failure.
Chapter 12
covers this in more detail.
Replication Administration and Maintenance
Setting up replication probably isn't something you'll do constantly, unless you have
many servers. But once it's in place, monitoring and administering your replication
topology will be a regular job, no matter how many servers you have.
You should try to automate this work as much as possible. You might not need to write
your own tools for this purpose, though: in
Chapter 16
, we discuss several productivity
tools for MySQL, many of which have built-in replication monitoring capabilities or
plugins.
Monitoring Replication
Replication increases the complexity of MySQL monitoring. Although replication ac-
tually happens on both the master and the replica, most of the work is done on the
replica, and that is where the most common problems occur. Are all the replicas work-
ing? Has any replica had errors? How far behind is the slowest replica? MySQL provides
most of the information you need to answer these questions, but automating the mon-
itoring process and making replication robust is left up to you.
