为什么从属计算机在数据库复制中读取速度比主计算机更快 [英] why is the slave machine faster for reading than master in database replication

问题描述

在主/从数据库复制中，所有写操作都将发送到主计算机。所有读取都进入从机。主机将数据复制到从机。
当主服务器将数据复制到从服务器时，从计算机必须执行写操作，这将锁定该行。从从机读取速度更快，但是为什么呢？

In the master/slave database replication, all writes go to the master machine. All reads go to the slave machine. The master machine replicates data to the slave. When the master replicates data to the slave, the slave machine has to do a write which would lock up the row. Reading from the slave machine is faster but why?

推荐答案

主服务器非常忙

• 它并行地写入数据（很明显）


• 它导致磁盘I / O写入二进制日志，将完成的SQL集合序列化为它的二进制日志


• 它通过将完整的SQL从其二进制日志传递到从服务器的I / O线程（通过 SHOW PROCESSLIST在主服务器上可见）管理复制; 和用户名系统用户）。

• It writes data in parallel (that's obvious)
• It incurs disk I/O writing to binary logs, serializing the collection of completed SQL into its binary logs
• It manages replication in terms of passing completed SQL from its binary logs to the I/O thread of the Slave (visible on the Master via SHOW PROCESSLIST; with the username system user). This can slightly slow things down the more Slaves are connected to the Master.

从站不那么忙，因为它...

Slaves are less busy because it ...

• 序列化MySQL复制的I / O
  
  
  
  
  • 通过Master从Master收集SQL IO线程
  
  
  • 将IO线程中的SQL记录到其最新的中继日志中
  
  
  • SQL线程从中继日志中读取下一个可用的SQL
  
  

  使从属设备和主设备处于相同水平的竞争环境的唯一例外是

  The only exceptions that would put the Slave and the Master on the same level playing field would be

  
  
  • 如果从站启用了二进制日志记录。如果从站只是一个从站，则不需要这样做，但是如果从站也是主站，则不需要这样做。
  
  
  • 如果将多个SQL语句作为单个InnoDB事务处理，则为
  。
  
  • 如果硬件不同
    
    
    
    
    • 主磁盘具有更快的磁盘，更多的内核，更多的内存
    
    
    • 从服务器是商品服务器
    
    
    • If the Slave had binary logging enabled. That is not required if the Slave is just a Slave, but would be required if the Slave was also a Master
    • If multiple SQL statements were processed as a single InnoDB transaction.
    • If Hardware is Different
      • Master has Faster Disks, more cores, more RAM
      • Slave was a commodity server

      这篇关于为什么从属计算机在数据库复制中读取速度比主计算机更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！