Menu

InnoDB Native Partitioning – Early Access

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:

MySQL

1

2

3

4

5

6

CREATETABLE`t1`(

`a`int(10)unsignedNOT NULLAUTO_INCREMENT,

`b`varchar(1024)DEFAULTNULL,

PRIMARY KEY(`a`)

)ENGINE=InnoDBDEFAULTCHARSET=latin1

PARTITION BYHASH(a)PARTITIONS8192;

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:

Post navigation

7 thoughts on “InnoDB Native Partitioning – Early Access”

Creating a partitioning aware InnoDB handler seems a great idea.
Has it been abstracted and is it a new storage engine API that can be used by other storage engines or is it innoDB only ?
This approach could benefit to other storage engines that implement partitionin (like SPIDER or CONNECT in MariaDB)