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

Remove write support for ROW_FORMAT=COMPRESSED

Details

    Description

      Between 2004 and 2007, when I designed and implemented ROW_FORMAT=COMPRESSED in the InnoDB Plugin for MySQL 5.1 based on Heikki Tuuri’s rough idea, it might have been a good idea to trade some CPU cycles for I/O bandwidth. Nowadays, with fast solid-state storage being a commodity, it is less so. ROW_FORMAT=COMPRESSED is introducing quite a bit of complexity to the buffer pool and crash recovery. We could make the server much faster if we removed write support.

      The format is also too rigid to support innodb_page_size larger than 16 KiB or things like instant ADD COLUMN (MDEV-11369) or per-record transaction identifiers (MDEV-17598).

      To allow users to upgrade from old databases, we must retain read support at least for a few major versions.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-23497 make ROW_FORMAT=COMPRESSED read-only by default

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27058 Buffer page descriptors are too large

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12152 KEY_BLOCK_SIZE strangeness in ALTER TABLE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-17598 InnoDB index option for per-record transaction ID

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21727 Optimize redo logging for ROW_FORMAT=COMPRESSED

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23835 InnoDB: Failing assertion: bpage->buf_fix_count == 0 in buf_relocate

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24797 Column Compression - ERROR 1265 (01000): Data truncated for column

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26400 ALTER TABLE does not remove KEY_BLOCK_SIZE for non-Compressed InnoDB tables

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30563 Failure 1478: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' upon OPTIMIZE TABLE after Recovery

          • Major - Major loss of function.
          • Open
          links to

          Web Link DBA Stack Exchange with a few comments on compression acheived

          Activity

            Ascending order - Click to sort in descending order
            View 27 older comments
            marko Marko Mäkelä added a comment -

            Based on community feedback to MDEV-23497, and because MDEV-27058 already addressed the main technical motivation of removing the write support for ROW_FORMAT=COMPRESSED, I no longer think that we need to remove the write support in the foreseeable future.

            marko Marko Mäkelä added a comment - Based on community feedback to MDEV-23497 , and because MDEV-27058 already addressed the main technical motivation of removing the write support for ROW_FORMAT=COMPRESSED , I no longer think that we need to remove the write support in the foreseeable future.
            stronk7 Eloy Lafuente (stronk7) added a comment - - edited

            Hi,

            idiot question but... does this imply that ROW_FORMAT=COMPRESSED is going to be writeable by default in the future or the setting will continue being needed for that forever?

            Ciao

            PS, edited: I'm just trying to imagine what to do in the future, to continue with COMPRESSED (with or without the setting) for new installations or switch to any other, better / recommended alternative to it.

            stronk7 Eloy Lafuente (stronk7) added a comment - - edited Hi, idiot question but... does this imply that ROW_FORMAT=COMPRESSED is going to be writeable by default in the future or the setting will continue being needed for that forever? Ciao PS, edited: I'm just trying to imagine what to do in the future, to continue with COMPRESSED (with or without the setting) for new installations or switch to any other, better / recommended alternative to it.
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Ok, so now the 10.6 release notes page says:

            We intended to deprecate and eventually remove the InnoDB's COMPRESSED row format. The first step was to make the tables read-only by default, but this plan was abandoned from MariaDB 10.6.6 (MDEV-23497) (MDEV-27736)

            And also, from InnoDB's COMPRESSED Row format:

            From MariaDB 10.6.0 until MariaDB 10.6.5, tables that are of the COMPRESSED row format are read-only by default. This was intended to be the first step towards removing write support and deprecating the feature.
            This plan has been scrapped, and from MariaDB 10.6.6, COMPRESSED tables are no longer read-only by default.
            From MariaDB 10.6.0 to MariaDB 10.6.5, set the innodb_read_only_compressed variable to OFF to make the tables writable.

            Thanks!

            stronk7 Eloy Lafuente (stronk7) added a comment - Ok, so now the 10.6 release notes page says: We intended to deprecate and eventually remove the InnoDB's COMPRESSED row format . The first step was to make the tables read-only by default , but this plan was abandoned from MariaDB 10.6.6 ( MDEV-23497 ) ( MDEV-27736 ) And also, from InnoDB's COMPRESSED Row format : From MariaDB 10.6.0 until MariaDB 10.6.5, tables that are of the COMPRESSED row format are read-only by default. This was intended to be the first step towards removing write support and deprecating the feature. This plan has been scrapped, and from MariaDB 10.6.6, COMPRESSED tables are no longer read-only by default. From MariaDB 10.6.0 to MariaDB 10.6.5, set the innodb_read_only_compressed variable to OFF to make the tables writable. Thanks!
            marko Marko Mäkelä added a comment -

            stronk7, thank you. I have now also updated MDEV-23497 with a note that the plan has been cancelled.

            marko Marko Mäkelä added a comment - stronk7 , thank you. I have now also updated MDEV-23497 with a note that the plan has been cancelled.
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Thanks for confirming, and for everything else, marko !

            stronk7 Eloy Lafuente (stronk7) added a comment - Thanks for confirming, and for everything else, marko !

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              22 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.