主页 / dba / 问题 / 164879
Accepted
nelaaro
Asked: 2017-02-21 03:07:52 +0800 CST

Tokudb ROW_FORMAT 未被接受

  • 772

我正在尝试创建一些 tokudb 表来试验不同的行格式选项，以比较可用的压缩。

https://www.percona.com/doc/percona-server/5.7/tokudb/using_tokudb.html

我已经尝试了以下所有
TOKUDB_SNAPPY
TOKUDB_ZLIB
TOKUDB_DEFAULT

没有效果。

如果我只是忽略它，则表是使用 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
mysql mariadb

1 个回答

  1. Best Answer

    将 MariaDB 与 tokudb 插件一起使用时，语法为

    COMPRESSION=TOKUDB_LZMA并不是ROW_FORMAT=TOKUDB_LZMA

    从Mariadb 网站，

    DDL 语法不同。Tokutek 的二进制文件具有修补的 SQL 解析器，而 MariaDB 中的 TokuDB 使用特殊的存储引擎 API 扩展。因此，在 Tokutek 二进制文件中，您编写 CLUSTERED KEY（列），例如 ROW_FORMAT=TOKUDB_LZMA。在 MariaDB 中，您编写 KEY（列）CLUSTERING=YES 和 COMPRESSION=TOKUDB_LZMA。

    该页面还提供了有关实际检查压缩格式的更多信息。

    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 } }'

    上面的不同版本带有表名，以便更容易地检查特定的表名。

    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

    会产生像

    head /var/tmp/tables.compressiontype.lst 
TableName   ./database/TableName-key-entity_index   11
TableName   ./database/TableName-main   11

    各种整数的含义可以在github repo中找到

        // compression methods
printf("typedef enum toku_compression_method {\n");
printf("    TOKU_NO_COMPRESSION = 0,\n");  // "identity" compression
printf("    TOKU_SNAPPY_METHOD  = 7,\n");  // google snappy
printf("    TOKU_ZLIB_METHOD    = 8,\n");  // RFC 1950 says use 8 for zlib.  It reserves 15 to allow more bytes.
printf("    TOKU_QUICKLZ_METHOD = 9,\n");  // We use 9 for QUICKLZ (the QLZ compression level is stored int he high-order nibble).  I couldn't find any standard for any other numbers, so I just use 9. -Bradley
printf("    TOKU_LZMA_METHOD    = 10,\n");  // We use 10 for LZMA.  (Note the compression level is stored in the high-order nibble).
printf("    TOKU_ZLIB_WITHOUT_CHECKSUM_METHOD = 11,\n"); // We wrap a zlib without checksumming compression technique in our own checksummed metadata.
printf("    TOKU_DEFAULT_COMPRESSION_METHOD = 1,\n");  // default is actually quicklz
printf("    TOKU_FAST_COMPRESSION_METHOD = 2,\n");  // friendlier names
printf("    TOKU_SMALL_COMPRESSION_METHOD = 3,\n");
printf("} TOKU_COMPRESSION_METHOD;\n");
    • 3

相关问题