Home > Best practices, Database, English, open-source, Techie > MySQL and MariaDB innodb_file_per_table

MySQL and MariaDB innodb_file_per_table

For those of you pushing your MySQL instances to higher levels, you’ll have realized at some point that all InnoDB tables share their indexing space into a single file (ibdata1, usually).

This has several very bad outcomes:

  • the ibdata1 file becomes huge after some time (we’ve seen it pass the 8GB bar)
  • starting the MySQL server takes longer
  • deleting an InnoDB table *doesn’t* reduce the space used by ibdata1
  • you have to shutdown your server, remove ibdata1 completely, then restart the server in order to reinitialize the ibdata1 file to a 0-size file (but this is, of course, difficult or dangerous to do in a high-availability context)

I just found there was a setting called “innodb_file_per_table” since MySQL 5.1 at least:

There is an explanation on what the advantages and disadvantages of the method are (but as you might have guessed, the main disadvantage of using separate files is that you have more files, taking a little more time to do I/O operations when you scan them all – and of course retro-compatibility with previous versions of MySQL)

This seems to be available in MariaDB as well.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: