Two Common Reasons for Replication Lag | from Oracle to MySQL
As a MySQL support engineer, I see this so often, that I felt it could help to write a post about it.
Customers contact us to ask about replication lag – ie, a slave is very far behind the master, and not catching up. ("Very far" meaning hours behind.)
The most common reason I encounter is databases having InnoDB tables without explicit primary keys. Especially if you are using row-based replication ("RBR"), you want explicit primary keys on all your tables. Otherwise, MySQL will scan the entire table for each row that is updated. (See bug 53375 . ) Maybe I'm a relational purist, but why would you want to have tables without explicit primary keys, anyway? (On the other, less-purist, hand, for performance reasons, sometimes a short surrogate PK may be preferred to a lengthy logical one. )
The other common reason is that the slave is single-threaded, and single-threaded performance can't keep up with the multi-threaded master. In this case, if multiple databases are being updated, enabling the multi-threaded slave can help. ( See the manual for more.)
Nothing new or groundshaking here, but I hope this helps someone who is googling "MySQL replication lag".
Read full article from Two Common Reasons for Replication Lag | from Oracle to MySQL
No comments:
Post a Comment