Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.32, 10.2.14
-
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
- relates to
-
MDEV-22839 ROW_FORMAT=COMPRESSED vs PAGE_COMPRESSION=1 - size comparison
-
- Open
-
-
-
- Closed
-
Activity
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.
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.
Yes punching holes works fine:
- fallocate -l 1G test.1g
- du test.1g
1048576 test.1g - fallocate -p -o 0 -l 1g test.1g
- du test.1g
0 test.1g - du --apparent-size test.1g
1048576 test.1g
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.
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!
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