There are currently 2 implementations of the "trim" functionality in MariaDB

• 10.1 . The implementation is based on DeviceIoControl(FSCTL_FILE_LEVEL_TRIM). The intention is to send TRIM or SCSI UNMAP command directly to the storage for the file blocks that are not used anymore. The downside of this method is that (as it seems after testing) it is almost universally unimplemented, it does not free space, on FS level, and it is not possible to actually check whether the blocks are freed. Besides, that functionality was incorrectly implemented, with DeviceIoControl passing NULL for OVERLAPPED parameter on a file opened for asynchronous access.

• 10.2 The implementation is taken from MySQL 5.7, and based on NTFS sparse files, DeviceIoControl(FSCTL_SET_SPARSE). The downside is that it does not actually work in default configuration, because the actual "trim" DeviceIoControl(FSCTL_SET_ZERO_DATA) would only write binary zeroes rather than making a hole(returning unused space to FS). The effect comes from implementation of sparse files, only large range "holes" (16*cluster size = 64KB in default NTFS configuration) to actually free the space.

There is however a much simpler possible implementation based on NTFS transparent compression DeviceIoControl(FSCTL_SET_COMPRESSION). It works similarly to the homebrew innodb compression, and similar to sparse files, in that it does not allocate space in FS if blocks can be compressed. Besides sparse file optimization (compressing runs of 0 bytes), it also features data compression, is optimized for random IO, and space savings can actually be seen in explorer, or programmatically retrieved with GetCompressedFileSize(). The downside is that file gets fragmented, but this is the problem any sparse file implementation, and "punch hole" will have to face.

The purpose of this work is to enable NTFS file compression whenever PAGE_COMPRESSED option is used during CREATE TABLE, or ALTER TABLE.

Handling encryption with compression.

Usually, the intended use of compression on Windows would be this . User enables PAGE_COMPRESSION=1, but sets innodb_compression_algorithm=none. The DeviceIoControl(FSCTL_SET_COMPRESSION) will ensure that it is NTFS that actually compressed the data, and does the "trim". There will be no homebrew compression inside Innodb in this case.

However, if data is encrypted, it won't compressing well. In this case, user would need to set innodb_compression_algorithm=zlib, so innodb compressed data. NTFS compression in this case would not be able to compress already compressed data. However, it still can handle runs of binary zeros, so it will do the "trim" functionality. My tests verified that NTFS compression on top of Innodb compression works alright, and is able to save about the same (or even slightly more) space as NTFS compression alone. This, at least in theory, should add a little overhead on NTFS side (it would be trying unsuccessfully to compress already compressed data), that needs to be measured.