Finding and optimizing fragmented tables in mysql
With increasing data, mysql tables can get defragmented and it is a good idea to optimize tables every once in a while.
Getting the list of all tables that need optimization :
> mysql -p -e “select concat(TABLE_SCHEMA,’.’,TABLE_NAME),Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN (‘information_schema’,’mysql’) and Data_free>0;” >> fragdb.txt
To Generate the output to include the command to optimize table, so that you can process with shell script use the following command :
>
mysql -p -e “select concat(‘optimize table ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’)
> from information_schema.TABLES
> where TABLE_SCHEMA NOT IN (‘information_schema’,’mysql’)
> and Data_free > 0;” >> dbfrag.txt
In replication environment , if you donot want to replicate optimization, which you dont want in most cases, use the following syntax :
> OPTIMIZE or TABLE