Hello All,

I have compiled mariadb 10.1.4 from source to have a play with innodb page compression. Not sure if these are bug or misunderstanding on my part, probably a combination of both…

I am running in a CentOS 7 x64 VirtualBox VM 10.1.4-MariaDB-wsrep. The compile support for page compression seems to have worked ok…

SHOW STATUS LIKE ‘innodb_have%’;

My experiments have been with the following example database…

https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

When this is loaded onto a default install of mariadb (InnoDB Antelope / no compression) The db size is…

du -sh /var/lib/mysql/employees - 385MB

The when I run ALTER TABLE .. ROW_FORMAT=COMPRESSED the db size is…

du -sh /var/lib/mysql/employees - 119MB

Next I performed a series of reloads of this database after changing the compression algorithm. Note the total db size doesn’t change apart from when compression is lzma.

SET GLOBAL innodb_compression_algorithm='zlib';

du -sh /var/lib/mysql/employees - 385MB

SET GLOBAL innodb_compression_algorithm='lz4';

du -sh /var/lib/mysql/employees - 385MB

SET GLOBAL innodb_compression_algorithm='lzo';

du -sh /var/lib/mysql/employees - 385MB

SET GLOBAL innodb_compression_algorithm='lzma';

du -sh /var/lib/mysql/employees - 308MB

SET GLOBAL innodb_compression_algorithm='bzip2';

du -sh /var/lib/mysql/employees - 385MB

SET GLOBAL innodb_compression_algorithm='snappy';

du -sh /var/lib/mysql/employees - 385MB

SET GLOBAL innodb_compression_algorithm='lzma';

SET GLOBAL innodb_compression_level=9;

du -sh /var/lib/mysql/employees - 232M

SET GLOBAL innodb_compression_algorithm='zlib';

SET GLOBAL innodb_compression_level=9;

du -sh /var/lib/mysql/employees - 385MB

Note if you then try to execute..

ALTER TABLE departments ROW_FORMAT=COMPRESSED;

Table storage engine 'InnoDB' does not support the create option 'PAGE_COMPRESSED'

On further reading the documentation I noted the “Persistent Trim” section. So I tried…

SET GLOBAL innodb_compression_algorithm='zlib';

SET GLOBAL innodb_compression_level=9;

SET GLOBAL innodb_use_trim=ON;

du -sh /var/lib/mysql/employees - 320MB

Note the db size is smaller by 65MB

FYI innodb_page_compression_saved = 0 at this point. Next I tried the following settings..

vi /etc/my.cnf.d/innodb_compression.cnf

innodb_file_format=Barracuda

innodb_file_format_max=Barracuda

innodb_compression_algorithm=zlib

innodb_compression_level=9

innodb_use_trim=1

innodb_use_fallocate=1

du -sh /var/lib/mysql/employees - 232M

SET GLOBAL innodb_compression_algorithm='lz4';

du -sh /var/lib/mysql/employees - 232M

innodb_page_compression_saved = 0 # All other counters for SHOW STATUS LIKE '%compres%' are zero

SET GLOBAL innodb_compression_algorithm='lzma';

du -sh /var/lib/mysql/employees - 232M

SET GLOBAL innodb_compression_algorithm='bzip2';

du -sh /var/lib/mysql/employees - 232M

SET GLOBAL innodb_compression_algorithm='snappy';

du -sh /var/lib/mysql/employees - 232M

innodb_page_compression_saved = 0 # All other counters for SHOW STATUS LIKE '%compres%' are zero

information_schema tables INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM & INNODB_CMP_RESET all zero.

So I guess the questions here would be…

1. Why when trim is not used is the db size unchanged apart from lzma?

2. Why is the compressed size the same for all algorithms? Is the correct algorithm being used?

3. Can I confirm which algorithm has been used for a page. I can’t see anything in information_schema

4. Status variables / info schema not working?

5. ALTER TABLE … PAGE_COMPRESSED = 1 doesn’t work. Must I always do a full dump & load to enable page compression?

6. Lzma/9 compression is not that impressive. Is it meant to rival tokudb? (converting to tokudb results in a db size of ~ 54MB and this includes logs!)

Cheers,

Rhys Campbell

Database Administrator

TradingScreen, Inc.

23 York House, 5^th Floor

London WC2B 6UJ

Email: rhys.campbell@tradingscreen.com

Follow TradingScreen on Twitter , Facebook and our blog Trading Smarter

This message is intended only for the recipient(s) named above and may contain confidential information. If you are not an intended recipient, you should not review, distribute or copy this message. Please notify the sender immediately by e-mail if you have received this message in error and delete it from your system.