一、系统环境
系统配置：4C16G500G
操作系统：centOS 7.9.2009
二、系统设置
1、Jdk
Java -version
2、sys租户参数
alter system set enable_auto_leader_switch=false;
alter system set enable_one_phase_commit=false;
alter system set enable_monotonic_weak_read = true;
alter system set weak_read_version_refresh_interval='5s';
alter system _ob_minor_merge_schedule_interval='5s';
alter system set memory_limit_percentage = 90;
alter system set memstore_limit_percentage = 55;
alter system set freeze_trigger_percentage = 70;
alter system set minor_freeze_times = 50;
alter system set minor_warm_up_duration_time = 0;
alter system set merge_thread_count = 32;
alter system set minor_merge_concurrency = 8;
alter system set _mini_merge_concurrency = 4;
3、PROXY 参数
alter proxyconfig set proxy_mem_limited='4G';
alter proxyconfig set enable_compression_protocol=false;
alter proxyconfig set work_thread_num=32;
alter proxyconfig set enable_compression_protocol=false;
alter proxyconfig set enable_metadb_used=false;
alter proxyconfig set enable_standby=false;
alter proxyconfig set enable_strict_stat_time=false;
alter proxyconfig set use_local_dbconfig=true;
4、设置租户，防止事务超时
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;
三、BenchmarkSQL安装配置
1、下载BenchmarkSQL
https://github.com/obpilot/benchmarksql-5.0
2、解压BenchmarkSQL
unzip benchmarksql-5.0-master.zip
复制到root目录下
cp -r benchmarksql-5.0-master /root/benchmarksql/
3、修改配置文件
vim /home/benchmarksql-5.0-master/run/props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@obmysql#obdemo
password=tpcc123
warehouses=2
loadWorkers=10
terminals=10
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=1
//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
4、登陆数据库
obclient -h 192.168.3.232 -P 2883 -uroot@obmysql#obdemo -p -c -A oceanbase
5、创建测试库
6、创建用户
create user tpcc identified by 'tpcc123';
7、授权
grant all privileges on tpcc.* to tpcc ;
8、修改脚本
runSQL.sh
runLoader.sh
runBenchmark.sh
这三个文件中的这一行，加载绝对路径。
source funcs.sh $1 ###############原文件
source /root/benchmarksql/run/funcs.sh $1 ##############修改后
9、创建表
sh runSQL.sh props.ob sql.common/tableCreates.sql
10、加载数据
sh runLoader.sh props.ob
11、创建索引
obclient -h 192.168.3.232 -P 2883 -utpcc@obmysql#obdemo -p -c -A tpcc
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;
12、运行测试
sh runBenchmark.sh props.ob
13、TPC-C TOP SQL分析
13.1查询TOP10 sql
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='tpcc' and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 10;
13.2对 elapsed 时间最长的前三条 sql 进行分析
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 request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 3;
select distinct query_sql from gv$sql_audit where sql_id='B1F3DF2C7803B02C326BAB74BB140979';
select distinct query_sql from gv$sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F';
select distinct query_sql from gv$sql_audit where sql_id='958908427DC1272ECC2E393F2776589F'\G;
14、分析执行计划
explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND s_quantity < 14 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 = 1 AND d_id = 3))
查询plan_id
SELECT * FROM v$plan_cache_plan_stat WHERE tenant_id= 1001 AND STATEMENT LIKE 'SELECT count(*) AS low_stock FROM%'\G
实际执行计划
SELECT * FROM v$plan_cache_plan_explain WHERE tenant_id = 1001 AND plan_id = 180;
