Oceanbase 扩容TPC-H测试

实现功能：自身集群扩容+租户资源扩容+租户资源扩容tpc-h测试

1.机器准备：

172.20.2.120:2881;172.20.2.121:2881;172.20.2.122:2881;172.20.2.120:3881;172.20.2.121:3881;172.20.2.122:3881

2.使用 OBD 自动化部署集群

1. 安装 OBD 软件（在线）:
   yum install -y yum-utils
   yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
   yum install -y ob-deploy
2. 编辑配置文件
    vim obce-3zones.yaml

   #  Only need to configure when remote login is required

   user:
      username: admin
   #    password: your password if need
      key_file: /home/admin/.ssh/id_rsa.pub
      port: your ssh port, default 22
   #    timeout: ssh connection timeout (second), default 30
   oceanbase-ce:
     servers:
       - name: z01
         # Please don't use hostname, only IP can be supported
         ip: 172.20.2.120
       - name: z02
         ip: 172.20.2.121
       - name: z03
         ip: 172.20.2.122
     global:
       # Please set devname as the network adaptor's name whose ip is  in the setting of severs.
       # if set severs as "127.0.0.1", please set devname as "lo"
       # if current ip is 192.168.1.10, and the ip's network adaptor's name is "eth0", please use "eth0"
       devname: ens192
       cluster_id: 1
       # please set memory limit to a suitable value which is matching resource.
       memory_limit: 21G # The maximum running memory for an observer
       system_memory: 8G # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G.
       stack_size: 512K
       cpu_count: 16
       cache_wash_threshold: 1G
       __min_full_resource_pool_memory: 268435456
       workers_per_cpu_quota: 10
       schema_history_expire_time: 1d
       # The value of net_thread_count had better be same as cpu's core number.
       net_thread_count: 4
       major_freeze_duty_time: Disable
       minor_freeze_times: 10
       enable_separate_sys_clog: 0
       enable_merge_by_turn: FALSE
       #datafile_disk_percentage: 20 # The percentage of the data_dir space to the total disk space. This value takes effect only when datafile_size is 0. The default value is 90.
       datafile_size: 50G
       syslog_level: WARN # System log level. The default value is INFO.
       enable_syslog_wf: false # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true.
       enable_syslog_recycle: true # Enable auto system log recycling or not. The default value is false.
       max_syslog_file_count: 10 # The maximum number of reserved log files before enabling auto recycling. The default value is 0.
       # observer cluster name, consistent with obproxy's cluster_name
       appname: obce-3zones
       root_password: Pwd2022# # root user password, can be empty
       proxyro_password: Pwd2022# # proxyro user pasword, consistent with obproxy's observer_sys_password, can be empty
     z01:
       mysql_port: 2881 # External port for OceanBase Database. The default value is 2881.
       rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882.
       #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
       home_path: /home/admin/oceanbase-ce
       # The directory for data storage. The default value is $home_path/store.
       data_dir: /data
       # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
       redo_dir: /redo
       zone: zone1
     z02:
       mysql_port: 2881 # External port for OceanBase Database. The default value is 2881.
       rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882.
       #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
       home_path: /home/admin/oceanbase-ce
       # The directory for data storage. The default value is $home_path/store.
       data_dir: /data
       # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
       redo_dir: /redo
       zone: zone2
     z03:
       mysql_port: 2881 # External port for OceanBase Database. The default value is 2881.
       rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882.
       #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
       home_path: /home/admin/oceanbase-ce
       # The directory for data storage. The default value is $home_path/store.
       data_dir: /data
       # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
       redo_dir: /redo
       zone: zone3
   obproxy:
     servers:
       - 172.20.2.120
       - 172.20.2.121
       - 172.20.2.122
     # Set dependent components for the component.
     # When the associated configurations are not done, OBD will automatically get the these configurations from the dependent components.
     depends:
       - oceanbase-ce
     global:
       listen_port: 2883 # External port. The default value is 2883.
       prometheus_listen_port: 2884 # The Prometheus port. The default value is 2884.
       home_path: /home/admin/obproxy
       # oceanbase root server list
       # format: ip:mysql_port;ip:mysql_port
       rs_list: 172.20.2.120:2881;172.20.2.121:2881;172.20.2.122:2881
       enable_cluster_checkout: false
       # observer cluster name, consistent with oceanbase-ce's appname
       cluster_name: obce-3zones
       obproxy_sys_password: Pwd2022# # obproxy sys user password, can be empty
       observer_sys_password: Pwd2022# # proxyro user pasword, consistent with oceanbase-ce's proxyro_password, can be empty

3. 部署集群
   obd cluster deploy obce-3zones -c obce-zones.yaml

4. 检查集群部署状态

5. 初始化集群
   obd cluster start obce-3zones

6. 查看集群状态
   obd cluster list

7. 查看机器列表（登录）

8. 集群扩容
   
   新建配置文件
   vim obce-3zones2.yaml
   #  Only need to configure when remote login is required

   user:
      username: admin
   #    password: your password if need
      key_file: /home/admin/.ssh/id_rsa.pub
      port: your ssh port, default 22
   #    timeout: ssh connection timeout (second), default 30
   oceanbase-ce:
     servers:
       - name: z04
         # Please don't use hostname, only IP can be supported
         ip: 172.20.2.120
       - name: z05
         ip: 172.20.2.121
       - name: z06
         ip: 172.20.2.122
     global:
       # Please set devname as the network adaptor's name whose ip is  in the setting of severs.
       # if set severs as "127.0.0.1", please set devname as "lo"
       # if current ip is 192.168.1.10, and the ip's network adaptor's name is "eth0", please use "eth0"
       devname: ens192
       cluster_id: 1
       # please set memory limit to a suitable value which is matching resource.
       memory_limit: 21G # The maximum running memory for an observer
       system_memory: 8G # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G.
       stack_size: 512K
       cpu_count: 16
       cache_wash_threshold: 1G
       __min_full_resource_pool_memory: 268435456
       workers_per_cpu_quota: 10
       schema_history_expire_time: 1d
       # The value of net_thread_count had better be same as cpu's core number.
       net_thread_count: 4
       major_freeze_duty_time: Disable
       minor_freeze_times: 10
       enable_separate_sys_clog: 0
       enable_merge_by_turn: FALSE
       #datafile_disk_percentage: 20 # The percentage of the data_dir space to the total disk space. This value takes effect only when datafile_size is 0. The default value is 90.
       datafile_size: 50G
       syslog_level: WARN # System log level. The default value is INFO.
       enable_syslog_wf: false # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true.
       enable_syslog_recycle: true # Enable auto system log recycling or not. The default value is false.
       max_syslog_file_count: 10 # The maximum number of reserved log files before enabling auto recycling. The default value is 0.
       # observer cluster name, consistent with obproxy's cluster_name
       appname: obce-3zones
       root_password: Pwd2022# # root user password, can be empty
       proxyro_password: Pwd2022# # proxyro user pasword, consistent with obproxy's observer_sys_password, can be empty
     z04:
       mysql_port: 3881 # External port for OceanBase Database. The default value is 2881.
       rpc_port: 3882 # Internal port for OceanBase Database. The default value is 2882.
       #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
       home_path: /home/admin/oceanbase-ce
       # The directory for data storage. The default value is $home_path/store.
       data_dir: /data/ob2
       # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
       redo_dir: /redo/ob2
       zone: zone1
     z05:
       mysql_port: 3881 # External port for OceanBase Database. The default value is 2881.
       rpc_port: 3882 # Internal port for OceanBase Database. The default value is 2882.
       #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
       home_path: /home/admin/oceanbase-ce
       # The directory for data storage. The default value is $home_path/store.
       data_dir: /data/ob2
       # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
       redo_dir: /redo/ob2
       zone: zone2
     z06:
       mysql_port: 3881 # External port for OceanBase Database. The default value is 2881.
       rpc_port: 3882 # Internal port for OceanBase Database. The default value is 2882.
       #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
       home_path: /home/admin/oceanbase-ce
       # The directory for data storage. The default value is $home_path/store.
       data_dir: /data/ob2
       # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
       redo_dir: /redo/ob2
       zone: zone3

9. obd cluster deploy obce-3zones2 -c obce-3zones2.yaml

10. 新文件copy进旧配置文件
    1.obd cluster list
    2.追加

11. 重启集群
    obd cluster start obce-3zones

12. 添加新进程（登录）
    alter system add server '172.20.2.120:3882' zone 'zone1';
    alter system add server '172.20.2.121:3882' zone 'zone2';
    alter system add server '172.20.2.122:3882' zone 'zone3';
13. 查看成功结果
    select svr_ip,id,zone,status from __all_server;

3.新建租户
1.连接数据库
2.创建资源单元
CREATE RESOURCE UNIT S1 max_cpu=2, min_cpu=2, max_memory='2G', min_memory='2G',max_iops=1000, min_iops=1000, max_session_num=10000, max_disk_size='20G';
3.创建租户
create tenant test_tenant resource_pool_list=('pool1'); MySQL [(none)]> ALTER TENANT test_tenant SET VARIABLES ob_tcp_invited_nodes='%';
4.设置租户unit_num=1
alter resource pool pool1 unit_num=1;

4.tpc-h 测试
1.软件安装
TPC-H Tools Downloadhttp://tpc.org/TPC_Documents_Current_Versions/download_programs/tools-download-request5.asp?bm_type=TPC-H&bm_vers=3.0.0&mode=CURRENT-ONLYcd /root/TPC-H_Tools_v3.0.0/dbgen
cp makefile.suite Makefile

2.修改 Makefile 文件中的 CC、DATABASE、MACHINE 和 WORKLOAD 等参数定义。
vim Makefile +103
CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are:  TPCH
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH
3.修改 tpcd.h 文件，并添加新的宏定义。

vim tpcd.h +$
#ifdef MYSQL
#define GEN_QUERY_PLAN ""
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;n"
#define SET_DBASE "use %s;n"
#endif

4.编译：make
5.数据文件准备
   ./dbgen -s 100  --此处100参数可变，指定文件大小，单位g
6.将准备好的文件移至指定目录
   mkdir tpch10 mv *.tbl tpch100/
 7.查询语句准备
     mkdir tpch10 cp qgen queries/ cp dists.dss queries/ cd queries/ for i in `seq 22`; do echo $i;               ./qgen -d $i -s 10 > db"$i".sql; done dos2unix *.sql
 8.sql准备
    样例
          

  9.租户参数设置
         set global autocommit=ON;
         set global ob_query_timeout=36000000000;
         set global ob_trx_timeout=36000000000;
         set global max_allowed_packet=67108864;
         set global ob_sql_work_area_percentage=80;
         
         set global parallel_max_servers=260;
         set global parallel_servers_target=260;

  10.建表
              create tablegroup tpch_tg_100g_lineitem_order_group binding true partition by key 1            partitions 9;
      create tablegroup tpch_tg_100g_partsupp_part binding true partition by key 1 partitions 9;
      
      drop database if exists tpch_100g_part;
      create database tpch_100g_part;
      use tpch_100g_part;
      
      CREATE TABLE lineitem (
          l_orderkey bigint NOT NULL,
          l_partkey bigint NOT NULL,
          l_suppkey bigint NOT NULL,
          l_linenumber bigint NOT NULL,
          l_quantity bigint NOT NULL,
          l_extendedprice bigint NOT NULL,
          l_discount bigint NOT NULL,
          l_tax bigint NOT NULL,
          l_returnflag char(1) DEFAULT NULL,
          l_linestatus char(1) DEFAULT NULL,
          l_shipdate date NOT NULL,
          l_commitdate date DEFAULT NULL,
          l_receiptdate date DEFAULT NULL,
          l_shipinstruct char(25) DEFAULT NULL,
          l_shipmode char(10) DEFAULT NULL,
          l_comment varchar(44) DEFAULT NULL,
          primary key(l_orderkey, l_linenumber)
      )    tablegroup = tpch_tg_100g_lineitem_order_group
          partition by key (l_orderkey) partitions 9;
      create index I_L_ORDERKEY on lineitem(l_orderkey) local;
      create index I_L_SHIPDATE on lineitem(l_shipdate) local;
      
      CREATE TABLE orders (
          o_orderkey bigint NOT NULL,
          o_custkey bigint NOT NULL,
          o_orderstatus char(1) DEFAULT NULL,
          o_totalprice bigint DEFAULT NULL,
          o_orderdate date NOT NULL,
          o_orderpriority char(15) DEFAULT NULL,
          o_clerk char(15) DEFAULT NULL,
          o_shippriority bigint DEFAULT NULL,
          o_comment varchar(79) DEFAULT NULL,
          PRIMARY KEY (o_orderkey))
          tablegroup = tpch_tg_100g_lineitem_order_group
          partition by key(o_orderkey) partitions 9;
          create index I_O_ORDERDATE on orders(o_orderdate) local;
      
      
      CREATE TABLE partsupp (
          ps_partkey bigint NOT NULL,
          ps_suppkey bigint NOT NULL,
          ps_availqty bigint DEFAULT NULL,
          ps_supplycost bigint DEFAULT NULL,
          ps_comment varchar(199) DEFAULT NULL,
          PRIMARY KEY (ps_partkey, ps_suppkey))
          tablegroup tpch_tg_100g_partsupp_part
          partition by key(ps_partkey) partitions 9;
      
      
      CREATE TABLE part (
        p_partkey bigint NOT NULL,
        p_name varchar(55) DEFAULT NULL,
        p_mfgr char(25) DEFAULT NULL,
        p_brand char(10) DEFAULT NULL,
        p_type varchar(25) DEFAULT NULL,
        p_size bigint DEFAULT NULL,
        p_container char(10) DEFAULT NULL,
        p_retailprice bigint DEFAULT NULL,
        p_comment varchar(23) DEFAULT NULL,
        PRIMARY KEY (p_partkey))
        tablegroup tpch_tg_100g_partsupp_part
        partition by key(p_partkey) partitions 9;
      
      CREATE TABLE customer (
        c_custkey bigint NOT NULL,
        c_name varchar(25) DEFAULT NULL,
        c_address varchar(40) DEFAULT NULL,
        c_nationkey bigint DEFAULT NULL,
        c_phone char(15) DEFAULT NULL,
        c_acctbal bigint DEFAULT NULL,
        c_mktsegment char(10) DEFAULT NULL,
        c_comment varchar(117) DEFAULT NULL,
        PRIMARY KEY (c_custkey))
        partition by key(c_custkey) partitions 9;
      
      CREATE TABLE supplier (
        s_suppkey bigint NOT NULL,
        s_name char(25) DEFAULT NULL,
        s_address varchar(40) DEFAULT NULL,
        s_nationkey bigint DEFAULT NULL,
        s_phone char(15) DEFAULT NULL,
        s_acctbal bigint DEFAULT NULL,
        s_comment varchar(101) DEFAULT NULL,
        PRIMARY KEY (s_suppkey)
      ) partition by key(s_suppkey) partitions 9;
      
      CREATE TABLE nation (
        n_nationkey bigint NOT NULL,
        n_name char(25) DEFAULT NULL,
        n_regionkey bigint DEFAULT NULL,
        n_comment varchar(152) DEFAULT NULL,
        PRIMARY KEY (n_nationkey));
      
      CREATE TABLE region (
        r_regionkey bigint NOT NULL,
        r_name char(25) DEFAULT NULL,
        r_comment varchar(152) DEFAULT NULL,
        PRIMARY KEY (r_regionkey));
 11. 加载数据
      
 export MYSQL_PWD=123456
 python load.py

12.执行合并(使用 sys 租户登录)
    alter system major freeze;
    检查合并是否完成
    select name,value from oceanbase.__all_zone where name='frozen_version' or name='last_merged_version';--备注：frozen_version 和 last_merged_version 的值相等即表示合并完成。

13.测试sh
          vi tpch.sh    
    #!/bin/bash
    TPCH_TEST="obclient -h $host_ip -P $host_port -utpch_100g_part@tpch_mysql  -D tpch_100g_part  -ptest -c"
    #warmup预热
    for i in {1..22}
    do
       sql1="source db${i}.sql"
       echo $sql1| $TPCH_TEST >db${i}.log  || ret=1
    done
    #正式执行
    for i in {1..22}
    do
       starttime=`date +%s%N`
       echo `date  '+[%Y-%m-%d %H:%M:%S]'` "BEGIN Q${i}"
       sql1="source db${i}.sql"
       echo $sql1| $TPCH_TEST >db${i}.log  || ret=1
       stoptime=`date +%s%N`
       costtime=`echo $stoptime $starttime | awk '{printf "%0.2fn", ($1 - $2) / 1000000000}'`
       echo `date  '+[%Y-%m-%d %H:%M:%S]'` "END,COST ${costtime}s"
    done
14.在扩容前导入数据执行sh tpch.sh得出单节点tpc-h 100g性能数据

15.扩容后修改租户unit_num数据量为3
     alter resource pool pool1 unit_num=3;

16.再次运行sh tpch.sh得出3节点下tpc-h性能数据

结果
 

OceanBase 社区https://open.oceanbase.com/docs/observer-cn/V3.1.0/10000000000016273OceanBase 社区https://open.oceanbase.com/docs/tutorials-cn/V1.0.0/10000000000012297OceanBase 社区https://open.oceanbase.com/docs/tutorials-cn/V1.0.0/10000000000012299