MySQL Slow? Check for Fragmentation.
Posted by Lee Thompson in Customer Service, SoftLayer, Technology, Tips and TricksLet’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:
SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA, CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB')FREE from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free < 0;
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:
#!/bin/bash MYSQL_LOGIN='-u<user name> --password=<passowrd>' for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema") do TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN | grep -v Tables_in_) echo "Switching to database $db" for table in $TABLES do echo -n " * Optimizing table $table ... " echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN >/dev/null echo "done." done done
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.
-Lee
June 8th, 2011 at 11:26 am
It would be nice if you had a script you could run as root to check all tables for hosting providers like us? Nice article!
June 8th, 2011 at 3:40 pm
Nice SQL script, thanks
From looking at your bash script, Would it be an idea to concat the output to a logfile, for review afterwards?Optimise table can potentially output errors and warnings, which may be important
If anyone has replication setup, dont forget that optimise table will replicate out, so you need to factor in any downtime on the slaves too
August 16th, 2011 at 9:38 am
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.
August 19th, 2011 at 10:07 am
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.
September 21st, 2011 at 7:02 am
Lee,
why not use only:
mysqlcheck -Aao -auto-repair -u root -p”$MYSQLPWD”
I mean, this will rebuild and optimize all tables in the MySQL instance… like your script, right?
September 28th, 2011 at 12:53 pm
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.
Thank you.
June 13th, 2012 at 1:32 pm
I believe the query you’re using is incorrect. Running this:
SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), ‘MB’) DATA, CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), ‘MB’) FREE from information_schema.TABLES where TABLE_SCHEMA NOT IN (‘information_schema’,'mysql’) and Data_free 0 is I believe what you’re looking for.
June 20th, 2012 at 2:22 pm
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.
October 14th, 2012 at 6:36 pm
If your site is that important that it needs a defrag, a planned maintenance isn’t the end of the world.
October 28th, 2012 at 6:45 pm
in your SQL statement, I reckon the data free should be greater than zero, not less than…
otherwise, good article
April 17th, 2013 at 7:51 am
Thanks Lee..
April 22nd, 2013 at 12:53 pm
[...] I was working with a mail archive MySQL database today and was twiddling my thumbs waiting for simple queries to complete. The database has about 12 million rows and is on a 2x2GHz 2GB Linux Server x64. I wanted to try to optimize the database and found this little gem from Lee at SoftLayer blog post. [...]