August 29, 2014

How does MySQL Replication really work?

While we do have many blog posts on replication on our blog, such as on replication being single-threaded, on semi-synchronous replication or on estimating replication capacity, I don’t think we have one that covers the very basics of how MySQL replication really works on the high level. Or it’s been so long ago I can’t even find it. So, I decided to write one now.

Of course, there are many aspects of MySQL replication, but my main focus will be the logistics – how replication events are written on the master, how they are transferred to the replication slave and then how they are applied there. Note that this is NOT a HOWTO setup replication, but rather a howstuffworks type of thing.

Replication events

I say replication events in this article because I want to avoid discussion about different replication formats. These are covered pretty well in the MySQL manual here. Put simply, the events can be one of two types:

  • Statement based – in which case these are write queries
  • Row based – in this case these are changes to records, sort of row diffs if you will

But other than that, I won’t be going back to differences in replication with different replication formats, mostly because there’s very little that’s different when it comes to transporting the data changes.

On the master

So now let me start with what is happening on the master. For replication to work, first of all master needs to be writing replication events to a special log called binary log. This is usually very lightweight activity (assuming events are not synchronized to disk), because writes are buffered and because they are sequential. The binary log file stores data that replication slave will be reading later.

Whenever a replication slave connects to a master, master creates a new thread for the connection (similar to one that’s used for just about any other server client) and then it does whatever the client – replication slave in this case – asks. Most of that is going to be (a) feeding replication slave with events from the binary log and (b) notifying slave about newly written events to its binary log.

Slaves that are up to date will mostly be reading events that are still cached in OS cache on the master, so there is not going to be any physical disk reads on the master in order to feed binary log events to slave(s). However, when you connect a replication slave that is few hours or even days behind, it will initially start reading binary logs that were written hours or days ago – master may no longer have these cached, so disk reads will occur. If master does not have free IO resources, you may feel a bump at that point.

On the replica

Now let’s see what is happening on the slave. When you start replication, two threads are started on the slave:

1. IO thread

This process called IO thread connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log. That’s all.

Even though there’s only one thread reading binary log from the master and one writing relay log on the slave, very rarely copying of replication events is a slower element of the replication. There could be a network delay, causing a steady delay of few hundred milliseconds, but that’s about it.

If you want to see where IO thread currently is, check the following in “show slave status\G”:

  • Master_Log_File – last file copied from the master (most of the time it would be the same as last binary log written by a master)
  • Read_Master_Log_Pos – binary log from master is copied over to the relay log on the slave up until this position.

And then you can compare it to the output of “show master status\G” from the master.

2. SQL thread

The second process – SQL thread – reads events from a relay log stored locally on the replication slave (the file that was written by IO thread) and then applies them as fast as possible.

This thread is what people often blame for being single-threaded. Going back to “show slave status\G”, you can get the current status of SQL thread from the following variables:

  • Relay_Master_Log_File – binary log from master, that SQL thread is “working on” (in reality it is working on relay log, so it’s just a convenient way to display information)
  • Exec_Master_Log_Pos – which position from master binary log is being executed by SQL thread.

 

Replication lag

Now I want to briefly touch the subject of replication lag in this context. When you are dealing with replication lag, first thing you want to know is which of the two replication threads is behind. Most of the time it will be the SQL thread, still it makes sense to double check. You can do that by comparing the replication status variables mentioned above to the master binary log status from the output of “show master status\G” from the master.

If it happens to be IO thread, which, as I mentioned many times already, is very rare, one thing you may want to try to get that fixed is enabling slave compressed protocol.

Otherwise, if you are sure it is SQL thread, then you want to understand what is the reason and that you can usually observe by vmstat. Monitor server activity over time and see if it is “r” or “b” column that is “scoring” most of the time. If it is “r”, replication is CPU-bound, otherwise – IO. If it is not conclusive, mpstat will give you better visibility by CPU thread.

Note this assumes that there is no other activity happening on the server. If there is some activity, then you may also want to look at diskstats or even do a query review for SQL thread to get a good picture.

If you find that replication is CPU bound, this maybe very helpful.

If it is IO bound, then fixing it may not be as easy (or rather, as cheap). Let me explain. If replication is IO bound, most of the time that means that SQL thread is unable to read fast enough because reads are single threaded. Yes, you got that right – it is reads that are limiting replication performance, not writes. Let me explain this further.

Assume you have a RAID10 with a bunch of disks and write-back cache. Writes, even though they are serialized, will be fast because they are buffered in the controller cache and because internally RAID card can parallelize writes to disks. Hence replication slave with similar hardware can write just as fast as master can.

Now Reads. When your workset does not fit in memory, then the data that is about to get modified is going to have to be read from disk first and this is where it is limited by the single-threaded nature of the replication, because one thread will only ever read from one disk at a time.

That being said, one solution to fix IO-bound replication is to increase the amount of memory so working set fits in memory. Another – get IO device that can do much more IO operations per second even with a single thread – fastest traditional disks can do up to 250 iops, SSDs – in the order of 10,000 iops.

Questions? Comments? Concerns?

About Aurimas Mikalauskas

Aurimas joined Percona in 2006, a few months after Peter and Vadim founded the company. His primary focus is on high performance, but he also specializes in full text search, high availability, content caching techniques and MySQL data recovery.

Comments

  1. Akshay says:

    Hi Aurismas,

    Thanks for writing such a great high-level view on “Behind the Scenes Replication”. Just to dive into lower level details of replication, you mentioned that the master signals the Slave_IO thread for any new binlog events. Could you please enlighten how actually the IO thread copies the binlog events to Relay logs. My understanding of a simplistic approach would be the Slave_IO thread executing “Show binlog events in from ” and a single event is copied at a time which is bound by “max_allowed_packet”. Also where does the Binlog Dump thread stands in this picture.

    Also in one section you mentioned about the single-threaded performance for Reads, internally mysql especially innodb read IO threads (multiple) and some myisam read process could be responsible for that, isnt it. General understanding is that only one thread has to do all the DMLs serially which on the other hand was done in concurrent manner on the master.

    Please correct if wrong.

    Cheers

  2. billy says:

    after 5.6 GTID manner make replication easy use and to control sush as failover . switchover or filter etc.

  3. What about binlog_format=MIXED?

  4. Akshay, -

    Replication uses its own protocol. “show binlog events” command is mostly to be used by DBAs. I guess there is some similarity to how it is implemented, but I wouldn’t know. However, I just double checked with tcpdump and I do see that events are not copied one by one, but rather in chunks. That is of course if there’s more than one event to fetch.

    Note that since MySQL 5.1.64 and 5.5.26 there’s a new variable slave_max_allowed_packet which takes precedence over max_allowed_packet for a single event.

    Binlog Dump is the replication thread on the master. I guess I should have named it in the article.

    Regarding Reads, the multiple IO threads can only be used for read-ahead which in reality rarely happens. Otherwise, reads are always single-threaded – done in foreground by replication SQL thread – and most of the time reads are going to be the bottle-neck of the replication if it is IO-bound. It could be writes too, but only if the system does not have write-back cache or if it has less powerful IO subsystem than the master. Besides, writes are often pretty well optimized to be sequential while reads are mostly random.

  5. Daniël, -

    MIXED only suggests that either Statement or Row based replication is going to be used, depending on what is best for a given situation (or what MySQL thinks is best). However, I wanted to keep this article simple and not dive too deep into some details.

    A discussion on different replication formats is certainly an interesting one though, I may want to do it sometime if there’s interest.

    Aurimas

  6. Valerie Parham-Thompson says:

    Thank you, Aurimas. Good stuff. I’d like to see a discussion on different replication formats in this context as well.

  7. Raghunandan says:

    Hello Aurimas,

    Is it possible to tweak replication and replicate only inserts from master and ignore the rest of the activity? I might be a little out of league asking for this, but is it possible to filter binlog events to filter before it is applied on the slave? I have a specific requirement that i am working on… Any ideas would be great help :) I thought of using mysql proxy, but its still not GA…

    Thanks,
    Raghu

  8. Raghunandan, -

    I’m not sure MySQL Proxy is ever going to go GA :) Although we were using it quite succesfully with some projects regardless of the alpha status.

    I don’t think there’s any filtering other than by table and/or schema available in the stock MySQL, so you are definitely looking at something outside the box, yet I’m not quite sure yet how one would engage mysql-proxy or sql relay in filtering replication traffic..

    Either way, let me know if we can help you with implementation, I’m sure we can figure something out, but that sounds like a feature request to me.

  9. Raghunandan says:

    Hello Aurimas,

    Thanks for the response. Here is what I am trying to do, might sound crazy :) I have a master/slave setup and the master has heavy writes, for which, I have created partitions so that the innodb memory would hold only a day’s worth of data. That way my insert performance wont degrade as the no. of inserts increase on the table. Now on the slave, the indexing and reporting requirements are different. On the master, once the partition is not required, I will truncate that partition and keep that to minimum. Now here comes my requirement. I dont want to replicate the truncate partition statement, but the inserts should go to slave. :) Can I achieve this?

  10. Abhijit Buchake says:

    Hello Aurimas,

    Many thanks for the article. It is very useful.

    I have a basic doubt.

    As we transfer binary logs from Master to Slave as relay logs (both are OS files), why do we need to create a user on Master with REPLICATION SLAVE privilege to enable replication?
    Why does slave need to connect MySQL on master?

    Thanks in advance.

    Abhijit Buchake

  11. Thanks for your question, Abhijit. The reason is actually pretty simple – all communication goes through MySQL and therefore in order to fetch binary logs from master, slave must authenticate, otherwise anyone could knock and get binary logs. Moreover, later on slave thread is “pinged” about every new event arrived on the master binary log so it would start fetching it. If you copy binary logs manually and parse them locally (eg. using mysqlbinlog utility), then you do not need to connect to master, but you can only use that for delayed replication.

    Let me know if that answers your question. See here for explanation on what REPLICATION SLAVE privilege does.

    Aurimas

  12. Madhu says:

    Hi,

    Can anyone pls explain how does the slave know that a particular command is executed successfully on slave. I mean how does the acknowledgement actually happen to move the relay log position one step ahead.?

  13. Madhu, -

    I’m not sure how deep do you want to dig here, I can only scratch the surface, but the SQL threadreads an event from relay log, tries to execute it and after the execution it checks if the command returns the same result as it did on the master (this information is carried in the binary logs (and therefore is copied to relay logs)), if it does – master.info is updated and next event is read. If not, SQL thread stops, throws an error and waits for a user input.

    Note that master.info in MySQL 5.5 and earlier is not syncronised to disk (Percona Server has very nice workarounds for that), hence in case of a OS crash, the true position and position written in master.info will differ most of the time.

    Hope that makes sense.

    Aurimas

  14. Madhu says:

    Thanks a lot for the info.. :) That helps!

  15. azhar says:

    Thank you for you great article;

    Need to ask about mysql replication;

    you have talked about master to slave replication.

    Will it also handle slave to master?

    The actual scenario is;

    when online the software posts to the hosted server mysql db.

    the online web server will replicate to the local server mysql server.

    When the web is offline then the slave mysql entries could be made and then replicated to the main server.

    can mysql replication handle the above?

    Azhar

  16. Ken Prassens says:

    Could you tell me how replication stops. You mentioned how it is started. The slave contacts the master and requests log information. This starts the process. But when does the process stop, or does it simply go on forever. I don’t believe it goes on forever, but what set of conditions cause the replication work to terminate. Like maybe it runs until is completely caught up and then it terminates. Can the user specify the conditions the replication should stop, or does it really go on forever.

  17. Azhar, I don’t quite understand your question, but you can potentially set up two mysql servers to replicate to each other. While it’s not recommended setup (active-active), in your case, as I understand, it would be passive-active master-master so it might work. Just make sure you don’t write to both servers at once and if you want to, then consider PXC instead.

  18. Ken, essentially the replication slave does replicate infinitely, however slave obviously reaches a point where it caught up with the master at which point it stops and waits until master will “ping” the slave about a new event.

    As for the second part, yes – you can configure the replication to stop at some point, however this technique is mostly used for point-in-time recovery: start slave until [condition x].

  19. Tanuj says:

    Thank you for writing this brief overview.

Speak Your Mind

*