Is innodb_file_per_table advisable?

Question

We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave just as one .ibd? I read some articles say do not go with it as you need more disk access when there joins to be performed? We will have join between this table and others for reporting generation purposes.

Answer 1

You must go with innodb_file_per_table and you need to do some how cleaning with InnoDB's current infrastructure.

I have seen many DB hosting clients setup MySQL and leave InnoDB in its default state. This causes the system tablespace (better known as ibdata1) grow wildly.

Even if you switched to innodb_file_per_table, the .ibd file would have to be extracted from ibdata1 and ibdata will never shrink. For example, if you have a table called mydb.mytable that is inside ibdata1 taking up 2GB, to extract it you have to the following:

STEP 01) Add this to /etc/my.cnf

[mysqld]
innodb_file_per_table

STEP 02) service mysql restart

STEP 03) ALTER TABLE mydb.mytable ENGINE=InnoDB;

That will make the file /var/lib/mysql/mydb/mytable.ibd

Unfortunately, the 2GB of space that was occupied by the table before the change cannot be reclaimed. I wrote past posts about how and why to cleanup InnoDB's infrastructure:

• Why does InnoDB store all databases in one file?
• Scheduled optimization of tables in MySQL InnoDB
• ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
• My Original Post in StackOverflow

Once you make this major change, don't forget to increase innodb_open_files (default 300). Otherwise, disk access is very limited.

With regard to joins, make sure you have proper indexes that support the join criteria.

UPDATE 2012-04-02 11:30 EDT

Using innodb_file_per_table in a fresh installattion cause ibdata1 to grow very slowly because all DDL is done external to ibdata. You can shrink any InnoDB table as I mentioned before like this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

UPDATE 2012-04-02 16:50 EDT

When it comes to backups, be extremely careful making copies of .ibd files. Why?

Inside every .ibd file is special value known as the tablespace_id. There is a list of tablespace_id values in ibdata1. If you ever perform table maintenance that requires dropping and recreating the table, the tablespace_id will become different. Making a copy of such a .ibd file can only be intergated back into the database for usage if you also make a copy of ibdata1. That endangers the tablespace_id of all other InnoDB tables. In light of this, it is preferable that you perform mysqldump backups because mysqldumps are logical copies of the data. In other words, the backup is independent of the point-in-time of the ibdata1 and you are free to reload without operability issues.

Answer 2

Agree with @RolandoMySQLDBA, for something he doesn't mention: backups.

If you have your MySQL backup regime all worked out, AND you aren't including its .ibd file in your file system backup strategy, then this is not so important. But consider that adding ONE BYTES to any innodb table will cause an incremental backup of your .ibd table other wise, and you can see that you'll quickly be running out of backup storage.

Answer 3

I have an application where I have exactly this situation: some big tables and some smaller ones.

I decided to leave the small ones in the ibdata1 while putting the bigger ones in their own files.

I do so by having innodb_file_per_table switched on by default and only switch it off temporarily for moving a table to ibdata1 with ALTER TABLE.