How to shrink/purge ibdata1 file in MySQL

Question

I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.

It's working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.

I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?

Thanks!

Answer 1

When you delete innodb tables, MySQL does not free the space inside the ibdata file, that's why it keeps growing. These files hardly ever shrink.

How to shrink an existing ibdata file:

http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

You can script this and schedule the script to run after a fixed period of time, but for the setup described above it seems that multiple tablespaces are an easier solution.

If you use the configuration option innodb_file_per_table, you create multiple tablespaces. That is, MySQL creates separate files for each table instead of one shared file. These separate files a stored in the directory of the database, and they are deleted when you delete this database. This should remove the need to shrink/purge ibdata files in your case.

More information about multiple tablespaces:

http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

Answer 2

Hi!

That ibdata1 isn't shrinking is a particular annoying feature of MySQL which I was struck by. The ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump. But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large.

It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf in order to enable this and you do that by adding:

[mysqld]
innodb_file_per_table=1

http://dev.mysql.com/doc/refman/5.5/en/multiple-tablespaces.html

As you want to reclaim the space from ibdata1 you actually have to delete the file:

1. Do a mysqldump of all databases, procedures, triggers etc
2. Drop all databases
3. Stop mysql
4. Delete ibdata1 and ib_log-files
5. Start mysql
6. Restore from dump

When you start MySQL in step 5 the ibdata1 and ib-log files will be recreated.

Now your fit to go. When you create a new database for analysis, the tables will be located in separate ibd-files, not in ibdata1. As you usually drop the database soon after the ibd files will be deleted.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

You have probably seen this: http://bugs.mysql.com/bug.php?id=1341

Hope this helps!