第六期直播实践练习(可选):对一个OB集群(带 OBProxy)进行Benchamrk测试
环境准备
由于手上正好有7台物理机,在作业三中会使用OBD直接部署了2:2:2架构的OceanBase集群。这里直接拿来进行TPC-C测试。
- 机器信息如下:
机器类型 | 主机信息 |
---|---|
IP | 10.144.2.112,10.144.2.111,10.144.2.110,10.144.2.109, 10.144.2.108,10.144.2.107,10.144.2.107 |
网卡名 | bond0 |
OS | CentOS Linux release 7.9.2009 (Core) |
CPU | 64 |
内存 | 256G,可用230G+ |
磁盘1 | /data/1 |
磁盘2 | data/2 |
- 机器划分如下:
角色 | 机器 | 备注 |
---|---|---|
OBD | 10.144.2.112 | 中控机,自动化安装部署软件 |
OBSERVER | 10.144.2.111 | OceanBase数据库,zone1 |
10.144.2.110 | OceanBase数据库,zone2 | |
10.144.2.109 | OceanBase数据库,zone3 | |
10.144.2.108 | OceanBase数据库,zone1 | |
10.144.2.107 | OceanBase数据库,zone2 | |
10.144.2.106 | OceanBase数据库,zone3 | |
OBPROXY | 10.144.2.111 | OceanBase访问反向代理 |
10.144.2.110 | OceanBase访问反向代理 | |
10.144.2.109 | OceanBase访问反向代理 | |
10.144.2.108 | OceanBase访问反向代理 | |
10.144.2.107 | OceanBase访问反向代理 | |
10.144.2.106 | OceanBase访问反向代理 | |
OBCLIENT | 10.144.2.112 | OceanBase命令行客户端 |
测试方案
- 使用 OBD 部署OceanBase 数据库集群。TPC-C 单独部署在一台机器上, 作为客户端的压力机器。
- OceanBase 集群规模为 2:2:2。部署成功后,新建执行 TPC-C 测试的租户及用户:租户tpcc,用户benchmarksql。将租户的
primary_zone
设置为RANDOM
。RANDOM
表示新建表分区的 Leader 随机到这 6 台机器。
测试规格
warehouses=2000
loadWorkers=100
terminals=20
runMins=10
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
安装 Benchmark SQL
按照以下步骤安装 Benchmark SQL:
-
下载 Benchmark SQL。
本次使用的是开源社区进行适配了mysql的benchmarksql。所以不包含修改源代码适配过程。
-
解压 Benchmark SQL。
unzip ./benchmarksql-5.0.zip
适配OceanBase
这里测试实验的是已经适配了mysql数据库的benchmarksql.
创建ob测试使用的配置文件
benchmarksql\run文件夹内创建prop.ob文件。
prop.ob中的参数说明:
-
JDBC 连接串:conn=jdbc:mysql:loadbalance://10.144.2.106:2883,10.144.2.107:2883,10.144.2.108:2883,10.144.2.109:2883,10.144.2.110:2883,10.144.2.111:2883/benchmark?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000
user=benchmarksql@tpcc
password=benchmarksql -
rewriteBatchedStatements:
- 参数非常重要,会严重影响导数据效率,不可以忽略。
- 如果导数据较慢,可以用对应租户登录上去通过show full processlist检查是否开启。
- new order事务中也用到了batch update,因此导数和benchmark阶段都需要开启。
-
并发数量(terminals):200,mysql 租户配置下并发需要结合具体配置动态调整。
-
useLocalSessionState:是否使用autocommit,read_only和transaction isolation的内部值(jdbc端的本地值),建议设置为true,如果设置为false,则需要发语句到远端请求,增加发送请求频次,影响性能。
-
warehouses/loadWorkers这两项用于设置压测数据量,可以适当调整。
-
numTerminals > 0 && numTerminals <= 10*numWarehouses,terminals的范围需要在这个区间内。
-
db=mysql 目前开源版只支持mysql租户,所以这里设置mysql
-
warehouses
:指定仓库数。通常仓库数就决定了这个性能测试理论上的成绩。如果期望测试结果越高,仓库数就不能太低。生产环境机器测试,建议 5000 仓库起步。如果机器配置较差,建议 100 仓起步。
-
loadWorkers
:指定仓库数据加载时的并发。如果机器配置很好,该值可以设置大一些,比如说 100 个。 如果机器配置不高(尤其是内存),该值需要设置小一些,如 10 个并发。并发指定得过高,可能导致内存消耗太快,出现报错,导致数据加载前功尽弃。
-
terminals
:指定性能压测时的并发数。建议并发数不要高于仓库数 * 10 。否则,会有不必要的锁等待。在生产环境中,该并发数设置到 1000 就很高了。一般环境测试建议从 100 开始。
-
runMins
:指定性能测试持续的时间。时间越久,越能考验数据库的性能和稳定性。建议不要少于 10 分钟。生产环境中机器建议不少于 1 小时。
-
LoadStartW
和LoadStopW
:指定补仓时的开始值和截止值。如果导数据时发现某个仓库数据导入失败(大事务超时),您可以指定这个仓库重新导入。
修改建表语句
修改benchmarksql/run/sql.mysql/tableCreates.sql
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 128;
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 128;
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 128;
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 128;
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 128;
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 128;
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 128;
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 128;
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 partition by hash(s_w_id) partitions 128;
修改索引创建语句
修改benchmarksql/run/sql.mysql/indexCreates.sql
create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
修改删除语句
修改benchmarksql/run/sql.mysql/tableDrops.sql
drop table bmsql_config;
drop table bmsql_new_order;
drop table bmsql_order_line;
drop table bmsql_oorder;
drop table bmsql_history;
drop table bmsql_customer;
drop table bmsql_stock;
drop table bmsql_item;
drop table bmsql_district;
drop table bmsql_warehouse;
purge recyclebin;
-- tpcc_group
drop tablegroup tpcc_group
环境调优
OBProxy 调优
请在sys租户下执行。
在系统租户下执行命令。
(1)启动配置
alter proxyconfig set enable_strict_kernel_release=false;
alter proxyconfig set automatic_match_work_thread=false;
(2)跑性能需要调整
alter proxyconfig set proxy_mem_limited='4G'; --防止oom
alter proxyconfig set enable_compression_protocol=false; --关闭压缩,降低cpu%
alter proxyconfig set slow_proxy_process_time_threshold='500ms';
alter proxyconfig set enable_ob_protocol_v2=false;
alter proxyconfig set enable_qos=false;
alter proxyconfig set syslog_level='error';
初始后需要调整的参数
[admin@localhost ~]$ mysql -h10.144.2.106 -uroot@sys -P2883 -p -c -A oceanbase
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
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]> alter proxyconfig set enable_strict_kernel_release=false;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter proxyconfig set automatic_match_work_thread=false;
Query OK, 0 rows affected (0.01 sec)
跑性能前需要调整的参数
[admin@localhost ~]$ mysql -h10.144.2.106 -uroot@sys -P2883 -p -c -A oceanbase
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
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]> alter proxyconfig set proxy_mem_limited='4G';
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter proxyconfig set enable_compression_protocol=false;
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> alter proxyconfig set slow_proxy_process_time_threshold='500ms';
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> alter proxyconfig set enable_ob_protocol_v2=false;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter proxyconfig set enable_qos=false;
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> alter proxyconfig set syslog_level='error';
Query OK, 0 rows affected (0.01 sec)
测试操作执行
以下命令均在 …/benchmarksql/run 目录下执行。按照以下步骤进行 TPC-C 测试:
导数
导数前调优
OceanBase 数据库导数据前sys租户调优
请在sys租户下执行。
在系统租户下执行 obclient -h$host_ip -P$host_port -uroot@sys -A
命令。
alter system set memory_chunk_cache_size ='0';
alter system set trx_try_wait_lock_timeout='0ms';
alter system set large_query_threshold='1s';
alter system set trace_log_slow_query_watermark='500ms';
alter system set syslog_io_bandwidth_limit='30m';
alter system set enable_async_syslog=true;
alter system set merger_warm_up_duration_time='0';
alter system set merger_switch_leader_duration_time='0';
alter system set large_query_worker_percentage=10;
alter system set builtin_db_data_verify_cycle = 0;
alter system set enable_merge_by_turn = False;
alter system set minor_merge_concurrency=30;
alter system set memory_limit_percentage = 85;
alter system set memstore_limit_percentage = 80;
alter system set freeze_trigger_percentage = 30;
alter system set enable_syslog_recycle='True';
alter system set max_syslog_file_count=100;
alter system set minor_freeze_times=500;
alter system set minor_compact_trigger=5;
alter system set max_kept_major_version_number=1;
alter system set sys_bkgd_io_high_percentage = 90;
alter system set sys_bkgd_io_low_percentage = 70;
alter system set merge_thread_count = 45;
alter system set merge_stat_sampling_ratio = 1;
alter system set writing_throttling_trigger_percentage=75 tenant=xxx;
alter system set writing_throttling_maximum_duration='15m';
set global ob_plan_cache_percentage=20;
alter system set enable_perf_event='false';
alter system set use_large_pages='true';
alter system set micro_block_merge_verify_level=0;
alter system set builtin_db_data_verify_cycle=20;
alter system set net_thread_count=4;
[admin@localhost ~]$ mysql -h10.144.2.111 -uroot@sys -P2881 -p -c -A oceanbase
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3222798340
Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
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]> alter system set memory_chunk_cache_size ='0';
Query OK, 0 rows affected (0.03 sec)
MySQL [oceanbase]> alter system set trx_try_wait_lock_timeout='0ms';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set large_query_threshold='1s';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set trace_log_slow_query_watermark='500ms';
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set syslog_io_bandwidth_limit='30m';
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set enable_async_syslog=true;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set merger_warm_up_duration_time='0';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set merger_switch_leader_duration_time='0';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set large_query_worker_percentage=10;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set builtin_db_data_verify_cycle = 0;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set enable_merge_by_turn = False;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set minor_merge_concurrency=30;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set memory_limit_percentage = 85;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set memstore_limit_percentage = 80;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set freeze_trigger_percentage = 30;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set enable_syslog_recycle='True';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set max_syslog_file_count=100;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set minor_freeze_times=500;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set minor_compact_trigger=5;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set max_kept_major_version_number=1;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set sys_bkgd_io_high_percentage = 90;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set sys_bkgd_io_low_percentage = 70;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set merge_thread_count = 45;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set merge_stat_sampling_ratio = 1;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set writing_throttling_trigger_percentage=75 tenant=tpcc;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set writing_throttling_maximum_duration='15m';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> set global ob_plan_cache_percentage=20;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set enable_perf_event='false';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set use_large_pages='true';
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> alter system set micro_block_merge_verify_level=0;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set builtin_db_data_verify_cycle=20;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> alter system set net_thread_count=4;
Query OK, 0 rows affected (0.02 sec)
OceanBase 数据库导数据前业务租户调优
请在具体用户下执行。在测试用户下执行 obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A
命令。
数据库下租户设置,防止事务超时
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=100;
/*
parallel_max_servers推荐设置为测试租户分配的resource unit cpu数的10倍
如测试租户使用的unit配置为:create resource unit $unit_name max_cpu 26
那么该值设置为260
parallel_server_target推荐设置为parallel_max_servers * 机器数*0.8
那么该值为260*3*0.8=624
*/
set global parallel_max_servers=260;
set global parallel_servers_target=624;
执行调优参数
[admin@localhost ~]$ obclient -h10.144.2.106 -ubenchmarksql@tpcc -P2883 -pbenchmarksql -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
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]> set global ob_query_timeout=36000000000;
Query OK, 0 rows affected (0.012 sec)
MySQL [oceanbase]> set global ob_trx_timeout=36000000000;
Query OK, 0 rows affected (0.102 sec)
MySQL [oceanbase]> set global max_allowed_packet=67108864;
Query OK, 0 rows affected (0.001 sec)
MySQL [oceanbase]> set global ob_sql_work_area_percentage=100;
Query OK, 0 rows affected (0.002 sec)
MySQL [oceanbase]> set global parallel_max_servers=260;
Query OK, 0 rows affected, 1 warning (0.012 sec)
MySQL [oceanbase]> set global parallel_servers_target=624;
Query OK, 0 rows affected (0.012 sec)
调优参数设置完毕请重启集群
obd cluster restart $cluster_name
导数执行
-
运行以下命令,初始化环境:
./runDatabaseDestroy.sh prop.ob
-
运行以下命令,创建表并导入数据:
./runDatabaseBuild.sh prop.ob
导数后调优
合并
-
执行合并(需要使用sys租户登录)
Major 合并将当前大版本的 SSTable 和 MemTable 与前一个大版本的全量静态数据进行合并,使存储层统计信息更准确,生成的执行计划更稳定。
MySQL [(none)]> use oceanbase Database changed MySQL [oceanbase]> alter system major freeze; Query OK, 0 rows affected
-
查看合并是否完成
MySQL [oceanbase]> select name,value from oceanbase.__all_zone where name='frozen_version' or name='last_merged_version'; +---------------------+-------+ | name | value | +---------------------+-------+ | frozen_version | 2 | | last_merged_version | 2 | | last_merged_version | 2 | | last_merged_version | 2 | | last_merged_version | 2 | +---------------------+-------+
frozen_version
和last_merged_version
的值相等即表示合并完成。
OceanBase 数据库压力测试阶段sys租户调优
请在sys租户下执行。
在系统租户下执行 obclient -h$host_ip -P$host_port -uroot@sys -A
命令。
##如果导入阶段开启了限速需要关闭
alter system set writing_throttling_trigger_percentage=100 tenant=xxx;
alter system set writing_throttling_maximum_duration='1h';
alter system set memstore_limit_percentage = 80;
alter system set freeze_trigger_percentage = 30;
alter system set large_query_threshold = '200s';
alter system set trx_try_wait_lock_timeout = '0ms';
alter system set cpu_quota_concurrency = 4;
alter system set minor_warm_up_duration_time = 0;
alter system set minor_freeze_times=500;
alter system set minor_compact_trigger=3;
alter system set sys_bkgd_io_high_percentage = 90;
alter system set sys_bkgd_io_low_percentage = 70;
alter system set minor_merge_concurrency =20;
alter system set builtin_db_data_verify_cycle = 0;
alter system set trace_log_slow_query_watermark = '10s';
alter system set gts_refresh_interval='500us';
alter system set server_permanent_offline_time='36000s';
alter system set weak_read_version_refresh_interval=0;
alter system set _ob_get_gts_ahead_interval = '5ms';
##为频繁空查的宏块建立bloomfilter并缓存,减少磁盘IO和CPU消耗,提升写入性能
alter system set bf_cache_priority = 10;
alter system set user_block_cache_priority=5;
alter system set merge_stat_sampling_ratio = 0;
##close sql audit
alter system set enable_sql_audit=false;
##调整日志级别及保存个数
alter system set syslog_level='PERF';
alter system set max_syslog_file_count=100;
alter system set enable_syslog_recycle='True';
alter system set ob_enable_batched_multi_statement=true tenant=all;
alter system set _cache_wash_interval = '1m';
alter system set plan_cache_evict_interval = '30s';
alter system set enable_one_phase_commit=false;
alter system set enable_monotonic_weak_read = false;
OceanBase 数据库测试阶段业务租户调优
在进行测试TPCC的租户下执行。
在测试用户下执行 obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A
命令。
alter system set _clog_aggregation_buffer_amount=8;
alter system set _flush_clog_aggregation_buffer_timeout='1ms';
[admin@localhost ~]$ mysql -h10.144.2.111 -uroot@tpcc -P2881 -p -c -A oceanbase
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3222798341
Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
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]> alter system set _clog_aggregation_buffer_amount=8;
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> alter system set _flush_clog_aggregation_buffer_timeout='1ms';
Query OK, 0 rows affected (0.00 sec)
TPCC测试操作执行
执行以下命令,执行压力测试:
./runBenchmark.sh prop.ob
测试结果
-
2000仓,200并发
Term-00, Running Average tpmTOTAL: 742679.46 Current tpmTOTAL: 49075944 Memory Usage: 964MB / 2834MB 22:50:33,344 [Thread-36] INFO jTPCC : Term-00, 22:50:33,344 [Thread-36] INFO jTPCC : Term-00, 22:50:33,344 [Thread-36] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 334225.02 22:50:33,344 [Thread-36] INFO jTPCC : Term-00, Measured tpmTOTAL = 742589.64 22:50:33,345 [Thread-36] INFO jTPCC : Term-00, Session Start = 2022-01-14 22:40:33 22:50:33,345 [Thread-36] INFO jTPCC : Term-00, Session End = 2022-01-14 22:50:33 22:50:33,345 [Thread-36] INFO jTPCC : Term-00, Transaction Count = 7426997
-
2000仓,400并发
Term-00, Running Average tpmTOTAL: 894273.20 Current tpmTOTAL: 59132196 Memory Usage: 2937MB / 3748MB 00:46:04,613 [Thread-368] INFO jTPCC : Term-00, 00:46:04,614 [Thread-368] INFO jTPCC : Term-00, 00:46:04,614 [Thread-368] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 402109.83 00:46:04,614 [Thread-368] INFO jTPCC : Term-00, Measured tpmTOTAL = 894131.49 00:46:04,614 [Thread-368] INFO jTPCC : Term-00, Session Start = 2022-01-15 00:36:04 00:46:04,614 [Thread-368] INFO jTPCC : Term-00, Session End = 2022-01-15 00:46:04 00:46:04,614 [Thread-368] INFO jTPCC : Term-00, Transaction Count = 8943132
-
2000仓,600并发
Term-00, Running Average tpmTOTAL: 1036197.84 Current tpmTOTAL: 68482356 Memory Usage: 2370MB / 2662MB 01:33:52,474 [Thread-449] INFO jTPCC : Term-00, 01:33:52,474 [Thread-449] INFO jTPCC : Term-00, 01:33:52,474 [Thread-449] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 466181.02 01:33:52,474 [Thread-449] INFO jTPCC : Term-00, Measured tpmTOTAL = 1035911.66 01:33:52,475 [Thread-449] INFO jTPCC : Term-00, Session Start = 2022-01-15 01:23:52 01:33:52,475 [Thread-449] INFO jTPCC : Term-00, Session End = 2022-01-15 01:33:52 01:33:52,475 [Thread-449] INFO jTPCC : Term-00, Transaction Count = 10362586
-
2000仓,800并发
02:01:10,202 [Thread-514] INFO jTPCC : Term-00, 02:01:10,202 [Thread-514] INFO jTPCC : Term-00, 02:01:10,202 [Thread-514] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 482945.87 02:01:10,202 [Thread-514] INFO jTPCC : Term-00, Measured tpmTOTAL = 1073274.03 02:01:10,202 [Thread-514] INFO jTPCC : Term-00, Session Start = 2022-01-15 01:51:09 02:01:10,202 [Thread-514] INFO jTPCC : Term-00, Session End = 2022-01-15 02:01:10 02:01:10,202 [Thread-514] INFO jTPCC : Term-00, Transaction Count = 10739018
注意事项
-
终端数量无效。报错信息如下:
Invalid number of terminals!
这是 prop.oceanbase 中设置的 terminals 值不对,需填写 numTerminals <= 0 || numTerminals > 10*numWarehouses 范围内的 terminals 值。
-
事务超时。报错信息如下:
Worker 198: ERROR: Transaction is timeout Worker 192: ERROR: Transaction is timeout
需增大超时时间,测试租户下执行set global ob_query_timeout=36000000000;set global ob_trx_timeout=36000000000。
-
修改Obproxy参数,开启二次路由,提高性能
alter proxyconfig set enable_ob_protocol_v2=True; alter proxyconfig set enable_reroute=True; alter proxyconfig set enable_index_route=True;
-
关闭SQL审计文章来源:https://www.toymoban.com/news/detail-406999.html
ALTER SYSTEM SET enable_sql_audit = false;
-
修改关闭性能收集文章来源地址https://www.toymoban.com/news/detail-406999.html
alter system set enable_perf_event=false;
到了这里,关于实践练习五(可选):对 OceanBase 做性能测试的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!