Our latest article over at Database Journal is hot off the presses. Advanced MySQL Replication – Improving Performance discusses some of the best ways to improve the performance of your MySQL slave setup. If the slave is constantly getting further and further behind the master database, we discuss a number of techniques which may help you bring it under control.
#1 by William Newton on February 11, 2009 - 3:35 pm
Quote
I don’t think using MyIsam on the slave is such a good idea. Reads block writes with MyIsam. So if you have a slave for the long running read only queries .. MyIsam will cause replication to fall behind.
#2 by Sheeri Cabral on February 11, 2009 - 5:27 pm
Quote
The #1 cause of this type of slave lag that I have seen is poor query/schema design. “Optimize your schema, data and queries” should be the very first thing to do. GCN had a great article a while back on “Secrets of Optimization” written by Joab Jackson.
http://gcn.com/Articles/2008/08/15/Secrets-of-optimization.aspx
The article goes into optimizing queries and schema. However, there’s a third aspect, optimizing your data. This means having as small a data set as possible. Can you move historical data to another table? Can you use a SMALLINT instead of an INT?
Optimizing data/schema/queries solves 90% or more of common replication issues, and unfortunately did not end up on the list.