Windows 10 Pro 1607 x64, 128GB RAM, i7 6800k MariaDB 10.2.2 Beta

The page compression does not have any effect on file size, even if it worked
If 16K page can be compressed to 3K, then 3K would be written instead of 16K, and benefit is the smaller write, this avoids write amplification on SSD.

MDEV-11254

I suggest to convert it into a documentation issue. The KB page should clearly explain what are benefits of using compression without trimming (if any) and what should be expected with such configuration.

jplindst, could you please provide necessary first-hand information to greenman? (Or if you want to edit the page yourself, you can obviously do it).

It is rather confusing expecting compression to reduce the size of the table. Coming from Oracle and searching on Table Compression for MariaDB https://mariadb.com/kb/en/mariadb/compression/ is the first thing that comes up. In Oracle compression a table reduces the size of that table and I'm really unclear what benefit Page Compression actually has other than reducing writes on SSD which still sounds like it reduces the size of the data written. The documentaiton compares it to InnoDB Compressed at https://mariadb.com/kb/en/mariadb/xtradbinnodb-storage-formats/#compressed which says "The compressed format results in the smallest data size in most cases." Also coming from Hadoop world where zlib and snappy are data compression formats for hive that do actually reduce the table data file size can be confusing as well.

Also in reference to Vladislav's comment wouldn't storing 3k of data be less than storing 16k of data? In my case there is no reduction in the size of the table no matter how arbitrarily large I create the table nor how compressible the data may be so how is it writing smaller blocks with compression and yet still writing the same amount of data to the tablespace.

Another note the blog at https://mariadb.org/significant-performance-boost-with-new-mariadb-page-compression-on-fusionio/ says "The compression means less data is transferred between disk and memory, and takes up less space on disk and in memory." specifiaclly says it takes up less space on disk.

I was commenting about writing 3K instead of 16K , but it will also be written at the very start of the page . File size does not change , a page would still look like (3K compressed data, unused 13K).

So does that mean compressed table files are mostly empty space? Or would you be able to write more in a page?

It should be a sparse file, on Windows NTFS specifically, with the holes being just marked as zeroed space, and not taking physical disk.

There is at least one obvious bug that would prevent it (wrong DeviceIoControl call, synchronous on a file opened for async IO), but may be more, I did not try to debug this yet.

If you're up to experiment, you can set NTFS compressed attribute on a newly created .ibd files, this would give you sparse file, that works about the same as innodb envisioned it should have worked.

Firstly, following does not work for 10.2, do not use 10.2 on production.

You should separate the two issues, is tablespace compressed or not and the actual file system storage usage.

In page compression tablespace pages are compressed. What actually is written to the file system depends on innodb-use-trim parameter (we could not allow to use page compression if trim is not enabled and if file system does not support sparse files)

• innodb-use-trim=0, always write 16K, so no storage savings and feature is not that useful. To be honest we should remove the parameter and either always trim or disable page compression if we can't.

• innodb-use-trim=1, write actual payload aligned to file system block size (512K-4K) and rest of 16K-payload is marked as unused using fallocate punch_hole i.e. by creating a hole. Holes are "useful" in the sense that they reduce disk space use (they make more space available), space here could mean address space. Now what really happens for that unused space depends naturally on file system. InnoDB will always issue a read operation with length of 16K, I'm not sure but I think it will based on file system metadata physically read only the payload and not the hole (need verification). Now is that unused space really usable I do not really know, is unused address space e.g. on SSD used so that next suitable write could write there (I will ask this from expert).

Does it take less space on device depends on device and file system. For tools it could look like we have reserved some space for file but some of that space is not used (i.e. ls -l <file> vs ls -ls --block-size=1 <file>).

Note in the blog at https://mariadb.org/significant-performance-boost-with-new-mariadb-page-compression-on-fusionio/ says "The compression means less data is transferred between disk and memory, and takes up less space on disk and in memory.", Here memory means that on InnoDB buffer pool we have only uncompressed data compared to row_compressed where you could have both. Naturally, page compression does not save memory compared to uncompressed. Less data is transferred and we physically write only the payload, punch_hole operation in my understanding only operates on file system metadata.

Expanded docs to describe this

MDEV-11520 improved the data file size extension so that files for page_compressed tables would be extended logically, not physically.

The page_compressed could still be improved further:

• MDEV-15527 page_compressed does not work if innodb_doublewrite=0
• MDEV-15528 Punch holes when pages are freed