July 30, 2014

InnoDB page sizes: plans and ideas

It is well known fact that InnoDB standard page size is 16K or 16384 bytes. Sometime ago we added feature to Percona Server to change that to 4K or 8K (innodb_page_size). That maybe useful for SSD that operates with 4K blocks and you can get really much better throughput with 4K ( however we need to run benchmarks yet to see real effect from it).
The bigger limitation is that this setting is for whole database instance and you need to mysqldump your data, re-create database and load data.
It could be much better if we are able to set it per table, or even per index level – this is what we want to look into in near future.

Another area for improvements: support 32K and 64K pages. Why this is needed ? For InnoDB compression.
Right now if you use 16K and trying to compress, the only good choice is to compress to 8K pages (which gives you only 2:1 compress ratio), and you rarely will be able to compress to 4K.
However I think with 32K and 64K we should be able to compress to 8K and to 16K easily and get 4:1 compression rate, as we will be operate with bigger block size with more possibilities to compress (of course it is workload depended). This is also in our plans.

Having 4K-64K block sizes with ability to set parameter per table and per index would make InnoDB really flexible for different type of use cases and storage. As Oracle makes a lot of new good features in MySQL 5.6, I would like to see that implemented also.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. Justin Swanhart says:

    I’d personally like to see Oracle add real tablespace support, as well as transportable tablespaces. With Percona XtraBackup, it is possible to transport tablespaces, but support inside the main server would be very nice.

  2. Peter Zaitsev says:

    Vadim,

    There are more reasons for larger pages. You can get larger rows stored on the page (without going to external blob storage) and you have less pages in buffer pool which reduces overhead needed to deal with them.

    Support of per table (first) and per index page size selection would be best and I believe there is some work done which would make it a bit easier to support already as Innodb can support different page sizes for different tables… with limitations they must be compressed.

    It is probably easier if single tablespace has pages of single size, in such case we might need to have Innodb to be able to store every index in a separate tablespace file/table as it exists right now if not “proper” tablespace support.

  3. Mark Callaghan says:

    I am very interested in this feature.

  4. chang says:

    Hi Vadim
    Why default page size is the 16KB in innodb? Is there something (read-write speed and space overhead) related with the file system implementation on which Innodb bases? or something related with the stored data?

    Is there any quickest way to judge the page-size? In my situation(with embedded innodb), I can’t employ a long-time benchmark program such as IOZONE to get IO performance and then compute the best appropriate page-size.

    Thanks

  5. chang says:

    Hi Vadim
    Why default page size is the 16KB in innodb? Is there something (read-write speed and space overhead) related with the file system implementation on which Innodb bases? or something related with the stored data?

    Is there any quickest way to judge the page-size? In my situation(with embedded innodb), I can’t employ a long-time benchmark program such as IOZONE to get IO performance and then compute the best appropriate page-size.

    Thanks

  6. Mark Callaghan says:

    I am very interested in this feature.

  7. Vadim,

    There are more reasons for larger pages. You can get larger rows stored on the page (without going to external blob storage) and you have less pages in buffer pool which reduces overhead needed to deal with them.

    Support of per table (first) and per index page size selection would be best and I believe there is some work done which would make it a bit easier to support already as Innodb can support different page sizes for different tables… with limitations they must be compressed.

    It is probably easier if single tablespace has pages of single size, in such case we might need to have Innodb to be able to store every index in a separate tablespace file/table as it exists right now if not “proper” tablespace support.

  8. Justin Swanhart says:

    I’d personally like to see Oracle add real tablespace support, as well as transportable tablespaces. With Percona XtraBackup, it is possible to transport tablespaces, but support inside the main server would be very nice.

  9. peter steiner says:

    Hello, just a short question – where can i set this InnoDB page size? I have Ubuntu installed and don’t find this file: innobase/include/univ.i,

Speak Your Mind

*