MySQL Slow? Check for Fragmentation.

June 8, 2011

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:

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

Comments

June 8th, 2011 at 11:26am

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:40pm

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:38am

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:07am

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:02am

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:53pm

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:32pm

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:22pm

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:36pm

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:45pm

in your SQL statement, I reckon the data free should be greater than zero, not less than...
otherwise, good article :)

 

September 3rd, 2013 at 10:02am

My innodb table size is about 200Gbs. It is about 1 day i'm still waiting for the optimize table query to finish. Is it usually that slow?

November 11th, 2013 at 12:19am

ssh as root.
#Backup all databases first:
mysqldump --all-databases > all_databases.sql

#from command line run:
mysqlcheck -Agor --all-databases

I have never had a problem with this.

November 22nd, 2013 at 5:02am

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.

Should be:
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;

Leave a Reply

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <pre>, <blockcode>, <bash>, <c>, <cpp>, <drupal5>, <drupal6>, <java>, <javascript>, <php>, <python>, <ruby>. The supported tag styles are: <foo>, [foo].
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.

Comments

June 8th, 2011 at 11:26am

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:40pm

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:38am

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:07am

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:02am

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:53pm

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:32pm

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:22pm

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:36pm

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:45pm

in your SQL statement, I reckon the data free should be greater than zero, not less than...
otherwise, good article :)

 

September 3rd, 2013 at 10:02am

My innodb table size is about 200Gbs. It is about 1 day i'm still waiting for the optimize table query to finish. Is it usually that slow?

November 11th, 2013 at 12:19am

ssh as root.
#Backup all databases first:
mysqldump --all-databases > all_databases.sql

#from command line run:
mysqlcheck -Agor --all-databases

I have never had a problem with this.

November 22nd, 2013 at 5:02am

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.

Should be:
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;

Leave a Reply

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <pre>, <blockcode>, <bash>, <c>, <cpp>, <drupal5>, <drupal6>, <java>, <javascript>, <php>, <python>, <ruby>. The supported tag styles are: <foo>, [foo].
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.