Filed with Developers Set innodb_file_per_table=1 By Default for New Installs [Case 41802]

[alphawolf50]

Hi,

I'd like to request that the default my.cnf on new cPanel installations include "innodb_file_per_table=1". According to this thread, there is an internal feature request for this, but I'd like to make this a public request in order to gain support from the forums for the idea. I'd also like to expand upon the reasons why this is a good idea. The thread I linked says the feature request is case #41802, so I put that in the title here.

Expanded reason why innodb_file_per_table should be in the default my.cnf:

1. innodb_file_per_table is required in order to use advanced file formats like Barracuda. See link for reasons one might like to do this: Real-Life Use Case for “Barracuda” InnoDB File Format - MySQL Performance Blog
2. When innodb_file_per_table is not set, the shared table space can get very large, and reclaiming this space is extremely difficult. This page illustrates the complexity of reclaiming that space: Reclaiming Your InnoDB Tablespace — mysqlhacker
3. Going from innodb_file_per_table back to the shared tablespace (should one be inclined) is much less problem-prone than the reverse.
4. Unless you have several tens of thousands of tables with a heavy write load, the separate tablespace performs just as well as the shared tablespace. In some cases, the separate tablespace outperforms the shared space (see list item #1).
5. innodb_file_per_table avoids this issue:

   Issue with shared InnoDB /var/lib/mysql/ibdata1 storage
   Due to the shared tablespace, data corruption for one InnoDB table can result in MySQL failing to start up on the entire machine. Repairing InnoDB corruption can be extremely difficult to perform and can result in data loss for tables that were not corrupted originally during that repair process.

6. Separate tablespaces suffer less fragmentation than the shared tablespace.

For the record, this request is only to apply to new cPanel installations.

[eva2000]

Definitely would be good option for NEW WHM installs

[cPanelTristan]

It is the option for new installations of 11.31 and will be for 11.32 when that reaches CURRENT. The feature request was incorporated into the 11.31 product due to MySQL 5.5 being offered, which sets InnoDB as the default table engine for MySQL. Here is a note from the case describing how this option is set:

Starting with version 11.32, on new installs we need to enable innodb_file_per_table. This should occur when the MySQL version is 5.0+ on a fresh install.

For existing installations we should not automatically enable this directive.

[alphawolf50]

Fantastic, thanks Tristan