Administrators of InnoDB databases will recognize that InnoDB tables grow in size...but never shrink.
Whether this is a bug or a feature has been heavily discussed in years past, check here or here for more information, but either way the problem is that an InnoDB will grow the ibdata file, but not shrink it.
Other engines do not suffer this affliction, however, InnoDB has many useful features, I use the rollback feature on queries quite extensively myself. These features make it very useful in applications, causing the InnoDB to often be the database of choice.
Luckily, this is not an insurmountable challenge and there are a couple ways of handling it.
- Static tables.
If possible use static tables as much as possible. If static tables are not an option...
- Purge older data preventing the database from growing in size.
For example, I had a project that maintained data for a period of time. So I setup a process that would remove unneeded data on a daily basis that was older than the data I wanted to keep. In my case, I removed anything over 24 hours old.
The problem with the above approach is that the process had better run, or the database will again jump in size.
- Periodically shrink the database.
Over time, you will likely need to resort to this option and it is the purpose of this post.
If you follow the procedure below, I believe you will be able to get and keep a good grip on the size of your InnoDB database.
You will need to have the the MySQL services offline for parts of this procedure, so be sure to make the appropriate plans.
- Take the database offline, on my Redhat system this is done using service mysqld stop.
Always take a backup of the database before beginning this process so you can restore if needed. I use a third party backup software, but if that isn't available and you have then needed space you can use the tar utility.
tar -cvzPf /backuppath/full_db_backup.tgz /mySQLPath
Replace /backuppath/ with the location you want your backup placed and replace mySQLPath with the path to the MySQL data folders, /var/lib/mysql by default.
Before proceeding, we need to start MySQL again using service mysqld start.
Now we are going to use the mysqldump utility to export the schema of the database we plan to purge. We need this schema so we can re-create the database later.
mysqldump -uUNAME -pPASSWORD DB --no-data > /backuppath/DB_schema.sql
In the above, the UNAME is the user id used to connect to MySQL. The PASSWORD is the password for UNAME and DB is the database we want to export. The --no-data flag tells mysqldump to only output the schema omitting all data rows.
At this point we want to output any data we want to preserve.
If we want to keep all data then we would use the following.
mysqldump -uUNAME -pPASSWORD --database DB --single-transaction --disable-keys --skip-set-charset > /backuppath/DB_clean-dump.sql
If we only want to output a few of the tables we could use something like this.
mysqldump -uUNAME -pPASSWORD --database DB --table T1 --table T2 --single-transaction --disable-keys --skip-set-charset > /backuppath/DB_clean-dump.sql
Or if we wanted to do all of the tables except...
mysqldump -uUNAME -pPASSWORD --database DB --ignore-table=T3 --ignore-table=T4 --single-transaction --disable-keys --skip-set-charset > /backuppath/DB_clean-dump.sql
Now that we have our schema and the data we want to preserve, it is time to shrink things. To do this we first need to connect to the sql server and drop the database.
- mysql -uUNAME -pPASSWORD
- DROP DATABASE DB;
And again we need to stop the database, service mysqld stop. Once complete we can purge the unneeded files.
- rm ib* (Warning: Always be careful when removing files.)
- rmdir DB (only necessary is MySQL didn't remove them after the database drop.
Ok, so we have gotten rid of our space hogs and now we need to get the database back. To do this, we again need to restart MySQL using service mysqld start, once complete, connect and re-create the database.
- mysql -uUNAME -pPASSWORD
- CREATE DATABASE DB;
And finally, re-import what we exported earlier, schema first, then data.
mysql -uUNAME -pPASSWORD --database DB < /backuppath/DB_schema.sql
mysql -uUNAME -pPASSWORD --database DB < /backuppath/DB_clean-dump.sql
"That's all folks..."