Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 515
Kevin Avatar asked Oct 19 '12 19:10

Kevin


People also ask

What is the difference between master master and master-slave replication?

Answer: Master-Master: This is similar to Master-Slave architecture, the only difference is that both the nodes are masters and replica at the same time i.e. there will be circular replication between the nodes.

What are the benefits of master-slave replication?

And that's where master-slave architecture comes in handy. Basically, master-slave databases involve caching data from the master database to the slave databases. This replication process helps database administrators to replicate copies of the parent database to multiple servers simultaneously.

What is the difference between master and slave table?

The Master's session table can be different than the Slave's Session table. The Master can have both newly created sessions and existing sessions that are "refreshed", but the Slave will only have newly created sessions pushed from the Master times a multiplier.

Why in master-slave replication all writes have to go the same database master?

However, all writes must go to the master, because the data flow is one-way from master to slaves, and writing to a slave makes no sense.


2 Answers

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

like image 191
Bohemian Avatar answered Oct 19 '22 19:10

Bohemian


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
like image 23
RolandoMySQLDBA Avatar answered Oct 19 '22 18:10

RolandoMySQLDBA