[MDEV-16129] InnoDB page compression not working as documented Created: 2018-05-09  Updated: 2022-01-06

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.1.32, 10.2.14
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Guillaume Lefranc Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 16.04 "Xenial"

Issue Links:
Relates
relates to MDEV-22839 ROW_FORMAT=COMPRESSED vs PAGE_COMPRES... Open
relates to MDEV-25734 mbstream breaks page compression on XFS Closed

 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.

 Comments   
Comment by Vladislav Vaintroub [ 2018-05-09 ]

I think the stats could be a bit off, and count the same regions twice for example.
Keep in mind, the compression works by making files sparse, and this does not shown in ls -l output

https://askubuntu.com/questions/269480/why-does-ls-l-output-a-different-size-from-ls-s , the answer discusses how to check the "physical" size rather than just logical

Comment by Guillaume Lefranc [ 2018-05-09 ]

Yes I am aware of that, I checked df output and usage is the same. I'm quite sure df does report affected size, not perceived size (side note: I am using XFS. I forgot to mention it in the OP but the docs says it should work everywhere)

On the stats subject. I am aware that Data_length and Index_length are approximations based on number of pages and may be irrelevant for calculations.

Comment by Vladislav Vaintroub [ 2018-05-09 ]

Ok, if you checked the physical size and it stays the same, I dunno. Maybe jplindst can explain.
I'd try fallocate -d <filename> on this filesystem, to check if it fallocate can indeed punch some holes on the file, then compare ls -s vs ls -l.

Comment by Guillaume Lefranc [ 2018-05-09 ]

Yes punching holes works fine:

  1. fallocate -l 1G test.1g
  2. du test.1g
    1048576 test.1g
  3. fallocate -p -o 0 -l 1g test.1g
  4. du test.1g
    0 test.1g
  5. du --apparent-size test.1g
    1048576 test.1g
Comment by Marko Mäkelä [ 2021-10-25 ]

I am sorry for the extremely long delay.

thiru in MDEV-25734 proved that the XFS file system is more reluctant to punch holes than ext4fs. Which file system are you using? Is any space being saved according to the following command, between the two values of page_compressed?

ls -ls model_chart.ibd

Note: You should never disable innodb_doublewrite. It is needed for safe recovery, perhaps except when you are using innodb_page_size=4k or a special file system or block device that will never allow a write to be interrupted. Normally in Linux, I believe that writes can be terminated at any multiple of 4096 bytes. The I/O latency overhead of the doublewrite buffer was reduced in MariaDB Server 10.5.

See also https://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.html and MDEV-22839.

Comment by Guillaume Lefranc [ 2021-10-27 ]

Thanks for updating the issue, I have read MDEV-25734 which I also follow, and indeed using EXT4 the issue isn't reproducible. I guess it's an XFS issue then. Reaching out to the XFS maintainers could be interesting, I admit I have no clue myself why XFS is not handling sparse files as it should.

NB. doublewrite was just disabled as a test, no worries here!

Generated at Thu Feb 08 08:26:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.