服务器环境

12核48G，操作系统为centos 7.9系统，单节点三副本1-1-1集群。

1、准备benchmarksql

1）下载地址：https://github.com/meiq4096/benchmarksql-5.0。

2）把benchmarksql目录放置在/home/admin目录下。

 

3）修改或新建/home/admin/benchmarksql-5.0/props.ob文件。

//vi props.ob

db=oracle

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

conn=jdbc:oceanbase://127.0.0.1:2883/obtest?useUnicode=true&characterEncoding=utf-8

user=henry@tenant_henry#obcluster

password=mydb

warehouses=2

loadWorkers=1

terminals=5

//To run specified transactions per terminal- runMins must equal zero

runTxnsPerTerminal=0

//To run for specified minutes- runTxnsPerTerminal must equal zero

runMins=10

//Number of total transactions per minute

limitTxnsPerMin=0

//Set to true to run in 4.x compatible mode. Set to false to use the

//entire configured database evenly.

terminalWarehouseFixed=true

//The following five values must add up to 100

newOrderWeight=45

paymentWeight=43

orderStatusWeight=4

deliveryWeight=4

stockLevelWeight=4

// Directory name to create for collecting detailed result data.

// Comment this out to suppress.

resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

osCollectorScript=./misc/os_collector_linux.py

osCollectorInterval=1

2、检查sql审计开关

show variables like 'ob_enable_sql_audit';

 

如未开启则执行

set global ob_enable_sql_audit = on;

3、优化一下测试租户的设置，避免大事务超时。

用root用户登录tenant_herny租户

set global ob_timestamp_service='GTS' ;

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=100;

set global parallel_max_servers=800;

set global parallel_servers_target=800;

 

4、执行建表脚本

执行./runSQL.sh props.ob sql.common/tableCreates.sql创建表

 

执行./runLoader props.ob导入数据

 

 

创建索引

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;

 

5、执行测试

1）执行命令./runBenchmark.sh props.ob开始测试

 

2）查看TOP 15

select sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time

from gv$sql_audit s

where 1=1

and user_name='henry'

group by sql_id

order by avg_elapsed_time desc limit 15;

 

这里sql_id有乱码，暂时没找到原因。为了更清晰查看结果，用数据库连接工具执行上述查询。

3）分析TOP 15

select sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time,

round(avg(execute_time)) avg_exec_time,

s.svr_ip,s.svr_port,s.tenant_id,s.plan_id

from gv$sql_audit s

where 1=1

and user_name='henry'

group by sql_id

order by avg_elapsed_time desc limit 15;

 

这里sql_id有乱码，暂时没找到原因。为了更清晰查看结果，用数据库连接工具执行上述查询。

select query_sql from gv$sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F';

 

select query_sql from gv$sql_audit where sql_id='5984364296F35BE1B71CD5622426385A';

 

select query_sql from gv$sql_audit where sql_id='482BA7822AE7BE644CEBEB55213E7284';

 

 

4）分析执行计划

对上述3条sql进行分析。（sql_id分别为：F59A700FA168324279B0DBC25E19760F、5984364296F35BE1B71CD5622426385A、482BA7822AE7BE644CEBEB55213E7284。）

第一条的实际执行计划：

select ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.gv$plan_cache_plan_explain  where tenant_id=1001 and ip = '172.27.17.82' and port=4882 and plan_id=305;

 

第一条的解释执行计划：

explain SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 2 AND s_quantity < 16 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = 2 AND d_id = 8         )     );

 

第二条的实际执行计划：

select ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.gv$plan_cache_plan_explain  where tenant_id=1001 and ip = '172.27.17.82' and port=3882 and plan_id=74;

 

第二条的解释执行计划：

explain SELECT ol_i_id, ol_supply_w_id, ol_quantity,        ol_amount, ol_delivery_d     FROM bmsql_order_line     WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 2662     ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number;

 

第三条的实际执行计划：

select ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.gv$plan_cache_plan_explain  where tenant_id=1001 and ip = '172.27.17.82' and port=4882 and plan_id=322;

 

第三条的解释执行计划：

explain UPDATE bmsql_order_line     SET ol_delivery_d = '2022-10-31 14:26:08.366'     WHERE ol_w_id = 2 AND ol_d_id = 1 AND ol_o_id = 2109;

 

6、问题

1）props.ob文件中warehouses、loadWorkers和terminals参数分别表示仓库数、仓库加载时的并发数和性能压测时的并发数，如果服务器配置不高，测试时注意把这几个值调小一些。我的是12C48G服务器、单节点部署1-1-1集群，配置为warehouses=2、loadWorkers=1、terminals=5。

2）gv$sql_audit视图中，sql_id存在乱码，选择了三个没有乱码的sql_id，查询对应的执行计划。乱码问题暂时未解决。