0

If my MySQL master has heavy writes, my read replicas will in-turn gets bogged down by the replication logs & executing them. Then could it serve the reads?

Assume MYSQL master is executing at 100% CPU and disk utilization, now these needs to be executed in the read replicas too. So in turn read replicas would also incur 100% CPU and disk utilization. Then how could it serve the read requests?

Note: Assume the MYSQL master write is just updating a field Ex: UPDATE where ID = <>. No more complex queries so there is no possibility of optimizing in read replicas too.

1
  • Your queries might not be complex, but they still could do full table scans if you don't have a proper index. 100% CPU is a typical symptom. Have you checked your slow-query log? Have you examined the queries with EXPLAIN? Do you have proper indexes to avoid full table scans?
    – tombom
    May 13, 2019 at 11:13

1 Answer 1

0

If replication is using Row Based Replication (RBR), then, say, an UPDATE statement is faster on the Slave than on the Master. This is because all that is sent to the Slave is the changed record. The Slave does not have to think, does not have to search for the row, does not get the rows that don't need to change.

With Statement Based Replication, the Slave is essentially repeating whatever complex processing was done on the Master -- with an exception: If there are a bunch of SELECTs happening to figure out what to UPDATE/INSERT/DELETE, then those happen only on the Master.

If any server has "high" CPU usage, crank up the slow log; let's see what is causing it. Almost always, performance can be improved by a better index, a reformulation of a SELECT, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.