一 前言 TokuDB 是一个高性能、支持MVCC的MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是数据压缩功能出色,对高写压力的支持,由美国TokuTek公司(http://www.tokutek.com/) 研发，该公司于2015年4月份被Percona收购,理所当然地提供了TokuDB版本的Percona Server。本文使用Peronca server 5.6.30 版本进行测试安装。
二 安装前的准备 请参考官方文档 ，
三 安装步骤 3.1 关闭系统的大页 - echo never > /sys/kernel/mm/transparent_hugepage/enabled
- echo never > /sys/kernel/mm/transparent_hugepage/defrag
- echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
- echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag
3.2 禁用SELINUX root@rac2:~# >vim /etc/selinux/config 设置SELINUX=disabled root@rac2:~# >setenforce 0 root@rac2:~# >getenforce 3.3 利用percona 的yum 源进行安装 - yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
- yum list | grep percona #检查yum源里面是否有 tokudb相关的rpm包
- yum install Percona-Server-tokudb-56.x86_64 -y
3.4 初始化数据库实例并启动数据库 - /usr/bin/mysql_install_db --user=mysql --datadir=/srv/my3306/data --basedir=/usr/ --defaults-file=/srv/my3306/my.cnf &
- /usr/bin/mysqld_safe --defaults-file=/srv/my3306/my.cnf --user=mysql &
3.5 使用ps_tokudb_admin安装tokudb 存储引擎 ，记得实例必须是启动状态的。 - root@rac2:/srv/my3306/data# >ps_tokudb_admin --enable -uroot -h127.0.0.1 -P 3306
- Checking SELinux status...
- INFO: SELinux is in permissive mode.
- 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 not set in the config file.
- (needed only if THP is not disabled permanently on the system)
- Checking TokuDB engine plugin status...
- INFO: TokuDB engine plugin is not installed.
- Adding thp-setting=never option into /etc/my.cnf
- INFO: Successfully added thp-setting=never option into /etc/my.cnf
- Installing TokuDB engine...
- INFO: Successfully installed TokuDB engine plugin. --> 说明Tokudb 存储引擎插件安装成功
3.6 登陆实例进行检查 - root@rac2:/srv/my3306/data# >my 3306
- Server version: 5.6.31-77.0-log Percona Server (GPL), Release 77.0, Revision 5c1061c
- mysql> show engines;
- +---------+---------+--------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints
- +---------+---------+--------------------------------------------------------------+--------------+------+------------+
- | TokuDB | YES | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology| YES | YES | YES |
- +---------+---------+---------------------------------------------------------------+-------------+------+------------+
- 10 rows in set (0.02 sec)
- mysql> SELECT @@tokudb_version;
- +------------------+
- | @@tokudb_version |
- +------------------+
- | 5.6.31-77.0 |
- +------------------+
- 1 row in set (0.00 sec)
四 测试4.1 压缩比例 创建innodb 和tokudb存储引擎的表，测试插入 和文件大小。
- mysql> create table t_tokudb (id int,val varchar(256)) engine=tokudb default charset utf8;
- Query OK, 0 rows affected (0.05 sec)
- mysql> insert into t_tokudb(val) select val from t_tokudb;
- Query OK, 262144 rows affected (2.32 sec)
- Records: 262144 Duplicates: 0 Warnings: 0
- mysql> CREATE TABLE `t_innodb` (
- -> `id` int(11) DEFAULT NULL,
- -> `val` varchar(256) DEFAULT NULL
- -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- Query OK, 0 rows affected (0.13 sec)
- mysql> insert into t_innodb select * from t_tokudb;
- Query OK, 1048576 rows affected (10.40 sec)
- Records: 1048576 Duplicates: 0 Warnings: 0
查看innodb 表和tokudb 表的大小
root@rac2:/srv/my3306/data# >du -sm _yang_t_tokudb_main_5_2_1d.tokudb
8 _yang_t_tokudb_main_5_2_1d.tokudb
root@rac2:/srv/my3306/data/yang# >du -sm t_innodb.ibd
149 t_innodb.ibd
root@rac2:/srv/my3306/data/yang# >
innodb 149M
tokudb 8M
压缩比达到惊人的 149/8 = 18:1. 因为测试例子中val 的值都是相同的，生产环境中val值不相同的会比较多，压缩比会有所减小。 4.2 存储引擎转换 - mysql> show create table t1 \G
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `id` int(10) NOT NULL AUTO_INCREMENT,
- `val` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
- 1 row in set (0.01 sec)
-
- mysql> alter table t1 engine=tokudb;
- Query OK, 4 rows affected (0.25 sec)
- Records: 4 Duplicates: 0 Warnings: 0
4.3 online ddl 测试 - mysql> alter table t_tokudb add name varchar(30) default 'a';
- Query OK, 0 rows affected (0.06 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> alter table t_tokudb add key idx_a(name); Query OK, 0 rows affected (4.79 sec) Records: 0 Duplicates: 0 Warnings: 0
- mysql> alter table t_tokudb drop key idx_a; Query OK, 0 rows affected (0.57 sec) Records: 0 Duplicates: 0 Warnings: 0