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

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

tar zxvf oneproxy-rhel5-linux64*

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

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

passwd test

[

  {

        "table"   : "bigtable",

        "pkey"    : "id",

        "type"    : "int",

        "method"  : "hash",

        "partitions": 256,

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

  }

]

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

sh ./demo.sh

cat oneproxy.log

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

list backend;

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

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

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"

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

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

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