Feature: Page compression and multi-threaded flush for InnoDB/XtraDB

The code creates the following new InnoDB/XtraDB parameters:

innodb_use_atomic_writes=1 (use atomics. Required for proper page compression performance)

innodb_use_fallocate=1 (use fallocate to lay out files, required and enabled by default when atomics is enabled)

innodb_mtflush_threads=16 (number of threads used to do multi threaded page flushing (LRU and flush list))

innodb_use_mtflush=1 (use new multi-threaded flush feature)

Optional:
innodb_use_lz4=1 (available only if liblz4 is installed to system)

TRIM:
innodb_use_trim=1 (use trim to free up space of compressed blocks) :: NOTE:: Do not use for now !

New status variables:

compress_saved :: How many bytes is saved by page compression
compress_trim_sect512 :: How many trim operations are done with 512 bytes size
compress_trim_sect4096 :: How many trim operations are donw with 4096 bytes size
compress_pages_page_compressed :: How many pages are page compressed
compress_page_compressed_trim_op :: How many trim operations are done
compress_page_compressed_trim_op_saved :: How many trim operations were not done because earlier trim
compress_pages_page_decompressed :: How many pages compresseed with page compression are decompressed

Compressed tables are created with:

CREATE TABLE A(B INTEGER) ENGINE=InnoDB PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=6

Here PAGE_COMPRESSION_LEVEL is optional, if it is not given a default compression level is used (6 if nothing else is set up).

Tables to use atomic writes can be created with:

CREATE TABLE B(C INTEGER) ENGINE=InnoDB ATOMIC_WRITES=ON;

Tables not to use atomic writes can be created with:

CREATE TABLE C(D INTEGER) ENGINE=InnoDB ATOMIC_WRITES=OFF;

Tables using default atomic writes can be created with:

CREATE TABLE D(F INTEGER) ENGINE=InnoDB ATOMIC_WRITES=DEFAULT;

This is default and then innodb_use_atomic_writes value specifies do we really use atomic writes or not.

The core of the patch consists of multi threaded page flushing and page compression. fil_compress_page() and fil_decompress_page() gives a good overview of how the code works. Compression is moved to the lowest layer in InnoDB/XtraDB. Right before a page is written out it is compressed. The remainder of the 16k page that was freed due to compression is then trimmed via user space using fallocate(file, FALLOC_FL_PUNCH_HOLE | FALLOC_FL_KEEP_SIZE, off, trim_len). The underlying file system can then reallocate those trimmed blocks for other pages. The file system will report the trimmed space as free. When multi threaded page flushing is used, the code is able to compress pages in parallel thus utilizing as much CPU as possible.

Page compression is considered very simple compared to current row based compression. There’s no page splitting, tree rebalancing or compressed pages in memory. Page compression also supports the much faster lz4 algorithm. For storage efficiency, page compression is slightly better than current row based compression. With page compression, flash endurance is improved by 4x.

Note that the NVMFS file system is required for the trimming to work properly and efficiently. For now, you can test with innodb_use_trim=0. We expect to have an updated NVMFS binary for you within two weeks. We’d be very happy to receive your input on the code and testing results.