本次测试使用单台服务，启动3个observer服务模拟，从单节点扩容到三节点操作。
服务器信息：
zone1 192.168.5.200 2881 2882
zone2 192.168.5.200 3881 3882
zone3 192.168.5.200 4881 4882
1 系统版本系统
cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
2 空间要求
/data磁盘组大于150G
内存大于30G
3 系统初始化，软件安装相关内容参考
https://www.modb.pro/db/190763
4 建立相关数据目录
mkdir -p /data/{observer01,observer02,observer03,obproxy}
mkdir -p /data/observer{01,02,03}/store/{sort_dir,sstable,clog,ilog,slog}
5 启动zone1的observer，并初始化
#启动observer
cd /data/observer01/ && /home/admin/oceanbase/bin/observer -r "192.168.5.200:2882:2881" -o __min_full_resource_pool_memory=268435456,memory_limit=8G,system_memory=4G,stack_size=512K,cpu_count=16,cache_wash_threshold=1G,workers_per_cpu_quota=10,schema_history_expire_time=1d,net_thread_count=4,sys_bkgd_migration_retry_num=3,minor_freeze_times=10,enable_separate_sys_clog=0,enable_merge_by_turn=False,datafile_size=50G,enable_syslog_recycle=True,max_syslog_file_count=10 -z zone1 -p 2881 -P 2882 -c 1 -d /data/observer01/store -i ens192 -l INFO
#初始化集群
obclient -h 192.168.5.200 -u root -P 2881 -p -c -A
set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '192.168.5.200:2882';
#启动observer cd /data/observer01/ && /home/admin/oceanbase/bin/observer -r "192.168.5.200:2882:2881" -o __min_full_resource_pool_memory=268435456,memory_limit=8G,system_memory=4G,stack_size=512K,cpu_count=16,cache_wash_threshold=1G,workers_per_cpu_quota=10,schema_history_expire_time=1d,net_thread_count=4,sys_bkgd_migration_retry_num=3,minor_freeze_times=10,enable_separate_sys_clog=0,enable_merge_by_turn=False,datafile_size=50G,enable_syslog_recycle=True,max_syslog_file_count=10 -z zone1 -p 2881 -P 2882 -c 1 -d /data/observer01/store -i ens192 -l INFO #初始化集群 obclient -h 192.168.5.200 -u root -P 2881 -p -c -A set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '192.168.5.200:2882';
检查集群状态:
[admin@db01 observer01]$ obclient -h 192.168.5.200 -u root@sys -P 2881 -p -c -A oceanbase
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221487737
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [oceanbase]> select * from __all_server\G;
*************************** 1. row ***************************
gmt_create: 2021-12-07 12:37:13.406061
gmt_modified: 2021-12-07 12:37:25.591799
svr_ip: 192.168.5.200
svr_port: 2882
id: 1
zone: zone1
inner_port: 2881
with_rootserver: 1
status: active
block_migrate_in_time: 0
build_version: 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14)
stop_time: 0
start_service_time: 1638851842594506
first_sessid: 0
with_partition: 1
last_offline_time: 0
1 row in set (0.000 sec)
MySQL [oceanbase]> select * from __all_zone where name in ('region','status','zone_type');
+----------------------------+----------------------------+-------+-----------+-------+----------------+
| gmt_create | gmt_modified | zone | name | value | info |
+----------------------------+----------------------------+-------+-----------+-------+----------------+
| 2021-12-07 12:37:20.972468 | 2021-12-07 12:37:20.972468 | zone1 | region | 0 | default_region |
| 2021-12-07 12:37:20.971411 | 2021-12-07 12:37:20.971411 | zone1 | status | 2 | ACTIVE |
| 2021-12-07 12:37:20.972468 | 2021-12-07 12:37:20.972468 | zone1 | zone_type | 0 | ReadWrite |
+----------------------------+----------------------------+-------+-----------+-------+----------------+
3 rows in set (0.000 sec)
MySQL [oceanbase]>
6 启动zone2,zone3 observer
observer2 端口3882,3881
observer3端口4882,4881
#zone2 cd /data/observer02/ && /home/admin/oceanbase/bin/observer -r "192.168.5.200:3882:3881" -o __min_full_resource_pool_memory=268435456,memory_limit=8G,system_memory=4G,stack_size=512K,cpu_count=16,cache_wash_threshold=1G,workers_per_cpu_quota=10,schema_history_expire_time=1d,net_thread_count=4,sys_bkgd_migration_retry_num=3,minor_freeze_times=10,enable_separate_sys_clog=0,enable_merge_by_turn=False,datafile_size=50G,enable_syslog_recycle=True,max_syslog_file_count=10 -z zone2 -p 3881 -P 3882 -c 1 -d /data/observer02/store -i ens192 -l INFO #zone3 cd /data/observer03/ && /home/admin/oceanbase/bin/observer -r "192.168.5.200:4882:4881" -o __min_full_resource_pool_memory=268435456,memory_limit=8G,system_memory=4G,stack_size=512K,cpu_count=16,cache_wash_threshold=1G,workers_per_cpu_quota=10,schema_history_expire_time=1d,net_thread_count=4,sys_bkgd_migration_retry_num=3,minor_freeze_times=10,enable_separate_sys_clog=0,enable_merge_by_turn=False,datafile_size=50G,enable_syslog_recycle=True,max_syslog_file_count=10 -z zone3 -p 4881 -P 4882 -c 1 -d /data/observer03/store -i ens192 -l INFO
7 添加observer
7.1添加zone2,zone3并启动
obclient -h 192.168.5.200 -u root -P 2881 -p -c -A oceanbase
alter system add zone 'zone2' region 'default_region';
alter system add zone 'zone3' region 'default_region';
alter system start zone 'zone2';
alter system start zone 'zone3';
MySQL [oceanbase]> select * from __all_zone where name in ('region','status','zone_type');
+----------------------------+----------------------------+-------+-----------+-------+----------------+
| gmt_create | gmt_modified | zone | name | value | info |
+----------------------------+----------------------------+-------+-----------+-------+----------------+
| 2021-12-07 12:54:10.999649 | 2021-12-07 12:54:10.999649 | zone1 | region | 0 | default_region |
| 2021-12-07 12:54:10.998589 | 2021-12-07 12:54:10.998589 | zone1 | status | 2 | ACTIVE |
| 2021-12-07 12:54:10.999649 | 2021-12-07 12:54:10.999649 | zone1 | zone_type | 0 | ReadWrite |
| 2021-12-07 12:57:29.571265 | 2021-12-07 12:57:29.571265 | zone2 | region | 0 | default_region |
| 2021-12-07 12:57:29.569309 | 2021-12-07 12:59:13.794151 | zone2 | status | 2 | ACTIVE |
| 2021-12-07 12:57:29.572309 | 2021-12-07 12:57:29.572309 | zone2 | zone_type | 0 | LOCAL |
| 2021-12-07 12:57:53.763849 | 2021-12-07 12:57:53.763849 | zone3 | region | 0 | default_region |
| 2021-12-07 12:57:53.763849 | 2021-12-07 12:59:14.248954 | zone3 | status | 2 | ACTIVE |
| 2021-12-07 12:57:53.763849 | 2021-12-07 12:57:53.763849 | zone3 | zone_type | 0 | LOCAL |
+----------------------------+----------------------------+-------+-----------+-------+----------------+
9 rows in set (0.000 sec)
7.2 添加observer
alter system add server '192.168.5.200:3882' zone 'zone2';
alter system add server '192.168.5.200:4882' zone 'zone3';
MySQL [oceanbase]> SELECT svr_ip,svr_port, cpu_total, mem_total, disk_total, zone FROM __all_virtual_server_stat ;
+---------------+----------+-----------+------------+-------------+-------+
| svr_ip | svr_port | cpu_total | mem_total | disk_total | zone |
+---------------+----------+-----------+------------+-------------+-------+
| 192.168.5.200 | 2882 | 14 | 4294967296 | 53687091200 | zone1 |
| 192.168.5.200 | 3882 | 14 | 4294967296 | 53687091200 | zone2 |
| 192.168.5.200 | 4882 | 14 | 4294967296 | 53687091200 | zone3 |
+---------------+----------+-----------+------------+-------------+-------+
3 rows in set (0.002 sec)
MySQL [oceanbase]> select * from __all_server;
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | last_offline_time |
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| 2021-12-07 15:52:36.583969 | 2021-12-07 15:52:48.009135 | 192.168.5.200 | 2882 | 1 | zone1 | 2881 | 1 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1638863566011754 | 0 | 1 | 0 |
| 2021-12-07 15:56:07.681043 | 2021-12-07 15:56:30.391582 | 192.168.5.200 | 3882 | 2 | zone2 | 3881 | 0 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1638863788395674 | 0 | 0 | 0 |
| 2021-12-07 15:56:11.908905 | 2021-12-07 15:56:31.793939 | 192.168.5.200 | 4882 | 3 | zone3 | 4881 | 0 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1638863789811151 | 0 | 0 | 0 |
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
3 rows in set (0.001 sec)
MySQL [oceanbase]>
8 创建资源池和租户
CREATE resource unit S4C1G max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G';
CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1;
create tenant obmysql resource_pool_list=('my_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
#新建资源池，默认选择了3个zone。
MySQL [oceanbase]> select * from __all_unit_config;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | max_memory | min_memory | max_iops | min_iops | max_disk_size | max_session_num |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| 2021-12-07 12:54:10.896905 | 2021-12-07 12:54:10.896905 | 1 | sys_unit_config | 5 | 2.5 | 1288490188 | 1073741824 | 10000 | 5000 | 53687091200 | 9223372036854775807 |
| 2021-12-07 13:17:51.986267 | 2021-12-07 13:17:51.986267 | 1001 | S4C1G | 4 | 4 | 1073741824 | 1073741824 | 10000 | 1000 | 1099511627776 | 1000000 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
2 rows in set (0.000 sec)
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 12:54:10.902356 | 1 | sys_pool | 1 | 1 | zone1 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:22:43.153926 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | -1 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
9 登陆新建租户obmysql
[admin@db01 observer03]$ obclient -h 127.1 -uroot@obmysql -P2881 -p -c -A test
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221503962
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [test]> source bmsql.sql
10 建立数据脚本
bmsql.sql
/*
* benchmark initialize sql for oceanbase(mysql).
*/
create database tpccdb;
grant all privileges on tpccdb.* to tpcc identified by '123456';
grant select on oceanbase.* to tpcc;
create tablegroup tpcc_group partition by hash partitions 6;
show tablegroups;
show grants for tpcc;
use tpccdb;
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
drop tablegroup tpcc_group;
create tablegroup tpcc_group partition by hash partitions 9;
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
primary key(w_id)
)tablegroup=tpcc_group partition by hash(w_id) partitions 9;
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9),
PRIMARY KEY (d_w_id, d_id)
)tablegroup=tpcc_group partition by hash(d_w_id) partitions 9;
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500),
PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup=tpcc_group partition by hash(c_w_id) partitions 9;
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
)tablegroup=tpcc_group partition by hash(h_w_id) partitions 9;
create table bmsql_new_order (
no_w_id integer not null ,
no_d_id integer not null,
no_o_id integer not null,
PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup=tpcc_group partition by hash(no_w_id) partitions 9;
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp,
PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup=tpcc_group partition by hash(o_w_id) partitions 9;
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24),
PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup=tpcc_group partition by hash(ol_w_id) partitions 9;
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer,
PRIMARY KEY (i_id)
) duplicate_scope='cluster';
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24),
PRIMARY KEY (s_w_id, s_i_id)
)tablegroup=tpcc_group use_bloom_filter=true BLOCK_SIZE=16384 partition by hash(s_w_id) partitions 9;
11 修改sys_pool的zone的信息
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 12:54:10.902356 | 1 | sys_pool | 1 | 1 | zone1 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:24:49.721494 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | 1001 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
MySQL [oceanbase]> ALTER RESOURCE POOL sys_pool zone_list=('zone1','zone2','zone3');
Query OK, 0 rows affected (0.008 sec)
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 13:36:15.861925 | 1 | sys_pool | 1 | 1 | zone1;zone2;zone3 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:24:49.721494 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | 1001 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
12 遗留问题
CREATE resource unit S4C1G max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G';
CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1;
create tenant obmysql resource_pool_list=('my_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
#新建资源池，默认选择了3个zone。
MySQL [oceanbase]> select * from __all_unit_config;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | max_memory | min_memory | max_iops | min_iops | max_disk_size | max_session_num |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| 2021-12-07 12:54:10.896905 | 2021-12-07 12:54:10.896905 | 1 | sys_unit_config | 5 | 2.5 | 1288490188 | 1073741824 | 10000 | 5000 | 53687091200 | 9223372036854775807 |
| 2021-12-07 13:17:51.986267 | 2021-12-07 13:17:51.986267 | 1001 | S4C1G | 4 | 4 | 1073741824 | 1073741824 | 10000 | 1000 | 1099511627776 | 1000000 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
2 rows in set (0.000 sec)
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 12:54:10.902356 | 1 | sys_pool | 1 | 1 | zone1 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:22:43.153926 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | -1 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
9 登陆新建租户obmysql
[admin@db01 observer03]$ obclient -h 127.1 -uroot@obmysql -P2881 -p -c -A test
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221503962
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [test]> source bmsql.sql
10 建立数据脚本
bmsql.sql
/*
* benchmark initialize sql for oceanbase(mysql).
*/
create database tpccdb;
grant all privileges on tpccdb.* to tpcc identified by '123456';
grant select on oceanbase.* to tpcc;
create tablegroup tpcc_group partition by hash partitions 6;
show tablegroups;
show grants for tpcc;
use tpccdb;
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
drop tablegroup tpcc_group;
create tablegroup tpcc_group partition by hash partitions 9;
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
primary key(w_id)
)tablegroup=tpcc_group partition by hash(w_id) partitions 9;
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9),
PRIMARY KEY (d_w_id, d_id)
)tablegroup=tpcc_group partition by hash(d_w_id) partitions 9;
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500),
PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup=tpcc_group partition by hash(c_w_id) partitions 9;
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
)tablegroup=tpcc_group partition by hash(h_w_id) partitions 9;
create table bmsql_new_order (
no_w_id integer not null ,
no_d_id integer not null,
no_o_id integer not null,
PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup=tpcc_group partition by hash(no_w_id) partitions 9;
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp,
PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup=tpcc_group partition by hash(o_w_id) partitions 9;
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24),
PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup=tpcc_group partition by hash(ol_w_id) partitions 9;
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer,
PRIMARY KEY (i_id)
) duplicate_scope='cluster';
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24),
PRIMARY KEY (s_w_id, s_i_id)
)tablegroup=tpcc_group use_bloom_filter=true BLOCK_SIZE=16384 partition by hash(s_w_id) partitions 9;
11 修改sys_pool的zone的信息
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 12:54:10.902356 | 1 | sys_pool | 1 | 1 | zone1 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:24:49.721494 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | 1001 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
MySQL [oceanbase]> ALTER RESOURCE POOL sys_pool zone_list=('zone1','zone2','zone3');
Query OK, 0 rows affected (0.008 sec)
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 13:36:15.861925 | 1 | sys_pool | 1 | 1 | zone1;zone2;zone3 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:24:49.721494 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | 1001 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
12 遗留问题
bmsql.sql /* * benchmark initialize sql for oceanbase(mysql). */ create database tpccdb; grant all privileges on tpccdb.* to tpcc identified by '123456'; grant select on oceanbase.* to tpcc; create tablegroup tpcc_group partition by hash partitions 6; show tablegroups; show grants for tpcc; use tpccdb; create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); drop tablegroup tpcc_group; create tablegroup tpcc_group partition by hash partitions 9; create table bmsql_warehouse ( w_id integer not null, w_ytd decimal(12,2), w_tax decimal(4,4), w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9), primary key(w_id) )tablegroup=tpcc_group partition by hash(w_id) partitions 9; create table bmsql_district ( d_w_id integer not null, d_id integer not null, d_ytd decimal(12,2), d_tax decimal(4,4), d_next_o_id integer, d_name varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(20), d_state char(2), d_zip char(9), PRIMARY KEY (d_w_id, d_id) )tablegroup=tpcc_group partition by hash(d_w_id) partitions 9; create table bmsql_customer ( c_w_id integer not null, c_d_id integer not null, c_id integer not null, c_discount decimal(4,4), c_credit char(2), c_last varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), PRIMARY KEY (c_w_id, c_d_id, c_id) )tablegroup=tpcc_group partition by hash(c_w_id) partitions 9; create table bmsql_history ( hist_id integer, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) )tablegroup=tpcc_group partition by hash(h_w_id) partitions 9; create table bmsql_new_order ( no_w_id integer not null , no_d_id integer not null, no_o_id integer not null, PRIMARY KEY (no_w_id, no_d_id, no_o_id) )tablegroup=tpcc_group partition by hash(no_w_id) partitions 9; create table bmsql_oorder ( o_w_id integer not null, o_d_id integer not null, o_id integer not null, o_c_id integer, o_carrier_id integer, o_ol_cnt integer, o_all_local integer, o_entry_d timestamp, PRIMARY KEY (o_w_id, o_d_id, o_id) )tablegroup=tpcc_group partition by hash(o_w_id) partitions 9; create table bmsql_order_line ( ol_w_id integer not null, ol_d_id integer not null, ol_o_id integer not null, ol_number integer not null, ol_i_id integer not null, ol_delivery_d timestamp, ol_amount decimal(6,2), ol_supply_w_id integer, ol_quantity integer, ol_dist_info char(24), PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number) )tablegroup=tpcc_group partition by hash(ol_w_id) partitions 9; create table bmsql_item ( i_id integer not null, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer, PRIMARY KEY (i_id) ) duplicate_scope='cluster'; create table bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24), PRIMARY KEY (s_w_id, s_i_id) )tablegroup=tpcc_group use_bloom_filter=true BLOCK_SIZE=16384 partition by hash(s_w_id) partitions 9;
11 修改sys_pool的zone的信息
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 12:54:10.902356 | 1 | sys_pool | 1 | 1 | zone1 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:24:49.721494 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | 1001 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
MySQL [oceanbase]> ALTER RESOURCE POOL sys_pool zone_list=('zone1','zone2','zone3');
Query OK, 0 rows affected (0.008 sec)
MySQL [oceanbase]> select * from __all_resource_pool ;
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| gmt_create | gmt_modified | resource_pool_id | name | unit_count | unit_config_id | zone_list | tenant_id | replica_type | is_tenant_sys_pool |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
| 2021-12-07 12:54:10.899134 | 2021-12-07 13:36:15.861925 | 1 | sys_pool | 1 | 1 | zone1;zone2;zone3 | 1 | 0 | 0 |
| 2021-12-07 13:22:43.153926 | 2021-12-07 13:24:49.721494 | 1001 | my_pool | 1 | 1001 | zone1;zone2;zone3 | 1001 | 0 | 0 |
+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+
2 rows in set (0.000 sec)
12 遗留问题
解决方法：
https://www.modb.pro/db/190799
系统租户系统表默认还是单副本模式
MySQL [oceanbase]> show create table __all_zone; +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | __all_zone | CREATE TABLE `__all_zone` ( `gmt_create` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6), `gmt_modified` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `zone` varchar(128) NOT NULL, `name` varchar(128) NOT NULL, `value` bigint(20) NOT NULL, `info` varchar(4096) NOT NULL, PRIMARY KEY (`zone`, `name`) ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'none' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 TABLEGROUP = 'oceanbase' | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec)
sys租户默认还是单个zone:
MySQL [oceanbase]> select * from __all_tenant;
+----------------------------+----------------------------+-----------+-------------+-------------+-------------------+--------------+--------+----------------+-----------------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+
| gmt_create | gmt_modified | tenant_id | tenant_name | replica_num | zone_list | primary_zone | locked | collation_type | info | read_only | rewrite_merge_version | locality | logonly_replica_num | previous_locality | storage_format_version | storage_format_work_version | default_tablegroup_id | compatibility_mode | drop_tenant_time | status | in_recyclebin |
+----------------------------+----------------------------+-----------+-------------+-------------+-------------------+--------------+--------+----------------+-----------------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+
| 2021-12-07 12:54:10.906715 | 2021-12-07 12:54:10.906715 | 1 | sys | -1 | zone1 | zone1 | 0 | 0 | system tenant | 0 | 0 | FULL{1}@zone1 | 0 | | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 |
| 2021-12-07 13:24:51.370159 | 2021-12-07 13:24:51.370159 | 1001 | obmysql | -1 | zone1;zone2;zone3 | RANDOM | 0 | 0 | mysql tenant/instance | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 0 | | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 |
+----------------------------+----------------------------+-----------+-------------+-------------+-------------------+--------------+--------+----------------+-----------------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+
2 rows in set (0.000 sec)
