Quick and dirty MySQL optimizations

Armijn Hemel, June 18, 2009, 5344 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: ,

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.

Social networking: Tweet this article on Twitter Pass on this article on LinkedIn Bookmark this article on Google Bookmark this article on Yahoo! Bookmark this article on Technorati Bookmark this article on Delicious Share this article on Facebook Digg this article on Digg Submit this article to Reddit Thumb this article up at StumbleUpon Submit this article to Furl

Talkback

respond to this article