why is the slave machine faster for reading than m

问题:

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?

回答1:

The Master is very busy

• 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 ...

• Serializes I/O for MySQL Replication
  • Collects SQL from the Master via the IO Thread
  • Records SQL from IO Thread into its most recent Relay Log
  • SQL Thread reads next available SQL from Relay Logs
• Processes one SQL command / one Transaction at a time via the SQL Thread
• If Slave had all MyISAM and Master had all InnoDB, writes on the Slave to tables that had foreign key constraints would not have to perform any referential integrity checks. No MVCC would have to happen on the Slave.

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

• 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

回答2:

The slave should be a lot faster, because:

• The amount of data writes it does is the same as the master
• The writes to slave bypass all syntax and permissions checking (all that work - and it's a lot - is done by the master when processing the query - only the data changes are shipped to the slave)
• It does no other reads than what you are executing as a test
• Replication is an optimised process designed for the fastest possible synch speed, so if there us a failure of master, the shave is as up to date as possible. This means that by design it must cause as little work as possible on the slave

This all means that the slave is under considerably less load than the master.

Actually, redirecting reads to the slave is a known performance modification.