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

InnoDB page compression not working as documented

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1.32, 10.2.14
    • 10.2
    • None
    • Ubuntu 16.04 "Xenial"

    Description

      https://mariadb.com/kb/en/library/compression/ says:

      Page compression is available only if innodb-file-format=Barracuda and innodb-file-per-table=1.
      Page compression can be used on any file system but is most beneficial on SSDs and Non-Volatile Memory (NVM) devices like FusionIO atomic-series http://www.fusionio.com/products/atomic-series/.

      I setup page compression with the following directives:

      [mysqld]
      innodb_file_format=Barracuda
      innodb_compression_algorithm=zlib
      innodb_doublewrite=0
      innodb_use_trim=1
      innodb_use_fallocate=1
      

      create table statement:

      CREATE TABLE `model_chart` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `ownerSource` varchar(200) NOT NULL,
        `ownerId` varchar(200) NOT NULL,
        `content` longtext NOT NULL,
        `lastModified` datetime(6) NOT NULL,
        `name` varchar(200) NOT NULL,
        `resolution` varchar(10) NOT NULL,
        `symbol` varchar(50) NOT NULL,
        PRIMARY KEY (`id`),
        KEY `IDX_model_chart_ownerId` (`ownerId`)
      ) ENGINE=InnoDB AUTO_INCREMENT=650917 DEFAULT CHARSET=latin1 PAGE_COMPRESSED=1
      

      I load data from the original table (original size: 32G). According to statistics, compression occurs:

      | Innodb_page_compression_saved           | 26762067968 |
      | Innodb_num_pages_page_compressed        | 2204257     |
      

      That's 26G saved by compression, according to the stats.

      But the resulting .ibd tablespace on disk, and in SHOW TABLE STATUS is exactly the same as the original, uncompressed ibd tablespace (32G).

      NB. I have tried with lz4 and that hasn't worked either.

      Please comment.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              tanj Guillaume Lefranc
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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