原创

将MySQL去重操作优化到极致之三弹连发(三):用rocksdb替代innodb

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://wxy0327.blog.csdn.net/article/details/54378575

        前面已经建立了索引,优化了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)安装依赖包
sudo yum install -y cmake gcc-c++ bzip2-devel libaio-devel bison \
zlib-devel snappy-devel
sudo yum install -y gflags-devel readline-devel ncurses-devel \
openssl-devel lz4-devel gdb git

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

(2)安装gflags

git clone https://github.com/gflags/gflags.git
cd gflags
mkdir build && cd build
ccmake ..

        之后按下面的提示操作

/*****
- 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.
******/

make
make install

(3)安装lz4

git clone https://github.com/lz4/lz4.git
cd lz4
make
make install

(4)升级gcc

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

# 下载源码包并解压
wget http://ftp.gnu.org/gnu/gcc/gcc-6.1.0/gcc-6.1.0.tar.bz2
tar -jxvf  gcc-6.1.0.tar.bz2

# 下载编译所需依赖库
cd gcc-6.1.0
./contrib/download_prerequisites
cd ..

# 建立编译输出目录
mkdir gcc-build-6.1.0

# 进入此目录,执行以下命令,生成makefile文件
cd  gcc-build-6.1.0
../gcc-6.1.0/configure --enable-checking=release --enable-languages=c,c++ --disable-multilib

# 编译,j后面的是核心数,编译速度会比较快
make -j4

# 安装
make install

# 切换GCC到新版
# 确定新安装的GCC的路径,一般默认在/usr/local/bin下
ls /usr/local/bin | grep gcc
# 添加新GCC到可选项,倒数第三个是名字,倒数第二个参数为新GCC路径,最后一个参数40为优先级,设大一些之后就自动使用新版了
update-alternatives --install /usr/bin/gcc gcc /usr/local/bin/x86_64-pc-linux-gnu-gcc-6.1.0 40

# 定义环境变量,需要设置,否则在编译MyRocks时会报错
CC='/usr/local/bin/x86_64-pc-linux-gnu-gcc-6.1.0'; export CC;

# 确认当前版本已经切换为新版
gcc -v

(5)更新libstdc++

        需要更新libstdc++,否则启动mysqld时会报类似“/usr/lib64/libstdc++.so.6: version 'GLIBCXX_3.4.19' not found”的错误。
cp /root/gcc-build-6.1.0/x86_64-pc-linux-gnu/libstdc++-v3/src/.libs/libstdc++.so.6.0.22 /usr/lib64/
cd /usr/lib64/
rm -r libstdc++.so.6
ln -s libstdc++.so.6.0.22 libstdc++.so.6
strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX

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


图一

3. 编译MyRocks源码
# 下载项目源码
git clone https://github.com/facebook/mysql-5.6.git
cd mysql-5.6
git submodule init
# 调用 git submodule update 用来更新 submodule 信息。
git submodule update

# 生成makefile文件
cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system \
-DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DENABLED_LOCAL_INFILE=1

# 编译
make -j8

# 安装
make install

4. 运行mtr tests测试
cd mysql-test
./mysql-test-run.pl --mem --async-client --parallel=16 --fast \
--max-test-fail=1000 --retry=0 --force --mysqld=--rocksdb \
--mysqld=--default-storage-engine=rocksdb --mysqld=--skip-innodb \
--mysqld=--default-tmp-storage-engine=MyISAM --suite=rocksdb

三、启动MyRocks
1. 配置my.cnf
[mysqld]
# 指定存储引擎
rocksdb
default-storage-engine=rocksdb
# 禁用innodb
skip-innodb
# 指定临时表使用的存储引擎
default-tmp-storage-engine=MyISAM
# 指定字符集排序规则
collation-server=latin1_bin
# 禁用binlog
skip_log_bin
# 禁用自动提交
autocommit=0

# 不做严格的排序规则检查,如果不设置,在varchar字段建立索引时会报错
rocksdb_strict_collation_check=0
# 启用批量数据装载
rocksdb_bulk_load=1
# 指定批次记录数
rocksdb_bulk_load_size=10000
# 允许并行写内存表
rocksdb_allow_concurrent_memtable_write = 1
# 设置写缓冲区大小
rocksdb_db_write_buffer_size = 1G
# 不立即刷新内存缓冲区中的数据
rocksdb_write_disable_wal = 1

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


2. 使用mysql_install_db初始化数据库
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/usr/local/mysql/my.cnf --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

3. 启动mysqld
useradd mysql
chown -R mysql:mysql /usr/local/mysql/data
/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql &

四、在MyRocks上进行去重测试
1. 建立源表
create table t_source  
(  
item_id int,  
created_time datetime,  
modified_time datetime,  
item_name varchar(20),  
other varchar(20)  
);  
2. 建立目标表
create table t_target like t_source; 

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

delimiter //      
create procedure sp_generate_data()    
begin     
    set @i := 1;   
    
    while @i<=500000 do  
        set @created_time := date_add('2017-01-01',interval @i second);  
        set @modified_time := @created_time;  
        set @item_name := concat('a',@i);  
        insert into t_source  
        values (@i,@created_time,@modified_time,@item_name,'other');  
        set @i:=@i+1;    
    end while;  
    commit;    
    
    set @last_insert_id := 500000;  
    insert into t_source  
    select item_id + @last_insert_id,  
           created_time,  
           date_add(modified_time,interval @last_insert_id second),  
           item_name,  
           'other'   
      from t_source;  
    commit;
end     
//      
delimiter ;   

4. 生成测试数据   

call sp_generate_data(); 

insert into t_source  
select * from t_source where item_id=1;  
commit; 

5. 建立索引

create index idx_sort on t_source(created_time,item_name,item_id);  
analyze table t_source;  

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

delimiter //
create procedure sp_unique(i smallint)    
begin     
    set @a:='0000-00-00 00:00:00';  
    set @b:=' ';  
	if (i<4) then
        insert into t_target  
        select * from t_source force index (idx_sort)  
         where created_time >= date_add('2017-01-01',interval (i-1)*125000 second) 
           and created_time < date_add('2017-01-01',interval i*125000 second) 
           and (@a!=created_time or @b!=item_name) 
           and (@a:=created_time) is not null 
           and (@b:=item_name) is not null  
         order by created_time,item_name;  
        commit;
    else 
	    insert into t_target  
        select * from t_source force index (idx_sort)  
         where created_time >= date_add('2017-01-01',interval (i-1)*125000 second) 
           and created_time <= date_add('2017-01-01',interval i*125000 second) 
           and (@a!=created_time or @b!=item_name) 
           and (@a:=created_time) is not null 
           and (@b:=item_name) is not null  
         order by created_time,item_name;  
        commit;
    end if;	
end     
// 

delimiter ;

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

#!/bin/bash
/usr/local/mysql/bin/mysql -vvv test -e "truncate t_target" &>/dev/null 
date '+%H:%M.%N'
for y in {1..4}
do
  sql="call sp_unique($y)"
  /usr/local/mysql/bin/mysql -vvv test -e "$sql" &>par_sql1_$y.log &
done
wait
date '+%H:%M.%N'

8. 执行去重

chmod 755 duplicate_removal.sh
./duplicate_removal.sh

        并行执行的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
文章最后发布于: 2017-01-12 15:50:50
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览