Oceanbase系列–TPCC压测ob性能

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Oceanbase的性能应该是非常强劲的，但是闻名不如见面；还是自己测一下，眼见为实更靠谱。我这里测试参考了ob解决方案架构师的文章https://zhuanlan.zhihu.com/p/112894850，其在github上提供了benchmarkSQL，直接下载编辑一下即可。主要修改一下props.ob配置文件即可，如下：

[root@td1 run]# cat props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8
user=roger@oboracle#obdemo
password=roger
warehouses=2
loadWorkers=2
terminals=10
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//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@td1 run]# cat props.ob

db=oracle

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8

user=roger@oboracle#obdemo

password=roger

warehouses=2

loadWorkers=2

terminals=10

//To run specified transactions per terminal- runMins must equal zero

runTxnsPerTerminal=0

//To run for specified minutes- runTxnsPerTerminal must equal zero

runMins=10

//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

创建表结构

[admin@td1 run]$ ./runSQL.sh  props.ob ./sql.oceanbase/tableCreates.sql
# ------------------------------------------------------------
# Loading SQL file ./sql.oceanbase/tableCreates.sql
# ------------------------------------------------------------
create table bmsql_config (
cfg_name    varchar2(30) primary key,
cfg_value   varchar2(50)
);
create tablegroup "tpcc_group" ;
create table bmsql_warehouse (
w_id        integer   not null,
w_ytd       decimal(12,2),
w_tax       decimal(4,4),
w_name      varchar2(10),
w_street_1  varchar2(20),
w_street_2  varchar2(20),
w_city      varchar2(20),
w_state     char(2),
w_zip       char(9),
primary key(w_id)
)tablegroup='tpcc_group' ;
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       varchar2(10),
d_street_1   varchar2(20),
d_street_2   varchar2(20),
d_city       varchar2(20),
d_state      char(2),
d_zip        char(9),
PRIMARY KEY (d_w_id, d_id)
)tablegroup='tpcc_group' ;
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         varchar2(16),
c_first        varchar2(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     varchar2(20),
c_street_2     varchar2(20),
c_city         varchar2(20),
c_state        char(2),
c_zip          char(9),
c_phone        char(16),
c_since        timestamp,
c_middle       char(2),
c_data         varchar2(500),
PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup='tpcc_group' ;
create sequence bmsql_hist_id_seq;
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   varchar2(24)
)tablegroup='tpcc_group' ;
create table bmsql_new_order (
no_w_id  integer   not null ,
no_d_id  integer   not null,
no_o_id  integer   not null,
PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup='tpcc_group' ;
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,
PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup='tpcc_group' ;
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),
PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup='tpcc_group' ;
create table bmsql_item (
i_id     integer      not null,
i_name   varchar2(24),
i_price  decimal(5,2),
i_data   varchar2(50),
i_im_id  integer,
PRIMARY KEY (i_id)
);
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       varchar2(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),
PRIMARY KEY (s_w_id, s_i_id)
)tablegroup='tpcc_group' ;
[admin@td1 run]$

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

[admin@td1 run]$ ./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sql

# ------------------------------------------------------------

# Loading SQL file ./sql.oceanbase/tableCreates.sql

# ------------------------------------------------------------

create table bmsql_config (

cfg_name varchar2(30) primary key,

cfg_value varchar2(50)

);

create tablegroup "tpcc_group" ;

create table bmsql_warehouse (

w_id integer not null,

w_ytd decimal(12,2),

w_tax decimal(4,4),

w_name varchar2(10),

w_street_1 varchar2(20),

w_street_2 varchar2(20),

w_city varchar2(20),

w_state char(2),

w_zip char(9),

primary key(w_id)

)tablegroup='tpcc_group' ;

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 varchar2(10),

d_street_1 varchar2(20),

d_street_2 varchar2(20),

d_city varchar2(20),

d_state char(2),

d_zip char(9),

PRIMARY KEY (d_w_id, d_id)

)tablegroup='tpcc_group' ;

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 varchar2(16),

c_first varchar2(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 varchar2(20),

c_street_2 varchar2(20),

c_city varchar2(20),

c_state char(2),

c_zip char(9),

c_phone char(16),

c_since timestamp,

c_middle char(2),

c_data varchar2(500),

PRIMARY KEY (c_w_id, c_d_id, c_id)

)tablegroup='tpcc_group' ;

create sequence bmsql_hist_id_seq;

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 varchar2(24)

)tablegroup='tpcc_group' ;

create table bmsql_new_order (

no_w_id integer not null ,

no_d_id integer not null,

no_o_id integer not null,

PRIMARY KEY (no_w_id, no_d_id, no_o_id)

)tablegroup='tpcc_group' ;

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,

PRIMARY KEY (o_w_id, o_d_id, o_id)

)tablegroup='tpcc_group' ;

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),

PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)

)tablegroup='tpcc_group' ;

create table bmsql_item (

i_id integer not null,

i_name varchar2(24),

i_price decimal(5,2),

i_data varchar2(50),

i_im_id integer,

PRIMARY KEY (i_id)

);

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 varchar2(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),

PRIMARY KEY (s_w_id, s_i_id)

)tablegroup='tpcc_group' ;

[admin@td1 run]$

2. 调整ob和obproxy相关参数，优化性能

+++调整ob租户相关参数
obclient> set global recyclebin=off;
set global ob_query_timeout=1000000000;
set global ob_trx_idle_timeout=1200000000;
set global ob_trx_timeout=1000000000;Query OK, 0 rows affected (0.08 sec)
obclient> set global ob_query_timeout=1000000000;
Query OK, 0 rows affected (0.08 sec)
obclient> set global ob_trx_idle_timeout=1200000000;
Query OK, 0 rows affected (0.02 sec)
obclient> set global ob_trx_timeout=1000000000;
Query OK, 0 rows affected (0.06 sec)
obclient> 
+++调整obproxy参数
[admin@td1 ~]$ obclient -h127.1 -uroot@sys#obdemo -P2883 -p -c -A oceanbase      
Enter password: 
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 524294
Server version: 5.6.25 OceanBase 2.2.30 (r1872059-defe4fdc46b03542cd4195a67d7e55ce564ba716) (Built Mar 16 2020 22:43:07)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient> alter proxyconfig set enable_compression_protocol=False;
Query OK, 0 rows affected (0.01 sec)
obclient>

+++调整ob租户相关参数

obclient> set global recyclebin=off;

set global ob_query_timeout=1000000000;

set global ob_trx_idle_timeout=1200000000;

set global ob_trx_timeout=1000000000;Query OK, 0 rows affected (0.08 sec)

obclient> set global ob_query_timeout=1000000000;

Query OK, 0 rows affected (0.08 sec)

obclient> set global ob_trx_idle_timeout=1200000000;

Query OK, 0 rows affected (0.02 sec)

obclient> set global ob_trx_timeout=1000000000;

Query OK, 0 rows affected (0.06 sec)

obclient>

+++调整obproxy参数

[admin@td1 ~]$ obclient -h127.1 -uroot@sys#obdemo -P2883 -p -c -A oceanbase

Enter password:

Welcome to the OceanBase monitor. Commands end with ; or \g.

Your OceanBase connection id is 524294

Server version: 5.6.25 OceanBase 2.2.30 (r1872059-defe4fdc46b03542cd4195a67d7e55ce564ba716) (Built Mar 16 2020 22:43:07)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> alter proxyconfig set enable_compression_protocol=False;

Query OK, 0 rows affected (0.01 sec)

obclient>

不调整参数，可能load会报错，类似如下：

[admin@td1 run]$ ./runLoader.sh props.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8
user=roger@oboracle#obdemo
password=***********
warehouses=1
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: ERROR: internal error code, arguments: -6210, Transaction is timeout
[admin@td1 run]$

[admin@td1 run]$ ./runLoader.sh props.ob

Starting BenchmarkSQL LoadData

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8

user=roger@oboracle#obdemo

password=***********

warehouses=1

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: ERROR: internal error code, arguments: -6210, Transaction is timeout

[admin@td1 run]$

[admin@td1 run]$ ./runLoader.sh props.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8
user=roger@oboracle#obdemo
password=***********
warehouses=1
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

[admin@td1 run]$ ./runLoader.sh props.ob

Starting BenchmarkSQL LoadData

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8

user=roger@oboracle#obdemo

password=***********

warehouses=1

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

3. 创建index(确保性能)

[admin@td1 run]$  ./runSQL.sh props.ob ./sql.oceanbase/indexCreates.sql
# ------------------------------------------------------------
# Loading SQL file ./sql.oceanbase/indexCreates.sql
# ------------------------------------------------------------
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;
[admin@td1 run]$

[admin@td1 run]$ ./runSQL.sh props.ob ./sql.oceanbase/indexCreates.sql

# ------------------------------------------------------------

# Loading SQL file ./sql.oceanbase/indexCreates.sql

# ------------------------------------------------------------

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;

[admin@td1 run]$

这里你可以直接执行shell脚本，也可以直接在obclient中执行创建index语句。

4. 开始tpcc测试

[admin@td1 run]$ ./runBenchmark.sh props.ob
17:12:51,843 [main] INFO   jTPCC : Term-00, 
17:12:51,845 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:12:51,846 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
17:12:51,846 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:12:51,846 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
17:12:51,846 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
17:12:51,848 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
17:12:51,848 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:12:51,848 [main] INFO   jTPCC : Term-00, 
17:12:51,848 [main] INFO   jTPCC : Term-00, db=oracle
17:12:51,849 [main] INFO   jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
17:12:51,849 [main] INFO   jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8
17:12:51,849 [main] INFO   jTPCC : Term-00, user=roger@oboracle#obdemo
17:12:51,849 [main] INFO   jTPCC : Term-00, 
17:12:51,849 [main] INFO   jTPCC : Term-00, warehouses=1
17:12:51,849 [main] INFO   jTPCC : Term-00, terminals=2
17:12:51,850 [main] INFO   jTPCC : Term-00, runMins=1
17:12:51,850 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
17:12:51,850 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
17:12:51,851 [main] INFO   jTPCC : Term-00, 
17:12:51,851 [main] INFO   jTPCC : Term-00, newOrderWeight=45
17:12:51,851 [main] INFO   jTPCC : Term-00, paymentWeight=43
17:12:51,851 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
17:12:51,851 [main] INFO   jTPCC : Term-00, deliveryWeight=4
17:12:51,851 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
17:12:51,851 [main] INFO   jTPCC : Term-00, 
17:12:51,851 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
17:12:51,851 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:12:51,851 [main] INFO   jTPCC : Term-00, 
17:12:51,865 [main] INFO   jTPCC : Term-00, copied props.ob to my_result_2020-05-28_171251/run.properties
17:12:51,866 [main] INFO   jTPCC : Term-00, created my_result_2020-05-28_171251/data/runInfo.csv for runID 6
17:12:51,866 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2020-05-28_171251/data/result.csv
17:12:51,867 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:12:51,867 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
17:12:51,867 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
17:12:51,867 [main] INFO   jTPCC : Term-00, osCollectorDevices=null
17:12:51,941 [main] INFO   jTPCC : Term-00,
17:12:52,356 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 254
17:12:52,356 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    148
17:12:52,356 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 5453.81    Current tpmTOTAL: 36240    Memory Usage: 152MB / 575MB          
17:13:52,408 [Thread-2] INFO   jTPCC : Term-00, 
17:13:52,408 [Thread-2] INFO   jTPCC : Term-00, 
17:13:52,408 [Thread-2] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 2474.71
17:13:52,408 [Thread-2] INFO   jTPCC : Term-00, Measured tpmTOTAL = 5454.36
17:13:52,408 [Thread-2] INFO   jTPCC : Term-00, Session Start     = 2020-05-28 17:12:52
17:13:52,408 [Thread-2] INFO   jTPCC : Term-00, Session End       = 2020-05-28 17:13:52
17:13:52,408 [Thread-2] INFO   jTPCC : Term-00, Transaction Count = 5454

[admin@td1 run]$ ./runBenchmark.sh props.ob

17:12:51,843 [main] INFO jTPCC : Term-00,

17:12:51,845 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:12:51,846 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0

17:12:51,846 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:12:51,848 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:12:51,848 [main] INFO jTPCC : Term-00,

17:12:51,848 [main] INFO jTPCC : Term-00, db=oracle

17:12:51,849 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

17:12:51,849 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8

17:12:51,849 [main] INFO jTPCC : Term-00, user=roger@oboracle#obdemo

17:12:51,849 [main] INFO jTPCC : Term-00,

17:12:51,849 [main] INFO jTPCC : Term-00, warehouses=1

17:12:51,849 [main] INFO jTPCC : Term-00, terminals=2

17:12:51,850 [main] INFO jTPCC : Term-00, runMins=1

17:12:51,850 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0

17:12:51,850 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true

17:12:51,851 [main] INFO jTPCC : Term-00,

17:12:51,851 [main] INFO jTPCC : Term-00, newOrderWeight=45

17:12:51,851 [main] INFO jTPCC : Term-00, paymentWeight=43

17:12:51,851 [main] INFO jTPCC : Term-00, orderStatusWeight=4

17:12:51,851 [main] INFO jTPCC : Term-00, deliveryWeight=4

17:12:51,851 [main] INFO jTPCC : Term-00, stockLevelWeight=4

17:12:51,851 [main] INFO jTPCC : Term-00,

17:12:51,851 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

17:12:51,851 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

17:12:51,851 [main] INFO jTPCC : Term-00,

17:12:51,865 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2020-05-28_171251/run.properties

17:12:51,866 [main] INFO jTPCC : Term-00, created my_result_2020-05-28_171251/data/runInfo.csv for runID 6

17:12:51,866 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2020-05-28_171251/data/result.csv

17:12:51,867 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

17:12:51,867 [main] INFO jTPCC : Term-00, osCollectorInterval=1

17:12:51,867 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null

17:12:51,867 [main] INFO jTPCC : Term-00, osCollectorDevices=null

17:12:51,941 [main] INFO jTPCC : Term-00,

17:12:52,356 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 254

17:12:52,356 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 148

17:12:52,356 [main] INFO jTPCC : Term-00,

Term-00, Running Average tpmTOTAL: 5453.81 Current tpmTOTAL: 36240 Memory Usage: 152MB / 575MB

17:13:52,408 [Thread-2] INFO jTPCC : Term-00,

17:13:52,408 [Thread-2] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 2474.71

17:13:52,408 [Thread-2] INFO jTPCC : Term-00, Measured tpmTOTAL = 5454.36

17:13:52,408 [Thread-2] INFO jTPCC : Term-00, Session Start = 2020-05-28 17:12:52

17:13:52,408 [Thread-2] INFO jTPCC : Term-00, Session End = 2020-05-28 17:13:52

17:13:52,408 [Thread-2] INFO jTPCC : Term-00, Transaction Count = 5454

因为是虚拟机，资源配置非常低，因此第一次测试各方面参数都设置的很低。

将终端调整到最大值10后，性能有大幅提升：

[admin@td1 run]$ ./runBenchmark.sh props.ob
17:32:18,812 [main] INFO   jTPCC : Term-00, 
17:32:18,815 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:32:18,815 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
17:32:18,815 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:32:18,815 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
17:32:18,815 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
17:32:18,817 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
17:32:18,817 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:32:18,817 [main] INFO   jTPCC : Term-00, 
17:32:18,818 [main] INFO   jTPCC : Term-00, db=oracle
17:32:18,818 [main] INFO   jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
17:32:18,818 [main] INFO   jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8
17:32:18,818 [main] INFO   jTPCC : Term-00, user=roger@oboracle#obdemo
17:32:18,818 [main] INFO   jTPCC : Term-00, 
17:32:18,818 [main] INFO   jTPCC : Term-00, warehouses=1
17:32:18,818 [main] INFO   jTPCC : Term-00, terminals=10
17:32:18,820 [main] INFO   jTPCC : Term-00, runMins=10
17:32:18,820 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
17:32:18,820 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
17:32:18,820 [main] INFO   jTPCC : Term-00, 
17:32:18,820 [main] INFO   jTPCC : Term-00, newOrderWeight=45
17:32:18,820 [main] INFO   jTPCC : Term-00, paymentWeight=43
17:32:18,821 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
17:32:18,821 [main] INFO   jTPCC : Term-00, deliveryWeight=4
17:32:18,821 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
17:32:18,821 [main] INFO   jTPCC : Term-00, 
17:32:18,821 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
17:32:18,821 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:32:18,821 [main] INFO   jTPCC : Term-00, 
17:32:18,838 [main] INFO   jTPCC : Term-00, copied props.ob to my_result_2020-05-28_173218/run.properties
17:32:18,838 [main] INFO   jTPCC : Term-00, created my_result_2020-05-28_173218/data/runInfo.csv for runID 10
17:32:18,838 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2020-05-28_173218/data/result.csv
17:32:18,839 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:32:18,839 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
17:32:18,839 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
17:32:18,839 [main] INFO   jTPCC : Term-00, osCollectorDevices=null
17:32:18,914 [main] INFO   jTPCC : Term-00,
17:32:19,173 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 254
17:32:19,173 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    177
17:32:19,173 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 11895.86    Current tpmTOTAL: 786444    Memory Usage: 18MB / 465MB           
17:42:19,478 [Thread-1] INFO   jTPCC : Term-00, 
17:42:19,478 [Thread-1] INFO   jTPCC : Term-00, 
17:42:19,478 [Thread-1] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 5348.44
17:42:19,478 [Thread-1] INFO   jTPCC : Term-00, Measured tpmTOTAL = 11893.78
17:42:19,479 [Thread-1] INFO   jTPCC : Term-00, Session Start     = 2020-05-28 17:32:19
17:42:19,479 [Thread-1] INFO   jTPCC : Term-00, Session End       = 2020-05-28 17:42:19
17:42:19,479 [Thread-1] INFO   jTPCC : Term-00, Transaction Count = 118967

[admin@td1 run]$ ./runBenchmark.sh props.ob

17:32:18,812 [main] INFO jTPCC : Term-00,

17:32:18,815 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:32:18,815 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0

17:32:18,815 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:32:18,817 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:32:18,817 [main] INFO jTPCC : Term-00,

17:32:18,818 [main] INFO jTPCC : Term-00, db=oracle

17:32:18,818 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

17:32:18,818 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8

17:32:18,818 [main] INFO jTPCC : Term-00, user=roger@oboracle#obdemo

17:32:18,818 [main] INFO jTPCC : Term-00,

17:32:18,818 [main] INFO jTPCC : Term-00, warehouses=1

17:32:18,818 [main] INFO jTPCC : Term-00, terminals=10

17:32:18,820 [main] INFO jTPCC : Term-00, runMins=10

17:32:18,820 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0

17:32:18,820 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true

17:32:18,820 [main] INFO jTPCC : Term-00,

17:32:18,820 [main] INFO jTPCC : Term-00, newOrderWeight=45

17:32:18,820 [main] INFO jTPCC : Term-00, paymentWeight=43

17:32:18,821 [main] INFO jTPCC : Term-00, orderStatusWeight=4

17:32:18,821 [main] INFO jTPCC : Term-00, deliveryWeight=4

17:32:18,821 [main] INFO jTPCC : Term-00, stockLevelWeight=4

17:32:18,821 [main] INFO jTPCC : Term-00,

17:32:18,821 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

17:32:18,821 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

17:32:18,821 [main] INFO jTPCC : Term-00,

17:32:18,838 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2020-05-28_173218/run.properties

17:32:18,838 [main] INFO jTPCC : Term-00, created my_result_2020-05-28_173218/data/runInfo.csv for runID 10

17:32:18,838 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2020-05-28_173218/data/result.csv

17:32:18,839 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

17:32:18,839 [main] INFO jTPCC : Term-00, osCollectorInterval=1

17:32:18,839 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null

17:32:18,839 [main] INFO jTPCC : Term-00, osCollectorDevices=null

17:32:18,914 [main] INFO jTPCC : Term-00,

17:32:19,173 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 254

17:32:19,173 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 177

17:32:19,173 [main] INFO jTPCC : Term-00,

Term-00, Running Average tpmTOTAL: 11895.86 Current tpmTOTAL: 786444 Memory Usage: 18MB / 465MB

17:42:19,478 [Thread-1] INFO jTPCC : Term-00,

17:42:19,478 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 5348.44

17:42:19,478 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 11893.78

17:42:19,479 [Thread-1] INFO jTPCC : Term-00, Session Start = 2020-05-28 17:32:19

17:42:19,479 [Thread-1] INFO jTPCC : Term-00, Session End = 2020-05-28 17:42:19

17:42:19,479 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 118967

由于我这里是Oracle租户，通过监控系统发现cpu idle较高，因此我再次调整了租户资源限制：

obclient>  ALTER resource unit sys_unit_config max_cpu=3, min_cpu=2.5;
Query OK, 0 rows affected (0.01 sec)
obclient>  alter resource unit my_unit_config max_cpu=3, min_cpu=1;
Query OK, 0 rows affected (0.01 sec)

obclient> ALTER resource unit sys_unit_config max_cpu=3, min_cpu=2.5;

Query OK, 0 rows affected (0.01 sec)

obclient> alter resource unit my_unit_config max_cpu=3, min_cpu=1;

Query OK, 0 rows affected (0.01 sec)

然后重新初始化2个仓,再次进行测试，性能更进一步。

[admin@td1 run]$ ./runBenchmark.sh props.ob
17:57:44,322 [main] INFO   jTPCC : Term-00, 
17:57:44,326 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:57:44,326 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
17:57:44,326 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:57:44,326 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
17:57:44,326 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
17:57:44,329 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
17:57:44,329 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
17:57:44,329 [main] INFO   jTPCC : Term-00, 
17:57:44,329 [main] INFO   jTPCC : Term-00, db=oracle
17:57:44,330 [main] INFO   jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
17:57:44,330 [main] INFO   jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8
17:57:44,330 [main] INFO   jTPCC : Term-00, user=roger@oboracle#obdemo
17:57:44,330 [main] INFO   jTPCC : Term-00, 
17:57:44,330 [main] INFO   jTPCC : Term-00, warehouses=2
17:57:44,330 [main] INFO   jTPCC : Term-00, terminals=10
17:57:44,332 [main] INFO   jTPCC : Term-00, runMins=10
17:57:44,332 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
17:57:44,332 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
17:57:44,332 [main] INFO   jTPCC : Term-00, 
17:57:44,332 [main] INFO   jTPCC : Term-00, newOrderWeight=45
17:57:44,332 [main] INFO   jTPCC : Term-00, paymentWeight=43
17:57:44,333 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
17:57:44,333 [main] INFO   jTPCC : Term-00, deliveryWeight=4
17:57:44,333 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
17:57:44,333 [main] INFO   jTPCC : Term-00, 
17:57:44,333 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
17:57:44,333 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:57:44,333 [main] INFO   jTPCC : Term-00, 
17:57:44,382 [main] INFO   jTPCC : Term-00, copied props.ob to my_result_2020-05-28_175744/run.properties
17:57:44,382 [main] INFO   jTPCC : Term-00, created my_result_2020-05-28_175744/data/runInfo.csv for runID 11
17:57:44,382 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2020-05-28_175744/data/result.csv
17:57:44,383 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:57:44,383 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
17:57:44,383 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
17:57:44,384 [main] INFO   jTPCC : Term-00, osCollectorDevices=null
17:57:44,476 [main] INFO   jTPCC : Term-00,
17:57:44,757 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 158
17:57:44,757 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    44
17:57:44,758 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 16066.97    Current tpmTOTAL: 1062144    Memory Usage: 15MB / 465MB          
18:07:44,941 [Thread-9] INFO   jTPCC : Term-00, 
18:07:44,941 [Thread-9] INFO   jTPCC : Term-00, 
18:07:44,941 [Thread-9] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 7233.31
18:07:44,941 [Thread-9] INFO   jTPCC : Term-00, Measured tpmTOTAL = 16066.82
18:07:44,942 [Thread-9] INFO   jTPCC : Term-00, Session Start     = 2020-05-28 17:57:44
18:07:44,942 [Thread-9] INFO   jTPCC : Term-00, Session End       = 2020-05-28 18:07:44
18:07:44,942 [Thread-9] INFO   jTPCC : Term-00, Transaction Count = 160678

[admin@td1 run]$ ./runBenchmark.sh props.ob

17:57:44,322 [main] INFO jTPCC : Term-00,

17:57:44,326 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:57:44,326 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0

17:57:44,326 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:57:44,329 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

17:57:44,329 [main] INFO jTPCC : Term-00,

17:57:44,329 [main] INFO jTPCC : Term-00, db=oracle

17:57:44,330 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

17:57:44,330 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/roger?useUnicode=true&characterEncoding=utf-8

17:57:44,330 [main] INFO jTPCC : Term-00, user=roger@oboracle#obdemo

17:57:44,330 [main] INFO jTPCC : Term-00,

17:57:44,330 [main] INFO jTPCC : Term-00, warehouses=2

17:57:44,330 [main] INFO jTPCC : Term-00, terminals=10

17:57:44,332 [main] INFO jTPCC : Term-00, runMins=10

17:57:44,332 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0

17:57:44,332 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true

17:57:44,332 [main] INFO jTPCC : Term-00,

17:57:44,332 [main] INFO jTPCC : Term-00, newOrderWeight=45

17:57:44,332 [main] INFO jTPCC : Term-00, paymentWeight=43

17:57:44,333 [main] INFO jTPCC : Term-00, orderStatusWeight=4

17:57:44,333 [main] INFO jTPCC : Term-00, deliveryWeight=4

17:57:44,333 [main] INFO jTPCC : Term-00, stockLevelWeight=4

17:57:44,333 [main] INFO jTPCC : Term-00,

17:57:44,333 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

17:57:44,333 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

17:57:44,333 [main] INFO jTPCC : Term-00,

17:57:44,382 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2020-05-28_175744/run.properties

17:57:44,382 [main] INFO jTPCC : Term-00, created my_result_2020-05-28_175744/data/runInfo.csv for runID 11

17:57:44,382 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2020-05-28_175744/data/result.csv

17:57:44,383 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

17:57:44,383 [main] INFO jTPCC : Term-00, osCollectorInterval=1

17:57:44,383 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null

17:57:44,384 [main] INFO jTPCC : Term-00, osCollectorDevices=null

17:57:44,476 [main] INFO jTPCC : Term-00,

17:57:44,757 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 158

17:57:44,757 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 44

17:57:44,758 [main] INFO jTPCC : Term-00,

Term-00, Running Average tpmTOTAL: 16066.97 Current tpmTOTAL: 1062144 Memory Usage: 15MB / 465MB

18:07:44,941 [Thread-9] INFO jTPCC : Term-00,

18:07:44,941 [Thread-9] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 7233.31

18:07:44,941 [Thread-9] INFO jTPCC : Term-00, Measured tpmTOTAL = 16066.82

18:07:44,942 [Thread-9] INFO jTPCC : Term-00, Session Start = 2020-05-28 17:57:44

18:07:44,942 [Thread-9] INFO jTPCC : Term-00, Session End = 2020-05-28 18:07:44

18:07:44,942 [Thread-9] INFO jTPCC : Term-00, Transaction Count = 160678

大家可以看到tpm基本上到16000了。看上去还是不错。我这里的Oracle租户cpu max才3，内存最大为10gb.

在进行tpcc测试时，可以查询相关视图，监控db的性能状态，ob的oracle兼容模式也提供了类似oracle的v$session_wait 等待事件接口，这里我们来看看。

obclient> select event,state,count(1) from v$session_Wait group by event,state order by 3;
+---------------------------------+-------------------+----------+
| EVENT                           | STATE             | COUNT(1) |
+---------------------------------+-------------------+----------+
| sync rpc                        | WAITED SHORT TIME |        2 |
| latch: default spin rwlock wait | WAITED SHORT TIME |        2 |
| db file data read               | WAITED SHORT TIME |        4 |
| sync rpc                        | WAITED KNOWN TIME |        9 |
| mysql response wait client      | WAITED SHORT TIME |       19 |
+---------------------------------+-------------------+----------+
5 rows in set (0.01 sec)
obclient> select sid,event,p1text,p1,p2,p2text,p3,p3text,WAIT_CLASS#,STATE,WAIT_TIME_MICRO,TIME_REMAINING_MICRO from  v$session_Wait ;
+------------+---------------------------------+---------+-----------------+------------+--------+----+--------+-------------+-------------------+-----------------+----------------------+
| SID        | EVENT                           | P1TEXT  | P1              | P2         | P2TEXT | P3 | P3TEXT | WAIT_CLASS# | STATE             | WAIT_TIME_MICRO | TIME_REMAINING_MICRO |
+------------+---------------------------------+---------+-----------------+------------+--------+----+--------+-------------+-------------------+-----------------+----------------------+
| 3221487745 | sync rpc                        | pcode   |             595 |        210 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           13549 |                 NULL |
| 3221487815 | db file data read               | fd      |             160 |          0 | offset |  0 | size   |           8 | WAITED SHORT TIME |             271 |                 NULL |
| 3221488245 | db file data read               | fd      |            1840 |          0 | offset |  0 | size   |           8 | WAITED SHORT TIME |             618 |                 NULL |
| 3221489377 | sync rpc                        | pcode   |             515 |        655 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           13171 |                 NULL |
| 3221489514 | sync rpc                        | pcode   |             552 |        301 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           12768 |                 NULL |
| 3221490586 | sync rpc                        | pcode   |             515 |       1417 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           46715 |                 NULL |
| 3221495859 | sync rpc                        | pcode   |            1294 |       3914 | size   |  0 | NULL   |           7 | WAITED SHORT TIME |             775 |                 NULL |
| 3221497539 | sync rpc                        | pcode   |            1294 |       6973 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           35819 |                 NULL |
| 3221498632 | sync rpc                        | pcode   |             515 |       2215 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |          112505 |                 NULL |
| 3221498707 | sync rpc                        | pcode   |             516 |        203 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           26327 |                 NULL |
| 3221498708 | latch: default spin rwlock wait | address | 140648371133768 | 1073745962 | number |  1 | tries  |           4 | WAITED SHORT TIME |            2173 |                 NULL |
| 3221498712 | sync rpc                        | pcode   |             515 |       2215 | size   |  0 | NULL   |           7 | WAITED SHORT TIME |            1102 |                 NULL |
| 3221498764 | sync rpc                        | pcode   |             515 |       2215 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           46322 |                 NULL |
| 3221498765 | latch: default spin rwlock wait | address | 140648371133768 | 1073745962 | number |  1 | tries  |           4 | WAITED SHORT TIME |             986 |                 NULL |
| 3221498783 | sync rpc                        | pcode   |             526 |        669 | size   |  0 | NULL   |           7 | WAITED KNOWN TIME |           72230 |                 NULL |
| 3221498989 | db file data read               | fd      |            4096 |          0 | offset |  0 | size   |           8 | WAITED SHORT TIME |             310 |                 NULL |
| 3221498990 | db file data read               | fd      |           16384 |          0 | offset |  0 | size   |           8 | WAITED SHORT TIME |             229 |                 NULL |
| 3221498991 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              24 |                   -1 |
| 3221499010 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              45 |                   -1 |
| 3221499011 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |             436 |                   -1 |
| 3221499012 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              22 |                   -1 |
| 3221499013 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |             119 |                   -1 |
| 3221499014 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              34 |                   -1 |
| 3221499015 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |             145 |                   -1 |
| 3221499016 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              91 |                   -1 |
| 3221499017 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |            1145 |                   -1 |
| 3221499098 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              44 |                   -1 |
| 3221499099 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              24 |                   -1 |
| 3221499100 | latch: default spin rwlock wait | address | 140648371133768 | 3221229610 | number |  1 | tries  |           4 | WAITED SHORT TIME |            6495 |                 NULL |
| 3221499101 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              85 |                   -1 |
| 3221499102 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              35 |                   -1 |
| 3221499103 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              40 |                   -1 |
| 3221499104 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              48 |                   -1 |
| 3221499105 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |             112 |                   -1 |
| 3221499106 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |              35 |                   -1 |
| 3221499107 | mysql response wait client      | NULL    |               0 |          0 | NULL   |  0 | NULL   |           7 | WAITED SHORT TIME |             246 |                   -1 |
+------------+---------------------------------+---------+-----------------+------------+--------+----+--------+-------------+-------------------+-----------------+----------------------+
36 rows in set (0.01 sec)

obclient> select event,state,count(1) from v$session_Wait group by event,state order by 3;

+---------------------------------+-------------------+----------+

| EVENT | STATE | COUNT(1) |

+---------------------------------+-------------------+----------+

| sync rpc | WAITED SHORT TIME | 2 |

| latch: default spin rwlock wait | WAITED SHORT TIME | 2 |

| db file data read | WAITED SHORT TIME | 4 |

| sync rpc | WAITED KNOWN TIME | 9 |

| mysql response wait client | WAITED SHORT TIME | 19 |

+---------------------------------+-------------------+----------+

5 rows in set (0.01 sec)

obclient> select sid,event,p1text,p1,p2,p2text,p3,p3text,WAIT_CLASS#,STATE,WAIT_TIME_MICRO,TIME_REMAINING_MICRO from v$session_Wait ;

+------------+---------------------------------+---------+-----------------+------------+--------+----+--------+-------------+-------------------+-----------------+----------------------+

+------------+---------------------------------+---------+-----------------+------------+--------+----+--------+-------------+-------------------+-----------------+----------------------+

| 3221487745 | sync rpc | pcode | 595 | 210 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 13549 | NULL |

| 3221487815 | db file data read | fd | 160 | 0 | offset | 0 | size | 8 | WAITED SHORT TIME | 271 | NULL |

| 3221488245 | db file data read | fd | 1840 | 0 | offset | 0 | size | 8 | WAITED SHORT TIME | 618 | NULL |

| 3221489377 | sync rpc | pcode | 515 | 655 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 13171 | NULL |

| 3221489514 | sync rpc | pcode | 552 | 301 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 12768 | NULL |

| 3221490586 | sync rpc | pcode | 515 | 1417 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 46715 | NULL |

| 3221495859 | sync rpc | pcode | 1294 | 3914 | size | 0 | NULL | 7 | WAITED SHORT TIME | 775 | NULL |

| 3221497539 | sync rpc | pcode | 1294 | 6973 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 35819 | NULL |

| 3221498632 | sync rpc | pcode | 515 | 2215 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 112505 | NULL |

| 3221498707 | sync rpc | pcode | 516 | 203 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 26327 | NULL |

| 3221498708 | latch: default spin rwlock wait | address | 140648371133768 | 1073745962 | number | 1 | tries | 4 | WAITED SHORT TIME | 2173 | NULL |

| 3221498712 | sync rpc | pcode | 515 | 2215 | size | 0 | NULL | 7 | WAITED SHORT TIME | 1102 | NULL |

| 3221498764 | sync rpc | pcode | 515 | 2215 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 46322 | NULL |

| 3221498765 | latch: default spin rwlock wait | address | 140648371133768 | 1073745962 | number | 1 | tries | 4 | WAITED SHORT TIME | 986 | NULL |

| 3221498783 | sync rpc | pcode | 526 | 669 | size | 0 | NULL | 7 | WAITED KNOWN TIME | 72230 | NULL |

| 3221498989 | db file data read | fd | 4096 | 0 | offset | 0 | size | 8 | WAITED SHORT TIME | 310 | NULL |

| 3221498990 | db file data read | fd | 16384 | 0 | offset | 0 | size | 8 | WAITED SHORT TIME | 229 | NULL |

| 3221498991 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 24 | -1 |

| 3221499010 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 45 | -1 |

| 3221499011 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 436 | -1 |

| 3221499012 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 22 | -1 |

| 3221499013 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 119 | -1 |

| 3221499014 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 34 | -1 |

| 3221499015 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 145 | -1 |

| 3221499016 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 91 | -1 |

| 3221499017 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 1145 | -1 |

| 3221499098 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 44 | -1 |

| 3221499099 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 24 | -1 |

| 3221499100 | latch: default spin rwlock wait | address | 140648371133768 | 3221229610 | number | 1 | tries | 4 | WAITED SHORT TIME | 6495 | NULL |

| 3221499101 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 85 | -1 |

| 3221499102 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 35 | -1 |

| 3221499103 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 40 | -1 |

| 3221499104 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 48 | -1 |

| 3221499105 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 112 | -1 |

| 3221499106 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 35 | -1 |

| 3221499107 | mysql response wait client | NULL | 0 | 0 | NULL | 0 | NULL | 7 | WAITED SHORT TIME | 246 | -1 |

+------------+---------------------------------+---------+-----------------+------------+--------+----+--------+-------------+-------------------+-----------------+----------------------+

36 rows in set (0.01 sec)

从查询来看,mysql response wait client属于空闲等待；类似oracle的空闲等待。后续我抽空整理一下ob相关的等待事件。

祝大家玩得开心~~~~

Tags: benchmarksql for oceanbase, db file data read, enable_compression_protocol, latch: default spin rwlock wait, mysql response wait client, obclient, ob_query_timeout, ob_trx_idle_timeout, ob_trx_timeout, oceanbase, oracle tannet, sync rpc, sys_unit_config max_cpu, tpcc
Posted in OceanBase, 性能优化 on 5月 28, 2020

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

Oceanbase系列–TPCC压测ob性能

Leave a Reply