将MySQL去重操作优化到极致之三弹连发（三）：用rocksdb替代innodb

        前面已经建立了索引，优化了SQL语句，并将单线程变为多线程并行执行，去重时间由最初的35秒优化为3.5秒，是不是就到此为止呢？吴老师又使用了rocksdb存储引擎替代innodb的方法。这里有必要交代一下命题的背景。这道MySQL数据库优化的题目出自是阿里内部的竞赛题，当然我是听吴老师口述的，真正的题目及其竞赛规则与竞赛环境不甚明确，但有一条是允许自由选择MySQL存储引擎。在实际的生产环境中，几乎没有可能为了单一操作的优化而改变表的存储引擎，因为这样做的代价通常很高。

        顺便提一句，据刚刚得到的消息，吴老师在今天结束的决赛中获得了第三名的好成绩（
前两名都是数据库内核组的），祝贺！

一、MyRocks简介
MyRocks是FaceBook开源的MySQL分支。下面链接是一篇阿里系的介绍文章，在此不再赘述。
https://yq.aliyun.com/articles/59415
二、安装配置MyRocks

1. 支持的平台

官方支持OS：
CentOS 6.8
CentOS 7.2.x
经过验证的兼容编译器：
gcc 4.8.1
gcc 4.9.0
gcc 5.4.0
gcc 6.1.0
Clang 3.9.0
2. 安装前准备
（1）安装依赖包

1
2
3
4
5
1sudo yum install -y cmake gcc-c++ bzip2-devel libaio-devel bison \

2zlib-devel snappy-devel

3sudo yum install -y gflags-devel readline-devel ncurses-devel \

4openssl-devel lz4-devel gdb git

5

        这几个包缺一不可，gflags-devel和lz4-devel在yum中没有安装包，所以要下源码编译安装。

（2）安装gflags

1
2
3
4
5
1git clone https://github.com/gflags/gflags.git

2cd gflags

3mkdir build && cd build

4ccmake ..

5

        之后按下面的提示操作

/*****

• Press 'c' to configure the build system and 'e' to ignore warnings.
• Set CMAKE_INSTALL_PREFIX and other CMake variables and options.
• Continue pressing 'c' until the option 'g' is available.
• Then press 'g' to generate the configuration files for GNU Make.

******/

1
2
3
1make

2make install

3

（3）安装lz4

1
2
3
4
5
1git clone https://github.com/lz4/lz4.git

2cd lz4

3make

4make install

5

（4）升级gcc

        系统自带4.4.7，升级到任一验证过的编译器，这里升级到6.1.0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
1# 下载源码包并解压

2wget http://ftp.gnu.org/gnu/gcc/gcc-6.1.0/gcc-6.1.0.tar.bz2

3tar -jxvf  gcc-6.1.0.tar.bz2

4

5# 下载编译所需依赖库

6cd gcc-6.1.0

7./contrib/download_prerequisites

8cd ..

9

10# 建立编译输出目录

11mkdir gcc-build-6.1.0

12

13# 进入此目录，执行以下命令，生成makefile文件

14cd  gcc-build-6.1.0

15../gcc-6.1.0/configure --enable-checking=release --enable-languages=c,c++ --disable-multilib

16

17# 编译，j后面的是核心数，编译速度会比较快

18make -j4

19

20# 安装

21make install

22

23# 切换GCC到新版

24# 确定新安装的GCC的路径,一般默认在/usr/local/bin下

25ls /usr/local/bin | grep gcc

26# 添加新GCC到可选项，倒数第三个是名字，倒数第二个参数为新GCC路径，最后一个参数40为优先级，设大一些之后就自动使用新版了

27update-alternatives --install /usr/bin/gcc gcc /usr/local/bin/x86_64-pc-linux-gnu-gcc-6.1.0 40

28

29# 定义环境变量，需要设置，否则在编译MyRocks时会报错

30CC='/usr/local/bin/x86_64-pc-linux-gnu-gcc-6.1.0'; export CC;

31

32# 确认当前版本已经切换为新版

33gcc -v

34

（5）更新libstdc++

        需要更新libstdc++，否则启动mysqld时会报类似“/usr/lib64/libstdc++.so.6: version 'GLIBCXX_3.4.19' not found”的错误。

1
2
3
4
5
6
1cp /root/gcc-build-6.1.0/x86_64-pc-linux-gnu/libstdc++-v3/src/.libs/libstdc++.so.6.0.22 /usr/lib64/

2cd /usr/lib64/

3rm -r libstdc++.so.6

4ln -s libstdc++.so.6.0.22 libstdc++.so.6

5strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX

6

        查看包含的GLIBCXX结果如图一所示。

图一

3. 编译MyRocks源码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1# 下载项目源码

2git clone https://github.com/facebook/mysql-5.6.git

3cd mysql-5.6

4git submodule init

5# 调用 git submodule update 用来更新 submodule 信息。

6git submodule update

7

8# 生成makefile文件

9cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system \

10-DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DENABLED_LOCAL_INFILE=1

11

12# 编译

13make -j8

14

15# 安装

16make install

17

1
2
14. 运行mtr tests测试  

2

1
2
3
4
5
6
1cd mysql-test

2./mysql-test-run.pl --mem --async-client --parallel=16 --fast \

3--max-test-fail=1000 --retry=0 --force --mysqld=--rocksdb \

4--mysqld=--default-storage-engine=rocksdb --mysqld=--skip-innodb \

5--mysqld=--default-tmp-storage-engine=MyISAM --suite=rocksdb

6

1
2
1**三、启动MyRocks**  

2

1. 配置my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
1[mysqld]

2# 指定存储引擎

3rocksdb

4default-storage-engine=rocksdb

5# 禁用innodb

6skip-innodb

7# 指定临时表使用的存储引擎

8default-tmp-storage-engine=MyISAM

9# 指定字符集排序规则

10collation-server=latin1_bin

11# 禁用binlog

12skip_log_bin

13# 禁用自动提交

14autocommit=0

15

16# 不做严格的排序规则检查，如果不设置，在varchar字段建立索引时会报错

17rocksdb_strict_collation_check=0

18# 启用批量数据装载

19rocksdb_bulk_load=1

20# 指定批次记录数

21rocksdb_bulk_load_size=10000

22# 允许并行写内存表

23rocksdb_allow_concurrent_memtable_write = 1

24# 设置写缓冲区大小

25rocksdb_db_write_buffer_size = 1G

26# 不立即刷新内存缓冲区中的数据

27rocksdb_write_disable_wal = 1

28

        Oracle大师Thomas Kyte曾经说过大多数性能问题是设计不当引起的，尽管如此，作为DBA还是可以从数据库系统调整的角度做些有益的工作。上面这些针对rocksdb存储引擎所做的配置对去重实验的性能影响巨大。我从知道rocksdb到本实验才两天时间，谈不上多了解，只是有些直观的感受。例如，rocksdb_write_disable_wal感觉就和innodb_flush_log_at_trx_commit的作用差不多。

2. 使用mysql_install_db初始化数据库

1
2
1/usr/local/mysql/scripts/mysql_install_db --defaults-file=/usr/local/mysql/my.cnf --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

2

1
2
13. 启动mysqld  

2

1
2
3
4
1useradd mysql

2chown -R mysql:mysql /usr/local/mysql/data

3/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql &

4

1
2
1**四、在MyRocks上进行去重测试**  

2

1. 建立源表

1
2
3
4
5
6
7
8
9
1create table t_source  

2(  

3item_id int,  

4created_time datetime,  

5modified_time datetime,  

6item_name varchar(20),  

7other varchar(20)  

8);  

9

1
2
12. 建立目标表  

2

1
2
1create table t_target like t_source; 

2

3. 建立生成测试数据的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
1delimiter //      

2create procedure sp_generate_data()    

3begin     

4    set @i := 1;   

5    

6    while @i<=500000 do  

7        set @created_time := date_add('2017-01-01',interval @i second);  

8        set @modified_time := @created_time;  

9        set @item_name := concat('a',@i);  

10        insert into t_source  

11        values (@i,@created_time,@modified_time,@item_name,'other');  

12        set @i:=@i+1;    

13    end while;  

14    commit;    

15    

16    set @last_insert_id := 500000;  

17    insert into t_source  

18    select item_id + @last_insert_id,  

19           created_time,  

20           date_add(modified_time,interval @last_insert_id second),  

21           item_name,  

22           'other'   

23      from t_source;  

24    commit;

25end     

26//      

27delimiter ;   

28

4. 生成测试数据   

1
2
3
4
5
6
1call sp_generate_data(); 

2

3insert into t_source  

4select * from t_source where item_id=1;  

5commit; 

6

5. 建立索引

1
2
3
1create index idx_sort on t_source(created_time,item_name,item_id);  

2analyze table t_source;  

3

6. 建立并行执行的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
1delimiter //

2create procedure sp_unique(i smallint)    

3begin     

4    set @a:='0000-00-00 00:00:00';  

5    set @b:=' ';  

6   if (i<4) then

7        insert into t_target  

8        select * from t_source force index (idx_sort)  

9         where created_time >= date_add('2017-01-01',interval (i-1)*125000 second) 

10           and created_time < date_add('2017-01-01',interval i*125000 second) 

11           and (@a!=created_time or @b!=item_name) 

12           and (@a:=created_time) is not null 

13           and (@b:=item_name) is not null  

14         order by created_time,item_name;  

15        commit;

16    else 

17      insert into t_target  

18        select * from t_source force index (idx_sort)  

19         where created_time >= date_add('2017-01-01',interval (i-1)*125000 second) 

20           and created_time <= date_add('2017-01-01',interval i*125000 second) 

21           and (@a!=created_time or @b!=item_name) 

22           and (@a:=created_time) is not null 

23           and (@b:=item_name) is not null  

24         order by created_time,item_name;  

25        commit;

26    end if;   

27end     

28// 

29

30delimiter ;

31

7. 建立shell脚本文件duplicate_removal.sh

1
2
3
4
5
6
7
8
9
10
11
1#!/bin/bash

2/usr/local/mysql/bin/mysql -vvv test -e "truncate t_target" &>/dev/null 

3date '+%H:%M.%N'

4for y in {1..4}

5do

6  sql="call sp_unique($y)"

7  /usr/local/mysql/bin/mysql -vvv test -e "$sql" &>par_sql1_$y.log &

8done

9wait

10date '+%H:%M.%N'

11

8. 执行去重

1
2
3
1chmod 755 duplicate_removal.sh

2./duplicate_removal.sh

3

        并行执行的4个过程调用分别用时如图二所示。

图二

        可以看到，每个过程的执行时间均在2.4秒，因为是并行执行，
总的过程执行时间也小于2.5秒，比innodb速度提高了近三分之一。
参考：
AliOS编译安装MyRocks
Centos升级gcc4.4.7升级gcc4.8手记
解决类似/usr/lib64/libstdc++.so.6: version 'GLIBCXX_3.4.19' not found错误
https://github.com/MySQLOnRocksDB/mysql-5.6
https://github.com/facebook/mysql-5.6/wiki/Build-Steps
https://github.com/facebook/mysql-5.6/wiki/Getting-Started-with-MyRocks