How fast is MySQL replication?
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.
Hi Baron,
You could actually find that replicating across the wire but adding events as quickly as you can might show lower lags… I’d love to see the results. Your sleep of 10ms between events will give the slave thread on the master enough time to go to sleep and wait to be woken up instead of just moving on to the next event.
As for how you can find out the “real” latency, you could try using profiling to average the time taken to get to various steps in the process for the queries you’re inserting, and subtract that time from the observed time to replicate…
Regards,
Jeremy
Jeremy Cole
24 Oct 07 at 2:23 am
If you don’t mind using strace and seeing what is going on underneat the -tt option some filtering with -e should give you this information.
$ strace -e ‘socket,connect,accept,open,read,write,close’ \
-ff -tt -s 512 -o replication-master.txt -p
-ff for one file per thread. Doing the same on the slave and matching the timestamps when they hit the relay logs … minus some strace overhead.
Jan Kneschke
24 Oct 07 at 5:00 am
Baron,
I think it would be very interesting to organize this test as a UDF plus test program so one can run it in their live environment and plot graph like this, Ie have it sending “ping” query once per second for 24 hours and measure results.
Of course you need clocks between servers to be well synchronized for it to work good enough.
It would be nice than to plot a graph for avg/95%/max replication lag averaged say by each minute.
I would expect graphs to well show how things change over time with site load as well with some events like batch jobs, nightly backup etc.
Peter Zaitsev
24 Oct 07 at 5:58 am
Jeremy,
I tried it that way to begin with and it didn’t work out. I am running on a single-CPU system and my guess is the context switching added a lot of latency.
Xaprb
24 Oct 07 at 9:07 am
Jan,
I’ve never used strace but I could give it a try!
Xaprb
24 Oct 07 at 9:08 am
Peter,
I was thinking about extending the MySQL Heartbeat script in the MySQL Toolkit to add something along these lines. I also thought about the clock sync issue. One idea for roughly equalizing this would be to use Federated tables again. A Federated table on the slave could point at a view on the master, which just returns NOW_USEC(). That would give a rough idea of the difference between the clocks. Doing the same thing symmetrically on the master, then comparing the difference between the two measurements, should allow me to calculate the clock difference between the servers, if I’m thinking about this correctly.
I also thought about a view on the slave that returns NOW_USEC(), which I would access on the slave by means of a Federated table on the master that in turn is a Federated table pointing at the slave’s view.
Right now I’m not thinking very clearly so I’m not sure what makes sense in these suggestions.
Xaprb
24 Oct 07 at 9:12 am
Hi Baron,
I would expect bad results doing that on one machine, that’s why I said “over the wire”. Nonetheless, running any of these tests on a single CPU machine should be avoided.
Regards,
Jeremy
Jeremy Cole
24 Oct 07 at 1:18 pm
For the clock sync issue, have you considered using ntpd to sync time?
Dimitriy A
24 Oct 07 at 9:55 pm
Sure. But in my experience, even within the same subnet most servers are at least a few milliseconds out of sync with each other, and we’re already dealing in wee tenths of a millisecond. Is there a way to get NTP to sync more tightly?
Xaprb
24 Oct 07 at 10:15 pm
Baron,
How about syncing one against the other? On a local LAN you should be able to get them *really* close.
Jeremy Cole
24 Oct 07 at 10:37 pm
I’m pretty sure that’s what we’re doing at work (but I’ve forgotten) and I thought they were further apart than that. I’ll have to check. Maybe this will be easier than I thought. (The whole Federated view of Federated view idea wasn’t appealing much to me anyway).
Xaprb
24 Oct 07 at 10:57 pm
Hi Baron,
With two machines on the same subnet, you should be able to get them in sync with each other by way less than a millisecond. They won’t be that accurate to *reality* but syncing them to each other should be pretty damn close. The latency on the same switch will be ~0.1ms, and you should be able to get them in sync by at very least that.
Jeremy Cole
24 Oct 07 at 11:08 pm
[...] other MySQL news, Xaprb asks, how fast is MySQL replication? investigates, and provides answers: “Very fast, as it turns out… While writing the [...]
Pythian Group Blog » Blog Archive » Log Buffer #68: a Carnival of the Vanities for DBAs
26 Oct 07 at 2:19 pm
[...] Archives « How fast is MySQL replication? [...]
How I built the NOW_USEC() UDF for MySQL at Xaprb
30 Oct 07 at 8:58 am
I can’t seem to work out how anyone can rely on mysql replication or how they get it to work fast enough for applications.
In alot of web applications (from what I have found), a user may enter an html form which gets inserted into the master, then they may, for example, get redirected to there account page which will be getting selected from the slave… occasionally the slave hasn’t caught up from the data that just got inserted into master and you are trying to select that same data from the slave. It’s just not fast enough for me. :(
How can this problem be resolved?
Mark
18 Mar 08 at 7:41 am
This is a great question. As you know, replication is asynchronous, so a transaction on the master doesn’t have to wait on the slave. There are a variety of solutions to the resulting timing problems, including object versioning, read-write splitting based on whether your session has changed any data, timestamps, hierarchies, and many more. There are also patches to make replication semi-synchronous, but most people don’t need that.
The upcoming High Performance MySQL 2nd edition has probably the clearest and most comprehensive discussion of these strategies in print. (Shameless plug).
To give you an idea how some of these things can work, if you write to the master you can simply mark the session as having written something. Reads will then go to the master for some time thereafter. This is a simple approach that’s very effective. If reading from the master is unacceptable, you can use more complex approaches.
Xaprb
18 Mar 08 at 8:08 am
Oh — when I say reads will go to the master, I mean THAT SESSION’S reads, not all reads.
Xaprb
18 Mar 08 at 8:09 am
So basically, to make sure your data that you are selecting – is the data that just got inserted into the master, you have to select directly from the master?
Or is there some way to quickly tell the slave to get the masters binary log, execute the insert/update statement (that just occurred form the user), then select from the slave, all on the same spot? Is there are feature for that?
It would be great if you could give me some links to some articles about this. I have only just set up my first replication and trying to tie it into a php app.
By the way, I am using MySQL 5.0 do you think it’s a safe option to upgrade to the 5.1 beta to try the new row based replication. What is your thoughts on row based replication?
Cheers =)
Mark
18 Mar 08 at 4:36 pm
Mark,
You can do SHOW MASTER STATUS on the master, and then MASTER_POS_WAIT() on the slave to create a synchronization barrier, but there’s no way to force the slave to fetch the statement that just happened on the master and replay it. What you’re looking for doesn’t exist :-) You have to kind of assemble the pieces yourself.
If your app is running in production on 5.0, I would not upgrade to 5.1 until you are more familiar with 5.0. If you have not yet put the app into production, by all means upgrade and see how it goes. I don’t have a lot of experience with it yet myself.
Xaprb
18 Mar 08 at 4:59 pm
Thanks for the info and great responses. It sounds like you know a lot about MySQL replication.
Regarding the slave not having caught up to it’s master – and wanting to select from the slave…
Do you think it an okay idea to make a database link to the master – update the master close the connection, make a connection to the slave and also update the slave (from which you are just about to select from) (with the same data that just got inserted into the master) and then select from the slave.
Sounds messy and you have to make sure you are updating and selecting from the same slave in order to make use of it. Or would that be a bad idea?
Thanks again :)
Mark
18 Mar 08 at 5:37 pm
In most cases it would be a bad idea, because you might change something that would alter the results of queries the slave will replay:
update account set balance = balance + 5 where id = 10;
I think you ultimately have to build your application to tolerate slave lag. The more robust you make that, the better off you’ll be.
If you’re having trouble making this work right, a few hours of consulting help might be a good investment. Check out the companies I listed in this post.
Xaprb
19 Mar 08 at 9:29 am
Hi Baron,
I’m new to MySQL, so forgive me if I show some blatant lack of understanding here, but I have been replicating databases and using heart beat tables to calculate latency for many years. Typically, I’ll have a job periodically insert or update a row on the source/master with an ID and the source’s current time. When this hits the slave/target, the target current time is stored in another column. The delta is the lag.
Thanks for a very useful website!
Cheers,
-joe
Joe D
1 Sep 09 at 7:18 pm
Hi Joe,
That works on many database servers, but not MySQL, because the current timestamp at the time of execution is stored in the binary log, and functions such as NOW() will thus return the same result on the slave as they do on the master. See also mk-heartbeat from Maatkit.
Xaprb
1 Sep 09 at 7:25 pm
Hi again Baron.
I was suspicious I was missing something basic here. Well, that’s quite interesting and thanks for the quick reply!
-joe
PS You’re book should be here in two more days!
Joe D
1 Sep 09 at 7:44 pm