Friday, June 23, 2006

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 ...


Anonymous said...

I have read many articles and comments about optimizing tables for InnoDB and MyISAM... they range from it should be performed once a week to once a month or the manual states "..if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows ...". However, when does one really know when to optimize tables? Can one actually assess when a specific table needs optimization? and if so, how does one do that?

Anonymous said...

--delay-key-writes is good even for read heavy tables, since it doesn't have to wait as much for disk on writes.
The biggest problem with this flag of course is that if your server goes down, your indexes *WILL* have to be rebuilt.

If you want to see on a MyISAM table if you should optimize, one simple way can be the data_free in SHOW TABLE STATUS output. This displays how much fragmentation you have within the datafile.
The datafile itself may be fragmented, though, this you will have to check with filesystem fragmentation tools.

A detailed analysis you can get from the shell with myisamchk -dvv tablename (just make sure nothing is using the table while you run this. a LOCK TABLE blah READ; FLUSH TABLE blah; in another shell will fix this - don't forget to unlock after the check is ran with UNLOCK TABLES;)
This will give you detailed statistics about your table.

Frank said...

Hi Tobias,

Thank you for an insightful comment.

So the higher values of Data_free would indicate that the table needs optimization.


Anonymous said...

I'm new to PHP and recently setup my local machine with PHP and MySQL for doing development. I was sort of stuck when I needed to post my work for the user to test and review. After looking around a bit I found a site that hosts PHP and MySQL apps. I was surprised that it was free - it seems they're offering the service at no cost until 2012. At that point they'll change over to a fee-based service. However, in the meantime, it's a great place to do anything from demo and sandbox right up to posting sites for real.

Their pitch is as follows:

"This is absolutely free, there is no catch. You get 350 MB of disk space and 100 GB bandwidth. They also have cPanel control panel which is amazing and easy to use website builder. Moreover, there is not any kind of advertising on your pages."

Check it out using this link:

Important: There's one catch in that you must make sure you visit the account every 14 days - otherwise the account is marked 'Inactive' and the files are deleted!!!

Thanks and good luck!