How Does MySQL Replication Works?

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 how stuff works 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 on the MySQL official website(https://dev.mysql.com/doc/refman/5.1/en/replication.html). 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 statusG”:

• 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 statusG” 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 statusG”, 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 statusG” 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.