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." donedone
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
- Lilah Brown's Planets, Part II (or, Season II preview) - November 8th, 2009 [November 8th, 2009]
- Snow White needs a bailout - November 8th, 2009 [November 8th, 2009]
- To the moon - November 8th, 2009 [November 8th, 2009]
- S/1 90482 (2005) needs your help - November 8th, 2009 [November 8th, 2009]
- We'll always have Regulus - November 8th, 2009 [November 8th, 2009]
- Orcus Porcus - November 8th, 2009 [November 8th, 2009]
- Kant's Crowded Universe - November 8th, 2009 [November 8th, 2009]
- Look up! - November 8th, 2009 [November 8th, 2009]
- Baby Pictures - November 8th, 2009 [November 8th, 2009]
- Encore: Yelping at Saints - November 8th, 2009 [November 8th, 2009]
- Godspeed - November 8th, 2009 [November 8th, 2009]
- Heavens above! - November 8th, 2009 [November 8th, 2009]
- Homeward bound - November 8th, 2009 [November 8th, 2009]
- Sony Pictures and the end of the world - November 8th, 2009 [November 8th, 2009]
- Thank you from the future - November 8th, 2009 [November 8th, 2009]
- Lunar dreams - November 8th, 2009 [November 8th, 2009]
- The first of the Pluto books! - November 8th, 2009 [November 8th, 2009]
- Don't try to blame it on Rio - November 8th, 2009 [November 8th, 2009]
- Rio roundup - November 8th, 2009 [November 8th, 2009]
- The long road to a Titan storm - November 8th, 2009 [November 8th, 2009]
- Planetary Placemats - November 8th, 2009 [November 8th, 2009]
- Fog! Titan! Titan Fog! (and a peer review experiment) - November 8th, 2009 [November 8th, 2009]
- Millard Canyon Memories - November 8th, 2009 [November 8th, 2009]
- The problem with science - November 8th, 2009 [November 8th, 2009]
- P.S. on the problem with science - November 8th, 2009 [November 8th, 2009]
- How Big is 10 TB? - November 8th, 2009 [November 8th, 2009]
- Showing You Your Servers - November 8th, 2009 [November 8th, 2009]
- Pick Your Partnership: Referral Partners, Resellers and Affiliates - November 8th, 2009 [November 8th, 2009]
- Server Form Factors: Towers v. Rack-Mounts - November 8th, 2009 [November 8th, 2009]
- Lights-Out in the Data Centers - November 8th, 2009 [November 8th, 2009]
- Disruptive Technologies: Virtualization and The Cloud - November 8th, 2009 [November 8th, 2009]
- Know Thy Backups – Part I - November 8th, 2009 [November 8th, 2009]
- Know Thy Backups – Part II - November 8th, 2009 [November 8th, 2009]
- Boo Bash 2009 – Desktop Costume Included! - November 8th, 2009 [November 8th, 2009]
- Why No One Will Talk About “Cloud Computing” in 10 Years - November 8th, 2009 [November 8th, 2009]
- The end of the fall - December 13th, 2009 [December 13th, 2009]
- We Love ‘Server Huggers’ - December 13th, 2009 [December 13th, 2009]
- All About the Cloud: An Interview with Dell’s Cloud Evangelist - December 13th, 2009 [December 13th, 2009]
- Happy Solstice - December 21st, 2009 [December 21st, 2009]
- A ghost of Christmas past - December 31st, 2009 [December 31st, 2009]
- Learning from a Blender - January 5th, 2010 [January 5th, 2010]
- Changing my world - January 6th, 2010 [January 6th, 2010]
- A Server. From Scratch. - January 7th, 2010 [January 7th, 2010]
- The Planet Sand Castle: Upgrade Your Sandbox - January 12th, 2010 [January 12th, 2010]
- Hosting for Haiti - January 20th, 2010 [January 20th, 2010]
- Redefining Value - January 26th, 2010 [January 26th, 2010]
- My Experience as a Newbie at The Planet - January 28th, 2010 [January 28th, 2010]
- Confessions of Another New Planeteer - February 1st, 2010 [February 1st, 2010]
- How I Learned to Stop Worrying and Love Permissions - February 11th, 2010 [February 11th, 2010]
- Where at The Planet is Rachel? - February 15th, 2010 [February 15th, 2010]
- The Planet Storage Cloud: FYI - February 19th, 2010 [February 19th, 2010]
- Meet us in March - February 25th, 2010 [February 25th, 2010]
- The Planet in “The Channel” - March 2nd, 2010 [March 2nd, 2010]
- The Planet Server Challenge - March 13th, 2010 [March 13th, 2010]
- The Definitive Guide to Finding The Planet at SXSW - March 13th, 2010 [March 13th, 2010]
- The SXSW Iron Geek Champion! - March 15th, 2010 [March 15th, 2010]
- Drinking from the Fire Hose - March 16th, 2010 [March 16th, 2010]
- The Fastest Hands at SXSW - March 17th, 2010 [March 17th, 2010]
- System.out.println(“Hello World!”); - March 22nd, 2010 [March 22nd, 2010]
- Westmere – Get it Here - March 23rd, 2010 [March 23rd, 2010]
- Orbit on Your iPhone: A Sign of Things to Come - March 24th, 2010 [March 24th, 2010]
- #ShowMeMyServer 2.0 - March 25th, 2010 [March 25th, 2010]
- Get to Know Your Visitors - March 30th, 2010 [March 30th, 2010]
- The Next Big Thing in Hosting: The Hostatulator - April 1st, 2010 [April 1st, 2010]
- Storage Cloud and the City - April 4th, 2010 [April 4th, 2010]
- American Heart – Why I Walk - April 7th, 2010 [April 7th, 2010]
- The Cake Shouldn’t Be a Lie - April 8th, 2010 [April 8th, 2010]
- April Showers Bring May Flowers - April 9th, 2010 [April 9th, 2010]
- First at The Planet: Nehalem EX 4-Socket Servers - April 15th, 2010 [April 15th, 2010]
- Intel Guest Blog: Xeon 5600 - April 16th, 2010 [April 16th, 2010]
- Inside the Office: A Birthday Surprise - April 18th, 2010 [April 18th, 2010]
- The Planet @ Cloud Expo East - April 19th, 2010 [April 19th, 2010]
- The Planet @ ad:tech SF - April 22nd, 2010 [April 22nd, 2010]
- ad:tech Server Challenge - April 22nd, 2010 [April 22nd, 2010]
- ad:tech Panel: Developing Communities Online - April 23rd, 2010 [April 23rd, 2010]
- The Planet @ Interop Las Vegas - April 27th, 2010 [April 27th, 2010]
- Overflowing With Value: 10TB is Back! - April 28th, 2010 [April 28th, 2010]
- The Cloud is NOT the Revolution - April 29th, 2010 [April 29th, 2010]
- The Importance of Orbit 2.0 - May 5th, 2010 [May 5th, 2010]
- The Planet @ Web 2.0 Expo - May 6th, 2010 [May 6th, 2010]