一、环境准备
1.下载benchmarksql
wget -c https://jaist.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
unzip benchmarksql-5.0.zip
2.编辑配置文件
[admin@10 run]$ cd /home/admin/benchmarksql-5.0/run
[admin@10 run]$ vi props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
//driver=com.alipay.oceanbase.jdbc.Driver
conn=jdbc:oceanbase://127.0.0.1:2883/obtest?useUnicode=true&characterEncoding=utf-8
user=root@sys#obdemo
password=123456
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=5
//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@localhost run]# pwd
/home/admin/benchmarksql-5.0/run
3.执行建表脚本
[admin@10 run]$ ./runSQL.sh props.ob sql.common/tableCreates.sql
# ------------------------------------------------------------
# Loading SQL file sql.common/tableCreates.sql
# ------------------------------------------------------------
4.导入数据
[admin@10 run]$ ./runLoader.sh props.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.0.0.1:2883/obtest?useUnicode=true&characterEncoding=utf-8
user=root@sys#obdemo
password=***********
warehouses=2
loadWorkers=1
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 1
Worker 000: Loading Warehouse 1 done
Worker 000: Loading Warehouse 2
Worker 000: Loading Warehouse 2 done
MySQL [obtest]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
| obtest |
+--------------------+
8 rows in set (0.004 sec)
MySQL [obtest]> use obtest;
Database changed
MySQL [obtest]> show tables;
+------------------+
| Tables_in_obtest |
+------------------+
| bmsql_config |
| bmsql_customer |
| bmsql_district |
| bmsql_history |
| bmsql_item |
| bmsql_new_order |
| bmsql_oorder |
| bmsql_order_line |
| bmsql_stock |
| bmsql_warehouse |
| customer |
| district |
| history |
| item |
| new_orders |
| order_line |
| orders |
| stock |
| warehouse |
+------------------+
19 rows in set (0.002 sec)
MySQL [obtest]> select count(*) from bmsql_item;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.210 sec)
5.执行测试脚本
[admin@10 run]$ ./runBenchmark.sh props.ob
18:26:13,935 [main] INFO jTPCC : Term-00,
18:26:13,938 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:26:13,938 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
18:26:13,939 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:26:13,939 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
18:26:13,940 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
18:26:13,945 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
18:26:13,945 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:26:13,945 [main] INFO jTPCC : Term-00,
18:26:13,945 [main] INFO jTPCC : Term-00, db=oracle
18:26:13,945 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
18:26:13,945 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://10.0.2.15:2883/obtest?useUnicode=true&characterEncoding=utf-8
18:26:13,945 [main] INFO jTPCC : Term-00, user=root@sys#obdemo
18:26:13,945 [main] INFO jTPCC : Term-00,
18:26:13,945 [main] INFO jTPCC : Term-00, warehouses=2
18:26:13,945 [main] INFO jTPCC : Term-00, terminals=5
18:26:13,947 [main] INFO jTPCC : Term-00, runMins=5
18:26:13,947 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
18:26:13,947 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
18:26:13,947 [main] INFO jTPCC : Term-00,
18:26:13,947 [main] INFO jTPCC : Term-00, newOrderWeight=45
18:26:13,947 [main] INFO jTPCC : Term-00, paymentWeight=43
18:26:13,948 [main] INFO jTPCC : Term-00, orderStatusWeight=4
18:26:13,948 [main] INFO jTPCC : Term-00, deliveryWeight=4
18:26:13,948 [main] INFO jTPCC : Term-00, stockLevelWeight=4
18:26:13,948 [main] INFO jTPCC : Term-00,
18:26:13,948 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
18:26:13,949 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
18:26:13,949 [main] INFO jTPCC : Term-00,
18:26:13,964 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-02-02_182613/run.properties
18:26:13,964 [main] INFO jTPCC : Term-00, created my_result_2022-02-02_182613/data/runInfo.csv for runID 21
18:26:13,964 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-02-02_182613/data/result.csv
18:26:13,965 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
18:26:13,965 [main] INFO jTPCC : Term-00, osCollectorInterval=1
18:26:13,965 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
18:26:13,965 [main] INFO jTPCC : Term-00, osCollectorDevices=null
18:26:14,059 [main] INFO jTPCC : Term-00,
18:26:15,031 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 24
18:26:15,032 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 121
18:26:15,032 [main] INFO jTPCC : Term-00, Term-00, Running Average tpmTOTAL: 7.33 Cur18:32:31,802 [Thread-4] INFO jTPCC : Term-00,
18:32:31,802 [Thread-4] INFO jTPCC : Term-00,
18:32:31,802 [Thread-4] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 2.86
18:32:31,803 [Thread-4] INFO jTPCC : Term-00, Measured tpmTOTAL = 7.33
18:32:31,803 [Thread-4] INFO jTPCC : Term-00, Session Start = 2022-02-02 18:26:15
18:32:31,803 [Thread-4] INFO jTPCC : Term-00, Session End = 2022-02-02 18:32:31
18:32:31,803 [Thread-4] INFO jTPCC : Term-00, Transaction Count = 45
二、查看TOP 10
SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT
FROM oceanbase.gv$sql_audit t1 WHERE tenant_id = 1001 AND
IS_EXECUTOR_RPC = 0 AND request_time > (time_to_usec(now()) - 10000000) AND
request_time < time_to_usec (now())
GROUP BY t1.sql_id ORDER BY QPS DESC LIMIT 10;
三、查看执行计划
SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 2119;
SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 1 AND s_i_id = 44448 FOR UPDATE;
UPDATE bmsql_stock SET s_quantity = 50, s_ytd = s_ytd + 1, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 2 AND s_i_id = 12177;
SQL: SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 2119;
查看格式化信息
======================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------
|0 |SCALAR GROUP BY| |1 |720140|
|1 | TABLE SCAN |bmsql_order_line|1 |720140|
======================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_SUM(bmsql_order_line.ol_amount(0x7f42ab91ab10))(0x7f42ab91a500)]), filter(nil),
group(nil), agg_func([T_FUN_SUM(bmsql_order_line.ol_amount(0x7f42ab91ab10))(0x7f42ab91a500)])
1 - output([bmsql_order_line.ol_amount(0x7f42ab91ab10)]), filter([bmsql_order_line.ol_w_id(0x7f433871e0f0) = 2(0x7f433871da70)], [bmsql_order_line.ol_d_id(0x7f42ab919490) = 4(0x7f42ab918e10)], [bmsql_order_line.ol_o_id(0x7f42ab91a270) = 2119(0x7f42ab919bf0)]),
access([bmsql_order_line.ol_w_id(0x7f433871e0f0)], [bmsql_order_line.ol_d_id(0x7f42ab919490)], [bmsql_order_line.ol_o_id(0x7f42ab91a270)], [bmsql_order_line.ol_amount(0x7f42ab91ab10)]), partitions(p0),
is_index_back=false, filter_before_indexback[false,false,false],
range_key([bmsql_order_line.__pk_increment(0x7f4338727940)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
*/
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "obtest.bmsql_order_line"@"SEL$1")
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
bmsql_order_line:table_rows:335632, physical_range_rows:606672, logical_range_rows:335632, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[bmsql_order_line], estimation info[table_id:1099511677808, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643711135349663-1643711137230299, logical_rc:0, physical_rc:268776), (table_type:7, version:1643711137230299-1643773772120042-1643773772120042, logical_rc:335423, physical_rc:337244), (table_type:5, version:1643711137230299-1643773772120042-1643773772120042, logical_rc:0, physical_rc:0), (table_type:0, version:1643773772120042-1643773772120042-9223372036854775807, logical_rc:209, physical_rc:652)]
Parameters
-------------------------------------
SQL: SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 1 AND s_i_id = 44448 FOR UPDATE;
查看格式化信息
============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------
|0 |TABLE SCAN|bmsql_stock|11 |250911|
============================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_stock.s_quantity(0x7f42973199b0)], [bmsql_stock.s_data(0x7f4297319ed0)], [bmsql_stock.s_dist_01(0x7f429731a3f0)], [bmsql_stock.s_dist_02(0x7f429731a910)], [bmsql_stock.s_dist_03(0x7f433871eba0)], [bmsql_stock.s_dist_04(0x7f433871f0c0)], [bmsql_stock.s_dist_05(0x7f433871f5e0)], [bmsql_stock.s_dist_06(0x7f433871fb00)], [bmsql_stock.s_dist_07(0x7f4338720020)], [bmsql_stock.s_dist_08(0x7f4338720540)], [bmsql_stock.s_dist_09(0x7f4338720be0)], [bmsql_stock.s_dist_10(0x7f4338721100)]), filter([bmsql_stock.s_w_id(0x7f433871e0e0) = 1(0x7f433871da60)], [bmsql_stock.s_i_id(0x7f4297319490) = 44448(0x7f4297318e10)]),
access([bmsql_stock.__pk_increment(0x7f4297363a50)], [bmsql_stock.s_w_id(0x7f433871e0e0)], [bmsql_stock.s_i_id(0x7f4297319490)], [bmsql_stock.s_quantity(0x7f42973199b0)], [bmsql_stock.s_data(0x7f4297319ed0)], [bmsql_stock.s_dist_01(0x7f429731a3f0)], [bmsql_stock.s_dist_02(0x7f429731a910)], [bmsql_stock.s_dist_03(0x7f433871eba0)], [bmsql_stock.s_dist_04(0x7f433871f0c0)], [bmsql_stock.s_dist_05(0x7f433871f5e0)], [bmsql_stock.s_dist_06(0x7f433871fb00)], [bmsql_stock.s_dist_07(0x7f4338720020)], [bmsql_stock.s_dist_08(0x7f4338720540)], [bmsql_stock.s_dist_09(0x7f4338720be0)], [bmsql_stock.s_dist_10(0x7f4338721100)]), partitions(p0),
is_index_back=false, filter_before_indexback[false,false],
range_key([bmsql_stock.__pk_increment(0x7f4297363a50)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
*/
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "obtest.bmsql_stock"@"SEL$1")
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
bmsql_stock:table_rows:102930, physical_range_rows:202313, logical_range_rows:102930, index_back_rows:0, output_rows:10, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[bmsql_stock], estimation info[table_id:1099511677810, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643711133341028-1643711137434089, logical_rc:0, physical_rc:97070), (table_type:7, version:1643711137434089-1643773772483289-1643773772483289, logical_rc:102930, physical_rc:104976), (table_type:5, version:1643711137434089-1643773772483289-1643773772483289, logical_rc:0, physical_rc:0), (table_type:0, version:1643773772483289-1643773772483289-9223372036854775807, logical_rc:0, physical_rc:267)]
Parameters
-------------------------------------
SQL: UPDATE bmsql_stock SET s_quantity = 50, s_ytd = s_ytd + 1, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 2 AND s_i_id = 12177;
查看格式化信息
=============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
---------------------------------------------
|0 |UPDATE | |11 |264318|
|1 | TABLE SCAN|bmsql_stock|11 |264308|
=============================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{bmsql_stock: ({bmsql_stock: (bmsql_stock.__pk_increment(0x7f429730c4d0), bmsql_stock.s_w_id(0x7f429730c760), bmsql_stock.s_i_id(0x7f429730c9f0), bmsql_stock.s_quantity(0x7f433871d590), bmsql_stock.s_ytd(0x7f433871da60), bmsql_stock.s_order_cnt(0x7f4297309b50), bmsql_stock.s_remote_cnt(0x7f429730a930), bmsql_stock.s_data(0x7f429730cc80), bmsql_stock.s_dist_01(0x7f429730cf10), bmsql_stock.s_dist_02(0x7f429730d1a0), bmsql_stock.s_dist_03(0x7f429730d430), bmsql_stock.s_dist_04(0x7f429730d6c0), bmsql_stock.s_dist_05(0x7f429730d950), bmsql_stock.s_dist_06(0x7f429730dbe0), bmsql_stock.s_dist_07(0x7f429730de70), bmsql_stock.s_dist_08(0x7f429730e100), bmsql_stock.s_dist_09(0x7f429730e510), bmsql_stock.s_dist_10(0x7f429730e7a0))})}]),
update([bmsql_stock.s_quantity(0x7f433871d590)=?], [bmsql_stock.s_ytd(0x7f433871da60)=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_ytd(0x7f433871da60) + 1(0x7f433871e1c0), INT(-1, 0))(0x7f429736e910))(0x7f429736d430)], [bmsql_stock.s_order_cnt(0x7f4297309b50)=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_order_cnt(0x7f4297309b50) + 1(0x7f429730a2b0), INT(-1, 0))(0x7f4297370950))(0x7f429736f470)], [bmsql_stock.s_remote_cnt(0x7f429730a930)=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_remote_cnt(0x7f429730a930) + 0(0x7f429730b090), INT(-1, 0))(0x7f4297372990))(0x7f42973714b0)])
1 - output([bmsql_stock.__pk_increment(0x7f429730c4d0)], [bmsql_stock.s_w_id(0x7f429730c760)], [bmsql_stock.s_i_id(0x7f429730c9f0)], [bmsql_stock.s_quantity(0x7f433871d590)], [bmsql_stock.s_ytd(0x7f433871da60)], [bmsql_stock.s_order_cnt(0x7f4297309b50)], [bmsql_stock.s_remote_cnt(0x7f429730a930)], [bmsql_stock.s_data(0x7f429730cc80)], [bmsql_stock.s_dist_01(0x7f429730cf10)], [bmsql_stock.s_dist_02(0x7f429730d1a0)], [bmsql_stock.s_dist_03(0x7f429730d430)], [bmsql_stock.s_dist_04(0x7f429730d6c0)], [bmsql_stock.s_dist_05(0x7f429730d950)], [bmsql_stock.s_dist_06(0x7f429730dbe0)], [bmsql_stock.s_dist_07(0x7f429730de70)], [bmsql_stock.s_dist_08(0x7f429730e100)], [bmsql_stock.s_dist_09(0x7f429730e510)], [bmsql_stock.s_dist_10(0x7f429730e7a0)], [remove_const(?)(0x7f42973196a0)], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_ytd(0x7f433871da60) + 1(0x7f433871e1c0), INT(-1, 0))(0x7f429736e910))(0x7f429736d430)], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_order_cnt(0x7f4297309b50) + 1(0x7f429730a2b0), INT(-1, 0))(0x7f4297370950))(0x7f429736f470)], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_remote_cnt(0x7f429730a930) + 0(0x7f429730b090), INT(-1, 0))(0x7f4297372990))(0x7f42973714b0)]), filter([bmsql_stock.s_w_id(0x7f429730c760) = 2(0x7f429730ef00)], [bmsql_stock.s_i_id(0x7f429730c9f0) = 12177(0x7f429730fa50)]),
access([bmsql_stock.s_quantity(0x7f433871d590)], [bmsql_stock.s_ytd(0x7f433871da60)], [bmsql_stock.s_order_cnt(0x7f4297309b50)], [bmsql_stock.s_remote_cnt(0x7f429730a930)], [bmsql_stock.__pk_increment(0x7f429730c4d0)], [bmsql_stock.s_w_id(0x7f429730c760)], [bmsql_stock.s_i_id(0x7f429730c9f0)], [bmsql_stock.s_data(0x7f429730cc80)], [bmsql_stock.s_dist_01(0x7f429730cf10)], [bmsql_stock.s_dist_02(0x7f429730d1a0)], [bmsql_stock.s_dist_03(0x7f429730d430)], [bmsql_stock.s_dist_04(0x7f429730d6c0)], [bmsql_stock.s_dist_05(0x7f429730d950)], [bmsql_stock.s_dist_06(0x7f429730dbe0)], [bmsql_stock.s_dist_07(0x7f429730de70)], [bmsql_stock.s_dist_08(0x7f429730e100)], [bmsql_stock.s_dist_09(0x7f429730e510)], [bmsql_stock.s_dist_10(0x7f429730e7a0)]), partitions(p0),
is_index_back=false, filter_before_indexback[false,false],
range_key([bmsql_stock.__pk_increment(0x7f429730c4d0)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
*/
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"UPD$1" "obtest.bmsql_stock"@"UPD$1")
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
bmsql_stock:table_rows:102930, physical_range_rows:202323, logical_range_rows:102930, index_back_rows:0, output_rows:10, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[bmsql_stock], estimation info[table_id:1099511677810, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643711133341028-1643711137434089, logical_rc:0, physical_rc:97070), (table_type:7, version:1643711137434089-1643773772483289-1643773772483289, logical_rc:102930, physical_rc:104976), (table_type:5, version:1643711137434089-1643773772483289-1643773772483289, logical_rc:0, physical_rc:0), (table_type:0, version:1643773772483289-1643773772483289-9223372036854775807, logical_rc:0, physical_rc:277)]
Parameters
-------------------------------------
{obj:{"INT":50}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"INT", collation:"binary", coercibility:"NUMERIC"}}
四、FQA
[admin@10 run]$ ./runSQL.sh props.ob sql.common/tableCreates.sql
# ------------------------------------------------------------
# Loading SQL file sql.common/tableCreates.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
– 设置ORACLE_HOME环境变量（可以是任意目录），在ORACLE_HOME下建立lib目录，将OceanBase的jdbc驱动放入该目录.$ORACLE_HOME/lib/oceanbase-client-1.1.10.jar；经过上面的操作，benchmarksql便可以找到jdbc驱动了。
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
– 这个问题没有继续查，个人理解是因为mysql不支持序列的原因，所以mysql租户应该不支持建立序列。
./runSQL.sh: line 14: source: funcs.sh: file not found
– 这个问题——应该说不是问题，看其他同学使用绝对路径方式解决问题。实际可以直接执行脚本来解决‘./runSQL.sh props.ob sql.common/tableCreates.sql’