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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16129 InnoDB page compression not working as documented

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-22839 ROW_FORMAT=COMPRESSED vs PAGE_COMPRESSION=1 - size comparison

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13023 mariabackup does not preserve "holes punched" for tables with page_compressed=1

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15527 page_compressed compressed page partially during import tablespace

          • Major - Major loss of function.
          • Closed

          Activity

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

            euglorg, thank you. Let’s address this in the documentation.

            I would expect the InnoDB page_compressed tables to be able to save space in a traditional file system, such as Linux ext4 or Microsoft NTFS, at the cost of increased fragmentation.

            The documentation might also include an example how to check the actually allocated physical blocks. That could be ls -ls on GNU/Linux.

            On file systems that support snapshots, the allocation of blocks could be quite different, and questions like ‘how much space does a file consume’ become more complicated. We have observed that enabling O_DIRECT (MDEV-24854) caused problems for several Linux file systems.

            marko Marko Mäkelä added a comment - euglorg , thank you. Let’s address this in the documentation. I would expect the InnoDB page_compressed tables to be able to save space in a traditional file system, such as Linux ext4 or Microsoft NTFS, at the cost of increased fragmentation. The documentation might also include an example how to check the actually allocated physical blocks. That could be ls -ls on GNU/Linux. On file systems that support snapshots, the allocation of blocks could be quite different, and questions like ‘how much space does a file consume’ become more complicated. We have observed that enabling O_DIRECT ( MDEV-24854 ) caused problems for several Linux file systems.
            tanj Guillaume Lefranc added a comment -

            It seems that Percona has fixed this by adding fallocate support in xbstream, it would be nice if MariaDB did backport this to mbstream.
            https://jira.percona.com/browse/PXB-2614

            In relevance to my previous comments, fallocate --dig-holes works with XFS, if speculative allocation is disabled (allocsize=64k in mount options)

            tanj Guillaume Lefranc added a comment - It seems that Percona has fixed this by adding fallocate support in xbstream, it would be nice if MariaDB did backport this to mbstream. https://jira.percona.com/browse/PXB-2614 In relevance to my previous comments, fallocate --dig-holes works with XFS, if speculative allocation is disabled (allocsize=64k in mount options)
            valerii Valerii Kravchuk added a comment -

            So, do we plan to fix anything in mariabackup implementation for XFS in frames of this bug report?

            valerii Valerii Kravchuk added a comment - So, do we plan to fix anything in mariabackup implementation for XFS in frames of this bug report?
            marko Marko Mäkelä added a comment -

            I think that tanj’s suggestion is reasonable. This should be relatively simple to implement, but a bit tricky to test.

            marko Marko Mäkelä added a comment - I think that tanj ’s suggestion is reasonable. This should be relatively simple to implement, but a bit tricky to test.
            marko Marko Mäkelä added a comment -

            This change looks OK to me. This will add some fallocate() system calls, but only when page_compressed tables are being used.

            marko Marko Mäkelä added a comment - This change looks OK to me. This will add some fallocate() system calls, but only when page_compressed tables are being used.

            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.