1、下载BenmarkSQL
2、解压安装
unzip benchmarksql-5.0-master.zip |
3、修改参数文件在目录benchmarksql-5.0-master/run/props.ob下面,编辑后内容如下：
[root@ob run]# cat props.ob db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://172.22.150.7:2883/trade?useUnicode=true&characterEncoding=utf-8 user=root@obtest#test password=Zls19830907)
warehouses=1 loadWorkers=5
terminals=5 //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 |
执行创建表脚本报错：
[root@ob run]# sh runSQL.sh props.ob sql.common/tableCreates.sql runSQL.sh: line 14: source: funcs.sh: file not found [root@ob run]# |
该报错解决如下，修改runSQL.sh文件，修改内容如下：
vi runSQL.sh # ---- # Load common functions # ---- source /tmp/benchmarksql-5.0-master/run/funcs.sh $1 |
在执行脚本：sh runSQL.sh props.ob sql.common/tableCreates.sql
[root@ob run]# sh runSQL.sh props.ob sql.common/tableCreates.sql # ------------------------------------------------------------ # Loading SQL file sql.common/tableCreates.sql # ------------------------------------------------------------ create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); 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) ); 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) ); 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) ); create sequence bmsql_hist_id_seq; You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'sequence bmsql_hist_id_seq' at line 1 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) ); create table bmsql_new_order ( no_w_id integer not null, no_d_id integer not null, no_o_id integer not null ); 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 ); 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) ); 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 ); 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) ); |
造数据：
[root@ob run]# sh runLoader.sh props.ob runLoader.sh: line 8: source: funcs.sh: file not found [root@ob run]# vi runLoader.sh #!/usr/bin/env bash
if [ $# -lt 1 ] ; then echo "usage: $(basename $0) PROPS_FILE [ARGS]" >&2 exit 2 fi
source funcs.sh $1 shift
setCP || exit 1
java -cp "$myCP" -Dprop=$PROPS LoadData $* |
解决办法类似，如下：
[root@ob run]# vi runLoader.sh #!/usr/bin/env bash
if [ $# -lt 1 ] ; then echo "usage: $(basename $0) PROPS_FILE [ARGS]" >&2 exit 2 fi
source /tmp/benchmarksql-5.0-master/run/funcs.sh $1 shift
setCP || exit 1
java -cp "$myCP" -Dprop=$PROPS LoadData $* |
在执行造数据脚本：
[root@ob run]# sh runLoader.sh props.ob Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://172.22.150.7:2883/trade?useUnicode=true&characterEncoding=utf-8 user=root@test#obtest password=*********** warehouses=1 loadWorkers=2 fileLocation (not defined) csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM Worker 001: Loading Warehouse 1 Worker 000: Loading ITEM done Worker 001: Loading Warehouse 1 done |
创建索引
create index bmsql_customer_idx1 on bmsql_customer(c_w_id,c_d_id,c_last,c_first); Query OK, 0 rows affected (2.574 sec)
obclient [trade]> create index bmsql_order_idx1 on bmsql_oorder(o_w_id,o_d_id,o_carrier_id,o_id) local; Query OK, 0 rows affected (1.274 sec) |
执行性能压测：
sh runBenchmark.sh props.ob 也需要修改该runBenchmark.sh路径 Term-00, Running Average tpmTOTAL: 19.70 Current tpmTOTAL: 204 Memory Usage: 25MB / 241MB 06:17:19,860 [Thread-1] INFO jTPCC : Term-00, 06:17:19,860 [Thread-1] INFO jTPCC : Term-00, 06:17:19,861 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 7.57 06:17:19,861 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 19.7 06:17:19,861 [Thread-1] INFO jTPCC : Term-00, Session Start = 2024-03-09 06:16:00 06:17:19,862 [Thread-1] INFO jTPCC : Term-00, Session End = 2024-03-09 06:17:19 06:17:19,862 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 25 |
查看前20 SQL的执行计划
obclient [oceanbase]> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time FROM v$sql_audit s WHERE s.db_name='trade' GROUP BY sql_id order by avg_elapsed_time desc limit 20; +----------------------------------+----------+------------------+---------------+ | sql_id | count(*) | avg_elapsed_time | avg_exec_time | +----------------------------------+----------+------------------+---------------+ | 4939724541E5F423190D4AF52F2ACCD7 | 1 | 993384 | 711934 | | F86483FB17A84B0EA8381F4FA4EBA1AA | 1 | 734731 | 729334 | | 1838E7E251B1BA4339CCC88C5146FB81 | 1 | 621995 | 618027 | | 7E4721D8365262EE06FC3AE4C343580A | 1 | 614337 | 611455 | | 8E574096EBB891E4B8AB89AB1E228D0C | 1 | 465439 | 460976 | | 44B9A0759F26D257A6809DA60454F3A4 | 1 | 418019 | 393424 | | 49E1673CDABDC24B09B1E20E10F29F55 | 1 | 414378 | 412815 | | C84218767866D760C2C9F33BF5A114A2 | 1 | 381376 | 228203 | | 7686EEE3A85D9D6FF5C0A0D0F2772377 | 1 | 370221 | 520 | | E97FF6485D3853BB783B0AA3D6E8B05D | 1 | 305043 | 303772 | | 3B6366A4BBB3F3DD91FEE90A15DF4C2A | 1 | 271564 | 266488 | | 2D1366676CD073B5199DF5888FB3B0D9 | 1 | 257289 | 252037 | | 2447C440D7D94DAA18896F2EEFBD4D4C | 1 | 207978 | 199833 | | A4525988E72C9537ED712A53518E5C6F | 1 | 184059 | 175067 | | EB7BE9AF0EC5544B58DBB4D74AD3D336 | 1 | 151798 | 142944 | | 7393444B5280DCEDF76E817AA70D51B2 | 1 | 139644 | 86200 | | A7BD91B95A5D4861C13E0994AFC106D0 | 1 | 49004 | 35975 | | EED5B82FBC97FA98E3F2C706A41CD449 | 2 | 30694 | 131 | | 01828903736DEE7D4C5F36EEF3616498 | 1 | 25697 | 249 | | ED9D53F3FD4BF914D81F9B7F5CD88F11 | 1 | 20847 | 263 | +----------------------------------+----------+------------------+---------------+ 20 rows in set (0.353 sec) |
查看执行计划
obclient [trade]> explain select * from bmsql_config; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =========================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------- |0 |TABLE SCAN|bmsql_config|4 |37 | ===========================================
Outputs & filters: ------------------------------------- 0 - output([bmsql_config.cfg_name], [bmsql_config.cfg_value]), filter(nil), access([bmsql_config.cfg_name], [bmsql_config.cfg_value]), partitions(p0) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.016 sec)
|
查看真实的执行计划
obclient [oceanbase]> select * from gv$plan_cache_plan_explain where tenant_id=1001 and plan_id=58 and IP='xxxx' and port='2882'; Empty set (0.002 sec)
obclient [oceanbase]> |
--获取某个DB平均执行时间最慢的20条SQL SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time FROM v$sql_audit s WHERE s.db_name='trade' GROUP BY sql_id order by avg_elapsed_time desc limit 20;
获取SQL文本和SQL_ID select distinct query_sql,plan_id,TENANT_ID from v$sql_audit where sql_id='A7BD91B95A5D4861C13E0994AFC106D0';
--查看真实执行计划 select * from gv$plan_cache_plan_explain where tenant_id=1001 and plan_id=58 and IP='xxxx' and port='2882'; |
