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:

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:

  1. A handler per partition (~6MB per table instance)
  2. The internal caches (prebuilt struct) per partition (~29MB per table instance)
  3. The prefetch row cache per partition (~66MB per table instance)**
  4. 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.

7 thoughts on “InnoDB Native Partitioning – Early Access

  1. 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)

    1. There is an underlying change in the Storage Engine API that breaks out all partitioning specific methods into an own class which can be used by any engine supporting partitioning.

    1. This feature does not remove the restriction that all partitioning fields must be a part of all unique indexes.

      But it will make it easier to implement global indexes (indexes with different partitioning), which is a way to solve that restriction.

    1. There are no such current plans, since it will still be used to provide partitioning support for other engines.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.