改造Benchmark SQL5及SQL表性能调优适配OceanBase开源版数据库跑TPC-C测试
作者：马顺华
从事运维管理工作多年，目前就职于六棱镜（杭州）科技有限公司，熟悉运维自动化、OceanBase部署运维、MySQL 运维以及各种云平台技术和产品。并已获得OceanBase认证OBCA、OBCP证书。
前言
适配 Benchmark SQL5，由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，网络上大部分都是使用mysql数据库和oracle数据库测试Benchmark 。本节将详细介绍如何通过修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库。
什么是 TPC-C
TPC-C 是一个对 OLTP（联机交易处理）系统进行测试的规范，使用一个商品销售模型对 OLTP 系统进行测试。
数据库模型
在测试开始前，TPC-C Benchmark 规定了数据库的初始状态（也就是数据库中数据生成的规则），其中 ITEM 表中固定包含 10 万种商品，仓库的数量可进行调整，假设 WAREHOUSE 表中有 W 条记录，那么：
- STOCK 表中应有 W×10 万条记录（每个仓库对应 10 万种商品的库存数据）；
- DISTRICT 表中应有 W×10 条记录（每个仓库为 10 个地区提供服务）；
- CUSTOMER 表中应有 W×10×3000 条记录（每个地区有 3000 个客户）；
- HISTORY 表中应有 W×10×3000 条记录（每个客户一条交易历史）；
- ORDER 表中应有 W×10×3000 条记录（每个地区 3000 个订单），并且最后生成的 900 个订单将被添加到 NEW-ORDER 表中，每个订单随机生成 5~15 条 ORDER-LINE 记录。
在测试过程中，每一个地区（DISTRICT）都有一个对应的终端（Terminal），模拟为用户提供服务。在每个终端的生命周期内，要循环往复地执行各类事务，当终端执行完一个事务的周期后，就进入下一个事务的周期。
客户下单后，包含若干个订单明细（ORDER-LINE）的订单（ORDER）被生成，并被加入新订单（NEW-ORDER）列表。
客户对订单支付会产生交易历史（HISTORY）。 每个订单（ORDER）平均包含 10 条订单项（ORDER-LINE），其中 1% 需要从远程仓库中获取。这些就是 TPC-C 模型中的 9 个数据表。其中，仓库的数量 W 可以根据系统的实际情况进行调整，以使系统性能测试结果达到最佳。
事务类型
该 benchmark 包含 5 类事务：
-
NewOrder：新订单请求
从某一仓库中随机选取 5~15 件商品，创建新订单。其中 1% 的事务需要回滚（即 err）。一般来说新订单请求不可能超出全部事务请求的 45% 。
-
Payment：订单付款
更新客户账户余额，反映其支付情况。在全部事务请求中占比 43% 。
-
OrderStatus：最近订单查询
随机选择一个用户，查询其最近一条订单，显示该订单内的每个商品状态。在全部事务请求中占比 4% 。
-
Delivery：配送
模拟批处理交易，更新该订单用户的余额，把发货单从 neworder 中删除。在全部事务请求中占比 4% 。
-
StockLevel : 库存
缺货状态分析，在全部事务请求中占比 4% 。
安装环境部署版本
| 软件名 | 版本 | 安装方式 | 备注 |
|---|---|---|---|
| java | 1.8.0 | BenchmarkSQL本身是使用Java语言编写的 | |
| BenchmarkSQL | 5.0 | git在线 | 压测工具BenchmarkSQL |
| obclient | 2.0.0-2.el7.x86_64 | yum安装 | OBserver客户端工具 |
| oceanbase-ce | 3.1.3.el7.x86_64 | yum安装 | OBserver集群 |
| Ant | 1.9.4 | yum安装 | 用来对BenchmarkSQL进行编译 |
| mysql | 5.7.16 | yum安装 | 数据库 |
测试方案
- 使用 OBD 部署 OceanBase 数据库集群。ODP 和 TPC-C 单独部署在一台机器上, 作为客户端的压力机器。
- OceanBase 集群规模为 1：1：1。部署成功后，新建执行 TPC-C 测试的租户及用户（sys租户是管理集群的内置系统租户，请勿直接使用 sys 租户进行测试）。将租户的
primary_zone设置为RANDOM。RANDOM表示新建表分区的 Leader 随机到这 3 台机器。
测试规格
warehouses=10
loadWorkers=10
terminals=10
runMins=10
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
一、安装 Benchmark SQL
1、安装 Ant
按照以下步骤安装 Ant：
-
下载 Ant。
wget "http://archive.apache.org/dist/ant/binaries/apache-ant-1.10.6-bin.zip" -
设置环境变量。
export ANT_HOME=xx/benchmarksql/apache-ant-1.10.6/ -
检查是否安装成功。
ant -version如果返回以下信息，则安装成功。
Apache Ant(TM) version 1.10.6 compiled on May 2 2019
##
2、安装 Benchmark SQL5
BenchmarkSQL是通过jdbc连接各个数据库的。此次OceanBase的测试租户是Oracle类型，所以需要借用 lib/oracle 目录，然后把相关jar包一并放入其中。其中 oceanbase-client-*.jar 是OceanBase提供的，其他jar包可以从互联网获取。
-
进入官网下载地址 MySQL :: Download Connector/J
-
选择 （注：select Operating System这里一定要修改！！！）
-
编译 Benchmark SQL。
进入 Benchmark SQL 解压后的目录，使用 Ant 编译 Benchmark SQL：
[root@CAIP131 benchmarksql]# ant
Buildfile: /soft/benchmarksql/build.xml
init:
compile:
[javac] Compiling 11 source files to /soft/benchmarksql/build
dist:
[jar] Building jar: /soft/benchmarksql/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 1 second
由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，本节将详细介绍如何通过修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库。
二、适配Benchmark SQL
修改 benchmarksql-5.0/src/client/jTPCC.java 文件，增加 OceanBase 数据库相关内容。
[root@CAIP131 client]# vim jTPCC.java
if (iDB.equals("firebird"))
dbType = DB_FIREBIRD;
else if (iDB.equals("oracle"))
dbType = DB_ORACLE;
else if (iDB.equals("postgres"))
dbType = DB_POSTGRES;
else if (iDB.equals("oceanbase"))
dbType = DB_OCEANBASE;
else
{
log.error("unknown database type '" + iDB + "'");
return;
}
修改 benchmarksql-5.0/src/client/jTPCCConfig.java 文件，增加 OceanBase 数据库类型。
[root@CAIP131 client]# vim jTPCCConfig.java
public final static int DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
DB_OCEANBASE = 4;
5.修改 benchmarksql-5.0/src/client/jTPCCConnection.java 文件，在 SQL 子查询增加"AS L"别名。
default:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? 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 = ? AND d_id = ? " +
" ) " +
" )AS L");
break;
6.重新编译修改后的源码。
[root@CAIP131 benchmarksql-5.0]# ant
Buildfile: /soft/benchmarksql-5.0/build.xml
init:
compile:
[javac] Compiling 11 source files to /soft/benchmarksql-5.0/build
dist:
[jar] Building jar: /soft/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 1 second
[root@CAIP131 benchmarksql]# ant
Buildfile: /soft/benchmarksql/build.xml
init:
compile:
[javac] Compiling 11 source files to /soft/benchmarksql/build
dist:
[jar] Building jar: /soft/benchmarksql/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 0 seconds
7.在 benchmarksql-5.0/run 目录下，创建文件 prop.oceanbase。
db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://172.20.2.120:2883/tpcc?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000
user=tpcc@tenantdemo#obce_demo
password=Pwd123#
warehouses=10
loadWorkers=10
terminals=10
database=tpcc
//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
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
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
//osCollectorSSHAddr=user@dbhost
//osCollectorDevices=net_eth0 blk_sda
prop.oceanbase 中的参数说明：
-
JDBC 连接串：
conn=jdbc:mysql://x.x.x.x(ip):xx(port)/xxxx(dbname)?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000 -
rewriteBatchedStatements：
- 参数非常重要，会严重影响导数据效率，不可以忽略。
- 如果导数据较慢，可以用对应租户登录上去通过 show full processlist 检查是否开启。
- new order 事务中也用到了 batch update，因此导数和 benchmark 阶段都需要开启。
-
并发数量（terminals）：10，MySQL 租户配置下并发需要结合具体配置动态调整。
-
useLocalSessionState：是否使用 autocommit，read_only 和 transaction isolation 的内部值（jdbc 端的本地值），建议设置为 true，如果设置为 false，则需要发语句到远端请求，增加发送请求频次，影响性能。
-
warehouses/loadWorkers 这两项用于设置压测数据量，可以适当调整。
-
numTerminals > 0 && numTerminals <= 10*numWarehouses，terminals 的范围需要在这个区间内。
-
修改文件：
benchmarksql-5.0/run/funcs.sh，添加 OceanBase 数据库类型。function setCP() { case "$(getProp db)" in firebird) cp="../lib/firebird/*:../lib/*" ;; oracle) cp="../lib/oracle/*" if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then cp="${cp}:${ORACLE_HOME}/lib/*" fi cp="${cp}:../lib/*" ;; postgres) cp="../lib/postgres/*:../lib/*" ;; oceanbase) cp="../lib/oceanbase/*:../lib/*" ;; esac myCP=".:${cp}:../dist/*" export myCP } ...省略 case "$(getProp db)" in firebird|oracle|postgres|oceanbase) ;; "") echo "ERROR: missing db= config option in ${PROPS}" >&2 exit 1 ;; *) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2 exit 1 ;; esac
-
添加 mysql java connector 驱动，推荐 mysql-connector-java-5.1.47.jar。
[root@CAIP131 benchmarksql-5.0]# mkdir lib/oceanbase [root@CAIP131 benchmarksql-5.0]# cp mysql-connector-java-5.1.47.jar lib/oceanbase/
-
修改
benchmarksql-5.0/run/runDatabaseBuild.sh。AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish" # 修改为： AFTER_LOAD="indexCreates buildFinish"原配置
改配置
-
删除
benchmarksql-5.0/run/sql.common目录下 sql 文件的 sequence。
[root@CAIP131 sql.common]# ls
buildFinish.sql foreignKeys.sql indexCreates.sql indexDrops.sql tableCreates.sql tableDrops.sql tableTruncates.sql
tableCreates.sql 中去掉。
[root@CAIP131 sql.common]# vim tableCreates.sql
create sequence bmsql_hist_id_seq;
tableDrops.sql 中去掉。
[root@CAIP131 benchmarksql-5.0]# vim run/sql.common/tableDrops.sql
drop sequence bmsql_hist_id_seq; 
三、改造 BenchMarkSQL5 中的 SQL。
-
[root@CAIP131 benchmarksql-5.0]# cp run/sql.common/tableCreates.sql run/sql.common/tableCreates.sql-bak [root@CAIP131 benchmarksql-5.0]# vim run/sql.common/tableCreates.sql备份并重写
benchmarksql-5.0/run/sql.common/tableCreates.sql。建表脚本通常放在
run/sql.common下或者其他指定目录下。建表脚本可以选择非分区表方案和分区表方案。1、非分区表
create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(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 varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(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 varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(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 varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), PRIMARY KEY (c_w_id, c_d_id, c_id) )tablegroup=tpcc_group ; create table bmsql_history ( hist_id integer, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) )tablegroup=tpcc_group ; create table bmsql_new_order ( no_w_id integer not null , no_d_id integer not null, [detached from 1248.pts-0.obce-0000] [root@obce-0000 run]# cat sql.common/tableCreates.sql create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(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 varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(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 varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(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 varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), PRIMARY KEY (c_w_id, c_d_id, c_id) )tablegroup=tpcc_group ; create table bmsql_history ( hist_id integer, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) )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 varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer, PRIMARY KEY (i_id) )tablegroup=tpcc_group; create table bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24), PRIMARY KEY (s_w_id, s_i_id) )tablegroup=tpcc_group;2、非分区表改分区表
分区表语法
分区表是一种水平拆分方案，大部分表按照仓库 ID 做 HASH 分区。分区数取决于要测试的数据规模和机器数。
如果只有三台机器，分区数以 3 ~ 9 个为宜。如果是 5000 仓，9 台机器，则分区数可以调整到 99 或 100。通常来说 HASH 分区数没必要过 100。（此处为1-1-1集群部署，仅做测试使用3分区，正常以实际情况调整）
create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); -- drop tablegroup tpcc_group; create tablegroup tpcc_group partition by hash partitions 128; 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), primary key(w_id) )tablegroup='tpcc_group' partition by hash(w_id) partitions 128; 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), PRIMARY KEY (d_w_id, d_id) )tablegroup='tpcc_group' partition by hash(d_w_id) partitions 128; create table bmsql_customer ( c_w_id integer not null, c_d_id integer not null, c_id integer not null, c_discount decimal(4,4), c_credit char(2), c_last varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), PRIMARY KEY (c_w_id, c_d_id, c_id) )tablegroup='tpcc_group' partition by hash(c_w_id) partitions 128; create table bmsql_history ( hist_id integer, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) )tablegroup='tpcc_group' partition by hash(h_w_id) partitions 128; 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' partition by hash(no_w_id) partitions 128; 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' partition by hash(o_w_id) partitions 128; 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' partition by hash(ol_w_id) partitions 128; create table bmsql_item ( i_id integer not null, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer, PRIMARY KEY (i_id) ) duplicate_scope='cluster'; create table bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24), PRIMARY KEY (s_w_id, s_i_id) )tablegroup='tpcc_group' use_bloom_filter=true partition by hash(s_w_id) partitions 128;非分区表
分区表
注意：
- 建表语句中的分区数目可以根据实际情况调整，跟集群节点数有关。如果集群是3台(1-1-1)，建议是6个或6的倍数；如果集群是6台(2-2-2)，建议是12个或12的倍数；如果集群是9台(3-3-3)，建议是36个或36的倍数。这样是方便后期弹性伸缩测试的时候能尽可能保证每个节点上的分区数均衡。
- 上面bmsql_item使用了【复制表】功能，在租户的所有节点上都会有一个副本。当然主副本始终只有一个。有关【复制表】功能介绍请参考《OceanBase事务引擎特性和应用实践分享》。
- 建表语句不包含非主键索引，是为了后面加载数据性能更快。
4、删除表
删除表就是删除所有的表和表分组，在需要修改表结构分区数的时候执行。
备份并重写 benchmarksql-5.0/run/sql.common/tableDrops.sql。
-
[root@CAIP131 benchmarksql-5.0]# cp run/sql.common/tableDrops.sql run/sql.common/tableDrops.sql-bak [root@CAIP131 benchmarksql-5.0]# vim run/sql.common/tableDrops.sqldrop table bmsql_config; drop table bmsql_new_order; drop table bmsql_order_line; drop table bmsql_oorder; drop table bmsql_history; drop table bmsql_customer; drop table bmsql_stock; drop table bmsql_item; drop table bmsql_district; drop table bmsql_warehouse; drop tablegroup tpcc_group;
5、创建索引
当数据初始化完后再补充两个索引。
备份并重写
benchmarksql-5.0/run/sql.common/indexCreates.sql。[root@CAIP131 benchmarksql-5.0]# cp run/sql.common/indexCreates.sql run/sql.common/indexCreates.sql-bak [root@CAIP131 benchmarksql-5.0]# vim run/sql.common/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;
备份并重写
benchmarksql-5.0/run/sql.common/indexDrops.sql。[root@CAIP131 benchmarksql-5.0]# cp run/sql.common/indexDrops.sql run/sql.common/indexDrops.sql-bak [root@CAIP131 benchmarksql-5.0]# vim run/sql.common/indexDrops.sql
alter table bmsql_customer drop index bmsql_customer_idx1; alter table bmsql_oorder drop index bmsql_oorder_idx1;
四、环境调优
-
ODP 调优，请在 sys 租户下执行。
BenchmarkSQL会加载大量数据，短时间内对OceanBase内存消耗速度会很快，因此需要针对内存冻结合并和限流参数做一些调优。
[root@CAIP131 benchmarksql-5.0]# obclient -h172.20.2.120 -uroot@sys#obce_demo -p -P2883 -A -c oceanbase Enter password: Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.25 OceanBase 3.1.3 (r10100032022041510-a09d3134c10665f03fd56d7f8bdd413b2b771977) (Built Apr 15 2022 02:16:22) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
在系统租户下执行
obclient -h$host_ip -P$host_port -uroot@sys -A命令。（1）启动配置
MySQL [oceanbase]> alter proxyconfig set enable_strict_kernel_release=false; Query OK, 0 rows affected (0.003 sec) MySQL [oceanbase]> alter proxyconfig set automatic_match_work_thread=false; Query OK, 0 rows affected (0.004 sec)
（2）跑性能需要调整
MySQL [oceanbase]> alter proxyconfig set proxy_mem_limited='4G'; #防止oom Query OK, 0 rows affected (0.003 sec) MySQL [oceanbase]> alter proxyconfig set enable_compression_protocol=false; #关闭压缩，降低cpu% Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set work_thread_num=4; #num 等于租户分配的 cpu 核数，该参数需要重启 obproxy 才能生效 Query OK, 0 rows affected (0.003 sec) MySQL [oceanbase]> alter proxyconfig set slow_proxy_process_time_threshold='500ms'; Query OK, 0 rows affected (0.003 sec) MySQL [oceanbase]> alter proxyconfig set enable_ob_protocol_v2=false; Query OK, 0 rows affected (0.004 sec) MySQL [oceanbase]> alter proxyconfig set enable_qos=false; Query OK, 0 rows affected (0.003 sec) MySQL [oceanbase]> alter proxyconfig set syslog_level='error'; Query OK, 0 rows affected (0.001 sec)
-
OceanBase 数据库导数据阶段，请在 sys 租户下执行。
在系统租户下执行
obclient -h$host_ip -P$host_port -uroot@sys -A命令。MySQL [oceanbase]> alter system set memory_chunk_cache_size ='0'; Query OK, 0 rows affected (0.059 sec) MySQL [oceanbase]> alter system set trx_try_wait_lock_timeout='0ms'; Query OK, 0 rows affected (0.044 sec) MySQL [oceanbase]> alter system set large_query_threshold='1s'; Query OK, 0 rows affected (0.035 sec) MySQL [oceanbase]> alter system set trace_log_slow_query_watermark='500ms'; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set syslog_io_bandwidth_limit='30m'; Query OK, 0 rows affected (0.038 sec) MySQL [oceanbase]> alter system set enable_async_syslog=true; Query OK, 0 rows affected (0.038 sec) MySQL [oceanbase]> alter system set merger_warm_up_duration_time='0'; Query OK, 0 rows affected (0.036 sec) MySQL [oceanbase]> alter system set merger_switch_leader_duration_time='0'; Query OK, 0 rows affected (0.039 sec) MySQL [oceanbase]> alter system set large_query_worker_percentage=10; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set builtin_db_data_verify_cycle = 0; Query OK, 0 rows affected (0.038 sec) MySQL [oceanbase]> alter system set enable_merge_by_turn = False; Query OK, 0 rows affected (0.045 sec) MySQL [oceanbase]> alter system set minor_merge_concurrency=30; Query OK, 0 rows affected (0.038 sec) MySQL [oceanbase]> alter system set memory_limit_percentage = 85; Query OK, 0 rows affected (0.058 sec) MySQL [oceanbase]> alter system set memstore_limit_percentage = 80; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set freeze_trigger_percentage = 30; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set enable_syslog_recycle='True'; Query OK, 0 rows affected (0.035 sec) MySQL [oceanbase]> alter system set max_syslog_file_count=100; Query OK, 0 rows affected (0.036 sec) MySQL [oceanbase]> alter system set minor_freeze_times=500; Query OK, 0 rows affected (0.043 sec) MySQL [oceanbase]> alter system set minor_compact_trigger=5; Query OK, 0 rows affected (0.040 sec) MySQL [oceanbase]> alter system set max_kept_major_version_number=1; Query OK, 0 rows affected (0.045 sec) MySQL [oceanbase]> alter system set sys_bkgd_io_high_percentage = 90; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set sys_bkgd_io_low_percentage = 70; Query OK, 0 rows affected (0.059 sec) MySQL [oceanbase]> alter system set merge_thread_count = 45; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set merge_stat_sampling_ratio = 1; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set writing_throttling_trigger_percentage=75 tenant=tenantdemo; Query OK, 0 rows affected (0.013 sec) MySQL [oceanbase]> alter system set writing_throttling_maximum_duration='15m'; Query OK, 0 rows affected (0.068 sec) MySQL [oceanbase]> set global ob_plan_cache_percentage=20; Query OK, 0 rows affected (0.064 sec) MySQL [oceanbase]> alter system set enable_perf_event='false'; Query OK, 0 rows affected (0.052 sec) MySQL [oceanbase]> alter system set use_large_pages='true'; Query OK, 0 rows affected (0.035 sec) MySQL [oceanbase]> alter system set micro_block_merge_verify_level=0; Query OK, 0 rows affected (0.033 sec) MySQL [oceanbase]> alter system set builtin_db_data_verify_cycle=20; Query OK, 0 rows affected (0.044 sec) MySQL [oceanbase]> alter system set net_thread_count=4; Query OK, 0 rows affected (0.038 sec)
-
在 OBServer 部署之后，在进行测试 TPCC 的租户下执行。
[root@CAIP131 benchmarksql-5.0]# obclient -h172.20.2.120 -utpcc@tenantdemo#obce_demo -P2883 -p -c -A Enter password: Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.25 OceanBase 3.1.3 (r10100032022041510-a09d3134c10665f03fd56d7f8bdd413b2b771977) (Built Apr 15 2022 02:16:22) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
在测试用户下执行
obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A命令。MySQL [(none)]> alter system set _clog_aggregation_buffer_amount=8; Query OK, 0 rows affected (0.025 sec) MySQL [(none)]> alter system set _flush_clog_aggregation_buffer_timeout='1ms'; Query OK, 0 rows affected (0.014 sec)
-
OceanBase 数据库压力测试阶段，请在 sys 租户下执行。
在系统租户下执行
obclient -h$host_ip -P$host_port -uroot@sys -A命令。[root@CAIP131 benchmarksql-5.0]# obclient -h172.20.2.120 -uroot@sys#obce_demo -p -P2883 -A -c oceanbase Enter password: Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 1048577 Server version: 5.6.25 OceanBase 3.1.3 (r10100032022041510-a09d3134c10665f03fd56d7f8bdd413b2b771977) (Built Apr 15 2022 02:16:22) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
如果导入阶段开启了限速需要关闭
MySQL [oceanbase]> alter system set writing_throttling_trigger_percentage=100 tenant=tenantdemo; Query OK, 0 rows affected (0.016 sec) MySQL [oceanbase]> alter system set writing_throttling_maximum_duration='1h'; Query OK, 0 rows affected (0.054 sec) MySQL [oceanbase]> alter system set memstore_limit_percentage = 80; Query OK, 0 rows affected (0.058 sec) MySQL [oceanbase]> alter system set freeze_trigger_percentage = 30; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set large_query_threshold = '200s'; Query OK, 0 rows affected (0.042 sec) MySQL [oceanbase]> alter system set trx_try_wait_lock_timeout = '0ms'; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set cpu_quota_concurrency = 4; Query OK, 0 rows affected (0.031 sec) MySQL [oceanbase]> alter system set minor_warm_up_duration_time = 0; Query OK, 0 rows affected (0.042 sec) MySQL [oceanbase]> alter system set minor_freeze_times=500; Query OK, 0 rows affected (0.044 sec) MySQL [oceanbase]> alter system set minor_compact_trigger=3; Query OK, 0 rows affected (0.046 sec) MySQL [oceanbase]> alter system set sys_bkgd_io_high_percentage = 90; Query OK, 0 rows affected (0.036 sec) MySQL [oceanbase]> alter system set sys_bkgd_io_low_percentage = 70; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set minor_merge_concurrency =20; Query OK, 0 rows affected (0.047 sec) MySQL [oceanbase]> alter system set builtin_db_data_verify_cycle = 0; Query OK, 0 rows affected (0.034 sec) MySQL [oceanbase]> alter system set trace_log_slow_query_watermark = '10s'; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set gts_refresh_interval='500us'; Query OK, 0 rows affected (0.040 sec) MySQL [oceanbase]> alter system set server_permanent_offline_time='36000s'; Query OK, 0 rows affected (0.046 sec) MySQL [oceanbase]> alter system set weak_read_version_refresh_interval=0; Query OK, 0 rows affected (0.037 sec) MySQL [oceanbase]> alter system set _ob_get_gts_ahead_interval = '5ms'; Query OK, 0 rows affected (0.048 sec)
为频繁空查的宏块建立bloomfilter并缓存，减少磁盘IO和CPU消耗，提升写入性能
MySQL [oceanbase]> alter system set bf_cache_priority = 10; Query OK, 0 rows affected (0.040 sec) MySQL [oceanbase]> alter system set user_block_cache_priority=5; Query OK, 0 rows affected (0.036 sec) MySQL [oceanbase]> alter system set merge_stat_sampling_ratio = 0; Query OK, 0 rows affected (0.030 sec)
##close sql audit
MySQL [oceanbase]> alter system set enable_sql_audit=false; Query OK, 0 rows affected (0.037 sec)
调整日志级别及保存个数
MySQL [oceanbase]> alter system set syslog_level='PERF'; Query OK, 0 rows affected (0.039 sec) MySQL [oceanbase]> alter system set max_syslog_file_count=100; Query OK, 0 rows affected (0.045 sec) MySQL [oceanbase]> alter system set enable_syslog_recycle='True'; Query OK, 0 rows affected (0.048 sec) MySQL [oceanbase]> alter system set ob_enable_batched_multi_statement=true tenant=all; Query OK, 0 rows affected (0.021 sec) MySQL [oceanbase]> alter system set _cache_wash_interval = '1m'; Query OK, 0 rows affected (0.039 sec) MySQL [oceanbase]> alter system set plan_cache_evict_interval = '30s'; Query OK, 0 rows affected (0.045 sec) MySQL [oceanbase]> alter system set enable_one_phase_commit=false; Query OK, 0 rows affected (0.076 sec) MySQL [oceanbase]> alter system set enable_monotonic_weak_read = false; Query OK, 0 rows affected (0.049 sec)
-
设置租户。请在具体用户下执行。在测试用户下执行
obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A命令。[root@CAIP131 benchmarksql-5.0]# obclient -h172.20.2.120 -utpcc@tenantdemo#obce_demo -P2883 -p -c -A Enter password: Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 1048578 Server version: 5.6.25 OceanBase 3.1.3 (r10100032022041510-a09d3134c10665f03fd56d7f8bdd413b2b771977) (Built Apr 15 2022 02:16:22) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.数据库下租户设置，防止事务超时
OceanBase跟Oracle/MySQL相比，会有个默认SQL超时和事务超时机制。这个可能会导致后面查看修改数据的SQL报错。所以先修改一下这些参数。
MySQL [(none)]> set global ob_query_timeout=36000000000; Query OK, 0 rows affected (0.004 sec) MySQL [(none)]> set global ob_trx_timeout=36000000000; Query OK, 0 rows affected (0.004 sec) MySQL [(none)]> set global max_allowed_packet=67108864; Query OK, 0 rows affected (0.003 sec) MySQL [(none)]> set global ob_sql_work_area_percentage=100; Query OK, 0 rows affected (0.003 sec) MySQL [(none)]> set global parallel_max_servers=80; Query OK, 0 rows affected, 1 warning (0.043 sec) MySQL [(none)]> set global parallel_servers_target=192; Query OK, 0 rows affected (0.004 sec) /* parallel_max_servers推荐设置为测试租户分配的resource unit cpu数的10倍 如测试租户使用的unit配置为：create resource unit $unit_name max_cpu 26 那么该值设置为260 parallel_server_target推荐设置为parallel_max_servers * 机器数*0.8 那么该值为260*3*0.8=624 */
-
调优参数设置完毕后请执行
obd cluster restart $cluster_name命令重启集群。[admin@CAIP131 ~]$ obd cluster list +------------------------------------------------------------------+ | Cluster List | +-----------+------------------------------------+-----------------+ | Name | Configuration Path | Status (Cached) | +-----------+------------------------------------+-----------------+ | obce_demo | /home/admin/.obd/cluster/obce_demo | running | +-----------+------------------------------------+-----------------+ [admin@CAIP131 ~]$ obd cluster restart obce_demo Get local repositories and plugins ok Load cluster param plugin ok Open ssh connection ok Cluster status check ok Connect to observer ok Server check ok Observer rotation restart ok Wait for observer init ok +------------------------------------------------+ | observer | +--------------+---------+------+-------+--------+ | ip | version | port | zone | status | +--------------+---------+------+-------+--------+ | 172.20.2.120 | 3.1.3 | 2881 | zone1 | active | | 172.20.2.121 | 3.1.3 | 2881 | zone2 | active | | 172.20.2.122 | 3.1.3 | 2881 | zone3 | active | +--------------+---------+------+-------+--------+ Stop obproxy ok Start obproxy ok obproxy program health check ok Connect to obproxy ok +------------------------------------------------+ | obproxy | +--------------+------+-----------------+--------+ | ip | port | prometheus_port | status | +--------------+------+-----------------+--------+ | 172.20.2.120 | 2883 | 2884 | active | | 172.20.2.121 | 2883 | 2884 | active | | 172.20.2.122 | 2883 | 2884 | active | +--------------+------+-----------------+--------+ obce_demo restart
四、跑TPC-C 测试
以下命令均在 ../benchmarksql5/run 目录下执行。按照以下步骤进行 TPC-C 测试：
-
运行以下命令，初始化环境：
[root@CAIP131 run]# ./runDatabaseDestroy.sh prop.oceanbase # ------------------------------------------------------------ # Loading SQL file ./sql.common/tableDrops.sql # ------------------------------------------------------------ drop table bmsql_config; drop table bmsql_new_order; drop table bmsql_order_line; drop table bmsql_oorder; drop table bmsql_history; drop table bmsql_customer; drop table bmsql_stock; drop table bmsql_item; drop table bmsql_district; drop table bmsql_warehouse; purge recyclebin; -- tpcc_group
-
运行以下命令，创建表并导入数据：
[root@CAIP131 run]# ./runDatabaseBuild.sh prop.oceanbase # ------------------------------------------------------------ # Loading SQL file ./sql.oceanbase/tableCreates.sql # ------------------------------------------------------------ create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); -- drop tablegroup tpcc_group; create tablegroup tpcc_group partition by hash partitions 128; 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), primary key(w_id) )tablegroup='tpcc_group' partition by hash(w_id) partitions 128; 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), PRIMARY KEY (d_w_id, d_id) )tablegroup='tpcc_group' partition by hash(d_w_id) partitions 128; create table bmsql_customer ( c_w_id integer not null, c_d_id integer not null, c_id integer not null, c_discount decimal(4,4), c_credit char(2), c_last varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), PRIMARY KEY (c_w_id, c_d_id, c_id) )tablegroup='tpcc_group' partition by hash(c_w_id) partitions 128; create table bmsql_history ( hist_id integer, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) )tablegroup='tpcc_group' partition by hash(h_w_id) partitions 128; 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' partition by hash(no_w_id) partitions 128; 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' partition by hash(o_w_id) partitions 128; 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' partition by hash(ol_w_id) partitions 128; create table bmsql_item ( i_id integer not null, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer, PRIMARY KEY (i_id) ) duplicate_scope='cluster'; create table bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24), PRIMARY KEY (s_w_id, s_i_id) )tablegroup='tpcc_group' use_bloom_filter=true partition by hash(s_w_id) partitions 128; Starting BenchmarkSQL LoadData driver=com.mysql.jdbc.Driver conn=jdbc:mysql://172.20.2.120:2883,172.20.2.121,172.20.2.122/tpcc?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000 user=tpcc@tenantdemo#obce_demo password=*********** warehouses=10 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 001: Loading Warehouse 3 Worker 000: Loading Warehouse 2 done Worker 000: Loading Warehouse 4 Worker 001: Loading Warehouse 3 done Worker 001: Loading Warehouse 5 Worker 000: Loading Warehouse 4 done Worker 000: Loading Warehouse 6 Worker 001: Loading Warehouse 5 done Worker 001: Loading Warehouse 7 Worker 000: Loading Warehouse 6 done Worker 000: Loading Warehouse 8 Worker 001: Loading Warehouse 7 done Worker 001: Loading Warehouse 9 Worker 000: Loading Warehouse 8 done Worker 000: Loading Warehouse 10 Worker 001: Loading Warehouse 9 done Worker 000: Loading Warehouse 10 done # ------------------------------------------------------------ # 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; # ------------------------------------------------------------ # Loading SQL file ./sql.common/buildFinish.sql # ------------------------------------------------------------ -- ---- -- Extra commands to run after the tables are created, loaded, -- indexes built and extra's created. -- ----
此处运行时间比较长，需要等待
2）观察数据加载性能（sys租户登录）
MySQL [oceanbase]> SELECT tenant_id, ip, round(active/1024/1024/1024) active_gb, round(total/1024/1024/1024) total_gb, round(freeze_trigger/1024/1024/1024) freeze_trg_gb, round(mem_limit/1024/1024/1024) mem_limit_gb -> , freeze_cnt , round((active/freeze_trigger),2) freeze_pct, round(total/mem_limit, 2) mem_usage -> FROM `gv$memstore` -> WHERE tenant_id =1001 -> ORDER BY tenant_id, ip; +-----------+--------------+-----------+----------+---------------+--------------+------------+------------+-----------+ | tenant_id | ip | active_gb | total_gb | freeze_trg_gb | mem_limit_gb | freeze_cnt | freeze_pct | mem_usage | +-----------+--------------+-----------+----------+---------------+--------------+------------+------------+-----------+ | 1001 | 172.20.2.120 | 1 | 1 | 1 | 3 | 0 | 0.50 | 0.23 | | 1001 | 172.20.2.121 | 1 | 1 | 1 | 3 | 0 | 0.50 | 0.23 | | 1001 | 172.20.2.122 | 1 | 1 | 1 | 3 | 0 | 0.50 | 0.23 | +-----------+--------------+-----------+----------+---------------+--------------+------------+------------+-----------+ 3 rows in set (0.078 sec)
为了对数据写入速度进行观察，可以在sys租户下反复执行下面SQL，主要是观察增量内存增速和增量内存总量，以及是否接近总增量内存限制
-
执行合并（需要使用 sys 租户登录）
建议跑性能测试之前先做一次集群合并（
major freeze）。[root@CAIP131 run]# obclient -h172.20.2.120 -uroot@sys#obce_demo -p -P2883 -A -c oceanbase Enter password: Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 131 Server version: 5.6.25 OceanBase 3.1.3 (r10100032022041510-a09d3134c10665f03fd56d7f8bdd413b2b771977) (Built Apr 15 2022 02:16:22) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Major 合并将当前大版本的 SSTable 和 MemTable 与前一个大版本的全量静态数据进行合并，使存储层统计信息更准确，生成的执行计划更稳定。
MySQL [oceanbase]> alter system major freeze; Query OK, 0 rows affected
-
查看合并是否完成
MySQL [oceanbase]> select name,value from oceanbase.__all_zone where name='frozen_version' or name='last_merged_version'; +---------------------+-------+ | name | value | +---------------------+-------+ | frozen_version | 2 | | last_merged_version | 1 | | last_merged_version | 1 | | last_merged_version | 1 | | last_merged_version | 1 | +---------------------+-------+ 5 rows in set (0.053 sec) MySQL [oceanbase]> select name,value from oceanbase.__all_zone where name='frozen_version' or name='last_merged_version'; +---------------------+-------+ | name | value | +---------------------+-------+ | frozen_version | 2 | | last_merged_version | 2 | | last_merged_version | 2 | | last_merged_version | 2 | | last_merged_version | 2 | +---------------------+-------+ 5 rows in set (0.003 sec)
frozen_version和last_merged_version的值相等即表示合并完成。 -
运行以下命令，执行压力测试：
[root@CAIP131 run]# ./runBenchmark.sh prop.oceanbase 16:40:52,122 [main] INFO jTPCC : Term-00, 16:40:52,124 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 16:40:52,124 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 16:40:52,124 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 16:40:52,124 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 16:40:52,124 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 16:40:52,125 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 16:40:52,125 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 16:40:52,125 [main] INFO jTPCC : Term-00, 16:40:52,126 [main] INFO jTPCC : Term-00, db=oceanbase 16:40:52,126 [main] INFO jTPCC : Term-00, driver=com.mysql.jdbc.Driver 16:40:52,126 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://172.20.2.120:2883,172.20.2.121,172.20.2.122/tpcc?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000 16:40:52,126 [main] INFO jTPCC : Term-00, user=tpcc@tenantdemo#obce_demo 16:40:52,126 [main] INFO jTPCC : Term-00, 16:40:52,126 [main] INFO jTPCC : Term-00, warehouses=10 16:40:52,126 [main] INFO jTPCC : Term-00, terminals=10 16:40:52,127 [main] INFO jTPCC : Term-00, runMins=5 16:40:52,127 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0 16:40:52,127 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 16:40:52,127 [main] INFO jTPCC : Term-00, 16:40:52,127 [main] INFO jTPCC : Term-00, newOrderWeight=45 16:40:52,127 [main] INFO jTPCC : Term-00, paymentWeight=43 16:40:52,127 [main] INFO jTPCC : Term-00, orderStatusWeight=4 16:40:52,127 [main] INFO jTPCC : Term-00, deliveryWeight=4 16:40:52,127 [main] INFO jTPCC : Term-00, stockLevelWeight=4 16:40:52,127 [main] INFO jTPCC : Term-00, 16:40:52,127 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 16:40:52,127 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 16:40:52,128 [main] INFO jTPCC : Term-00, 16:40:52,140 [main] INFO jTPCC : Term-00, copied prop.oceanbase to my_result_2022-05-04_164052/run.properties 16:40:52,140 [main] INFO jTPCC : Term-00, created my_result_2022-05-04_164052/data/runInfo.csv for runID 6 16:40:52,140 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-05-04_164052/data/result.csv 16:40:52,141 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 16:40:52,141 [main] INFO jTPCC : Term-00, osCollectorInterval=1 16:40:52,141 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 16:40:52,141 [main] INFO jTPCC : Term-00, osCollectorDevices=null 16:40:52,202 [main] INFO jTPCC : Term-00, 16:40:52,554 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 219 Term-00, Running 16:45:52,756 [Thread-2] INFO jTPCC : Term-00, 746280 Memory Usage: 44MB / 350MB 16:45:52,756 [Thread-2] INFO jTPCC : Term-00, 16:45:52,756 [Thread-2] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 10061.99 16:45:52,757 [Thread-2] INFO jTPCC : Term-00, Measured tpmTOTAL = 22468.0 16:45:52,757 [Thread-2] INFO jTPCC : Term-00, Session Start = 2022-05-04 16:40:52 16:45:52,757 [Thread-2] INFO jTPCC : Term-00, Session End = 2022-05-04 16:45:52 16:45:52,757 [Thread-2] INFO jTPCC : Term-00, Transaction Count = 112357
五、查看执行计划
压测环境中的 SQL 执行效率，一定程度上决定了系统的性能，因此 SQL 诊断是性能调优过程中最重要的一个环节，本文主要介绍如何定位并优化慢 SQL。
1、根据 plan cache 的统计，找出高频的 SQL 类别：
MySQL [oceanbase]> select plan_id, sql_id, hit_count, avg_exe_usec, substr(statement, 1, 100) from gv$plan_cache_plan_stat where tenant_id=1001 order by hit_count desc limit 10; +---------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+ | plan_id | sql_id | hit_count | avg_exe_usec | substr(statement, 1, 100) | +---------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+ | 542 | A460265EC2F0763A15DD27CE9E4E2200 | 1396 | 40502 | SELECT i_price, i_name, i_data FROM bmsql_item WHERE i_id = ? | | 32 | 7229213613983BC5FDA15AD11EC70D01 | 1394 | 2168498 | SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_di | | 544 | 7229213613983BC5FDA15AD11EC70D01 | 1390 | 2179138 | SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_di | | 39 | 6BF8C26F446FB5D1844675495F378BBA | 1252 | 1224217 | UPDATE bmsql_stock SET s_quantity = ?, s_ytd = s_ytd + ?, s_order_cnt = s_order_cnt + ?, | | 555 | 6BF8C26F446FB5D1844675495F378BBA | 1252 | 6748411 | UPDATE bmsql_stock SET s_quantity = ?, s_ytd = s_ytd + ?, s_order_cnt = s_order_cnt + ?, | | 505 | 69D4EC82CE11CB6BA481591FD96467E2 | 432 | 642 | SELECT value from oceanbase.__all_sys_stat where name = ? | | 438 | B447DE16B3F42D2409B2A2BE50328E63 | 185 | 966 | UPDATE bmsql_warehouse SET w_ytd = w_ytd + ? WHERE w_id = ? | | 543 | E9FB4BD02AD743EF78C51D0F30745F43 | 168 | 1981 | INSERT INTO bmsql_history ( h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_d | | 442 | E9FB4BD02AD743EF78C51D0F30745F43 | 168 | 740 | INSERT INTO bmsql_history ( h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_d | | 536 | AE32C84F890055A535A28B262C649D41 | 167 | 98669 | SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_ | +---------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+ 10 rows in set (0.064 sec)
2、查看TOP 10的 SQL
MySQL [oceanbase]> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time -> FROM gv$sql_audit s -> WHERE 1=1 -> and user_name='tpcc' -> and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) -> GROUP BY sql_id -> order by avg_elapsed_time desc limit 10; +----------------------------------+----------+------------------+---------------+ | sql_id | count(*) | avg_elapsed_time | avg_exec_time | +----------------------------------+----------+------------------+---------------+ | 7229213613983BC5FDA15AD11EC70D01 | 1 | 12234226 | 12232982 | | F59A700FA168324279B0DBC25E19760F | 1 | 12107961 | 12077736 | | E1F2BDA1D7391B757859ED3704E5AFB7 | 1 | 5814926 | 1264606 | | E177E398602B2CD92F2E1347DBC60625 | 1 | 5176866 | 323510 | | 1496760678C1855ED516955457012F0E | 1 | 4938977 | 293100 | | 17A66CE0FF8F6CB4B5ACCD7147677B28 | 1 | 4924207 | 293204 | | D09B3FFE2B125F8F531025ABCF667BCA | 1 | 4900257 | 251526 | | 3788C3E5095971313A7EB1B162AA33E2 | 1 | 4881357 | 278378 | | 932B0EAAC451813117F21C864E4CD142 | 1 | 4877486 | 269028 | | DCE3A9F32ED0BDA17F31935F141A8B4A | 1 | 4840700 | 295099 | +----------------------------------+----------+------------------+---------------+ 10 rows in set (0.071 sec)
3、 对elapsed时间最长的前三条sql进行分析
查询获取实际执行计划需要的必要信息
MySQL [oceanbase]> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, -> round(avg(execute_time)) avg_exec_time, -> s.svr_ip, -> s.svr_port, -> s.tenant_id, -> s.plan_id -> FROM gv$sql_audit s -> WHERE 1=1 -> and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) -> GROUP BY sql_id -> order by avg_elapsed_time desc limit 3; +----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+ | sql_id | count(*) | avg_elapsed_time | avg_exec_time | svr_ip | svr_port | tenant_id | plan_id | +----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+ | 7229213613983BC5FDA15AD11EC70D01 | 1 | 12234226 | 12232982 | 172.20.2.121 | 2882 | 1001 | 544 | | F59A700FA168324279B0DBC25E19760F | 1 | 12107961 | 12077736 | 172.20.2.122 | 2882 | 1001 | 22 | | E1F2BDA1D7391B757859ED3704E5AFB7 | 1 | 5814926 | 1264606 | 172.20.2.121 | 2882 | 1001 | 557 | +----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+ 3 rows in set (0.076 sec)
1、 获取第一条sql的文本
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | query_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 = 8 AND s_i_id = 3448 FOR UPDATE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.046 sec)
查询plan_id
MySQL [oceanbase]> SELECT * FROM v$plan_cache_plan_stat WHERE tenant_id= 1001 AND STATEMENT LIKE 'SELECT count(*) AS low_stock FROM%'\G *************************** 1. row *************************** tenant_id: 1001 svr_ip: 172.20.2.122 svr_port: 2882 plan_id: 22 sql_id: F59A700FA168324279B0DBC25E19760F type: 3 is_bind_sensitive: 0 is_bind_aware: 0 db_id: 18446744073709551615 statement: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = ? AND s_quantity < ? 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 - ? AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ? ) ) query_sql: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 9 AND s_quantity < 14 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 = 9 AND d_id = 2 ) ) special_params: param_infos: {1,0,0,0,5},{1,0,0,0,5},{1,0,0,0,5},{1,0,0,0,5},{1,0,0,0,5} sys_vars: 45,6291456,2,4,1,0,0,32,3,1,0,1,1,0,10485760,1,1,0,1,BINARY,BINARY,AL32UTF8,AL32UTF8,BYTE,FALSE,1,100,64,200,0,13,NULL,1,1,1 plan_hash: 9468503252095317567 first_load_time: 2022-05-06 15:27:05.929625 schema_version: 1651821677418216 merged_version: 2 last_active_time: 2022-05-06 15:37:04.867223 avg_exe_usec: 3124432 slowest_exe_time: 2022-05-06 15:27:18.007381 slowest_exe_usec: 12107961 slow_count: 18 hit_count: 17 plan_size: 116376 executions: 18 disk_reads: 1389 direct_writes: 0 buffer_gets: 427230 application_wait_time: 0 concurrency_wait_time: 2169 user_io_wait_time: 7995489 rows_processed: 18 elapsed_time: 56239793 cpu_time: 48236152 large_querys: 6 delayed_large_querys: 3 delayed_px_querys: 0 outline_version: 0 outline_id: -1 outline_data: /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("VIEW1"@"SEL$1" "tpcc.bmsql_stock"@"SEL$1" )) USE_HASH(@"SEL$1" ("tpcc.bmsql_stock"@"SEL$1" )) LEADING(@"SEL$3" ("tpcc.bmsql_order_line"@"SEL$3" "tpcc.bmsql_district"@"SEL$3" )) USE_NL(@"SEL$3" ("tpcc.bmsql_district"@"SEL$3" )) USE_NL_MATERIALIZATION(@"SEL$3" ("tpcc.bmsql_district"@"SEL$3" )) FULL(@"SEL$3" "tpcc.bmsql_order_line"@"SEL$3") FULL(@"SEL$3" "tpcc.bmsql_district"@"SEL$3") FULL(@"SEL$1" "tpcc.bmsql_stock"@"SEL$1") END_OUTLINE_DATA*/ acs_sel_info: table_scan: 1 evolution: 0 evo_executions: 0 evo_cpu_time: 0 timeout_count: 0 ps_stmt_id: -1 sessid: 0 temp_tables: is_use_jit: 0 object_type: SQL_PLAN hints_info: hints_all_worked: 1 pl_schema_id: NULL is_batched_multi_stmt: 0 1 row in set (0.116 sec)
实际执行计划
第一个sql_id 有两个不同的文本，这两个文本的不同仅仅是传入参数不同，可以共享执行计划，这个计划的plan_id 为40，运行下面sql查询这条语句的实际执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1001 AND ip = '172.20.2.121' AND port=2882 AND plan_id=544\G; *************************** 1. row *************************** ip: 172.20.2.121 plan_depth: 0 plan_line_id: 0 operator: PHY_DIRECT_RECEIVE name: NULL rows: 2 cost: 1373657 property: NULL *************************** 2. row *************************** ip: 172.20.2.121 plan_depth: 1 plan_line_id: 1 operator: PHY_DIRECT_TRANSMIT name: NULL rows: 2 cost: 1373655 property: NULL *************************** 3. row *************************** ip: 172.20.2.121 plan_depth: 2 plan_line_id: 2 operator: PHY_TABLE_SCAN name: bmsql_stock rows: 2 cost: 1373655 property: table_rows:1000000, physical_range_rows:1000000, logical_range_rows:1000000, index_back_rows:0, output_rows:1, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453786, (table_type:1, version:0-1651821796117201-1651821796117201, logical_rc:1000000, physical_rc:1000000), (table_type:7, version:1651821786118659-1651821796117201-1651821814971635, logical_rc:0, physical_rc:0), (table_type:5, version:1651821786118659-1651821796117201-1651821814971635, logical_rc:0, physical_rc:0), (table_type:0, version:1651821814971635-1651821814971635-9223372036854775807, logical_rc:0, physical_rc:0)] 3 rows in set (0.031 sec)
可以看到，这条语句实际的执行计划和解释执行计划是相同的，都做了全表扫描，成本显示也相同。
2、获取第二条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit where 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 = 9 AND s_quantity < 14 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 = 9 AND d_id = 2 ) ) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.075 sec)
实际执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1001 AND ip = '172.20.2.122' AND port=2882 AND plan_id=22\G; *************************** 1. row *************************** ip: 172.20.2.122 plan_depth: 0 plan_line_id: 0 operator: PHY_SCALAR_AGGREGATE name: NULL rows: 1 cost: 3138297 property: NULL *************************** 2. row *************************** ip: 172.20.2.122 plan_depth: 1 plan_line_id: 1 operator: PHY_HASH_JOIN name: NULL rows: 1141 cost: 3138079 property: NULL *************************** 3. row *************************** ip: 172.20.2.122 plan_depth: 2 plan_line_id: 2 operator: PHY_SUBPLAN_SCAN name: NULL rows: 1112 cost: 2003342 property: NULL *************************** 4. row *************************** ip: 172.20.2.122 plan_depth: 3 plan_line_id: 3 operator: PHY_NESTED_LOOP_JOIN name: NULL rows: 1112 cost: 2003189 property: NULL *************************** 5. row *************************** ip: 172.20.2.122 plan_depth: 4 plan_line_id: 4 operator: PHY_PX_FIFO_COORD name: NULL rows: 30024 cost: 1997807 property: NULL *************************** 6. row *************************** ip: 172.20.2.122 plan_depth: 5 plan_line_id: 5 operator: PHY_PX_REDUCE_TRANSMIT name: NULL rows: 30024 cost: 1992122 property: NULL *************************** 7. row *************************** ip: 172.20.2.122 plan_depth: 6 plan_line_id: 6 operator: PHY_TABLE_SCAN name: bmsql_order_line rows: 30024 cost: 1992122 property: table_rows:3002365, physical_range_rows:3002365, logical_range_rows:3002365, index_back_rows:0, output_rows:30023, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453784, (table_type:1, version:0-1651821796117201-1651821796117201, logical_rc:3002365, physical_rc:3002365), (table_type:7, version:1651821785987371-1651821796117201-1651821808825721, logical_rc:0, physical_rc:0), (table_type:5, version:1651821785987371-1651821796117201-1651821808825721, logical_rc:0, physical_rc:0), (table_type:0, version:1651821808825721-1651821808825721-9223372036854775807, logical_rc:0, physical_rc:0)] *************************** 8. row *************************** ip: 172.20.2.122 plan_depth: 4 plan_line_id: 7 operator: PHY_MATERIAL name: NULL rows: 1 cost: 108 property: NULL *************************** 9. row *************************** ip: 172.20.2.122 plan_depth: 5 plan_line_id: 8 operator: PHY_PX_FIFO_COORD name: NULL rows: 1 cost: 108 property: NULL *************************** 10. row *************************** ip: 172.20.2.122 plan_depth: 6 plan_line_id: 9 operator: PHY_PX_REDUCE_TRANSMIT name: NULL rows: 1 cost: 108 property: NULL *************************** 11. row *************************** ip: 172.20.2.122 plan_depth: 7 plan_line_id: 10 operator: PHY_TABLE_SCAN name: bmsql_district rows: 1 cost: 108 property: table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1100611139453779, (table_type:1, version:0-1651821796117201-1651821796117201, logical_rc:100, physical_rc:100), (table_type:7, version:1651821786122765-1651821796117201-1651821808960416, logical_rc:0, physical_rc:0), (table_type:5, version:1651821786122765-1651821796117201-1651821808960416, logical_rc:0, physical_rc:0), (table_type:0, version:1651821808960416-1651821808960416-9223372036854775807, logical_rc:0, physical_rc:0)] *************************** 12. row *************************** ip: 172.20.2.122 plan_depth: 2 plan_line_id: 11 operator: PHY_TABLE_SCAN name: bmsql_stock rows: 5569 cost: 1131014 property: table_rows:1000000, physical_range_rows:1000000, logical_range_rows:1000000, index_back_rows:0, output_rows:5568, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453786, (table_type:1, version:0-1651821796117201-1651821796117201, logical_rc:1000000, physical_rc:1000000), (table_type:7, version:1651821786118659-1651821796117201-1651821808768055, logical_rc:0, physical_rc:0), (table_type:5, version:1651821786118659-1651821796117201-1651821808768055, logical_rc:0, physical_rc:0), (table_type:0, version:1651821808768055-1651821808768055-9223372036854775807, logical_rc:0, physical_rc:0)] 12 rows in set (0.014 sec)
explain 命令查看解释执行计划
如何使用 EXPLAIN 查看理论执行计划 查看执行计划需要一个好的客户端，这样能将注意力集中在执行计划上。推荐使用以下客户端： 命令行 OBClient 命令。OBClient 的好处是格式化显示结果，当一行结果太长的时候格式会 不美观，这个时候可以让 SQL 以 \G 替换 ; 结尾，结果集会列传行展示，提高可读性。 OceanBase 官方客户端 ODC。直接选中 SQL，点击右上角的执行计划，会以表格形式展示 执行计划。 开源的 DBeaver 客户端工具。由于 DBeaver 和 OceanBase 还没有紧密适配，所以只能 以 EXPLAIN SQL 的方式查看执行计划，目前这种方式体验最好
学习遵从由简入繁原则，先从默认的 BASIC 格式入手，不用指定。FORMAT 有 TRADITIONAL 和 JSON 两种格式，默认是 TRADITIONAL 格式，可读性更好，JSON 格式对程序解析比较友好(tpcc用户执行)
MySQL [tpcc]> explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock -> WHERE s_w_id = 2 AND s_quantity < 13 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 = 2 AND d_id = 8))\G; *************************** 1. row *************************** Query Plan: ================================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | ---------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |3140997| |1 | HASH RIGHT SEMI JOIN | |1136 |3140780| |2 | SUBPLAN SCAN |VIEW1 |1113 |2004905| |3 | NESTED-LOOP JOIN | |1113 |2004752| |4 | PX COORDINATOR | |30036 |1999331| |5 | EXCHANGE OUT DISTR |:EX10000 |30036 |1993645| |6 | TABLE SCAN |bmsql_order_line|30036 |1993645| |7 | MATERIAL | |1 |146 | |8 | PX COORDINATOR | |1 |146 | |9 | EXCHANGE OUT DISTR|:EX20000 |1 |146 | |10| TABLE SCAN |bmsql_district |1 |146 | |11| TABLE SCAN |bmsql_stock |4457 |1132520| ================================================================ Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)]), filter(nil), group(nil), agg_func([T_FUN_COUNT(*)]) 1 - output([1]), filter(nil), equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil) 2 - output([VIEW1.ol_i_id]), filter(nil), access([VIEW1.ol_i_id]) 3 - output([bmsql_order_line.ol_i_id]), filter(nil), conds([bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20], [bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id]), nl_params_(nil) 4 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil) 5 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil), is_single, dop=1 6 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 8]), access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0) 7 - output([bmsql_district.d_next_o_id]), filter(nil) 8 - output([bmsql_district.d_next_o_id]), filter(nil) 9 - output([bmsql_district.d_next_o_id]), filter(nil), is_single, dop=1 10 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 8], [bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_next_o_id]), partitions(p0) 11 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_quantity < 13], [bmsql_stock.s_w_id = 2]), access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0) 1 row in set (0.012 sec)
这条语句的解释执行计划和实际执行计划也是相同的。
3、获取第三条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit where sql_id='E1F2BDA1D7391B757859ED3704E5AFB7'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | query_sql | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | UPDATE bmsql_stock SET s_quantity = 36, s_ytd = s_ytd + 4, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 2 AND s_i_id = 96248 | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.046 sec)
实际执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1001 AND ip = '172.20.2.121' AND port=2882 AND plan_id=557\G; *************************** 1. row *************************** ip: 172.20.2.121 plan_depth: 0 plan_line_id: 0 operator: PHY_DIRECT_RECEIVE name: NULL rows: 2 cost: 1439831 property: NULL *************************** 2. row *************************** ip: 172.20.2.121 plan_depth: 1 plan_line_id: 1 operator: PHY_DIRECT_TRANSMIT name: NULL rows: 2 cost: 1439831 property: NULL *************************** 3. row *************************** ip: 172.20.2.121 plan_depth: 2 plan_line_id: 2 operator: PHY_UPDATE name: NULL rows: 2 cost: 1439831 property: NULL *************************** 4. row *************************** ip: 172.20.2.121 plan_depth: 3 plan_line_id: 3 operator: PHY_TABLE_SCAN name: bmsql_stock rows: 2 cost: 1439830 property: table_rows:1000000, physical_range_rows:1000000, logical_range_rows:1000000, index_back_rows:0, output_rows:1, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453786, (table_type:1, version:0-1651821796117201-1651821796117201, logical_rc:1000000, physical_rc:1000000), (table_type:7, version:1651821786118659-1651821796117201-1651821814971635, logical_rc:0, physical_rc:0), (table_type:5, version:1651821786118659-1651821796117201-1651821814971635, logical_rc:0, physical_rc:0), (table_type:0, version:1651821814971635-1651821814971635-9223372036854775807, logical_rc:0, physical_rc:0)] 4 rows in set (0.006 sec)
explain 命令查看解释执行计划
如何使用 EXPLAIN 查看理论执行计划 查看执行计划需要一个好的客户端，这样能将注意力集中在执行计划上。推荐使用以下客户端： 命令行 OBClient 命令。OBClient 的好处是格式化显示结果，当一行结果太长的时候格式会 不美观，这个时候可以让 SQL 以 \G 替换 ; 结尾，结果集会列传行展示，提高可读性。 OceanBase 官方客户端 ODC。直接选中 SQL，点击右上角的执行计划，会以表格形式展示 执行计划。 开源的 DBeaver 客户端工具。由于 DBeaver 和 OceanBase 还没有紧密适配，所以只能 以 EXPLAIN SQL 的方式查看执行计划，目前这种方式体验最好
学习遵从由简入繁原则，先从默认的 BASIC 格式入手，不用指定。FORMAT 有 TRADITIONAL 和 JSON 两种格式，默认是 TRADITIONAL 格式，可读性更好，JSON 格式对程序解析比较友好(tpcc用户执行)
MySQL [tpcc]> explain SELECT ol_i_id, ol_supply_w_id, ol_quantity, -> ol_amount, ol_delivery_d -> FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182 -> ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number\G; *************************** 1. row *************************** Query Plan: =================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | --------------------------------------------------- |0 |SORT | |11 |2828203| |1 | TABLE SCAN|bmsql_order_line|11 |2828177| =================================================== Outputs & filters: ------------------------------------- 0 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d]), filter(nil), sort_keys([bmsql_order_line.ol_number, ASC]) 1 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), filter([bmsql_order_line.ol_o_id = 1182], [bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 4]), access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), partitions(p0) 1 row in set (0.010 sec)
这条语句的解释执行计划和实际执行计划也是相同的，都是全表扫描之后进行排序。
遇到的问题
执行建表报错
[root@CAIP131 run]# ./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sql
create sequence bmsql_hist_id_seq;
You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'sequence bmsql_hist_id_seq' at line 1
解决
tableCreates.sql 中去掉。
[root@CAIP131 sql.common]# vim tableCreates.sql
create sequence bmsql_hist_id_seq;
执行./runDatabaseBuild.sh prop.oceanbase报错如下：语法错误
You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'varchar2(30) primary key,
访问文档路径是/sql.oceanbase/tableCreates.sql的，文档路径是/sql.common/tableCreates.sql是错误的，访问文档路径是/sql.oceanbase/tableCreates.sql并没有修改，默认是varchar2，但没有修改，修改/sql.oceanbase/的tableCreates.sql就可以了。
最后
OceanBase多次TPC-C榜首，证明了分布式数据库在性能、可用行、扩展性、强一致性上的巨大进步与突破。本次测试来源网络，只能在一定程度上参考。大家可以结合实际案例测试，案例也证明了分布式可以支持高并发OLTP场景。最后，希望分布式数据库在未来，加速发展、快速迭代、继续投入，拓展出更加美好的未来。
2838
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
