Herr Bischoff

Syntax Error on ALTER TABLE ROW_FORMAT=TOKUDB_LZMA

The MariaDB 5.5.37 TokuDB 7.1.6 Users Guide lists under Chapter 3.5.1 “Changing a table’s compression” the following MySQL command:

ALTER TABLE <tablename> row_format=<rowformat>

Where <rowformat> is one of the following:

tokudb_zlib: Compress using the zlib library, which provides mid-range compression and CPU utilization. tokudb_quicklz: Compress using the quicklz library, which provides light compression and low CPU utilization. tokudb_lzma: Compress using the lzma library, which provides the highest compression and high CPU utilization. tokudb_uncompressed: This setting turns off compression and is useful for tables with uncompressible data.

Unfortunately this results in a plain and unfriendly

Error in query (1064): Syntax error near 'tokudb_lzma' at line 1

Looking around all over the net, I tried one variant that worked like a charm:

ALTER TABLE <tablename> compression=<rowformat>

Just to point out the obvious, the key here is using compression instead of row_format. Weird. To be fair, I’m using MariaDB 10.0.11 (on Debian 7.5), not 5.5.37, but this really shouldn’t be happening.

By the way: TokuDB’s lzma compression on database tables is freaking awesome. Some tables got compressed up to 75%, meaning that now they only occupy a quarter of the disk space they used to. I have not been able to find any reduction in performance due to the compression. Just imagine the very real cost savings when running a high performance database from precious flash memory based storage (SSDs).