MySQL Slaves Behind Master 2

In my earlier post, I touched on the subject of investigating reasons why MySQL slaves get behind master. In this post I will continue that discussion.

1. Different Specs on the Master and Slave servers. This is especially important if we have a write intensive replication environment. If our master server is more powerful than the slave servers then there is a great chance that during peak times our slaves will start lagging behind. I have seen environments where the master server was twice as powerful as the slave server and slaves will fall considerably behind. If that is the case then the best solution is to upgrade the slave server so it is as powerful as the master server.
   
2. Unoptimized Tables: Recently I was working in an environment where the tables weren't optimized ever since they were created. Optimized tables can give faster access to data vs. unoptimized tables especially if you variable length records and perform a lot of INSERT/UPDATE/DELETE operations. When we optimize tables using "OPTIMIZE TABLE" command, MySQL defragments the table's data files, sorts the index files and rebuilds the index statistics. The table is then stored as a single, continous page.
   
   From the MySQL Manual:
   
   

   OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.
   

   Although running the "OPTIMIZE TABLE" statement regularly on the master and slave servers will improve performance, it will not ensure that the slaves are always up to date especially if huge differences in the specs exist between the master and the slave.
   


3. Unoptimized RAID version on the slaves: If you're using RAID 5, you may want to seriously consider using RAID 10 as RAID 5 is expensive when it comes to disk operations and traffic between the disk and the controller.
   
   Many DBAs choose RAID 10 as their primary choice for databases espcially when the load on the databases gets high. One of the main reasons for this is that with RAID 10 there is a lack of parity to calculate as compared to RAID 5, therefore allowing for faster writes in RAID 10. For more information see the RAID post on my other blog or read the Wikipedia article on RAID.
   
   Using RAID 10 or RAID 01 will allow for faster writes (more throughput) which may help slaves stay relatively more up to date.
   
4. Unoptimized Queries: This one is a no brainer as if you have unoptimized queries reading data, they are going to take longer and will use the server resources that would otherwise go into staying up to date with the master.
   
5. Disable Key writes: If you do not perform reads on the slaves, you may want to disable the key writes on slaves as suggested by Boyd Hemphill in this discussion.
6. InndoDB vs MyISAM: If our application is read intensive, using InnoDB can help in terms of table locking (Thanks Rick James). I recommend reading Peter's recent article about using MyISAM tables in production which was in response to Arjen's excellent article.

To be continued ...