MySQL Slow? Check for Fragmentation.

Let's say you have a website and you notice that any calls to your MySQL database take longer to render. If you don't have a Database Administrator (DBA), this can be pretty frustrating. SoftLayer's Managed Hosting line of business employs some of the best DBAs in the country and is one of the only managed hosting providers that offers MySQL and MsSQL DBA services, and I don't just say that because I'm one of them ... We've got the certifications to prove it. :-)

Given my area of expertise, I wanted to share a few some simple tips with you to help you tweak variables and improve the performance of your MySQL server. Given that every application is different, this isn't necessarily a one-size-fits-all solution, but it'll at least give you a starting point for troubleshooting.

First: Get mysqltuner.pl. This is a fine script by Major Hayden that will give you some valuable information regarding the performance of your MySQL server.

Second: Look for fragmented tables. What are fragmented tables? If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages or that there are many unused pages in the 64-page blocks that were allocated to the index. The symptoms of fragmented tables can be that table can take more disk space than needed or the results may return slower with more disk I/O than needed. INNODB users need to check the fragmentation often because when INNODB marks data as deleted, it never overwrites the blocks with new data ... It just marks them as unusable. As a result, the data size is artificially inflated and data retrieval is slowed.

Fortunately, there is a way to see your table fragmentation and that is to run a query against the information_schemea to show all tables that are fragmented and the percentage of fragmentation:

Fixing the fragmentation is easy, but there are a few caveats. When defragmenting a table, it will lock the table, so make sure you can afford the lock. To fix fragmented tables, you can simply run optimize table <table name>; to rebuild the table and all indexes or you can change the engine of the table with alter table <table name> engine = INNODB;

I have written a simple bash script in bash to go through, defragment and optimize your tables:

You'd be surprised how much of an impact table fragmentation has on MySQL performance, and this is an easy way to quickly troubleshoot your database that "isn't as fast as it used to be." If you follow the above steps and still can't make sense of what's causing your database to lag, our Managed Hosting team is always here to work with you to get your servers back in shape ... And with the flexibility of month-to-month contract terms and the ability to add managed capabilities to specific pieces of your infrastructure, we have to earn your business every month with spectacular service.

My entire web site is 100% data-driven. If I lock the tables, it will bring the entire web site down for a while. The Googlebots are hitting my web site every 30 to 90 seconds. If the site is down when the Googlebot comes and the bot sees that the site is down, it can hurt me in the rankings. How fast the site responds is part of Google's algorithm. It would be nice to copy the entire file some place else, defrag it and then move it back.

Rob,
You are correct, for a site that relies on the the database, you will need to discover other ways. One way I use this is to run the script on the slave and then change my config script to use the slave and rebuild the master. This usually takes less than 1 sec. So there should be no impact to SEO.

Ricardo,
Perfect example of using the tools of MySQL. You are correct it will do the optimize and repair.

There are some bugs with mysqlcheck , for example mysqlcheck is 0, althought the corruption is detected. I do believe this was fixed for and I noticed this on a recent upgrade of MySQL using mysql_upgrade. There is no reason not to trust MySQL tools.

So what's the trick to defragment automatically without taking the site offline? If anyone knows how do to this I might be willing to pay for a solution because it seems like this is really important to keep things running fast. I just had an issue today where I think fragmented tables took my sites offline. After running mysqlcheck everything was fine but I had to restart mysql and nginx and this took the site offline for a few minutes--not good! Also, is there some way to track down which queries are fragmenting the tables in the first place? Maybe if I can find the root of the problem here... I don't want to deal with this on a regular basis.

Thanks Lee. But your query is *definitely* testing the wrong values for Data_free. It should be "> 0" - I looked through Major Hayden's mysqltuner.pl code to see why it reported different to your query.

My entire web site is 100% data-driven. If I lock the tables, it will bring the entire web site down for a while. The Googlebots are hitting my web site every 30 to 90 seconds. If the site is down when the Googlebot comes and the bot sees that the site is down, it can hurt me in the rankings. How fast the site responds is part of Google's algorithm. It would be nice to copy the entire file some place else, defrag it and then move it back.

Rob,
You are correct, for a site that relies on the the database, you will need to discover other ways. One way I use this is to run the script on the slave and then change my config script to use the slave and rebuild the master. This usually takes less than 1 sec. So there should be no impact to SEO.

Ricardo,
Perfect example of using the tools of MySQL. You are correct it will do the optimize and repair.

There are some bugs with mysqlcheck , for example mysqlcheck is 0, althought the corruption is detected. I do believe this was fixed for and I noticed this on a recent upgrade of MySQL using mysql_upgrade. There is no reason not to trust MySQL tools.

So what's the trick to defragment automatically without taking the site offline? If anyone knows how do to this I might be willing to pay for a solution because it seems like this is really important to keep things running fast. I just had an issue today where I think fragmented tables took my sites offline. After running mysqlcheck everything was fine but I had to restart mysql and nginx and this took the site offline for a few minutes--not good! Also, is there some way to track down which queries are fragmenting the tables in the first place? Maybe if I can find the root of the problem here... I don't want to deal with this on a regular basis.

Thanks Lee. But your query is *definitely* testing the wrong values for Data_free. It should be "> 0" - I looked through Major Hayden's mysqltuner.pl code to see why it reported different to your query.