MySQL InnoDB - innodb_file_per_table cons?

Question

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table.

I am wondering why innodb_file_per_table is not enabled by default. Are there downsides to using it?

Answer 1

I have the complete answer for this one !!!

Once innodb_file_per_table is put in place, and new InnoDB tables can be shrunk using 'ALTER TABLE ENGINE=InnoDB'; This will shrink new .ibd files GUARANTEED !!!

If you run 'ALTER TABLE ENGINE=InnoDB'; on an InnoDB table created before you used innodb_file_per_table, it will yank the data and indexes for that table out of the ibdata1 file and store it in a .ibd file, This will leave a permanent pidgeon whole in the ibdata1 that can never be reused.

The ibdata1 file normally houses four types of information
1) Table Data
2) Table Indexes
3) MVCC (Multiversioning Concurrency Control) Data
4) Table Metadata

Here is the guaranteed way to shrink the ibdata1 file pretty much forever...

1) MySQLDump all databases into a SQL text file (call it SQLData.sql)
2) Drop all databases (except mysql schema)
3) Shutdown mysql
4) Add the following lines to /etc/my.cnf
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

5) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

6) Restart mysql

This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1 at 1G each

7) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata and intermittent MVCC data.

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header) mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable OR ALTER TABLE mydb.mytable ENGINE=InnoDB and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this numerous times in my career as a MYSQL DBA without so much as a single problem thereafter. In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 50MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

Answer 2

See bug.

Are there downsides to using it?

• more open files
• open/reopen overhead
• .ibd file does not shrink(see 1, 2)

I always use innodb_file_per_table on large databases.

Answer 3

innodb_file_per_table is enabled by default in MariaDB.