使用分布式数据库集群做大数据分析之OneProxy
实时监控领域有两个显著的特点，一是数据来源很多而且数据量大，有来自监控摄像头、GPS、智能设备等；二是需要实时处理。我们的客户在做实时处理时，就遇到这样的问题。客户的某个数据表中有10亿条记录，希望统计类查询的响应时间在30s以内，而使用单台MySQL的响应时间在300s以上。OneProxy特有的并行查询功能，让响应时间降到10s以内。
在互联网+的时代，分库分表已经成为行业的标准解决方案之一。在众多应用中，数据每天都以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 oneproxy5.4.1-20150425 started2015-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倍。
使用分布式数据库集群做大数据分析之OneProxy
标签：
小编还为您整理了以下内容，可能对您也有帮助：
大数据处理软件用什么比较好
常见的数据处理软件有Apache Hive、SPSS、Excel、Apache Spark、 Jaspersoft BI 套件。
1、Apache Hive
Hive是一个建立在Hadoop上的开源数据仓库基础设施，通过Hive可以很容易的进行数据的ETL,对数据进行结构化处理,并对Hadoop上大数据文件进行查询和处理等。 Hive提供了一种简单的类似SQL的查询语言—HiveQL,这为熟悉SQL语言的用户查询数据提供了方便。
2、SPSS
SPSS for Windows软件分为若干功能模块。可以根据自己的分析需要和计算机的实际配置情况灵活选择。SPSS针对初学者、熟练者及精通者都比较适用。并且很多群体只需要掌握简单的操作分析，大多青睐于SPSS。
3、Excel
Excel 可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。Excel还提供高级查询功能，最操作的简单，其中自动汇总功能简单灵活。高级数学计算时，Excel也只用一两个函数轻松搞定。
4、Apache Spark
Apache Spark是Hadoop开源生态系统的新成员。它提供了一个比Hive更快的查询引擎，因为它依赖于自己的数据处理框架而不是依靠Hadoop的HDFS服务。同时，它还用于事件流处理、实时查询和机器学习等方面。
5、 Jaspersoft BI 套件
Jaspersoft包是一个通过数据库列生成报表的开源软件。行业领导者发现Jaspersoft软件是一流的, 许多企业已经使用它来将SQL表转化为pdf,，这使每个人都可以在会议上对其进行审议。另外，JasperReports提供了一个连接配置单元来替代HBase。
数据分析与处理方法：
采集
在大数据的采集过程中，其主要特点和挑战是并发数高，因为同时有可能会有成千上万的用户来进行访问和操作，比如火车票售票网站和淘宝，它们并发的访问量在峰值时达到上百万，所以需要在采集端部署大量数据库才能支撑。
并且如何在这些数据库之间进行负载均衡和分片的确是需要深入的思考和设计。
统计/分析
统计与分析主要利用分布式数据库，或者分布式计算集群来对存储于其内的大量数据进行普通的分析和分类汇总等，以满足大多数常见的分析需求，在这方面，一些实时性需求会用到EMC的GreenPlum、Oracle的Exadata，以及基于MySQL的列式存储Infobright等。
而一些批处理，或者基于半结构化数据的需求可以使用Hadoop。统计与分析这部分的主要特点和挑战是分析涉及的数据量大，其对系统资源，特别是I/O会有极大的占用。
导入/预处理
虽然采集端本身会有很多数据库，但是如果要对这些大量数据进行有效的分析，还是应该将这些来自前端的数据导入到一个集中的大型分布式数据库，或者分布式存储集群，并且可以在导入基础上做一些简单的清洗和预处理工作。
也有一些用户会在导入时使用来自Twitter的Storm来对数据进行流式计算，来满足部分业务的实时计算需求。导入与预处理过程的特点和挑战主要是导入的数据量大，每秒钟的导入量经常会达到百兆，甚至千兆级别。
大数据处理软件用什么比较好
常见的数据处理软件有Apache Hive、SPSS、Excel、Apache Spark、 Jaspersoft BI 套件。
1、Apache Hive
Hive是一个建立在Hadoop上的开源数据仓库基础设施，通过Hive可以很容易的进行数据的ETL,对数据进行结构化处理,并对Hadoop上大数据文件进行查询和处理等。 Hive提供了一种简单的类似SQL的查询语言—HiveQL,这为熟悉SQL语言的用户查询数据提供了方便。
2、SPSS
SPSS for Windows软件分为若干功能模块。可以根据自己的分析需要和计算机的实际配置情况灵活选择。SPSS针对初学者、熟练者及精通者都比较适用。并且很多群体只需要掌握简单的操作分析，大多青睐于SPSS。
3、Excel
Excel 可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。Excel还提供高级查询功能，最操作的简单，其中自动汇总功能简单灵活。高级数学计算时，Excel也只用一两个函数轻松搞定。
4、Apache Spark
Apache Spark是Hadoop开源生态系统的新成员。它提供了一个比Hive更快的查询引擎，因为它依赖于自己的数据处理框架而不是依靠Hadoop的HDFS服务。同时，它还用于事件流处理、实时查询和机器学习等方面。
5、 Jaspersoft BI 套件
Jaspersoft包是一个通过数据库列生成报表的开源软件。行业领导者发现Jaspersoft软件是一流的, 许多企业已经使用它来将SQL表转化为pdf,，这使每个人都可以在会议上对其进行审议。另外，JasperReports提供了一个连接配置单元来替代HBase。
数据分析与处理方法：
采集
在大数据的采集过程中，其主要特点和挑战是并发数高，因为同时有可能会有成千上万的用户来进行访问和操作，比如火车票售票网站和淘宝，它们并发的访问量在峰值时达到上百万，所以需要在采集端部署大量数据库才能支撑。
并且如何在这些数据库之间进行负载均衡和分片的确是需要深入的思考和设计。
统计/分析
统计与分析主要利用分布式数据库，或者分布式计算集群来对存储于其内的大量数据进行普通的分析和分类汇总等，以满足大多数常见的分析需求，在这方面，一些实时性需求会用到EMC的GreenPlum、Oracle的Exadata，以及基于MySQL的列式存储Infobright等。
而一些批处理，或者基于半结构化数据的需求可以使用Hadoop。统计与分析这部分的主要特点和挑战是分析涉及的数据量大，其对系统资源，特别是I/O会有极大的占用。
导入/预处理
虽然采集端本身会有很多数据库，但是如果要对这些大量数据进行有效的分析，还是应该将这些来自前端的数据导入到一个集中的大型分布式数据库，或者分布式存储集群，并且可以在导入基础上做一些简单的清洗和预处理工作。
也有一些用户会在导入时使用来自Twitter的Storm来对数据进行流式计算，来满足部分业务的实时计算需求。导入与预处理过程的特点和挑战主要是导入的数据量大，每秒钟的导入量经常会达到百兆，甚至千兆级别。
国内做分布式数据库开发的现状如何(分布式数据库适用于大数据分析吗)
基础软件创业其实我觉得是个好生意，尤其是数据库，但是前提是确实在技术上有所创新，这么一来技术壁垒就巨高，这就是护城河。如果只是去模仿Oracle，是没有太大前途的（当然靠关系那种就另说了，反正我本人不认为这样是正确的价值观），想想人家O记在这个领域做了30年，你走人家的老路凭什么干得动人家？目前来说我觉得之所以国内还没有太大成功的公司涌现说到底还是因为技术不行或者路子不对或者客户的历史包袱太重，拿个Hadoop改改就是大数据了吗？真正的OLTP业务敢碰吗？所以就造成了做项目挣快钱攒方案搞数据分析的公司扎堆，真正在OLTP端的创新没人敢碰。另外一个重要的问题就是，国内几乎没人懂开源。最近几年重要的基础软件创新都在开源社区，比如Docker/Kubenetes(Mesos)/Spark...凭一个公司的力量是很难跟上社区的发展速度的。国内的大多数开源项目不管是代码质量，用心程度，设计的视野上都太弱了，连最基本的英文交流都很少有开源项目注意，更不用说生态了。不过，还是有希望的，至少学术界最近几年的进展，让我们看到了在分布式OLTP系统(NewSQL)上的一些希望，而且这块在全球范围内都是一个蓝海。基于这个背景，我们创立了PingCAP，从零开始抛开一切历史包袱去实现一个全新的数据库TiDB，TiDB的目标就是瞄准世界顶级的通用分布式数据库开源项目和未来的行业标准去的。虽然这个东西确实很难,但我也不觉得我们会比硅谷的顶级基础软件公司差:)，不客气的讲，我们在这个领域也远远走到了各个友商的前面，另外一方面如果不难也没有做它的价值，如果未来的数据库还是需要像现在分库分表中间件Oracle，我觉得就太无趣了。就说一个Cloud-Native，目前来说基本没有OLTP的数据库能搞定。
大数据分析工具有哪些,好用的有吗
大数据分析的前瞻性使得很多公司以及企业都开始使用大数据分析对公司的决策做出帮助，而大数据分析是去分析海量的数据，所以就不得不借助一些工具去分析大数据，。一般来说，数据分析工作中都是有很多层次的，这些层次分别是数据存储层、数据报表层、数据分析层、数据展现层。对于不同的层次是有不同的工具进行工作的。下面小编就对大数据分析工具给大家好好介绍一下。
首先我们从数据存储来讲数据分析的工具。我们在分析数据的时候首先需要存储数据，数据的存储是一个非常重要的事情，如果懂得数据库技术，并且能够操作好数据库技术，这就能够提高数据分析的效率。而数据存储的工具主要是以下的工具。
1、MySQL数据库，这个对于部门级或者互联网的数据库应用是必要的，这个时候关键掌握数据库的库结构和SQL语言的数据查询能力。
2、SQL Server的最新版本，对中小企业，一些大型企业也可以采用SQL Server数据库，其实这个时候本身除了数据存储，也包括了数据报表和数据分析了，甚至数据挖掘工具都在其中了。
3、DB2，Oracle数据库都是大型数据库了，主要是企业级，特别是大型企业或者对数据海量存储需求的就是必须的了，一般大型数据库公司都提供非常好的数据整合应用平台;
接着说数据报表层。一般来说，当企业存储了数据后，首先要解决报表的问题。解决报表的问题才能够正确的分析好数据库。关于数据报表所用到的数据分析工具就是以下的工具。
1、Crystal Report水晶报表，Bill报表，这都是全球最流行的报表工具，非常规范的报表设计思想，早期商业智能其实大部分人的理解就是报表系统，不借助IT技术人员就可以获取企业各种信息——报表。
2、Tableau软件，这个软件是近年来非常棒的一个软件，当然它已经不是单纯的数据报表软件了，而是更为可视化的数据分析软件，因为很多人经常用它来从数据库中进行报表和可视化分析。
第三说的是数据分析层。这个层其实有很多分析工具，当然我们最常用的就是Excel，我经常用的就是统计分析和数据挖掘工具;
1、Excel软件，首先版本越高越好用这是肯定的;当然对Excel来讲很多人只是掌握了5%Excel功能，Excel功能非常强大，甚至可以完成所有的统计分析工作!但是我也常说，有能力把Excel玩成统计工具不如专门学会统计软件;
2、SPSS软件：当前版本是18，名字也改成了PASW Statistics;我从3.0开始Dos环境下编程分析，到现在版本的变迁也可以看出SPSS社会科学统计软件包的变化，从重视医学、化学等开始越来越重视商业分析，现在已经成为了预测分析软件。
最后说表现层的软件。一般来说表现层的软件都是很实用的工具。表现层的软件就是下面提到的内容。
1、PowerPoint软件：大部分人都是用PPT写报告。
2、Visio、SmartDraw软件：这些都是非常好用的流程图、营销图表、地图等，而且从这里可以得到很多零件;
3、Swiff Chart软件：制作图表的软件，生成的是Flash。