Mysql数据库引擎innodb转换为TokuDB
一，先安装带有tokudb引擎的mariadb数据库 二，调整内存使用的比率及删除外键 三，生成转换tokudb的脚本 四，更改tokudb的参数
说明
阿里云mysql数据库innodb引擎转换为tokudb
TokuDB是一个高性能、写密集型引擎，提供了更高的压缩和更好的性能 The TokuDB storage engine is for use in high-performance and write-intensive environments, offering increased compression and better performance.
使用须知 1、 TokuDB可以大幅度降低存储使用量和IOPS开销 2、 TokuDB支持在线DDL，添加/删除列和索引不会引起阻塞 3、 TokuDB无法支持外键Foreign Key 4、 TokuDB不适用于大量读取的场景
一，先安装带有tokudb引擎的mariadb数据库
install mariadb
# cat /etc/centos-release
CentOS release 6.7 (Final)
# vim /etc/yum.repos.d/mariadb.repo
# MariaDB 5.5 CentOS repository list - created 2013-08-11 14:22 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
# yum -y install MariaDB
Server version: 5.5.50-MariaDB MariaDB Server
或者在此网站下载mariadb安装包
http://archive.mariadb.org/
tokudb引擎依赖ha_tokudb.so，此模块在/usr/lib64/mysql/plugin/ha_tokudb.so，因此初始化mysql：
/usr/bin/mysql_install_db --user=mysql --datadir=/var/mysql/data --basedir=/usr --log-output=file --plugin-dir=/usr/lib64/mysql/plugin/ --log-error=/var/log/mysql/mysql-error.log --pid-file=/var/log/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --defaults-file=/etc/my.cnf
centos系统参数调整：
echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag
echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
启动mysql查看默认参数：
# service mysql start
Starting MySQL.. SUCCESS!
# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.50-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW VARIABLES LIKE 'tokudb_version'\G
*************************** 1. row ***************************
Variable_name: tokudb_version
Value: tokudb-7.5.7
MariaDB [(none)]> SHOW VARIABLES LIKE '%tokudb%';
+---------------------------------+--------------+
| Variable_name | Value |
+---------------------------------+--------------+
| tokudb_alter_print_error | OFF |
| tokudb_analyze_delete_fraction | 1.000000 |
| tokudb_analyze_time | 5 |
| tokudb_block_size | 4194304 |
| tokudb_bulk_fetch | ON |
| tokudb_cache_size | 134217728 |
| tokudb_check_jemalloc | 1 |
| tokudb_checkpoint_lock | OFF |
| tokudb_checkpoint_on_flush_logs | OFF |
| tokudb_checkpointing_period | 60 |
| tokudb_cleaner_iterations | 5 |
| tokudb_cleaner_period | 1 |
| tokudb_commit_sync | ON |
| tokudb_create_index_online | ON |
| tokudb_data_dir | |
| tokudb_debug | 0 |
| tokudb_directio | OFF |
| tokudb_disable_hot_alter | OFF |
| tokudb_disable_prefetching | OFF |
| tokudb_disable_slow_alter | OFF |
| tokudb_empty_scan | rl |
| tokudb_fs_reserve_percent | 5 |
| tokudb_fsync_log_period | 0 |
| tokudb_hide_default_row_format | ON |
| tokudb_killed_time | 4000 |
| tokudb_last_lock_timeout | |
| tokudb_load_save_space | ON |
| tokudb_loader_memory_size | 100000000 |
| tokudb_lock_timeout | 4000 |
| tokudb_lock_timeout_debug | 1 |
| tokudb_log_dir | |
| tokudb_max_lock_memory | 16777216 |
| tokudb_optimize_index_fraction | 1.000000 |
| tokudb_optimize_index_name | |
| tokudb_optimize_throttle | 0 |
| tokudb_pk_insert_mode | 1 |
| tokudb_prelock_empty | ON |
| tokudb_read_block_size | 65536 |
| tokudb_read_buf_size | 131072 |
| tokudb_read_status_frequency | 10000 |
| tokudb_row_format | tokudb_zlib |
| tokudb_rpl_check_readonly | ON |
| tokudb_rpl_lookup_rows | ON |
| tokudb_rpl_lookup_rows_delay | 0 |
| tokudb_rpl_unique_checks | ON |
| tokudb_rpl_unique_checks_delay | 0 |
| tokudb_support_xa | ON |
| tokudb_tmp_dir | |
| tokudb_version | tokudb-7.5.7 |
| tokudb_write_status_frequency | 1000 |
+---------------------------------+--------------+
50 rows in set (0.00 sec)
MariaDB [(none)]> show engine tokudb status;
参数设置参考 https://mariadb.com/kb/en/mariadb/tokudb-system-variables/ https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html
二，调整内存使用的比率及删除外键
1,阿里云的rds数据库服务器，有loose_tokudb_buffer_pool_ratio这个参数，如果数据库由innodb或者myisam转为tokudb，需要调整内存的使用比率，下面为阿里云rds的调整：
设置loose_tokudb_buffer_pool_ratio为合适的比例，也就是tokudb占用tokudb与innodb共用缓存的比例，默认在tokudb不使用的情况下是0，如果全部都用tokudb可以改为100，也可以在innodb转换tokudb前根据下面公式来计算：
select sum(data_length) into @all_size from information_schema.tables where engine='innodb';
select sum(data_length) into @change_size from information_schema.tables where engine='innodb' and concat(table_schema, '.', table_name) in ('XX.XXXX', 'XX.XXXX', 'XX.XXXX');
select round(@change_size/@all_size*100);
举个例子说明：
select sum(data_length) into @innodb_size from information_schema.tables where engine='innodb';
select sum(data_length) into @change_size from information_schema.tables where engine='innodb' and concat(table_schema, '.', table_name) in ('databasename.tablename');
select round(@tokudb_size/(@innodb_size+@tokudb_size)*100);
更改完这个参数后再开始转换引擎，如果是一个新的数据库，此步骤忽略
2,mariadb没有loose_tokudb_buffer_pool_ratio参数，我们可以修改配置文件，如下操作，参数根据实际情况设置：
# egrep -v "^$|^#" /etc/my.cnf
[client-server]
!includedir /etc/my.cnf.d
# egrep -v "^$|^#" /etc/my.cnf.d/server.cnf
[server]
innodb_buffer_pool_size = 512M
[mysqld]
[embedded]
[mysqld-5.5]
[mariadb]
[mariadb-5.5]
# egrep -v "^$|^#" /etc/my.cnf.d/tokudb.cnf
[mariadb]
tokudb_cache_size=512M
3，因为tokudb不支持外键，所以转换引擎之前删除
echo "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') FROM information_schema.table_constraints WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_SCHEMA LIKE 'prefix_of_my_tables_%'" | mysql ${MYSQL_CONN} | grep -v CONCAT > drop_all_foreign.sql
三，生成转换tokudb的脚本
1，直接生成alter的语句，用screen后台执行
MYSQL_USER=bbotte
MYSQL_PASS=bbotte.com
MYSQL_PORT=3306
MYSQL_HOST=127.0.0.1
MYSQL_CONN=" -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -P${MYSQL_PORT}"
SQLSTMT="SELECT CONCAT('mysql ${MYSQL_CONN} -e\"ALTER TABLE ',table_schema,'.', table_name,' ENGINE=TokuDB;\"') InnoDBConversionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY data_length"
mysql ${MYSQL_CONN} -e"${SQLSTMT}" >> tokudb.sh
2，如果用percona-toolkit的话，可以这样生成：
wget https://www.percona.com/downloads/percona-toolkit/2.2.18/deb/percona-toolkit_2.2.18-1.tar.gz
tar -xzf percona-toolkit_2.2.18-1.tar.gz
cd percona-toolkit-2.2.18/
yum install perl-DBD-MySQL perl-Time-HiRes
perl Makefile.PL
make
make install
pt-online-schema-change -h
下面为生成的shell
MYSQL_USER=bbotte
MYSQL_PASS=bbotte.com
MYSQL_PORT=3306
MYSQL_HOST=127.0.0.1
MYSQL_CONN=" -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -P${MYSQL_PORT}"
SQLSTMT="SELECT CONCAT('pt-online-schema-change ','${MYSQL_CONN}',' --no-version-check --execute --alter ','\"ENGINE=TokuDB\"',' D=',table_schema,',t=',table_name,' --recursion-method=none --no-check-replication-filters --quiet --critical-load=\"Threads_running=300\" ') InnoDBConversionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY data_length"
mysql ${MYSQL_CONN} -e"${SQLSTMT}" >> pt-tokudb.sh
还有几个参数对tokudb的转换速度影响较大 tokudb_cache_size: Size in bytes of the TokuDB cache，相当于innodb的innodb_buffer_pool_size tokudb_load_save_space : default is off and should be left alone unless you are low on disk space. tokudb_cache_size : if unset the TokuDB will allocate 50% of RAM for it’s own caching mechanism, we generally recommend leaving this setting alone. As you are running on an existing server you need to make sure that you aren’t over-committing memory between TokuDB, InnoDB, and MyISAM.
四，更改tokudb的参数
1，更改默认db引擎 set global default_storage_engine=TokuDB;
2，更改tokudb的使用内存 loose_tokudb_buffer_pool_ratio=100(阿里云rds) tokudb_cache_size=物理内存的60%
转换完成后，数据库的占用空间是以前的30%，压缩效果挺大的，更多信息请持续关注。
可以参考的资料：
MariaDB的tokudb介绍 percona的tokudb引擎介绍 RDS TokuDB小手册 RDS MySQL空间优化最佳实践 Percona TokuDB – Documentation
2016年07月16日 于 linux工匠 发表