Data & Architecture DBA

    mysql经过几个版本的发展，Replication的稳定性也越来越高，性能也得到了长足的发展。当建立起master-slave主从复制关系时，在slave端，会创建两个线程：Slave IO thread 和 Slave SQL thread. 这两个线程有各自的功能和作用。

Slave IO thread : 这个线程与master交互，当master产生新的日志时，向master发出COM_BINLOG_DUMP请求，在master端会创建一个binlog dump thread来dump新的日志信息，这个线程将新的日志信息发送给slave io thread,slave io thread通过net_safe_read()来安全接收master传递过来的binary logs,并把这些二制日志信息，写到slave端的relay logs,接着更新slave端的master.info文件.

Slave SQL thread : 这个线程读取relay logs中的日志信息，解析并且执行。

mysql的复制也可以实现级连，slave又可以作为另外一个slave的master,但需要在中间的这个slave上打开一个参数log_slave_updates,其体系架构如图所示：

Why 2 threads?

In MySQL 3.23, we had only one thread on the slave, which did the whole job: read one event from the connection to the master, executed it, read another event, executed it, etc.

In MySQL 4.0.2 we split the job into two threads, using a relay log file to exchange between them.

This makes code more complicated. We have to deal with the relay log being written at the end, read at another position, at the same time. Plus handling the detection of EOF on the relay log, switching to the new relay log. Also the SQL thread must do different reads, depending on how the relay log file it is reading is being used:

• If the file is being written to by the I/O thread, the relay log is partly in memory, not all on disk, and mutexes are needed to avoid confusion between threads.
• If the file has already been rotated (the I/O thread is not writing to it anymore), it is a normal file that no other threads touch.

The advantages of having 2 threads instead of one:

• Helps having a more up-to-date slave. Reading a statement is fast, executing it is slow. If the master dies (burns), there are good chances that the I/O thread has caught almost all updates issued on the master, and saved them in the relay log, for use by the SQL thread.
• Reduces the required master-slave connection time. If the slave has not been connected for a long time, it is very late compared to the master. It means the SQL thread will have a lot of executing to do. So with the single-thread read-execute-read-execute technique, the slave will have to be connected for a long time to be able to fetch all updates from the master. Which is stupid, as for a significant part of the time, the connection will be idle, because the single thread is busy executing the statement. Whereas with 2 threads, the I/O thread will fetch the binlogs from the master in a shorter time. Then the connection is not needed anymore, and the SQL thread can continue executing the relay log.

     采用新的复制实现方式，尽管在代码方面，要比原来要复杂许多，但换来的性能提升非常明显，并且当遇到异常情况时，主从的数据差异会比采用原有方式要好很多,数据的安全性得到了更好的保障。还有一点，如果mysql的主从复制的性能没有得到比较大的提高，slave与master的数据延迟比较大,那么现在利用mysql实现的读写分离方案都会遇到很大的问题，这种体系结构也不会像现在这么流行。

     通过两个线程来完成原来一个线程需要完成的工作，这种软件设计思想，我们的程序设计时，是否借鉴和使用过呢？