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: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table 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) http://dev.mysql.com/doc/refman/5.1/en/innodb-multiple-tablespaces.html This seems to be available in MariaDB as well.