I am trying to create some tokudb tables to experiment with the different row format options to compare the compression available.
https://www.percona.com/doc/percona-server/5.7/tokudb/using_tokudb.html
I have tried all the following
TOKUDB_SNAPPY
TOKUDB_ZLIB
TOKUDB_DEFAULT
With no effect.
If I just ignore it the tables are created with row_fromat = fixed.
MariaDB [eventlog]> show VARIABLES like "%row_format%";
+--------------------------------+-------------+
| Variable_name | Value |
+--------------------------------+-------------+
| tokudb_hide_default_row_format | ON |
| tokudb_row_format | tokudb_zlib |
+--------------------------------+-------------+
MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB, ROW_FORMAT=TOKUDB_DEFAULT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_DEFAULT' at line 1
MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ROW_FORMAT=tokudb_default;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tokudb_default' at line 1
MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB;
Query OK, 0 rows affected (0.09 sec)
MariaDB [eventlog]> show table status from eventlog\G;
*************************** 1. row ***************************
Name: stable1
Engine: TokuDB
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 9223372036854775807
Index_length: 0
Data_free: 18446744073709551615
Auto_increment: NULL
Create_time: 2017-02-20 12:26:18
Update_time: 2017-02-20 12:26:18
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
MariaDB [eventlog]> ALTER TABLE stable1 ROW_FORMAT=TOKUDB_SNAPPY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_SNAPPY' at line 1
version | 10.1.21-MariaDB
tokudb_version | 5.6.34-79.1
Best Answer
When using MariaDB with tokudb plugin, syntax is
COMPRESSION=TOKUDB_LZMA
and notROW_FORMAT=TOKUDB_LZMA
From Mariadb website,
The DDL syntax is different. While binaries from Tokutek have the patched SQL parser, TokuDB in MariaDB uses the special Storage Engine API extension. Thus in Tokutek binaries you write CLUSTERED KEY (columns) and, for example, ROW_FORMAT=TOKUDB_LZMA. And in MariaDB you write KEY (columns) CLUSTERING=YES and COMPRESSION=TOKUDB_LZMA.
That page also provides more info on actually checking the compression formats.
mysql -sNe 'SELECT dictionary_name, internal_file_name FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"' |perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /var/lib/mysql/$F[1]); foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[0]."\t".$1."\n"; last } }'
Different version of the above with tables names to make it easier to get check specific table names.
mysql -sNe 'SELECT dictionary_name, internal_file_name, table_name, " " FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"' | perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /srv/mysql/data/$F[1]); foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[2]."\t".$F[0]."\t".$1."\n"; last } }' > /var/tmp/tables.compressiontype.lst
Will produce out put like
The meaning of the various integers can be found in the github repo