使用 BenmarkSQL 运行 TPC-C
下载BenmarkSQL
[root@cts07 ~]# yum install git -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Package git-1.8.3.1-23.el7_8.x86_64 already installed and latest version
Nothing to do
下载benchmarksql
[root@cts07 ~]# git clone https://github.com/obpilot/benchmarksql-5.0.git
Cloning into 'benchmarksql-5.0'...
remote: Enumerating objects: 110, done.
remote: Counting objects: 100% (110/110), done.
remote: Compressing objects: 100% (90/90), done.
remote: Total 110 (delta 14), reused 105 (delta 12), pack-reused 0
Receiving objects: 100% (110/110), 5.58 MiB | 4.98 MiB/s, done.
Resolving deltas: 100% (14/14), done.
编辑props.ob配置文件
这个配置文件在/root/benchmarksql-5.0/run/目录下,编辑后的内容如下:
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=root123
warehouses=2
loadWorkers=2
terminals=2
//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
运行创建表语句
编辑一下runSQL.sh文件,更改内容如下:
#!/usr/bin/env bash
if [ $# -ne 2 ] ; then
echo "usage: $(basename $0) PROPS_FILE SQL_FILE" >&2
exit 2
fi
source /root/benchmarksql-5.0/run/funcs.sh $1
[admin@cts07 run]$ obclient -h 192.168.1.246 -P 2883 -uroot@sys#obdemo -proot123 -c -A oceanbase
MySQL [oceanbase]> create database obtest;
执行脚本
[admin@cts07 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)
);
数据装载
装载前先优化一下测试租户的设置,避免大事务超时,先登陆到租户
obclient -h 192.168.1.246 -P 2883 -uroot@sys#obdemo -proot123 -c -A oceanbase
设置以下租户全局变量:
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;
运行脚本,装载数据,输出显示装载成功,仓库数选的小一些
[admin@cts07 run]$ cat runLoader.sh
#!/usr/bin/env bash
if [ $# -lt 1 ] ; then
echo "usage: $(basename $0) PROPS_FILE [ARGS]" >&2
exit 2
fi
source /home/admin/benchmarksql-5.0/run/funcs.sh $1
shift
setCP || exit 1
java -cp "$myCP" -Dprop=$PROPS LoadData $*
[admin@cts07 run]$ sh 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=2
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 000: ERROR: Duplicate entry 'warehouses' for key 'PRIMARY'
Worker 001: Loading Warehouse 1
Worker 001: Loading Warehouse 1 done
Worker 001: Loading Warehouse 2
Worker 001: Loading Warehouse 2 done
登陆到数据库,创建两个索引
[admin@cts07 ~]$ obclient -h192.168.1.246 -uroot@sys#obdemo -P2883 -proot123 -c -A oceanbase
MySQL [test]> use obtest
Database changed
MySQL [obtest]> create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
Query OK, 0 rows affected (6.957 sec)
MySQL [obtest]> create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
Query OK, 0 rows affected (4.558 sec)
运行TPCC测试
[admin@cts07 run]$ sh runBenchmark.sh props.ob
23:58:34,605 [main] INFO jTPCC : Term-00,
23:58:34,635 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
23:58:34,635 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
23:58:34,636 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
23:58:34,636 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
23:58:34,636 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
23:58:34,645 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
23:58:34,645 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
23:58:34,645 [main] INFO jTPCC : Term-00,
23:58:34,646 [main] INFO jTPCC : Term-00, db=oracle
23:58:34,656 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
23:58:34,671 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.0.0.1:2883/obtest?useUnicode=true&characterEncoding=utf-8
23:58:34,671 [main] INFO jTPCC : Term-00, user=root@sys#obdemo
23:58:34,678 [main] INFO jTPCC : Term-00,
23:58:34,691 [main] INFO jTPCC : Term-00, warehouses=2
23:58:34,691 [main] INFO jTPCC : Term-00, terminals=2
23:58:34,701 [main] INFO jTPCC : Term-00, runMins=1
23:58:34,702 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
23:58:34,703 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
23:58:34,703 [main] INFO jTPCC : Term-00,
23:58:34,703 [main] INFO jTPCC : Term-00, newOrderWeight=45
23:58:34,704 [main] INFO jTPCC : Term-00, paymentWeight=43
23:58:34,704 [main] INFO jTPCC : Term-00, orderStatusWeight=4
23:58:34,704 [main] INFO jTPCC : Term-00, deliveryWeight=4
23:58:34,704 [main] INFO jTPCC : Term-00, stockLevelWeight=4
23:58:34,705 [main] INFO jTPCC : Term-00,
23:58:34,705 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
23:58:34,705 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
23:58:34,707 [main] INFO jTPCC : Term-00,
23:58:34,850 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2023-02-06_235834/run.properties
23:58:34,852 [main] INFO jTPCC : Term-00, created my_result_2023-02-06_235834/data/runInfo.csv for runID 7
23:58:34,854 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2023-02-06_235834/data/result.csv
23:58:34,857 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
23:58:34,858 [main] INFO jTPCC : Term-00, osCollectorInterval=1
23:58:34,858 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
23:58:34,858 [main] INFO jTPCC : Term-00, osCollectorDevices=null
23:58:35,280 [main] INFO jTPCC : Term-00,
23:58:36,417 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 207
23:58:36,417 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 103
23:58:36,417 [main] INFO jTPCC : Term-00, Term00:02:47,571 [Thread-1] INFO jTPCC : Term-00, pmTOTAL: 36 Memory Usage: 22MB / 299MB
00:02:47,572 [Thread-1] INFO jTPCC : Term-00,
00:02:47,572 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 0.47
00:02:47,572 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 0.95
00:02:47,572 [Thread-1] INFO jTPCC : Term-00, Session Start = 2023-02-06 23:58:36
00:02:47,572 [Thread-1] INFO jTPCC : Term-00, Session End = 2023-02-07 00:02:47
00:02:47,572 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 3
TPC-C TOP SQL分析
查询TOP SQL
MySQL [obtest]> SELECT /*+ PARALLEL(15)*/ avg_exe_usec,
-> tenant_id,
-> plan_id,
-> svr_ip,
-> svr_port,
-> sql_id,
-> db_id,
-> query_sql
-> FROM oceanbase.gv$plan_cache_plan_stat
-> WHERE db_id='18446744073709551615'
-> ORDER BY avg_exe_usec DESC LIMIT 3;
+--------------+-----------+---------+---------------+----------+----------------------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| avg_exe_usec | tenant_id | plan_id | svr_ip | svr_port | sql_id | db_id | query_sql |
+--------------+-----------+---------+---------------+----------+----------------------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 18720981 | 1 | 840 | 192.168.1.246 | 2882 | 7229213613983BC5FDA15AD11EC70D01 | 18446744073709551615 | 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 = 23452 FOR UPDATE |
| 9012268 | 1 | 843 | 192.168.1.246 | 2882 | E1F2BDA1D7391B757859ED3704E5AFB7 | 18446744073709551615 | UPDATE bmsql_stock SET s_quantity = 30, s_ytd = s_ytd + 7, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 1 AND s_i_id = 23452 |
| 6550077 | 1 | 835 | 192.168.1.246 | 2882 | AE32C84F890055A535A28B262C649D41 | 18446744073709551615 | SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 2 AND c_id = 2714 FOR UPDATE |
+--------------+-----------+---------+---------------+----------+----------------------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.332 sec)
执行SQL、查看执行计划
MySQL [obtest]> 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;
+---------------+
| sum_ol_amount |
+---------------+
| 49386.24 |
+---------------+
1 row in set (13.310 sec)
MySQL [obtest]> 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;
+------------+--------------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
| 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 |
+------------+--------------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
| 83 | c77MWxweZFU360RunLE8rzvOOir6fL0NbLSq | fY0NVkYOnuLnqoIdpGAi2MXw | LLzRFwgnQFuSHsbRg5LqAvs6 | WbBbq3Ua3ajw7UcBdWKk7SXR | n37B93xHYoYRBMgOz7s68JPq | aGFeUB0tLhtmmSMgB0RfpHtp | drrXTcXM8Hkjtor1Jv0M7Hg8 | Kw38MKJGb4rwt4oFddT24OWH | TGJ2mQwdwrolCQ2pqLEBmFog | oG2BEZL03Gks9KLqwa8y3CYk | V1hGJiFeQtSlnC7K0AzQxD3K |
| 32 | qUG76KNPwKkR9rzZGdlxb53qAN0CcrYd | zuLQIEbj8tTxaak5lnH3DA2Q | eXe2QshAQgyS7UyBqachNOYg | XgjQie2Ked0FO3GZiQZbJc7W | hAH6lMRh2QOJ3AX5U28xa5W3 | nHcy5pWeLRxpUkYCkkoF2Agn | Pbg7XvTNtkA9eJGyHseW3HJa | ZEU63NG6vEEMrCphxXdQleQA | YehPtGraIRVjzIhdpLXqgoC0 | VuK07QbUFhfPknlI2DmBxFVM | HaQNwmgZCKDERCTSxt4DE226 |
+------------+--------------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
2 rows in set (20.373 sec)
MySQL [obtest]> 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;
Query OK, 1 row affected (9.229 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [obtest]> 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;
+---------------+
| sum_ol_amount |
+---------------+
| 49386.24 |
+---------------+
1 row in set (9.690 sec)
MySQL [obtest]> explain SELECT c_data
-> FROM bmsql_customer
-> WHERE c_w_id = 1
-> AND c_d_id = 9
-> AND c_id = 511;\G
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------------------
|0 |TABLE SCAN|bmsql_customer(bmsql_customer_idx1)|60 |35479|
===================================================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_customer.c_data]), filter([bmsql_customer.c_id = 511]),
access([bmsql_customer.c_id], [bmsql_customer.c_data]), partitions(p0)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.134 sec)
MySQL [obtest]> explain UPDATE bmsql_district SET d_ytd = d_ytd + 479.86
-> WHERE d_w_id = 1
-> AND d_id = 10\G;
*************************** 1. row ***************************
Query Plan: ==============================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------
|0 |UPDATE | |1 |64 |
|1 | TABLE SCAN|bmsql_district|1 |64 |
==============================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{bmsql_district: ({bmsql_district: (bmsql_district.__pk_increment, bmsql_district.d_w_id, bmsql_district.d_id, bmsql_district.d_ytd, bmsql_district.d_tax, bmsql_district.d_next_o_id, bmsql_district.d_name, bmsql_district.d_street_1, bmsql_district.d_street_2, bmsql_district.d_city, bmsql_district.d_state, bmsql_district.d_zip)})}]),
update([bmsql_district.d_ytd=column_conv(DECIMAL,PS:(12,2),NULL,bmsql_district.d_ytd + 479.86)])
1 - output([bmsql_district.__pk_increment], [bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_ytd], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip], [column_conv(DECIMAL,PS:(12,2),NULL,bmsql_district.d_ytd + 479.86)]), filter([bmsql_district.d_w_id = 1], [bmsql_district.d_id = 10]),
access([bmsql_district.d_ytd], [bmsql_district.__pk_increment], [bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), partitions(p0)
1 row in set (0.072 sec)
MySQL [obtest]> explain SELECT d_name,
-> d_street_1,
-> d_street_2,
-> d_city,
-> d_state,
-> d_zip
-> FROM bmsql_district
-> WHERE d_w_id = 1
-> AND d_id = 10\G;
*************************** 1. row ***************************
Query Plan: =============================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
---------------------------------------------
|0 |TABLE SCAN|bmsql_district|1 |62 |
=============================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_district.d_name], [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), filter([bmsql_district.d_w_id = 1], [bmsql_district.d_id = 10]),
access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_name], [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), partitions(p0)
1 row in set (0.050 sec)
ERROR: No query specified