Bug #48829 tables in single files even if partitioned
Submitted: 17 Nov 2009 9:33 Modified: 17 Nov 2009 12:20
Reporter: Greg Kemnitz Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1.40 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb_file_per_table, partition

[17 Nov 2009 9:33] Greg Kemnitz
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.
[17 Nov 2009 12:20] Valeriy Kravchuk
Thank you for the feature request.
[26 Jan 2011 23:00] Mattias Jonsson
Related to bug#5968.