Friday, November 30, 2012

Simple analysis and improvement of MySQL schema

Here is some basic analysis and advises on improvement of MySQL database performance. From time to time you should check you MySQL schema status, I use the following request for it:
    table_name AS table_name,                                                                                                                                                                                                                                                  
    ROUND(data_length/1024/1024,2) AS total_data_size_mb,                                                                                                                                                                                                                      
    ROUND(index_length/1024/1024,2) AS total_index_size_mb,                                                                                                                                                                                                                    
    ROUND((data_length+index_length)/1024/1024,2) AS total_size_mb,                                                                                                                                                                                                            
    ROUND(data_free/1024/1024,2) AS total_free_size_mb,                                                                                                                                                                                                                        
    ROUND(data_free/(data_length/100)) AS defragment_percent,                                                                                                                                                                                                                  
Field defragment_percent value should be 0, but it may be even higher than 100(e.g. you loaded 3MB of data to the table and then deleted 2MB, the table will still be 3MB with real data of 1MB). The most prone to fragmentation are VARCHAR fields so pay attention to the tables where you use VARCHAR a lot. To optimize tables you can use MySQL command:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;
To optimize the whole schema you can use:
mysqlcheck -o <databasename>

Field total_index_size_mb should be less than total total_data_size_mb. If it is not so then review your indexes.

Also I'd recommend to use MySQLTuner. MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. To get it you can use:
Then run it with
This tool will provide you with analysis of your MySQL server and some advises on how to improve its performance.