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

mbstream breaks page compression on XFS

Details

    Description

      As InnoDB page compression uses “hole punching,” and it seems like ignored by somehow. mariabackup SST method leaves compressed data from donor to decompressed on joiner node. mariabackup SST not copying compressed files so page compression seems not beneficial in this case.

      MariaDB [tt]> show create table t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `msg` varchar(100) DEFAULT NULL,
        UNIQUE KEY `id` (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=34209256 DEFAULT CHARSET=latin1 `PAGE_COMPRESSED`='ON'
      1 row in set (0.005 sec)
       
      +--------------+------------+------------+
      | TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
      +--------------+------------+------------+
      | tt           | t1         | Dynamic    |
      +--------------+------------+------------+
       
      MariaDB [tt]> show global variables like '%compression%';
      +------------------------------------------+------------------+
      | Variable_name                            | Value            |
      +------------------------------------------+------------------+
      | column_compression_threshold             | 100              |
      | column_compression_zlib_level            | 6                |
      | column_compression_zlib_strategy         | DEFAULT_STRATEGY |
      | column_compression_zlib_wrap             | OFF              |
      | innodb_compression_algorithm             | zlib             |
      | innodb_compression_default               | ON               |
      | innodb_compression_failure_threshold_pct | 5                |
      | innodb_compression_level                 | 1                |
      | innodb_compression_pad_pct_max           | 50               |
      +------------------------------------------+------------------+
      9 rows in set (0.001 sec)
      

      page compression is enabled. Where size of tablespace file is:

      $ ls -lrth /var/lib/mysql/tt/t1.ibd
      2.1G /var/lib/mysql/tt/t1.ibd
       
      $ du -hs --block-size=1 /var/lib/mysql/tt/t1.ibd
      1098792960	/var/lib/mysql/tt/t1.ibd
       
      $ du -hs /var/lib/mysql/tt/t1.ibd
      1.1G	/var/lib/mysql/tt/t1.ibd
      

      Now, let's try mariabackup:

      mariabackup --backup --target-dir=/home/vagrant/backup/ -u root -p'xxxx'
      .
      [00] 2021-05-19 20:08:13 completed OK!
      

      Let's verify backup table t1 tablespace file size again:

      $ ls -la /home/vagrant/backup/tt/t1.ibd
      -rw-r-----. 1 root root 2231369728 May 19 20:08 /home/vagrant/backup/tt/t1.ibd
       
      $ du -hs --block-size=1 /home/vagrant/backup/tt/t1.ibd
      2214154240	/home/vagrant/backup/tt/t1.ibd
       
      $ du -hs /home/vagrant/backup/tt/t1.ibd
      2.1G	/home/vagrant/backup/tt/t1.ibd
      

      As per https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html
      Preserving page compression when moving a page-compressed tablespace file from one host to another requires a utility that preserves sparse files

      I think that is related xtrabackup report https://jira.percona.com/browse/PXB-1557

      Attachments

        Issue Links

          Activity

            muhammad.irfan Muhammad Irfan created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Component/s mariabackup [ 14500 ]
            Component/s Storage Engine - InnoDB [ 10129 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Assignee Marko Mäkelä [ marko ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            julien.fritsch Julien Fritsch made changes -
            Comment [ A comment with security level 'Developers' was removed. ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            muhammad.irfan Muhammad Irfan made changes -
            Comment [ [~thiru] It appears on CentOS7/8 and it happens on XFS filesystem during mariabackup SST ]
            muhammad.irfan Muhammad Irfan made changes -
            Comment [ Thirunarayanan Balathandayuthapani It appears on CentOS7/8 and it happens on XFS filesystem during mariabackup SST ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Component/s Galera SST [ 10121 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Jan Lindström [ jplindst ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            jplindst Jan Lindström (Inactive) made changes -
            Assignee Jan Lindström [ jplindst ]
            jplindst Jan Lindström (Inactive) made changes -
            Component/s Galera SST [ 10121 ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Marko Mäkelä [ marko ]
            wlad Vladislav Vaintroub made changes -
            Assignee Marko Mäkelä [ marko ] Alexander Barkov [ bar ]
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 122020 ] MariaDB v4 [ 144600 ]
            marko Marko Mäkelä made changes -
            Assignee Alexander Barkov [ bar ] Ian Gilfillan [ greenman ]
            greenman Ian Gilfillan made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            greenman Ian Gilfillan made changes -
            Status Stalled [ 10000 ] Open [ 1 ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Ian Gilfillan [ greenman ] Jacob Moorman [ jacob.moorman ]
            julien.fritsch Julien Fritsch made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            marko Marko Mäkelä made changes -
            Assignee Jacob Moorman [ jacob.moorman ] Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Summary mariabackup breaks page compression mbstream breaks page compression on XFS
            serg Sergei Golubchik made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Marko Mäkelä [ marko ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Sergei Golubchik [ serg ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4.32 [ 29300 ]
            Fix Version/s 10.5.23 [ 29012 ]
            Fix Version/s 10.6.16 [ 29014 ]
            Fix Version/s 10.10.7 [ 29018 ]
            Fix Version/s 10.11.6 [ 29020 ]
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.1.3 [ 29023 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 110530 150451 139362
            marko Marko Mäkelä made changes -

            People

              serg Sergei Golubchik
              muhammad.irfan Muhammad Irfan
              Votes:
              6 Vote for this issue
              Watchers:
              19 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.