- 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.
- 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:
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.
OPTIMIZE TABLEshould 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
TEXTcolumns). Deleted rows are maintained in a linked list and subsequent
INSERToperations reuse old row positions. You can use
OPTIMIZE TABLEto reclaim the unused space and to defragment the data file.
- 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.
- 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.
- 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.
- 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.
Friday, June 23, 2006
Wednesday, June 21, 2006
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-queriesoption. If we want we can also specify the filename where the slow queries log should be kept.
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 = 5in 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.
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.
Tuesday, June 20, 2006
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.
Monday, June 19, 2006
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.