MySQL 存储引擎 TokuDB
TokuDB是 Tokutek 公司开发的基于ft-index（Fractal Tree Index）键值对的存储引擎。
它使用索引加快查询速度，具有高扩展性、高压缩性、支持事务，并支持hot scheme modification，具有以下特点：
1. 插入性能快20~80倍；
2. 压缩数据减少存储空间；
3. 数据量可以扩展到几个TB；
4. 不会产生索引碎片；
5. 支持hot column addition，hot indexing，mvcc
适用场景：
1. 如果你的记录数量过亿，使用TokuDB；
2. 如果你要存储旧的记录，使用TokuDB；
3. 如果你想要缩小数据占用的存储空间，使用TokuDB；
4. 如果你要存储blob，不要使用TokuDB，因为它限制记录不能太大；
5. 如果你注重update的性能，不要使用TokuDB，它没有Innodb快；
TokuDB 与 InnoDB 对比
注：上述总结和性能数据来源于淘宝梁智超的《RethinkDB-&-TokuDB调研测试报告.pptx》
TokuDB和InnoDB性能压测报告，可参考：InnoDB and TokuDB on AWS
[mysqld] table-open-cache-instances=32 table_open_cache=8000 innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 16G innodb-flush-log-at-trx-commit = 1 innodb_log_compressed_pages =0 innodb-file-per-table = 1 innodb-buffer-pool-size = 20G innodb_write_io_threads = 8 innodb_read_io_threads = 32 innodb_open_files = 1024 innodb_old_blocks_pct =10 innodb_old_blocks_time =2000 innodb_checksum_algorithm = crc32 innodb_file_format =Barracuda innodb_io_capacity=1500 innodb_io_capacity_max=2000 metadata_locks_hash_instances=256 innodb_max_dirty_pages_pct=90 innodb_flush_neighbors=1 innodb_buffer_pool_instances=8 innodb_lru_scan_depth=4096 innodb_sync_spin_loops=30 innodb-purge-threads=16
[mysqld] tokudb_read_block_size=16K tokudb_fanout=128 table-open-cache-instances=32 table_open_cache=8000 metadata_locks_hash_instances=256 [mysqld_safe] thp-setting=never
以下安装基于Percona 5.6.31-77.0
安装 libjemalloc 库
该库在EPEL中有提供
# wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm # rpm -ivh epel-release-latest-6.noarch.rpm # yum install jemalloc -y
通过yum安装，生成的库文件为/usr/lib64/libjemalloc.so.1
[root@localhost ~]# rpm -qa | grep jemalloc jemalloc-3.6.0-1.el6.x86_64 [root@localhost ~]# rpm -ql jemalloc-3.6.0-1.el6.x86_64 /usr/bin/jemalloc.sh /usr/lib64/libjemalloc.so.1 /usr/share/doc/jemalloc-3.6.0 /usr/share/doc/jemalloc-3.6.0/COPYING /usr/share/doc/jemalloc-3.6.0/README /usr/share/doc/jemalloc-3.6.0/VERSION /usr/share/doc/jemalloc-3.6.0/jemalloc.html
修改配置文件my.cnf
在[mysqld_safe]下设置malloc-lib变量
malloc-lib=/usr/lib64/libjemalloc.so.1
重启mysqld_safe进程
启动过程中，会输出以下信息：
160810 20:11:46 mysqld_safe Adding '/usr/lib64/libjemalloc.so.1' to LD_PRELOAD for mysqld
如果不安装该库的话，则在加载TokuDB插件时会报如下错误：
mysql> INSTALL PLUGIN tokudb SONAME 'ha_tokudb.so'; 2016-08-10 16:25:02 1861 [ERROR] TokuDB is not initialized because jemalloc is not loaded 2016-08-10 16:25:02 1861 [ERROR] Plugin 'TokuDB' init function returned error. 2016-08-10 16:25:02 1861 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed. 2016-08-10 16:25:02 1861 [Note] Shutting down plugin 'TokuDB' ERROR 1123 (HY000): Can't initialize function 'tokudb'; Plugin initialization function failed.
禁用 Transparent huge pages
查看当前内核是否已启用Transparent huge pages
# cat /sys/kernel/mm/transparent_hugepage/enabled [always] madvise never
always 表示已启用，never 表示禁用
如何禁用Transparent huge pages呢？
echo never > /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/defrag
如果没有禁用的话，在后续安装插件的时候会报错：
mysql> INSTALL PLUGIN tokudb SONAME 'ha_tokudb.so'; ERROR 1123 (HY000): Can't initialize function 'tokudb'; Plugin initialization function failed.
启用 TokuDB
Percona是5.6.17-66.0将TokuDB插件引入到自己的二进制版本中，5.6.19-67.0起才能作为GA版本使用。
5.6.22-72.0起开发了一个脚本ps_tokudb_admin来简化TokuDB的安装工作。
首先，来看看TokuDB如何手动安装
主要是加载插件，TokuDB插件名为ha_tokudb.so，默认放到plugin_dir下。
登录mysql客户端，执行如下命令
INSTALL PLUGIN tokudb SONAME 'ha_tokudb.so'; INSTALL PLUGIN tokudb_file_map SONAME 'ha_tokudb.so'; INSTALL PLUGIN tokudb_fractal_tree_info SONAME 'ha_tokudb.so'; INSTALL PLUGIN tokudb_fractal_tree_block_map SONAME 'ha_tokudb.so'; INSTALL PLUGIN tokudb_trx SONAME 'ha_tokudb.so'; INSTALL PLUGIN tokudb_locks SONAME 'ha_tokudb.so'; INSTALL PLUGIN tokudb_lock_waits SONAME 'ha_tokudb.so'; INSTALL PLUGIN tokudb_background_job_status SONAME 'ha_tokudb.so';
查看插件是否加载成功
mysql> show engines;
| TokuDB | YES | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology | YES | YES | YES |
mysql> show plugins;
| TokuDB | ACTIVE | STORAGE ENGINE | ha_tokudb.so | GPL | | TokuDB_file_map | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL | | TokuDB_fractal_tree_info | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL | | TokuDB_fractal_tree_block_map | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL | | TokuDB_trx | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL | | TokuDB_locks | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL | | TokuDB_lock_waits | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL | | TokuDB_background_job_status | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | GPL |
创建一张表，测试一下
mysql> create table test.test(id int) engine=tokudb; Query OK, 0 rows affected (0.23 sec)
至此，TokuDB手动安装完毕~
脚本安装
其实，Percona官方在5.6.22-72.0版本中提供了一个脚本，可用于自动安装TokuDB插件，涉及的操作包括禁用透明大页，加载插件。
该脚本在二进制包的bin目录下。
[root@localhost bin]# ./ps_tokudb_admin --help
This script is used for installing and uninstalling TokuDB plugin for Percona Server 5.6.
It can also be used to install or uninstall the Percona TokuBackup plugin (requires mysql server restart).
If transparent huge pages are enabled on the system it adds thp-setting=never option to my.cnf
to disable it on runtime.
Valid options are:
--user=user_name, -u user_name mysql admin username
--password[=password], -p[password] mysql admin password (on empty will prompt to enter)
--socket=path, -S path the socket file to use for connection
--host=host_name, -h host_name connect to given host
--port=port_num, -P port_num port number to use for connection
--defaults-file=file specify defaults file instead of guessing
--enable, -e enable TokuDB plugin and disable transparent huge pages in my.cnf
--enable-backup, -b enable Percona TokuBackup and add preload-hotbackup option to my.cnf
(this option includes --enable option)
--disable, d disable TokuDB plugin and remove thp-setting=never option in my.cnf
(this option includes --disable-backup option)
--disable-backup, r disable Percona TokuBackup and remove preload-hotbackup option in my.cnf
--help show this help
For TokuDB requirements and manual steps for installation please visit this webpage:
http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_installation.html
该脚本是用shell写的，参数也比较简单，其中，--enable-backup还包括安装TokuDB在线热备插件。
脚本安装过程如下
./ps_tokudb_admin --enable --host=127.0.0.1 --port=3307 --user=root -p Enter password: Continuing without password... Checking SELinux status... INFO: SELinux is disabled. Checking if Percona Server is running with jemalloc enabled... INFO: Percona Server is running with jemalloc enabled. Checking transparent huge pages status on the system... INFO: Transparent huge pages are currently disabled on the system. Checking if thp-setting=never option is already set in config file... INFO: Option thp-setting=never is set in the config file. Checking TokuDB engine plugin status... INFO: TokuDB engine plugin is installed.
总结
TokuDB最新版本是7.5.7，实际上现在是以插件的形式包含在Percona二进制发行版中。但该插件并不能直接在MySQL社区版中使用。
TokuDB基于MySQL 5.5.30源码，重新打包了个二进制版本，mysql-5.5.30-tokudb-7.1.0-linux-x86_64.tar.gz，基于TokuDB 7.1.0。
但也仅限如此，MySQL 5.6和5.7的社区版中并不能直接使用TokuDB插件。
以后若要使用较新版本的MySQL和TokuDB，只能使用Percona版本的。
下面是官档中的说明：Please note that Tokutek made some changes to the MySQL source that are required to either fix bugs or aid in performance, so if you are compiling from source you must use the Tokutek version of MySQL that is based on the MySQL 5.5.30 source.
问题与解决
1、系统环境
操作系统：CentOS 7.6.1810
MySQL版本：MariaDB-10.3.11
事务级别：read-commited
日志格式：row
2、报错信息
TokuDB: Huge pages are enabled, disable them before continuing
2018-08-18 16:54:05 0 [ERROR] TokuDB: Huge pages are enabled, disable them before continuing 2018-08-18 16:54:05 0 [ERROR] ************************************************************ 2018-08-18 16:54:05 0 [ERROR] 2018-08-18 16:54:05 0 [ERROR] @@@@@@@@@@@ 2018-08-18 16:54:05 0 [ERROR] @@' '@@ 2018-08-18 16:54:05 0 [ERROR] @@ _ _ @@ 2018-08-18 16:54:05 0 [ERROR] | (.) (.) | 2018-08-18 16:54:05 0 [ERROR] | ` | 2018-08-18 16:54:05 0 [ERROR] | > ' | 2018-08-18 16:54:05 0 [ERROR] | .----. | 2018-08-18 16:54:05 0 [ERROR] .. |.----.| .. 2018-08-18 16:54:05 0 [ERROR] .. ' ' .. 2018-08-18 16:54:05 0 [ERROR] .._______,. 2018-08-18 16:54:05 0 [ERROR] 2018-08-18 16:54:05 0 [ERROR] TokuDB will not run with transparent huge pages enabled. 2018-08-18 16:54:05 0 [ERROR] Please disable them to continue. 2018-08-18 16:54:05 0 [ERROR] (echo never > /sys/kernel/mm/transparent_hugepage/enabled) 2018-08-18 16:54:05 0 [ERROR] 2018-08-18 16:54:05 0 [ERROR] ************************************************************ 2018-08-18 16:54:05 0 [ERROR] Plugin 'TokuDB' init function returned error. 2018-08-18 16:54:05 0 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.
3、解决方法
根据错误日志提示，执行命令：
echo never > /sys/kernel/mm/transparent_hugepage/enabled
修改前后的对比
# cat /sys/kernel/mm/transparent_hugepage/enabled [always] madvise never # echo never > /sys/kernel/mm/transparent_hugepage/enabled # cat /sys/kernel/mm/transparent_hugepage/enabled always madvise [never]
更完善的解决，每次开机通过开机脚本执行，如下：
1）编辑 rc.local
vim /etc/rc.d/rc.local
2）添加两行
echo never > /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/defrag
3）授权 rc.local
chmod +x /etc/rc.d/rc.local
重启系统则自动修改，不用手工修改了
参考推荐：
Ubuntu安装配置MySQL （推荐）
版权所有: 本文系米扑博客原创、转载、摘录，或修订后发表，最后更新于 2019-02-23 05:00:45
侵权处理: 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！
转载注明: MySQL 存储引擎 TokuDB （米扑博客）