The InnoDB labs release includes a snapshot of the InnoDB Native Partitioning feature.
To better understand why we implemented this, we need to start with some background on tables, storage engines, and handlers. In MySQL an open instance of a table has a handler object as an interface to the table’s storage engine. For a partitioned table there is a main table handler that implements the partitioning feature, but for storage, each partition has its own handler. This worked fairly well, but the more partitions you had the more overhead from the per partition handlers. So to remove this overhead for partitioned InnoDB tables we’re introducing Native Partitioning support! This means a new InnoDB partitioning aware handler, so that we have a single handler object for a partitioned table and not one handler object per partition.
Let us create a simple table with 8k partitions:
CREATE TABLE `t1` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY HASH (a) PARTITIONS 8192;
If we compare the amount of memory used when opening a single instance of this table, first using the old generic non-native partitioning, and then with InnoDB Native Partitioning we see the following:
One open instance of the table takes 49% less memory (111MB vs 218MB) with the current state of Native Partitioning support. With ten open instances of the table, we take up 90% less memory (113MB vs 1166MB)!
The base 111MB is used internally by the InnoDB data dictionary cache; 86MB for index level info, 21MB for table level info, and 4MB for statistics. This is likely to decrease even further due to de-duplicating info for each partition.
The overhead on memory with the old generic partitioning are due to the following factors:
- A handler per partition (~6MB per table instance)
- The internal caches (prebuilt struct) per partition (~29MB per table instance)
- The prefetch row cache per partition (~66MB per table instance)**
- Internal row templates (~2MB per table instance)
**The prefetch row cache is allocated for range and scan operations, so if you only use exact key searches it will not be allocated by the old partitioning engine.
By creating a partitioning aware InnoDB handler we also have a better foundation for supporting other features like Foreign Keys and FullText indexes with partitioned InnoDB tables.
We look forward to your feedback on this new feature! Please let us know if you run into any problems or have any other comments.