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

Investigating reasons why slaves get behind master

Since MySQL uses asynchronous replication, slaves can get behind the master from time to time. Among the reasons a slave can get behind includes heavy query load (SELECTs) on slave servers. From time to time I come across a slave server that will start lagging behind during the peak hours causing the Seconds_Behind_Master value to go higher and higher. As the load settles, the slave eventually catches up. I have seen Seconds_Behind_Master value in excess of 19000. With a value so high it is clear that the slave is falling considerably behind in reading the relay log.

As Mike pointed out in a comment to my earlier post "seconds behind master only tells you the difference between when a statement was put into the relay log on the slave and when it was executed, it doesn't account for the time it takes for the statement to get from the master to the slave."

On a slave server, MySQL compares the TIMESTAMP value taken from the slave and subtracts the TIMESTAMP value present in the binary log and then adds the difference between the UNIX_TIMESTAMP value taken from the master and the slave. (See Seconds Behind Master weirdness if you are getting a Seconds_Behind_Master value of 18446744073709551615).

If we want to decrease the value of the Seconds_Behind_Master, we need to find the queries that take extraordinarily long to execute on both the master and the slave. To do this we can use the slow query log (also see slow query log manual page for the latest version).

We can enable the logging of slow queries by starting the mysqld with the

--log-slow-queries

option. If we want we can also specify the filename where the slow queries log should be kept.

--log-slow-queries=/path/to/slow_queries_log

.

By default, MySQL will create the slow query log as hostname-slow.log in the data directory.

Using another option long_query_time, we can specify the amount of time that is considered acceptable. For instance, if we specify

long_query_time = 5

in the my.cnf file then all queries that take longer than 5 seconds will be considered slow queries and therefore logged.

I believe, the default value for long_query_time is 10 seconds.

Once you have the slow queries log, you can read it with mysqldumpslow.

By investigating and optimizing the "slow queries" we can help our slaves not get too behind the master.

More to come. Comments, questions and suggestions are welcome.

Finding out how far behind are the slaves using Seconds_Behind_Master

When managing a large number of MySQL slaves, often one needs to view a summarized list of the seconds by which the slaves are falling behind. Here is a small script that will allow you to do just that.

#!/usr/bin/bash
cat /list/slaves | while read i ; do echo $i; mysql -h $i -e "SHOW SLAVE STATUS \G" | grep "Seconds_Behind_Master" ; done

To use it create a list of slave hosts and place it in /list/slaves or wherever you like. Then create a script /scripts/slave_behind_all and paste the above code in it.

Proper way to shutdown a slave

There is a right way to stop a MySQL slave server and a wrong way. The wrong way is to simply shutdown the mysql server which can cause problems when starting up the server and slave.

The proper way to stop a slave server is to first issue "STOP SLAVE" and then shutdown the server. Here's how you can shutdown slave on multiple MySQL hosts.

cat /lists/dbs | while read i ; do echo $i; /path/to/mysql -h $i -e "STOP SLAVE"; done

Here's another way (thanks Toby for the alternative one liner to run a command against multiple hosts):

xargs -n 1 mysql -e "STOP SLAVE" -h < /lists/dbs

Stopping the slave first will ensure that the replication thread is stopped before the server goes down.

Running a command on multiple MySQL hosts

I have posted a one liner that can allow you to run a command on multiple MySQL hosts at my programming oneliners blog.

New Job

I apologize for the lack of posting on my blog. Life has been pretty busy as last week I started a new job as MySQL DBA in NY at a leading photo blogging community.

I am very excited about this job and look forward to working with a great team that is really passionate about what they are doing.

These days I am trying to get settled in NY/NJ area.

As time goes by I will be posting more. At the moment, thanks for staying tuned.

MySQL Performance Tuning Presentations

Peter Zaitsev maintains a very nice MySQL performance tuning blog at MySQL Performance Blog where he also shares his perfomance tuning presentations which I highly recommend to any MySQL DBA. At the MySQL Users Conference 2006, I attended (and really enjoyed Peter's session). You can get the slides of that presentation from MySQLUC.com.