Usually in our database there are some occurrence that we deleted a large part of a table or we made many changes to a table with variable-length rows, deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

You can use OPTIMIZE TABLE to reclaim the unused space and to de-fragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

The script below can be executed on a running system (i.e. there will be no downtime) and you could schedule to run this weekly or daily as well through a cronjob. Please note that “no downtime” only means you don’t have to stop MySQL. Any tables being optimized will be locked for the duration of the operation (can be a long time). It is vitally important not to interrupt the optimize operation because it involves doing a repair, which could leave the table in a broken state if stopped.

The first run will be significantly longer; So please don’t panic 🙂

#!/bin/bash
 
PWD=`cat /etc/.mysqlpw`
MYSQL_LOGIN='-u root -p'$PWD
 
for db in $(echo "SHOW DATABASES;" | mysql -N $MYSQL_LOGIN | grep -v -e "Database" -e "^information_schema$" -e "^performance_schema$" -e "^mysql$")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -N $MYSQL_LOGIN )
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                mysql $MYSQL_LOGIN $db -e "OPTIMIZE TABLE $table"  >/dev/null
                echo "done."
        done
done

The following cronjob will optimize all the tables in all databases every hour and write a log to /var/log/optimize_all_mysql_tables.log

13 8 * * * /bin/nice /root/mysql-scripts/optimize_all_mysql_tables.sh >> /var/log/optimize_all_mysql_tables.log 2>&1

This does the same on Sundays at 2:13AM

13 2 * * * 0 /bin/nice /root/mysql-tools/optimize_all_mysql_tables.sh >> /var/log/optimize_all_mysql_tables.log 2>&1