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 <table-name> row_format=<row-format>
<row-format> is one of the following:
tokudb_zlib: Compress using the
zliblibrary, which provides mid-range compression and CPU utilization.
tokudb_quicklz: Compress using the
quicklzlibrary, which provides light compression and low CPU utilization.
tokudb_lzma: Compress using the
lzmalibrary, 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 <table-name> compression=<row-format>
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).