Databases Reference
In-Depth Information
mysql>
SELECT ROUND(lag / 1000000.0, 4) * 1000 AS msec_lag, COUNT(*)
->
FROM lag
->
GROUP BY msec_lag
->
ORDER BY msec_lag;
+----------+----------+
| msec_lag | COUNT(*) |
+----------+----------+
| 0.1000 | 392 |
| 0.2000 | 468 |
| 0.3000 | 75 |
| 0.4000 | 32 |
| 0.5000 | 15 |
| 0.6000 | 9 |
| 0.7000 | 2 |
| 1.3000 | 2 |
| 1.4000 | 1 |
| 1.8000 | 1 |
| 4.6000 | 1 |
| 6.6000 | 1 |
| 24.3000 | 1 |
+----------+----------+
The results show that most small queries take less than 0.3 milliseconds to replicate,
from execution time on the master to execution time on the replica.
The part of replication this
doesn't
measure is how soon an event arrives at the replica
after being logged to the binary log on the master. It would be nice to know this, because
the sooner the replica receives the log event, the better. If the replica has received the
event, it can provide a copy if the master crashes.
Although our measurements don't show exactly how long this part of the process takes,
in theory it should be extremely fast (i.e., bounded only by the network speed). The
MySQL binlog dump process does
not
poll the master for events, which would be
inefficient and slow. Instead, the master notifies the replica of events. Reading a binary
log event from the master is a blocking network call that begins sending data practically
instantaneously after the master logs the event. Thus, it's probably safe to say the event
will reach the replica as quickly as the replication thread can wake up and the network
can transfer the data.
Advanced Features in MySQL Replication
Oracle released significant enhancements to replication in MySQL 5.5, and many more
are in development milestone releases, to be included in MySQL 5.6. Some of these
make replication more robust, others add multithreaded (parallel) replication apply to
alleviate the current single-threaded bottleneck, and still others add advanced features
for more flexibility and control. We won't speculate much on functionality that isn't
in a GA release, but there are a few things we want to mention about MySQL 5.5's
enhancements.
