Databases Reference
In-Depth Information
Replication Limitations
MySQL replication can fail or get out of sync, with or without errors, just because of
its inherent limitations. A fairly large list of SQL functions and programming practices
simply won't replicate reliably (we've mentioned many of them in this chapter). It's
hard to ensure that none of these finds a way into your production code, especially if
your application or team is large.
22
Another issue is bugs in the server. We don't want to sound negative, but many major
versions of the MySQL server have historically had bugs in replication, especially in the
first releases of the major version. New features, such as stored procedures, have usually
caused more problems.
For most users, this is not a reason to avoid new features. It's just a reason to test
carefully, especially when you upgrade your application or MySQL. Monitoring is also
important; you need to know when something causes a problem.
MySQL replication is complicated, and the more complicated your application is, the
more careful you need to be. However, if you learn how to work with it, it works quite
well.
How Fast Is Replication?
A common question about replication is “How fast is it?” The short answer is that it
runs as quickly as MySQL can copy the events from the master and replay them, with
very little overhead. If you have a slow network and very large binary log events, the
delay between binary logging and execution on the replica might be perceptible. If your
queries take a long time to run and you have a fast network, you can generally expect
the query time on the replica to contribute more to the time it takes to replicate an event.
A more complete answer requires measuring every step of the process and deciding
which steps will take the most time in your application. Some readers might care only
that there's usually very little delay between logging events on the master and copying
them to the replica's relay log. For those who would like more details, we did a quick
experiment.
We elaborated on the process described in the first edition of this topic, and methods
used by Giuseppe Maxia,
23
to measure replication speed with high precision. We built
a nondeterministic UDF that returns the system time to microsecond precision (see
“User-Defined Functions” on page 295
for the source code):
22. Alas, MySQL doesn't have a
forbid_operations_unsafe_for_replication
option. In recent versions,
however, it does warn pretty vigorously about some unsafe things, and even refuses certain ones.
