Description:
Our DB world is heavily partitioned, and straight-up benchmarking shows that setting innodb_file_per_table=1 decreases performance by about 20% for our application, as well as increasing DB size a lot, presumably due to allocated but unused space in the partition files. My guess is the performance difference is due to lots of open FDs, fsyncs, and other misc. overhead that goes with maintaining lots of open files.
But if performance weren't a big consideration - which it is for us - we'd prefer innodb_file_per_table so we could reclaim space by dropping databases.
Since InnoDB already supports having all partitions in a single database file if innodb_file_per_table=0, it would appear to be straightforward to have all partitions in a single table file that otherwise is similar to the current file arrangement if single_table_per_file is set.
How to repeat:
1. Set innodb_file_per_table=1
2. Create a database with partitions
Suggested fix:
Something like "innodb_file_per_table=2", or maybe a new option, "innodb_single_file_for_partitions=1" to indicate that a user wants to have all partitions for a given table in a single table file.
