使用分布式数据库集群做大数据分析之OneProxy

一、十亿数据，轻松秒出

实时监控领域有两个显著的特点，一是数据来源很多而且数据量大，有来自监控摄像头、GPS、智能设备等；二是需要实时处理。我们的客户在做实时处理时，就遇到这样的问题。客户的某个数据表中有10亿条记录，希望统计类查询的响应时间在30s以内，而使用单台MySQL的响应时间在300s以上。OneProxy特有的并行查询功能，让响应时间降到10s以内。如有疑问，请联系onesoft007（微信）

二、并行查询前提之分库分表

大家都说MySQL不具备并行查询能力，OneProxy做到了！

在互联网+的时代，分库分表已经成为行业的标准解决方案之一。在众多应用中，数据每天都以GB级别递增，其中若干个热点表会达到几十G或者几百G。众所周知，大的数据表在使用和维护时，存在诸多问题。

1）某些DDL操作，响应时间难以接受。添加、修改、删除数据表字段时，耗时太长（甚至达到几天），占用大量IO，导致影响线上业务；

2）性能难以提高。一张大表只能放在一台服务器上，而单台服务器的服务能力有限，导致系统的性能受制于单台服务器的能力；

3）统计分析，耗时太长。由于MySQL本身不支持单条SQL语句内的并发，因此一个统计分析型SQL语句，受制于全表扫描的速度，而这是难以接受的，尤其是表无法全部放在内存时。

对于上述问题，一个目前被广泛采用的解决方案，就是把一个大表拆成多个小表放到多个不同的数据库中，即分库分表，如图1。

　　　　图 1 分库分表

三、并行查询原理

OneProxy并行查询的实现依赖于其自身的分库分表功能。它通过内置的SQL解析器，将来自客户端的SQL查询改写成带有分片信息的多个查询并分发到不同的主机上并行执行，从而极大提高了响应速度，如图2。

　　　　　　　　图2 并行查询

 

四、部署

1） 多台linux主机，推荐Centos6 64bit

2）从公司官网下载安装文件（http://www.onexsoft.com/download） 
3） 安装

A）解压到指定目录

mv oneproxy-rhel5-linux64*  /usr/local/

tar zxvf oneproxy-rhel5-linux64*

B）更改启动文件 

cd oneproxy

cat demo.sh

       

#!/bin/bash

export ONEPROXY_HOME=/data/oneproxy

 

if [ -f ${ONEPROXY_HOME}/oneproxy.pid ]; then

   kill -9 `cat ${ONEPROXY_HOME}/oneproxy.pid`

fi

 

sleep 2

# valgrind --leak-check=full --show-reachable=yes \

${ONEPROXY_HOME}/oneproxy --proxy-address=:3307 --proxy-extra-address=:3308 \

  --proxy-master-addresses=192.168.1.119:3306@default \

  --proxy-user-list=test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test \

  --proxy-part-tables=${ONEPROXY_HOME}/part.txt \

  --proxy-charset=gbk_chinese_ci --proxy-found-rows \

  --proxy-group-policy=default:master-only \

  --event-threads=6 --proxy-group-security=default:0 \

  --log-file=${ONEPROXY_HOME}/oneproxy.log \

  --pid-file=${ONEPROXY_HOME}/oneproxy.pid

 

更改后

  

#/bin/bash

#

export ONEPROXY_HOME=/usr/local/oneproxy

 

if [ -f ${ONEPROXY_HOME}/oneproxy.pid ]; then

   kill -9 `cat ${ONEPROXY_HOME}/oneproxy.pid`

fi

 

sleep 2

# valgrind --leak-check=full --show-reachable=yes \

  ${ONEPROXY_HOME}/oneproxy --proxy-address=:3307 --proxy-extra-address=:3308 \

  --proxy-master-addresses=<IP1>:3306@data1 \

  --proxy-master-addresses=<IP2>:3306@data2 \

  --proxy-master-addresses=<IP3>:3306@data3 \

  --proxy-master-addresses=<IP4>:3306@data4 \

  --proxy-master-addresses=<IP5>:3306@data5 \

  --proxy-master-addresses=<IP6>:3306@data6 \

  --proxy-master-addresses=<IP7>:3306@data7 \

  --proxy-master-addresses=<IP8>:3306@data8 \

  --proxy-user-list=test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test \

  --proxy-part-tables=${ONEPROXY_HOME}/hashpart.txt \

  --proxy-charset=utf8_bin --proxy-found-rows \

  --proxy-group-policy=data1:master-only \

  --proxy-group-policy=data2:master-only \

  --proxy-group-policy=data3:master-only \

  --proxy-group-policy=data4:master-only \

  --proxy-group-policy=data5:master-only \

  --proxy-group-policy=data6:master-only \

  --proxy-group-policy=data7:master-only \

  --proxy-group-policy=data8:master-only \

  --event-threads=6  \

  --keepalive \

  --log-file=${ONEPROXY_HOME}/oneproxy.log \

  --pid-file=${ONEPROXY_HOME}/oneproxy.pid

 

注释：        

A)  --proxy-user-list=test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test 其中第一个test为连接proxy的用户名，该用户名也是用来连接后端数据库的用户名；1378F6CC3A8E8A43CA388193FBED5405982FBBD3为test用户的加密口令；最后一个test为各个数据库节点中的test数据库。 

B)  口令加密。启动OneProxy之后，进入管理端口,然后键入passwd <string>。

mysql -uadmin -pOneProxy -P4041 --protocol=TCP

passwd test

输出为：

 

C)  分表文件hashpart.txt

[

  {

        "table"   : "bigtable",

        "pkey"    : "id",

        "type"    : "int",

        "method"  : "hash",

        "partitions": 256,

        "groups": ["data1","data2","data3", "data4","data5","data6", "data7","data8"]

  }

]

 

其中table：拆分的表；pkey：分表列；type：分表列类型，目前支持的类型为int和char；method：分区方法，目前支持hash，range，list，global

4）. 安装数据节点，并赋予用户相应权限。

A） MySQL安装方法 请参考官方安装文档https://dev.mysql.com/doc/refman/5.6/en/linux-installation.html

如果你对线程池、SQL级别限流，秒杀解决方案等感兴趣，可以使用OneSQL。

下载地址：www.onexsoft.com/download ,解压替换系统中已有mysqld文件即可。具体使用方法见文档：http://www.onexsoft.com/OneSQL_Best_Practise.pdf。

    B）权限分配

     登录到各个底层数据库所在的linux主机上，运行如下命令：

mysql -e "grant all privileges on *.* to test@'%' identified by 'test' "

 

                      

5）. 启动

sh ./demo.sh

  检查启动输出

cat oneproxy.log

2015-05-04 19:38:15: (critical) plugin oneproxy 5.4.1-20150425 started
2015-05-04 19:38:15: (critical) valid config checksum = 88429892

 

6). 检查后端数据库节点状态

   进入管理端口（默认端口为4041）

mysql -uadmin -pOneProxy -P4041 --protocol=TCP

 

   然后键入

list backend;

可以看到输出为：

status这一栏为UP，表示集群正常。         

7）. 创建表

      登录OneProxy的转发端口

mysql -h<OneProxyIP> -P3307 -utest -ptest

     DDL语句：

CREATE TABLE `bigtable` (

            `id` bigint(20) NOT NULL,

            `start` datetime DEFAULT NULL,

            `icol3` int(11) DEFAULT NULL COMMENT ' 1-10 ',

            `icol4` int(11) DEFAULT NULL COMMENT ' 1-100 ',

            `icol5` int(11) DEFAULT NULL COMMENT ' 1-1000 ',

            `icol6` int(11) DEFAULT NULL,

            `vcol7` varchar(20) DEFAULT NULL,

            `vcol8` varchar(20) DEFAULT NULL,

            `vcol9` varchar(30) DEFAULT NULL,

            `vcol10` varchar(20) DEFAULT NULL,

            PRIMARY KEY (`id`)

          ) ENGINE=InnoDB DEFAULT CHARSET=latin1

执行这个语句，会看到报错： ERROR 1044 (42000): Access denied due to security policy, DDL disabled or DML restricted!

  解释：由于OneProxy为了安全起见，默认是禁止DDL语句的。

  解决办法：从OneProxy管理端口，运行如下命令

mysql  -h<OneProxyIP> -uadmin -pOneProxy -P4041 -e "set gaccess data1 0;set gaccess data2 0;set gaccess data3 0;set gaccess data4 0;set gaccess data5 0;set gaccess data6 0;set gaccess data7 0;set gaccess data8 0"

再次登录到OneProxy的转发端口，运行DDL命令即可成功。     

  经过上面的步骤之后，256个表就在8个底层数据库中创建好了，分别为bigtable_0 , bigtable_1 .....bigtable_255。

 

五、测试数据生成

     客户可以使用自己编写的程序来生成测试数据，也可以使用平民软件创始人（楼方鑫）编写的mydbtest软件。mydbtest不仅可以生成测试数据，也可以根据用户自定义的SQL语句去执行测试。程序下载地址：

http://www.onexsoft.com/software/mydbtest_linux64.zip

文档可以从百度云中找到：

http://pan.baidu.com/s/1mgJpukg#path=%252FOneSQL%252FDocument

然后找到 ”MySQL数据库测试工具“ 即可下载。具体的使用和详细的参数，请参考文档。

本文使用mydbtest来做数据装载工具，使用方法和配置文件如下

nohup ./mydbtest_linux64.bin query=insert.cnf degree=500 > /dev/null 2>&1 &

配置文件 insert.cnf

Option

  user test/test@<OneProxyIP>:3307:data1

  loop 100000000

  log insert_%p.log

  time 24h

declare

    vid bigseq 1  10000000000

    vstart TIMESTAMP -10 10

    vicol3 int 1 10

    vicol4 int 1 100

    vicol5 int 1 1000

    vicol6 int 1 100000

    vvcol7 char 10 19

    vvcol8 char 10 19

    vvcol9 char 10 29

    vvcol10 char 10 19

begin

insert into bigtable( id , start ,icol3, icol4 , icol5 , icol6 , vcol7, vcol8, vcol9, vcol10 )

values ( :vid , :vstart ,:vicol3, :vicol4 , :vicol5 , :vicol6 , :vvcol7, :vvcol8, :vvcol9, :vvcol10);

end

 

六、并行查询测试

       测试环境：

OS	Centos 6.3 x64
CPU	8Core 2.4GHZ
Memory	32G
Disk	RAID1 300G 7200r/min

 

MySQL 配置：

innodb_buffer_pool_size	24G
max_connections	4096
tcc_control_min_connections [OneSQL]	16

 

测试数据：

表名	bigtable
分表数	256
数据节点数	8
记录数	1,401,817,594
数据文件大小	25G * 8

 

 

测试结果

测试方法	响应时间
Select count(*) from bigtable   非并行	286.38s
Select /* parrallel */count(*) from bigtable  并行	8.23s
Select  icol3,count(*) from bigtable group by icol3; 非并行	652.64s
select /*parallel*/ icol3,count(*) from bigtable group by icol3  并行	22.24s

 

可以看出并行之后，count(*)性能提升约35倍。