Xaprb

Very fast, as it turns out.

While writing the chapter on replication for the upcoming second edition of High Performance MySQL, I decided to do a little test and measure replication speed more accurately than I’ve seen others do before. The first edition of the book measured replication speed by inserting on the master and polling on the slave. Giuseppe Maxia later followed up on that by improving the polling process, and found events typically replicated within a half a millisecond.

Polling can only get you so far; the extra overhead caused by polling skews the measurements (even if you poll smartly). I wanted to see if I could do this without polling the slave for results. It turned out to be easier than I thought it would be.

All I had to do was write a MySQL User-Defined Function that returns the system time to microsecond precision. I’ll write another post about that later; in this post I want to talk about the results.

The setup

After writing and installing the function, I tested it. Note that it’s non-deterministic, so you get different results even when you call it twice in the same query:

SELECT NOW_USEC(), NOW_USEC(); 
+----------------------------+----------------------------+ 
| NOW_USEC()                 | NOW_USEC()                 | 
+----------------------------+----------------------------+ 
| 2007-10-23 10:41:10.743917 | 2007-10-23 10:41:10.743934 | 
+----------------------------+----------------------------+ 

The rest is easy. I set up two MySQL instances on the same server (because there’s no way the clocks on two separate machines will be synced to the microsecond), and made one of them the master of the other. On the master,

CREATE TABLE test.lag_test( 
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   now_usec VARCHAR(26) NOT NULL 
 ); 

INSERT INTO test.lag_test(now_usec) VALUES( NOW_USEC() ); 

The results

Now all that remains is to compare the difference from the slave and the master. A Federated table is an easy way to do this. On the slave:

CREATE TABLE test.master_val ( 
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   now_usec VARCHAR(26) NOT NULL 
) ENGINE=FEDERATED 
   CONNECTION='mysql://user:pass@127.0.0.1/test/lag_test'; 

A simple join and the TIMESTAMPDIFF function can now show the microseconds of lag between the time the query executed on the master and the slave:

SELECT m.id, TIMESTAMPDIFF(FRAC_SECOND, m.now_usec, s.now_usec) AS usec_lag
FROM test.lag_test as s
   INNER JOIN test.master_val AS m USING(id); 
+----+----------+ 
| id | usec_lag | 
+----+----------+ 
|  1 |      476 | 
+----+----------+ 

I inserted a thousand rows into the master with a simple Perl script, with a 10-millisecond delay between rows to keep help the master and slave instances from fighting each other for CPU time. I then built a temporary table with the lag of each event, and grouped the results by lag time, to see what the most frequent lag times are:

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 | 
+----------+----------+

Not all that bad, eh? It looks to me like MySQL can replicate most small queries in 200 to 300 microseconds(!!!). Of course, the speed is bounded by a) how long it takes to transfer the binary log event across the network and b) how fast the query executes. In this case, both are very fast, showing that MySQL doesn’t add much overhead of its own to the replication process.

If anyone knows of a way to measure the delay between the event being logged in the master’s binary log, and the event being logged in the slave’s relay log, I’d be interested in seeing the results. I’m guessing it’s practically instantaneous for small events like this, and most of the lag is in reading, parsing, and executing the SQL.

You might also like:

1. How I built the NOW_USEC() UDF for MySQL
2. How to sync tables in master-master MySQL replication
3. MySQL Table Checksum 1.1.0 released
4. How to know if a MySQL slave is identical to its master
5. Why MySQL says the server is not configured as a slave

Continuing in the tradition, which I hope has been as helpful to you as it has been to me, I’m opening the floor for suggestions on chapter 9 of the upcoming High Performance MySQL, Second Edition. Unlike the other chapters for which I’ve listed outlines, this one isn’t substantially written yet. It’s in detailed outline form at this point (a tactic that has worked very well for us so far — I’ll write about that someday).

I’m trying to get feedback much earlier in this chapter’s lifecycle, for several reasons. Two of the most important are that this is one of the first chapters I’ve had a chance to really take from scratch, and the chapters I haven’t written from scratch have been harder to organize, as you’ve probably seen from the last few outlines I posted. There’s a lot of value in working top-down on this deep encyclopedia-style material.

The outline, as it stands now, is basically headings with bulleted lists of important details. Here are the top-level headings:

[Intro]
Scaling and High Availability Requirements
Replication Overview
Configuring Replication
Under the Hood of Replication
Replication Topologies
Replication Administration and Maintenance
Replication Problems and Solutions
The Future of MySQL Replication
Scaling MySQL Horizontally
Clustering with MySQL
   MySQL Cluster
   Other Clustering Solutions
Load Balancing

Just a few notes. These sections are top-level, and will likely be split into many sub-sections like other chapter outlines I’ve posted. A typical section has a couple dozen bullet-points in it, at a high level of granularity, such as “Using DRBD for log replication only.” I think we’ll also add in a separate section on fail-over and fail-back, but that’s not in the outline as of right now (what do you think belongs in it?).

I don’t know what it’s like for you to read outlines and see little bits of the book being assembled, but the process of writing this book is just fascinating to me. It’s endlessly interesting and educational — just the process of writing, let alone the subject matter! This is a really fun project. A heck of a lot of work, but fun nonetheless, and the openness of the project makes it even more fun for me. I’ve learned a lot of surprising and interesting things about writing. I keep wishing I had time to write about this process, but I really need to keep my eye on the deadlines and put that off for later.

Anyway, the usual requests apply: what’s missing, what do you think is cool and should be included, etc etc? Thanks, as usual, for your time and feedback.

You might also like:

1. More progress on High Performance MySQL, Second Edition
2. What are your favorite MySQL replication filtering rules?
3. Progress report on High Performance MySQL, Second Edition
4. High Performance MySQL, Second Edition: Backup and Recovery
5. Progress on High Performance MySQL, Second Edition