当前位置: 首页 > 工具软件 > TokuDB > 使用案例 >

tokudb引擎使用

步建茗
2023-12-01

环境:centos 7.3 + mysql 5.7.19

tokudb是一种高压缩率的引擎,压缩率最高可以达到90%,可以用于日志存储类db。

具体使用过程如下:

1,关闭大页面转换功能:

  • TokuDB如果在大页面转化启动的时候不会被启动。大页面转化是新内核版本的功能。可以通过以下语句检查时候启动。
$ cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never                #never表示未启动大页面转换,所以下面的关闭步骤不必执行
  • 关闭大页面转(建议写到 /etc/rc.local 中,重启后也可生效):
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

2,安装tuko引擎

[root@slave1 mysql]# yum localinstall -y Percona-Server-tokudb-57-5.7.19-17.1.el7.x86_64.rpm
[root@slave1 mysql]# ps_tokudb_admin --enable -uroot -proot                                #ps_tokudb_admin脚本会自动关闭大页面转化。
[root@slave1 mysql]# systemctl restart mysqld.service
[root@slave1 mysql]# ps_tokudb_admin --enable -uroot -proot
mysql> SHOW ENGINES;            #可以看到tukodb引擎安装成功
| TokuDB | YES | Tokutek TokuDB Storage Engine with Fractal Tree(tm) Technology | YES | YES | YES |
mysql> SELECT @@tokudb_version;

3, 迁移到TokuDB

> alter table yy engine=tokudb;

4,参数优化,修改my.cnf,添加tukodb相关部分:

#tokudb
#把TokuDB datadir以及logdir和MySQL的datadir分开,如果是rpm包安装的tokudb引擎,不可以加入下面两行,不然tukodb引擎就会失效。
#tokudb-data-dir = /data/mysql/tokudbdata
#tokudb-log-dir = /data/mysql/tokudbdata  
tokudb_cache_size=1G     ##指定tokuDB可使用的缓存类似innodb 的innodb_buffer_pool_size 默认是物理内存的50%
tokudb_commit_sync = 0
tokudb_directio = 1       ##写操作不经过缓存,直接写入磁盘
tokudb_read_block_size = 128K
tokudb_read_buf_size = 128K
innodb_use_native_aio=0        #如果不关闭aio,则执行set tuko_backup_dir命令会报错。

5,备份步骤:

mysql> SET TOKUDB_CHECKPOINT_LOCK=ON;
开始拷贝TokuDB的数据文件(不包含日志文件);
mysql> FLUSH TABLES WITH READ LOCK;
记录binlog位置,拷贝最新的binlog和TokuDB的日志文件(*.tokulog);
mysql> UNLOCK TABLES;
mysql> SET TOKUDB_CHECKPOINT_LOCK=OFF;
注意:TOKUDB_CHECKPOINT_LOCK,它的作用是允许拿到checkpoint锁,此时TokuDB的checkpoint会一直block到该锁释放(执行前要把tokudb_checkpoint_on_flush_logs关掉),目的是防止拷贝TokuDB数据文件的过程中做sharp checkpoint(注意:由于不做checkpoint,TokuDB的日志文件会逐渐增多),从而导致数据文件内部不一致(已拷贝的文件被修改)。整个热备过程中,只有步骤4是阻塞写的,但耗时较短。
  • 大实例备份恢复问题:
    当某个实例的数据量达到TB级,你会发现备库(基于备份)重搭后,启动会灰常灰常慢,因为他们都在recover redo-log,为什么呢?
mysql> SET TOKUDB_CHECKPOINT_LOCK=ON;
开始拷贝TokuDB的数据文件(不包含日志文件),由于拷贝TB级的数据非常耗时,在拷贝的时候数据库持续写入导致redo log增加甚至上万个
当TokuDB启动后,扫描和recover这几万个redo log将是灾难性的。
  • 解决这个问题比较简单,我们稍微调整下热备的顺序即可:
mysql> SET TOKUDB_CHECKPOINT_LOCK=ON;
mysql> FLUSH TABLES WITH READ LOCK;
记录binlog位置,拷贝最新的binlog和TokuDB的日志文件(*.tokulog);
mysql> UNLOCK TABLES;
开始拷贝TokuDB的数据文件(不包含日志文件) -移动到这里
mysql> SET TOKUDB_CHECKPOINT_LOCK=OFF;
这样在拷贝TokuDB数据文件的时候,就跟redo-log没半毛钱关系了,而且拷贝的redo-log数也大大减少!
  • 备份演练:
    yy表是tokudb引擎的表,在test数据库中
mysql> use test;
mysql> select * from yy;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

mysql> SET TOKUDB_CHECKPOINT_LOCK=ON;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000016 |      190 |              |                  | 5b820772-b886-11e7-a0b6-00163e004b29:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> flush logs;
[root@slave1 mysql]# cp tokudb.* /backup/
[root@slave1 mysql]# cp log000000000024.tokulog29  /backup/
mysql> unlock tables;
[root@slave1 mysql]# cp -r /data/mysql/test    /backup/
mysql> SET TOKUDB_CHECKPOINT_LOCK=OFF;
mysql> insert into yy values(100,'cccc');
mysql> drop table yy;
[root@slave1 mysql]#  cp mysql-bin.000017 /backup/       #备份mysql-bin.000016之后的所有binlog,还原后执行binlog即可以恢复增量数据
  • 恢复数据:
[root@slave1 test]# systemctl stop mysqld.service
[root@slave1 backup]# cp tokudb.* /data/mysql/
cp: overwrite '/data/mysql/tokudb.directory'? y
cp: overwrite '/data/mysql/tokudb.environment'? y
cp: overwrite '/data/mysql/tokudb.rollback'? y
[root@slave1 backup]# cp log000000000024.tokulog29  /data/mysql/
cp: overwrite '/data/mysql/log000000000024.tokulog29'? y
[root@slave1 backup]# rm -rf /data/mysql/test/
[root@slave1 backup]# cp -r test/ /data/mysql/
[root@slave1 backup]# chown -R mysql.mysql /data/mysql/*
[root@slave1 backup]# systemctl start mysqld.service
[root@slave1 backup]# mysql -uroot -p123456 test
mysql> select * from yy;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)
[root@slave1 backup]# mysqlbinlog mysql-bin.000017  -vv --base64-output=decode-rows  --skip-gtids |mysql -uroot -p123456             #恢复从备份之后的增量数据
  • 官方备份方法(原理是拷贝整个数据库所在文件夹,适用于一个实例中所有的表均为tukodb引擎的情况):
mysql> set tokudb_backup_dir='/backup';
官方还原方法:
[root@slave1 backup]# rm -rf /data/mysql/*
[root@slave1 backup]# cp -r /backup/*   /data/mysql/
[root@slave1 backup]# chown -R mysql.mysql /data/mysql/*
[root@slave1 backup]# systemctl start mysqld.service

6,排错:

mysql> set tokudb_backup_dir='/backup';
ERROR 1231 (42000): Variable 'tokudb_backup_dir' can't be set to the value of '/backup'
mysql> SELECT @@tokudb_backup_last_error;
+----------------------------+
| @@tokudb_backup_last_error |
+----------------------------+
|                         22 |
+----------------------------+
1 row in set (0.01 sec)

mysql>  SELECT @@tokudb_backup_last_error_string;
+---------------------------------------------------------------------+
| @@tokudb_backup_last_error_string                                   |
+---------------------------------------------------------------------+
| tokudb hot backup is disabled when innodb_use_native_aio is enabled |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)


[root@blockchain-101007377 /usr/share]
#ps_tokudb_admin --enable -uroot -proot
Checking SELinux status...
INFO: SELinux is disabled.
Checking if Percona Server is running with jemalloc enabled...
ERROR: Percona Server is not running with jemalloc, please restart mysql service to enable it and then run this script...

[root@blockchain-101007377 /usr/share]
#find / -name "*jemalloc*"
/var/lib/yum/yumdb/j/72d2365130e61a7ec5feac40814ba9dc1a0d48e1-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/jemalloc.html

[root@blockchain-101007377 /usr/share] vi /etc/my.cnf,添加以下选项后重启mysql:
[mysql_safe]
malloc_lib = /usr/lib64/libjemalloc.so.1

[root@blockchain-101007377 /usr/share] service mysql restart

7,安装修改版xtrabackup用于备份恢复tokudb引擎数据:https://github.com/XeLabs/tokudb-xtrabackup

#yum install flex bison automake autoconf libtool cmake libncurses-devel  libev-devel  vim-common  gcc-c++ gcc libaio-devel ncurses-devel libgcrypt-devel libcurl-devel libcurl -y
#cd tokudb-xtrabackup-2.4
#cmake .  -DBUILD_CONFIG=xtrabackup_release  -DWITH_BOOST=extra/boost/boost_1_59_0.tar.gz  -DWITH_MAN_PAGES=OFF  -DCMAKE_INSTALL_PREFIX=/opt/xtrabackup
#make VERBOSE=1
#make -j8
#make install
#cd /opt/xtrabackup/bin
#strip xtrabackup

#/usr/local/xtrabackup/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --parallel=2  /backup      #备份
#/usr/local/xtrabackup/bin/innobackupex --user=root --password=123456 --parallel=2  --apply-log --read-only    /backup      #准备还原
#/usr/local/xtrabackup/bin/innobackupex  --user=root --password=123456 --parallel=2 --copy-back   /backup                           #还原

8,token文件目录:

tokudb.directory                                 --表/索引文件信息
tokudb.environment                                 --TokuDB版本号信息
tokudb.rollback                                 --undo记录
log000000000001.tokulog27                         --redo记录
__tokudb_lock_dont_delete_me_*                 --文件锁,保证同一个datadir只能被一个TokuDB进程使用
test.frm
test_main_9f_2_1d.tokudb                        -----主文件
test_status_9f_3_1d.tokudb                        -----表元数据文件

token分区表文件:
data/_test_t2_P_p201508_key_c1_ba_4_1b.tokudb
data/_test_t2_P_p201508_key_id_ba_3_1b.tokudb
data/_test_t2_P_p201508_main_ba_2_1b.tokudb
data/_test_t2_P_p201508_status_ba_1_1b.tokudb
data/_test_t2_P_p201509_key_c1_bb_4_1b.tokudb
.........

可以看到每个分区表有4个文件:1个main文件,1个status文件,还有2个key索引文件。对于分区表来说,总的文件数目基本是:分区数目 * (1 + 1 + 索引数目)。

9,重要表:

TOKUDB_TRX表:
SELECT * FROM INFORMATION_SCHEMA.TOKUDB_TRX,INFORMATION_SCHEMA.PROCESSLIST WHERE trx_mysql_thread_id = id;
TOKUDB_LOCKS表:
SELECT id FROM INFORMATION_SCHEMA.TOKUDB_LOCKS,INFORMATION_SCHEMA.PROCESSLIST WHERE locks_mysql_thread_id = id;
TOKUDB_LOCK_WAITS表:
SELECT * FROM INFORMATION_SCHEMA.TOKUDB_LOCK_WAITS;
 类似资料: