使用benchmarksql壓測資料庫,產生高消耗的sql並測試資料庫效能
壓測環境部署
benchmarksql下載
git clone https://github.com/meiq4096/benchmarksql-5.0.git
修改配置檔案
vi ./run/props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://192.168.56.20:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=datax@mq_t1
password=datax
warehouses=2
loadWorkers=2
terminals=5
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=3
//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@innodb-cluster01 run]# sh runSQL.sh props.ob sql.common/tableCreates.sql
runSQL.sh: line 14: source: funcs.sh: file not found
--問題
runSQL.sh 提示行數的funcs.sh改為絕對路徑
調整資料庫引數
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_servers_target=800;
裝載資料
[root@innodb-cluster01 run]# sh runLoader.sh props.ob
runLoader.sh: line 8: source: funcs.sh: file not found
--問題
runLoader.sh 提示行數的funcs.sh改為絕對路徑
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://192.168.56.20:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=datax@mq_t1
password=***********
warehouses=2
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 000: Loading Warehouse 2
Worker 001: Loading Warehouse 1 done
Worker 000: Loading Warehouse 2 done
建立索引
cat sql.common/indexCreates.sql
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
效能測試
[root@innodb-cluster01 run]#sh runBenchmark.sh props.ob
10:46:22,654 [main] INFO jTPCC : Term-00,
10:46:22,658 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:46:22,658 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
10:46:22,658 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:46:22,658 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
10:46:22,658 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
10:46:22,661 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
10:46:22,661 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:46:22,661 [main] INFO jTPCC : Term-00,
10:46:22,661 [main] INFO jTPCC : Term-00, db=oracle
10:46:22,661 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
10:46:22,661 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://192.168.56.20:2883/tpcc?useUnicode=true&characterEncoding=utf-8
10:46:22,661 [main] INFO jTPCC : Term-00, user=datax@mq_t1
10:46:22,662 [main] INFO jTPCC : Term-00,
10:46:22,662 [main] INFO jTPCC : Term-00, warehouses=2
10:46:22,662 [main] INFO jTPCC : Term-00, terminals=5
10:46:22,665 [main] INFO jTPCC : Term-00, runMins=3
10:46:22,665 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
10:46:22,665 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
10:46:22,665 [main] INFO jTPCC : Term-00,
10:46:22,665 [main] INFO jTPCC : Term-00, newOrderWeight=45
10:46:22,665 [main] INFO jTPCC : Term-00, paymentWeight=43
10:46:22,666 [main] INFO jTPCC : Term-00, orderStatusWeight=4
10:46:22,666 [main] INFO jTPCC : Term-00, deliveryWeight=4
10:46:22,666 [main] INFO jTPCC : Term-00, stockLevelWeight=4
10:46:22,666 [main] INFO jTPCC : Term-00,
10:46:22,666 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
10:46:22,666 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
10:46:22,666 [main] INFO jTPCC : Term-00,
10:46:22,679 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2024-03-10_104622/run.properties
10:46:22,679 [main] INFO jTPCC : Term-00, created my_result_2024-03-10_104622/data/runInfo.csv for runID 7
10:46:22,680 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2024-03-10_104622/data/result.csv
10:46:22,681 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
10:46:22,681 [main] INFO jTPCC : Term-00, osCollectorInterval=1
10:46:22,681 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
10:46:22,681 [main] INFO jTPCC : Term-00, osCollectorDevices=null
10:46:22,754 [main] INFO jTPCC : Term-00,
10:46:23,088 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 174
10:46:23,088 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 246
10:46:23,088 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 880.90 Current tpmTOTAL: 888 Memory Usage: 48MB / 236MB
Term-00, Running Average tpmTOTAL: 1330.69 Current tpmTOTAL: 12456 Memory Usage: 36MB / 228MB
Term-00, Running Average tpmTOTAL: 1069.82 Current tpmTOTAL: 20892 Memory Usage: 7MB / 219MB
Term-00, Running Average tpmTOTAL: 830.32 Current tpmTOTAL: 20472 Memory Usage: 9MB / 219MB
10:50:10,596 [Thread-5] INFO jTPCC : Term-00,
10:50:10,597 [Thread-5] INFO jTPCC : Term-00,
10:50:10,597 [Thread-5] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 380.2
10:50:10,597 [Thread-5] INFO jTPCC : Term-00, Measured tpmTOTAL = 830.32
10:50:10,597 [Thread-5] INFO jTPCC : Term-00, Session Start = 2024-03-10 10:46:23
10:50:10,597 [Thread-5] INFO jTPCC : Term-00, Session End = 2024-03-10 10:50:10
10:50:10,597 [Thread-5] INFO jTPCC : Term-00, Transaction Count = 3146
tpmC用來衡量IOPS.
Transaction用來衡量TPS.
分析top sql
查詢top sql
SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time,
round(avg(execute_time)) avg_exec_time
FROM GV$OB_SQL_AUDIT s
WHERE DB_NAME='tpcc'
GROUP BY sql_id
order by avg_elapsed_time desc limit 10;
+----------------------------------+----------+------------------+---------------+
| sql_id | count(*) | avg_elapsed_time | avg_exec_time |
+----------------------------------+----------+------------------+---------------+
| B447DE16B3F42D2409B2A2BE50328E63 | 1 | 76518226 | 247658 |
| F59A700FA168324279B0DBC25E19760F | 1 | 76359957 | 64612631 |
| AB92F1B97A8D4DADFD477BB52C65A00B | 2 | 38008119 | 5118976 |
| AE32C84F890055A535A28B262C649D41 | 3 | 25339948 | 3428539 |
| F0EFFFCD85E71C241661E66EEA047C58 | 10 | 7604399 | 1026657 |
| 482BA7822AE7BE644CEBEB55213E7284 | 9 | 3071 | 3007 |
| AD9112FD1D4FF113292E5FE777B22F3C | 9 | 1693 | 1420 |
| 8D6E84735C0B8FB1823D199E2CA141C8 | 9 | 1586 | 1424 |
| EC66B09D06D688727D0F999BFCFF5348 | 9 | 816 | 115 |
| F95CD7A05C7064A028D76E0088B147F7 | 9 | 561 | 199 |
+----------------------------------+----------+------------------+---------------+
查詢對應的前三個sql
obclient [oceanbase]> select sql_id,QUERY_SQL from GV$OB_SQL_AUDIT where sql_id in('B447DE16B3F42D2409B2A2BE50328E63','F59A700FA168324279B0DBC25E19760F','AB92F1B97A8D4DADFD477BB52C65A00B')\G
*************************** 1. row ***************************
sql_id: AB92F1B97A8D4DADFD477BB52C65A00B
QUERY_SQL: SELECT c_id FROM bmsql_customer WHERE c_w_id = 2 AND c_d_id = 9 AND c_last = 'ATIONOUGHTPRI' ORDER BY c_first
*************************** 2. row ***************************
sql_id: F59A700FA168324279B0DBC25E19760F
QUERY_SQL: 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 < 12 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 = 9 ) )
*************************** 3. row ***************************
sql_id: B447DE16B3F42D2409B2A2BE50328E63
QUERY_SQL: UPDATE bmsql_warehouse SET w_ytd = w_ytd + 4691.27 WHERE w_id = 2
*************************** 4. row ***************************
sql_id: AB92F1B97A8D4DADFD477BB52C65A00B
QUERY_SQL: SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'PRESPRESANTI' ORDER BY c_first
4 rows in set (0.009 sec)
使用explain檢視sql的執行計劃
obclient [oceanbase]> select sql_id,QUERY_SQL from GV$OB_SQL_AUDIT where sql_id in('B447DE16B3F42D2409B2A2BE50328E63','F59A700FA168324279B0DBC25E19760F','AB92F1B97A8D4DADFD477BB52C65A00B')\G
*************************** 1. row ***************************
sql_id: AB92F1B97A8D4DADFD477BB52C65A00B
QUERY_SQL: SELECT c_id FROM bmsql_customer WHERE c_w_id = 2 AND c_d_id = 9 AND c_last = 'ATIONOUGHTPRI' ORDER BY c_first
*************************** 2. row ***************************
sql_id: F59A700FA168324279B0DBC25E19760F
QUERY_SQL: 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 < 12 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 = 9 ) )
*************************** 3. row ***************************
sql_id: B447DE16B3F42D2409B2A2BE50328E63
QUERY_SQL: UPDATE bmsql_warehouse SET w_ytd = w_ytd + 4691.27 WHERE w_id = 2
*************************** 4. row ***************************
sql_id: AB92F1B97A8D4DADFD477BB52C65A00B
QUERY_SQL: SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'PRESPRESANTI' ORDER BY c_first
4 rows in set (0.009 sec)
obclient [tpcc]> explain SELECT c_id FROM bmsql_customer WHERE c_w_id = 2 AND c_d_id = 9 AND c_last = 'ATIONOUGHTPRI' ORDER BY c_first;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------------- |
| |0 |TABLE RANGE SCAN|bmsql_customer(bmsql_customer_idx1)|12 |5 | |
| =============================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([bmsql_customer.c_id]), filter(nil), rowset=16 |
| access([bmsql_customer.c_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_customer.c_w_id], [bmsql_customer.c_d_id], [bmsql_customer.c_last], [bmsql_customer.c_first], [bmsql_customer.c_id]), range(2,9,ATIONOUGHTPRI, |
| MIN,MIN ; 2,9,ATIONOUGHTPRI,MAX,MAX), |
| range_cond([bmsql_customer.c_w_id = 2], [bmsql_customer.c_d_id = 9], [bmsql_customer.c_last = 'ATIONOUGHTPRI']) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.006 sec)
obclient [tpcc]> 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 < 12 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 = 9 ) )
->
-> ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |42 | |
| |1 |└─NESTED-LOOP JOIN | |2 |42 | |
| |2 | ├─SUBPLAN SCAN |VIEW2 |2 |11 | |
| |3 | │ └─HASH DISTINCT | |2 |11 | |
| |4 | │ └─NESTED-LOOP JOIN | |2 |11 | |
| |5 | │ ├─TABLE RANGE SCAN|bmsql_order_line|30 |7 | |
| |6 | │ └─MATERIAL | |1 |3 | |
| |7 | │ └─TABLE GET |bmsql_district |1 |3 | |
| |8 | └─DISTRIBUTED TABLE GET |bmsql_stock |1 |21 | |
| ====================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT(*)]) |
| 1 - output(nil), filter(nil), rowset=256 |
| conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id(:3)]), use_batch=false |
| 2 - output([VIEW2.VIEW1.ol_i_id]), filter(nil), rowset=256 |
| access([VIEW2.VIEW1.ol_i_id]) |
| 3 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| distinct([bmsql_order_line.ol_i_id]) |
| 4 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| conds([bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id], [bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20]), nl_params_(nil), use_batch=false |
| 5 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| access([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_number]), range(1,9,MIN,MIN ; 1, |
| 9,MAX,MAX), |
| range_cond([bmsql_order_line.ol_w_id = 1], [bmsql_order_line.ol_d_id = 9]) |
| 6 - output([bmsql_district.d_next_o_id], [bmsql_district.d_next_o_id - 20]), filter(nil), rowset=256 |
| 7 - output([bmsql_district.d_next_o_id], [bmsql_district.d_next_o_id - 20]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), rowset=256 |
| access([bmsql_district.d_next_o_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[1,9 ; 1,9], |
| range_cond([bmsql_district.d_w_id = 1], [bmsql_district.d_id = 9]) |
| 8 - output(nil), filter([bmsql_stock.s_quantity < 12]), rowset=256 |
| access([bmsql_stock.s_quantity]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_stock.s_w_id], [bmsql_stock.s_i_id]), range(MIN ; MAX), |
| range_cond([bmsql_stock.s_w_id = 1], [bmsql_stock.s_i_id = :3]) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
42 rows in set (0.061 sec)
obclient [tpcc]> explain SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'PRESPRESANTI' ORDER BY c_first;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------------- |
| |0 |TABLE RANGE SCAN|bmsql_customer(bmsql_customer_idx1)|1 |4 | |
| =============================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([bmsql_customer.c_id]), filter(nil), rowset=16 |
| access([bmsql_customer.c_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_customer.c_w_id], [bmsql_customer.c_d_id], [bmsql_customer.c_last], [bmsql_customer.c_first], [bmsql_customer.c_id]), range(1,1,PRESPRESANTI, |
| MIN,MIN ; 1,1,PRESPRESANTI,MAX,MAX), |
| range_cond([bmsql_customer.c_w_id = 1], [bmsql_customer.c_d_id = 1], [bmsql_customer.c_last = 'PRESPRESANTI']) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.007 sec)