背景
zabbix 數據庫由阿里云 RDS 遷移至自建 DB，遷移過程中發現 RDS 存儲引擎為 tokudb ，手把手擼一遍
前置要求 Prerequisites
- 安裝 libjemalloc library
yum install jemalloc -y
通過 yum 安裝，生成的庫文件為 /usr/lib64/libjemalloc.so.1
[root@yunwei /www/server/data]# rpm -qa |grep jemalloc
jemalloc-3.6.0-1.el7.x86_64
[root@yunwei /www/server/data]# rpm -ql jemalloc-3.6.0-1.el7.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
- Transparent huge pages 關閉內存大頁
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
檢查結果
root@ptest:~# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
root@ptest:~# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
安裝倉庫源
Installing Percona Server for MySQL from Percona yum repository
- Install the Percona repository
$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Retrieving https://repo.percona.com/yum/percona-release-latest.noarch.rpmPreparing... ########################################### [100%]1:percona-release ########################################### [100%]
- To install Percona Server for MySQL with SElinux policies
$ yum install http://repo.percona.com/centos/7/RPMS/x86_64/Percona-Server-selinux-56-5.6.42-rel84.2.el7.noarch.rpm
- 測試倉庫可用性
yum list | grep percona
...
Percona-Server-56-debuginfo.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-client-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-devel-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-server-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-shared-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-test-56.x86_64 5.6.25-rel73.1.el6 @percona-release-x86_64
Percona-Server-shared-compat.x86_64 5.1.68-rel14.6.551.rhel6 percona-release-x86_64
...
- 安裝 Percona Serve
yum install Percona-Server-server-56
安裝 TokuDB
You can install the Percona Server for MySQL with TokuDB engine by using the apt/yum commands:
[root@centos ~]# yum install Percona-Server-tokudb-56.x86_64
# debian 系列 OS
root@wheezy:~# apt-get install percona-server-tokudb-5.6
啟用 TokuDB 引擎
Enabling the TokuDB Storage EngineOnce the TokuDB server package has been installed following output will be shown:
This release of Percona Server is distributed with TokuDB storage engine. Run the following script to enable the TokuDB storage engine in Percona Server:
ps_tokudb_admin --enable -u <mysql_admin_user> -p[mysql_admin_pass] [-S <socket>] [-h <host> -P <port>]
See http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_installation.html for more installation details
See http://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_intro.html for an introduction to TokuDB
Percona Server for MySQL 5.6.22-72.0 has implemented ps_tokudb_admin script to make the enabling the TokuDB storage engine easier. This script will automatically disable Transparent huge pages, if they’re enabled, and install and enable the TokuDB storage engine with all the required plugins. You need to run this script as root or with sudo. After you run the script with required parameters:
ps_tokudb_admin --enable -uroot -pPassw0rd
Following output will be displayed:
Checking if Percona server is running with jemalloc enabled...
>> Percona server is running with jemalloc enabled.
Checking transparent huge pages status on the system...
>> Transparent huge pages are currently disabled on the system.
Checking if thp-setting=never option is already set in config file...
>> Option thp-setting=never is not set in the config file.
>> (needed only if THP is not disabled permanently on the system)
Checking TokuDB plugin status...
>> TokuDB plugin is not installed.
Adding thp-setting=never option into /etc/mysql/my.cnf
>> Successfuly added thp-setting=never option into /etc/mysql/my.cnf
Installing TokuDB engine...
>> Successfuly installed TokuDB plugin.
If the script returns no errors, TokuDB storage engine should be successfully enabled on your server. You can check it out by running:
mysql> SHOW ENGINES;
...
| TokuDB | YES | Tokutek TokuDB Storage Engine with Fractal Tree(tm) Technology | YES | YES | YES |
...
To check if all the TokuDB plugins have been installed correctly you should run:
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 |
...
TokuDB storage engine version can be checked with:
mysql> SELECT @@tokudb_version;
+------------------+
| @@tokudb_version |
+------------------+
| 5.6.27-76.0 |
+------------------+
1 row in set (0.00 sec)
TokuDB storage engine has the same version as Percona Server for MySQL after 5.6.26-74.0 release.
mysql 配置文件
# Percona-5.6.17, TokuDB-7.1.6，用于Zabbix數據庫參考配置
# 我的服務器配置：E5-2620 * 2，64G內存，1T可用磁盤空間（建議datadir所在分區設置為xfs文件系統）
# TokuDB版本：Percona-5.6.17, TokuDB-7.1.6(插件加載模式)
#
[client]
port = 3306
socket = mysql.sock
#default-character-set=utf8
[mysql]
prompt="\u@\h \D \R:\m:\s [\d]>
#pager="less -i -n -S"
tee=/home/mysql/query.log
no-auto-rehash
[mysqld]
open_files_limit = 8192
max_connect_errors = 100000
#buffer & cache
table_open_cache = 2048
table_definition_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#innodb
#只有部分小表保留InnoDB引擎，因此InnoDB Buffer Pool設置為1G基本上夠了
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_status_file = 1
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
#tokudb
malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
plugin-dir = /usr/local/mysql/lib/mysql/plugin/
plugin-load=ha_tokudb.so
#把TokuDB datadir以及logdir和MySQL的datadir分開，美觀點，也可以不分開，注釋掉本行以及下面2行即可
tokudb-data-dir = /data/mysql/zabbix_3306/tokudbData
tokudb-log-dir = /data/mysql/zabbix_3306/tokudbLog
#TokuDB的行模式，建議用 FAST 就足夠了，如果磁盤空間很緊張，建議用 SMALL
#tokudb_row_format = tokudb_small
tokudb_row_format = tokudb_fast
tokudb_cache_size = 44G
#其他大部分配置其實可以不用修改的，只需要幾個關鍵配置即可
tokudb_commit_sync = 0
tokudb_directio = 1
tokudb_read_block_size = 128K
tokudb_read_buf_size = 128K
參考
- [TokuDB Installation](https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_installation.html)
- [Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS](https://www.percona.com/doc/percona-server/5.6/installation/yum_repo.html#yum-repo)
- [自建Percona5.7.23同步阿里云RDS(MySQL5.6)TokuDB數據庫](https://yq.aliyun.com/articles/667969)
- [RDS MySQL 物理備份文件恢復到自建數據庫](https://help.aliyun.com/knowledge_detail/41817.html)
