Re: Master - slave connection
Posted by:
Rick James
Date: December 05, 2009 11:13PM
I assume SHOW SLAVE STATUS said 'Yes' and 'Yes' for the two threads, and had no error listed.
What might be happening...
* Multiple clients are performing writes (INSERT, etc) on the Master at 'the same time'.
* These are being replicated serially to the slaves.
* The slaves are executing them serially. Here, even before factoring in the SELECTs, you could be getting "behind". However, that is not your case, so I continue...
* The SELECTs are hitting the same table(s) as the replicated statements -- so they are blocking each other. This slows down both the writes and the SELECTs.
* You are using InnoDB, so we are (rarely) talking about "table locks". But even the "row" locking in InnoDB is not totally free.
Also, because of the serial nature of replication, it is usually important that the slaves be at least as powerful as the Master. Please describe the hardware on your Master and Slaves (cpu(s), ram, disk, RAID, etc).
I encounter this sometimes; my solution is to improve the performance of any/all statements. Turn on the slowlog on the master and slaves. See what statements are the slowest. Then provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
The solution may be as simple as adding an index to a frequent SELECT that does a table scan.
Subject
Views
Written By
Posted
3442
December 04, 2009 09:30AM
Re: Master - slave connection
1695
December 05, 2009 11:13PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.