CentrioHost Blog

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 de-fragment 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 de-fragment 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 de-fragmenting the database first to see if that improves the performance or not. In few cases, I have seen, performance improves over 60% after the de-fragmentation.

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 CentrioHost, 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 de-fragmenting 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.