Quick and dirty MySQL optimizations

Armijn Hemel, June 18, 2009, 2617 views.

Deleted data in a MySQL database often keeps occupying some space (depending on the storage engine), until the database is optimized. With a simple quick and dirty solution you can let cron do this for you.

Tags: administration, mysql

If you have data in a MySQL database and data gets deleted regularly you have without doubt encountered overhead: the space where the old data was stored has not been freed yet and is still occupied. If a database is optimized regularly the old cruft is removed and system resources (memory, disk) are freed. With small databases you won't notice this, but if you run a lot of them, or big ones, or a lot of big ones, you will.

With the mysqlcheck command you can optimize databases easily. You can simply automate this for all databases on your system:

#!/bin/sh
DATABASES=`mysql --defaults-file=/home/mysql/.my.cnf -B -e "show databases" | \
     grep -v Database | grep -v information_schema`
for DB in $DATABASES; do
        mysqlcheck --defaults-file=/home/mysql/.my.cnf -o $DB | \ 
        grep -v "already up to date" | grep -v "OK" | \
        grep -v "The storage engine for the table doesn't support optimize"
done

This script first asks mysql what all databases are and then optimizes all of them, regardless whether they are already optimized or not, or if the storage engine actually supports it. Run this script (or something similar) every week (or more) from cron and you should not suffer from databases with an incredible amount of overhead anymore.