Optimize mysql tables gives a 35% drop in disk usage a Drupal 7 performance tip

Today was the day my backup disk ran full. I was wondering why because it had been running fine for some time. I keep the 50 last days of backups on disk. I have a couple of scripts that allow me to backup my stuff and clean up old backups. This thing has been running for a couple of years now. As time progresses these databases create overhead.

Databases grow even when not a lot of new content is created. What happens is drupal mysql tables start to create overhead, especialy on tables with a lot of deletions. Cache tables, watchdog, access log, variable, ... . Sites that have been running for almost 5 years like this site for example create lots of overhead.
Optimize mysql tables

So what is the solution: the mysql optimize mysql tables. You can do it via the command line with a query, use php myadmin or automate this and dont worry about it. I decided to the last one to optimize mysql tables and this was the result.

A drop from the database disk space of 35%: From 67,5Mb to 43,8Mb.

This is saving me 23.7Mb * 50backups more than 1Gb on disk space.

When doing this for 15 sites on the shared host it saved about 4,5Gb. So it was worth it to optimize the tables.
How to optimize mysql tables just once

Go to phpmyadmin select all the tables in your database and in the select you will find optimize tables. Confirm and compare the results.
How to automate optimizing mysql tables

Use this module to keep your database lean and clean http://drupal.org/project/db_maintenance Just install it and it will optimize your tables in your sleep using cron.

From the project page "Think of OPTIMIZE TABLE like defragmenting your hard drive. Sure your system will run without it, and it might even be fast, but isn't it nice to run a defrag when you can?"

It sure saves disk space too.