金融行业实践：对 OceanBase 做性能测试

作者简介：张霁：数据库架构师。

环境准备

由于手上正好有7台物理机，在作业三中会使用OBD直接部署了2:2:2架构的OceanBase集群。这里直接拿来进行TPC-C测试。

• 机器信息如下：

机器划分如下：

测试方案

• 使用 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 审计

ALTER SYSTEM SET enable_sql_audit = false;

修改关闭性能收集

alter system set enable_perf_event=false;

————————————————

附录：

练习题：

实践练习一（必选）：OceanBase Docker 体验 

实践练习二（必选）：手动部署 OceanBase 集群 

实践练习三（可选）：使用OBD 部署一个 三副本OceanBase 集群 

实践练习四（必选）：迁移 MySQL 数据到 OceanBase 集群 

实践练习五（可选）：对 OceanBase 做性能测试 

实践练习六（必选）：查看 OceanBase 执行计划 

还没交作业的小伙伴要抓紧啦！

可以免费 带走 OBCP 考试券喔~~

方法一： 完成四道必选练习

方法二： 任意一道练习题 ➕ 结业考试超过80分

已经有很多同学抢先答题了，

加入钉钉群（ 群号3582 5151 ），和大家一起学习、交流～～

进群二维码：