InnoDB: Supporting Page Sizes of 32k and 64k

In the new InnoDB lab release we support page sizes of 32k and 64k. This gives users even more choices on the page size, allowing you to further customize InnoDB for your particular workload.

There are some things worthy of note related to this new feature:

  1. The extent size changes when the innodb_page_size is set 32k or 64k.
    The extent size is 2M for 32k page sizes, and 4M for 64k page sizes (the extent size is 1M for 4k, 8k, and 16k page sizes). If we do not enlarge the extent size then we will have too many extent headers on the allocation bitmap page, and the bitmap page will overflow.
  2. The innodb_log_buffer_size default value changes when the innodb_page_size is set to 32k or 64k. We change the innodb_log_buffer_size default value from 8M to 16M in this case. If we don’t increase the innodb_log_buffer_size then we may suffer from a lack of free log pages because we have fewer available log buffer pages.
  3. We do not support compression when innodb_page_size is set to 32k or 64k.
    When innodb_page_size is set to 32k or 64k, you will get the following warning if you include a ROW_FORMAT=COMPRESSED or a KEY_BLOCK_SIZE clause in your table DDL:
    Warning 1478 InnoDB: Cannot create a COMPRESSED table when innodb_page_size > 16k
    If innodb_strict_mode is ON, you will instead get the following error:
    Error 1031 Table storage engine for 't1' doesn't have this option
  4. The maximum record size is 16k when innodb_page_size is set to 64k.
    In theory we can store a record whose size is about 50% of the page size. In ROW_FORMAT=REDUNDANT, the record header contains 14-bit pointers to the end of each field, limiting the maximum record size to 16k. For simplicity, we enforce the 16k record size limit on all row formats when innodb_page_size is 64k.
  5. The innodb_page_size setting is global for all tablespaces within a single MySQL server instance.

We look forward to your feedback on this new feature! Please let us if you encounter any issues, or if you have any more general feedback.

Thank you for using MySQL!

9 thoughts on “InnoDB: Supporting Page Sizes of 32k and 64k

  1. I assume _all_ InnoDB tables on a server must use the same block size?

    Smaller block sizes have a use case where point-queries dominate and you are using SSDs. (Less I/O = faster)

    32KB allows records to be ~16KB; this is useful for a few users.

    But what is the use case for 64KB?

    1. The reason is that we don’t want to change row formats, the same reason we keep 16k as max record size for 64k page size.
      For compressed row format, we have 2 bytes dense slot for each record, but the 2 high bits used, so it can only support 16k page size.
      As we can see in page0zip.h:

      #define PAGE_ZIP_DIR_SLOT_MASK 0x3fff
      /** ‘owned’ flag */
      #define PAGE_ZIP_DIR_SLOT_OWNED 0x4000
      /** ‘deleted’ flag */
      #define PAGE_ZIP_DIR_SLOT_DEL 0x8000

  2. Rick,

    64K (or larger pages) have two immediate use cases.

    1. Less “off page” BLOB/TEXT values

    2. Transparent page IO compression using punch hole. If the underlying FS can only punch holes based on the FS block size then you get better compression with larger page sizes.

    Currently the buffer pool supports a single page size internally. This of course is restrictive. But that doesn’t mean it will always remain so 🙂


    1. “1. Less “off page” BLOB/TEXT values” — but if there is a hard limit of 16KB per row, this is only a few bytes more space for BLOB/TEXT than you can get from a 32KB page size. (Or am I missing something?) I assume that the on-page BLOB/TEXT values have to fit in the 16KB limit.

      1. Hi Rick,

        BLOB/TEXT values are moved off page when the row exceeds 1/2 a page – so by default ~8K. This means that a 32K page should be able to store about twice as much in-line.

        (The rules are a little different with compressed pages, where a row can be larger than 1/2 a page.)

  3. Tim,

    It is incremental work required for strategic reasons. Flexible buffer pool with arbitrary page sizes will allow us to add different index types e.g., LSM (or its variants).

    The lack of Zip support for larger page sizes has to do with the some of the internal data structures required by the Zip code.


  4. Hi

    I do have a big olap environment in windows / mysql 5.5 migrating to 5.7.11 and we are planning to use 64k pages given the amount of data scanned and returned. The question is: By default , MYSQL installs with 16K pages . Ive set innodb_page_size to 64k after install, removed the logs files in data directory, but im getting some catalog tables errors when the logs are being recreated. Is this the correct way to set MySQL to 64k pages? Or, there is a way which i can call mysqld to create the catalog tables already set with 64k pages?

    Im having some trouble with native page compression on all page sizes on windows either…


Leave a Reply

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

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