Most of the shared hosting users use different content management system for their websites. There are different types of content management systems. These content management systems use mysql database most of the time. A CMS can have degraded performance when the user’s database gets fragmented. Mysql Database fragmentation appears when you make lots of changes on your database cause lots of writes and updates. Periodically, these changes cause memory fragmentation on the database, mostly MyISAM storage engine performs tremendous slow on a fragmented condition! This tutorial should have the advises for shared hosting users, how often they should defragment their databases and how!
CMS like forum has more fragmentation than a blog. Most of the cases the log tables are seriously fragmented as they are periodically updates for very small number of changes. But nevertheless, mysql database of a blog can get fragmented as well when you periodically updates the posts and receives lots of comments. Age of your database matters a fact of fragmentation. A rule of thumb I have seen people follow, they defragment their database at least once a month. If you are running a pretty busy forum and seeing performance of your database is degrading, you should try defragmenting the database first to see if that improves the performance or not. In few cases, I have seen, performance improves over 60% after the defragmentation.
Now lets talk about how can you defragment your mysql database tables. Mysql provides a build in command called “Optimize” to defragment a database table. The easiest way to optimize a database is to visit Cpanel >> Mysql Databases >> Use the Optimize Database option from your database list.
You can also optimize all the tables of a database using phpmyadmin. Visit phpmyadmin from your cpanel >> Select the database, Check all the tables and issue the command “Optimize” from the dropdown. This would defragment all of the tables for you.
You should periodically run optimize command if you feel your database performance is important and the write/read performance on your database is degrading. On Mellowhost, we run Mysql check on all the databases of MyISAM storage engine once in 2 months period to make sure the performance doesn’t degrade if you are not defragmenting your databases regularly.
If a table is crashed then you should try to repair it (Repair is another tool given by Mysql similar to Optimize to fix corrupted tables), if it is not working, then you should immediately mail at our support to get your table restored from our backup garage.
Hi,
Thanks for sharing. This is a great article. I believe most of the web site owner rarely perform any database health check for their MySQL Server.
If you don’t mind I would like to invite you to share your experience on how to maintain and optimize MySQL server in my blog at http://www.wisdom-soup.com.
Your contribution is highly appreciate. Thanks.
Regards,
Jimmy Liew
Thanks, That’s gave a good idea.
I use a plugin for DB manage which defrag every 5 hrs as my site is little busy.
freq defrag make any prob to my DB?
Frequent defrag is not essential as this can potentially cause you a good amount of CPU. Defragment improves the performance on the cost of CPU. You have to calculate if the interval does suits the amount of CPU you are getting from the respective server.
Good luck.
You should only defragment your DB if you have deleted a large portion of a table. Otherwise it will not be helpful. And also be careful when using OPTIMIZE InnoDB tables, your AUTO_INCREMENT counter will reset if you have older version of MySQL.