Valery's Mlog

Mindlog of a Freak
February 22nd, 2007

MySQL: Reducing ibdata1

If you use the InnoDB storage engine for (some of) your MySQL tables, you’ve probably already came across a problem with its default configuration. As you may have noticed in your MySQL’s data directory (in Debian/Ubuntu – /var/lib/mysql) lies a file called ‘ibdata1’. It holds almost all the InnoDB data (it’s not a transaction log) of the MySQL instance and could get quite big. By default this file has an initial size of 10Mb and it automatically extends. Unfortunately, InnoDB data files cannot be shrinked and that’s by design. That’s why DELETEs, TRUNCATEs, DROPs, etc. can’t will not reclaim the space used by the file. Instead any freed regions are marked as unused and can be used later. Theoretically speaking, the file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file (in Debian/Ubuntu it’s located in /etc/mysql/my.cnf, and for in Mac OS take a look at the footnotes). Guess what! It’s not set by default. However you can configure your InnoDB engine as described in Section “14.2.1 Configuring InnoDB of the MySQL Reference Manual. Additionally you can force the server to create an *.ibd for each newly created InnoDB table by using the innodb_file_per_table option (quite intuitive, huh ? :)). You can take a look at the Section “14.2.1.4. InnoDB File-Per-Table Tablespaces of the MySQL Reference Manual for more details.

Read the rest of this entry »

%d bloggers like this: