Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23521

Implement an InnoDB row format that can use overflow pages for smaller columns

    XMLWordPrintable

    Details

      Description

      With the DYNAMIC row format, variable-length fields can only be stored on overflow pages if they are defined with a maximum size of 256 bytes or more., and fixed-length fields can only be stored on overflow pages if their maximum size is 768 bytes or more. From the documentation:

      For BLOB and TEXT columns, only values longer than 40 bytes are considered for storage on overflow pages. For VARBINARY and VARCHAR columns, only values longer than 255 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards these limits. These limits are only based on the length of the actual column's data.

      These limits differ from the limits for the COMPACT row format, where the limit is 767 bytes for all types.

      Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.

      https://mariadb.com/kb/en/library/innodb-dynamic-row-format/#overflow-pages-with-the-dynamic-row-format

      A lot of users run into "row size too large" errors due to this:

      https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/

      A counter-intuitive solution to many of these issues is to actually increase the length of variable-length fields, so that they can be stored on overflow pages.

      https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/#increasing-the-length-of-varbinary-columns

      https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/#increasing-the-length-of-varchar-columns

      It might be a good idea to implement a row format that allows smaller pages to be stored on overflow pages, so that users don't have to use this workaround.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration