写这篇文章的原因是，看到开源中国上的一篇投稿翻译文章，其中很多地方被翻译的错误百出。所以还是将原文保存在下面供参考吧。
- master 上写 binlog 是相对轻量的动作（顺序写+会被缓存到操作系统缓存里，轻量与否和磁盘同步设置相关）；
- slave 在主从复制中跟随很紧的情况下，会直接从 master 所在操作系统缓存中读出 binlog 事件；若在几小时或几天后才进行同步则将导致 master 进行读盘操作，耗费相应的 IO 资源；
- Master_Log_File/Read_Master_Log_Pos/Relay_Master_Log_File/Exec_Master_Log_Pos 的含义
- 由于 slave 上 IO 线程所引起的复制延迟是很少见的，如果确实是，一个可以尝试解决的方法是，使用备机数据压缩协议；若确认是由于 slave 上 SQL 线程导致的复制延迟，则可以通过 vmstat 来确定此情况属于“CPU密集型操作”还是“IO密集型操作”；另外可以通过 mpstat 来对 CPU 进行监控。
- 如果确认了当前复制延迟是由于 IO 密集导致的，那么意味着在大多数情况下，sql 线程会因为 slave 单线程读的实现而无法快速获取所需的事件。所以在这种情况下，确实是由于“读”操作而导致复制性能的受限，而不是“写”。
=================
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 readsthat 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.
