摘要：好消息！「 OceanBase 2.2 版本 」正式上线官网啦！（点击阅读原文即可直接下载）OceanBase 2.2版本是成功支撑2019年天猫双11大促的稳定版本，同时也是用于TPC-C测试且荣登TPC-C性能榜首的版本。
我们将在接下来的时间里为大家持续推出 “OceanBase 2.2 手把手系列” ，将手把手带大家一起体验OceanBase 2.2的强大功能。欢迎持续关注！
引言
OceanBase 2.2版本近期已通过官网提供下载 （https://oceanbase.alipay.com/download/resource），2.2支持Oracle租户。OceanBase在2019年10月2日荣膺国际事务委员会(TPC)审计发布的TPC-C基准测试榜首，用的就是Oracle租户。TPC-C测试使用了207多台阿里云高配ECS服务器，是因为TPC-C标准对应用、数据库等规范非常细致严格。一般来说普通企业或个人很难有那样的条件去测试。
BenchmarkSQL是开源的TPC-C测试程序，它弱化了TPC-C的关键标准(数据分布和应用执行行为方面)，使得用几台服务器就可以跑TPC-C成为可能。当然这个结果不能跟官方TPC-C的结果相比较。不过，使用BenchmarkSQL来比较不同的数据库的事务处理能力还是有一定参考意义的，尤其是相比Sysbench而言。
OceanBase测试租户准备
1.sys租户参数修改
BenchmarkSQL会加载大量数据，短时间内对OceanBase内存消耗速度会很快，因此需要针对内存冻结合并和限流参数做一些调优。在sys租户执行：
ALTER SYSTEM SET enable_merge_by_turn=FALSE; ALTER SYSTEM set minor_freeze_times=100; ALTER SYSTEM set freeze_trigger_percentage=70; ALTER SYSTEM set writing_throttling_trigger_percentage=70 tenant='obbmsql'; ALTER SYSTEM set writing_throttling_maximum_duration='10m' tenant='obbmsql'; show parameters where name in ('minor_freeze_times','freeze_trigger_percentage');
注意：业务租户限流参数的修改是在sys租户里，需要指定相应的租户名。然后查看确认需要到业务租户里。
在业务租户执行：
SHOW parameters WHERE name IN ('writing_throttling_trigger_percentage','writing_throttling_maximum_duration');
2. 业务租户参数修改
OceanBase跟Oracle/MySQL相比，会有个默认SQL超时和事务超时机制。这个可能会导致后面查看修改数据的SQL报错。所以先修改一下这些参数。
set global recyclebin=off; set global ob_query_timeout=1000000000; set global ob_trx_idle_timeout=1200000000; set global ob_trx_timeout=1000000000;
此外，需要为bmsql准备一个单独的schema(即用户)。
drop user tpcc cascade; create user tpcc identified by 123456;grant all privileges on tpcc.* to tpcc with grant option ; grant create, drop on *.* to tpcc;
至此测试租户用户名为：tpcc@obbmsql#obdemo 或 obdemo:obbmsql:tpcc。
3. OBProxy配置修改
OBProxy是OceanBase的访问代理，其内部一些参数也可能影响性能。如下面的压缩参数对CPU有一定消耗，测试时可以关闭。
$ obclient -h127.1 -uroot@sys#obdemo -P2883 -p123456 -c -A oceanbase alter proxyconfig set enable_compression_protocol=False; show proxyconfig like 'enable_compression_protocol';
该参数修改后，需要重启obproxy进程
[admin@xxx /home/admin] $kill -9 `pidof obproxy` [admin@h07d17167.sqa.eu95 /home/admin] $cd /opt/taobao/install/obproxy [admin@xxx /opt/taobao/install/obproxy] $bin/obproxy bin/obproxy
BenchmarkSQL准备
BenchmarkSQL 官方下载地址是： https://sourceforge.net/projects/benchmarksql/ ，下载后请参考 HOW-TO-RUN.txt 中说明先编译安装BenchmarkSQL。然后按下面建议修改部分脚本增加对OceanBase支持。也可以直接下载我编译修改好的BenchmarkSQL，地址是：https://github.com/obpilot/benchmarksql-5.0 。
1. 准备OceanBase驱动文件
BenchmarkSQL是通过jdbc连接各个数据库的。此次OceanBase的测试租户是Oracle类型，所以需要借用 lib/oracle 目录，然后把相关jar包一并放入其中。其中 oceanbase-client-*.jar 是OceanBase提供的，其他jar包可以从互联网获取。
[admin@xxx /home/admin/benchmarksql-5.0]$ll lib/oracle/ total 3728 -rwxr-xr-x 1 admin admin 52988 Jul 12 2019 commons-cli-1.3.1.jar -rwxr-xr-x 1 admin admin 245274 Jul 12 2019 commons-lang-2.3.jar -rwxr-xr-x 1 admin admin 2256213 Jul 12 2019 guava-18.0.jar-rwxr-xr-x 1 admin admin 54495 Jul 12 2019 json-20160810.jar -rwxr-xr-x 1 admin admin 1121698 Dec 3 15:04 oceanbase-client-1.0.8.jar-rwxr-xr-x 1 admin admin 174 Jul 12 2019 README.txt -rwxr-xr-x 1 admin admin 76997 Jul 12 2019 toolkit-common-logging-1.10.jar
2. 准备OB配置文件
$cat props.ob db=oracledriver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driverconn=jdbc:oceanbase://127.0.0.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8user=tpcc@obbmsql#obdemopassword=123456 warehouses=10loadWorkers=10//fileLocation=/home/t4/tmp 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 //osCollectorSSHAddr=user@dbhost //osCollectorDevices=net_eth0 blk_sda
注意：
a. 仓库数(warehouses)决定了数据量。正式的压测仓库数一般在10000以上。
b. loadworkers数决定了数据加载的性能。如果OceanBase租户资源很小(尤其是内存资源)，那加载速度也不要太快；否则容易把租户内存打爆。
c. 并发数(terminals)是后期做TPC-C测试的客户端并发数。这个每次测试都可以调整，以方便观察不同压力下的性能。
d. 压测时间(runMin)是每次测试时间，越长测试结果越好且稳定。因为有时候数据访问有个预热过程，效果会体现在内存命中率上。
3. 创建BenchmarkSQL相关表
1）建表脚本
该SQL脚本不需要直接执行。
create table bmsql_config ( cfg_name varchar2(30) primary key, cfg_value varchar2(50) ); create tablegroup tpcc_group partition by hash partitions 12; 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' partition by hash(w_id) partitions 12; 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' partition by hash(d_w_id) partitions 12; 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' use_bloom_filter=true compress partition by hash(c_w_id) partitions 12; 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' use_bloom_filter=true compress partition by hash(h_w_id) partitions 12; 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' use_bloom_filter=true compress partition by hash(no_w_id) partitions 12; 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' use_bloom_filter=true compress partition by hash(o_w_id) partitions 12; 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' use_bloom_filter=true compress partition by hash(ol_w_id) partitions 12; 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) )use_bloom_filter=true compress locality='F,R{all_server}@zone1, F,R{all_server}@zone2, F,R{all_server}@zone3' primary_zone='zone1' 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 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' use_bloom_filter=true compress partition by hash(s_w_id) partitions 12;
注意：
a. 建表语句中的分区数目可以根据实际情况调整，跟集群节点数有关。如果集群是3台(1-1-1)，建议是6个或6的倍数；如果集群是6台(2-2-2)，建议是12个或12的倍数；如果集群是9台(3-3-3)，建议是36个或36的倍数。这样是方便后期弹性伸缩测试的时候能尽可能保证每个节点上的分区数均衡。
b. 上面bmsql_item使用了【复制表】功能，在租户的所有节点上都会有一个副本。当然主副本始终只有一个。有关【复制表】功能介绍请参考《OceanBase事务引擎特性和应用实践分享》。
c. 建表语句不包含非主键索引，是为了后面加载数据性能更快。
2）建表
./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sql
建表后，可以查看主副本分布
SELECT t1.tenant_id,t1.tenant_name,t2.database_name,t3.table_id,t3.table_Name,t3.tablegroup_id,t3.part_num,t4.partition_Id,t4.zone,t4.svr_ip,t4.role, round(t4.data_size/1024/1024) data_size_mb from `gv$tenant` t1 join `gv$database` t2 on (t1.tenant_id = t2.tenant_id) join gv$table t3 on (t2.tenant_id = t3.tenant_id and t2.database_id = t3.database_id and t3.index_type = 0) left join `__all_virtual_meta_table` t4 on (t2.tenant_id = t4.tenant_id and ( t3.table_id = t4.table_id or t3.tablegroup_id = t4.table_id ) and t4.role in (1)) where t1.tenant_id = 1001 order by t3.tablegroup_id, t4.partition_Id, t3.table_name ;
4. 加载数据
1）开始加载数据
./runLoader.sh props.ob
2）观察数据加载性能
为了对数据写入速度进行观察，可以在sys租户下反复执行下面SQL，主要是观察增量内存增速和增量内存总量，以及是否接近总增量内存限制。
SELECT tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb , freeze_cnt , round((active/freeze_trigger),2) freeze_pct, round(total/mem_limit, 2) mem_usage FROM `gv$memstore` WHERE tenant_id IN (1001) ORDER BY tenant_id, ip;
当然，观察数据加载另外一个方法就是使用监控。OCP的监控或者dooba脚本监控。
python dooba.py -h 127.1 -uroot@sys#obdemo -P2883 -p123456
dooba 进去后，默认是sys租户。按字母小写'c'，选择业务租户。按数字'1'查看帮助，数字'2'查看租户总览，数字'3'查看租户的机器性能信息，按TAB切换当前焦点，按字母小写'd' 删除当前TAB，按字母大写R 恢复所有TAB。总览里的NET TAB没有意义可以删除以节省屏幕空间。
5. 建索引
索引很少，就2条。由于相关表是分区表，可以建全局索引或者本地索引。我们建本地索引。
$cat ./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;
开始建索引。OceanBase建索引很快就会返回，索引构建是异步的。
./runSQL.sh props.ob ./sql.oceanbase/indexCreates.sql
6. 数据校验
检查一下各个表的数据量
obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_CONFIG; *+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_STOCK;+----------+ | COUNT(*) | +----------+ | 4 | +----------+ 1 row in set (0.06 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_WAREHOUSE; +----------+ | COUNT(*) | +----------+ | 10 | +----------+ 1 row in set (0.06 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_DISTRICT; +----------+ | COUNT(*) | +----------+ | 100 | +----------+ 1 row in set (0.06 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_CUSTOMER; +----------+ | COUNT(*) | +----------+ | 300000 | +----------+ 1 row in set (0.34 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_HISTORY; +----------+ | COUNT(*) | +----------+ | 300000 | +----------+ 1 row in set (0.10 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_NEW_ORDER; +----------+ | COUNT(*) | +----------+ | 90000 | +----------+ 1 row in set (0.07 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_OORDER; +----------+ | COUNT(*) | +----------+ | 300000 | +----------+ 1 row in set (0.11 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_ORDER_LINE; +----------+ | COUNT(*) | +----------+ | 3001782 | +----------+ 1 row in set (0.27 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_ITEM; +----------+ | COUNT(*) | +----------+ | 100000 | +----------+ 1 row in set (0.08 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_STOCK; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.63 sec)
为了避免产生的数据不符合规范(如中间报错导致有事务失败)，运行下面校验脚本
#!/usr/bin/sh cc1=" SELECT /*+ no_use_px parallel(8) */ * FROM( SELECT w.w_id, w.w_ytd, d.sum_d_ytd FROM bmsql_warehouse w, (SELECT /*+ no_use_px parallel(8) */ d_w_id, sum(d_ytd) sum_d_ytd FROM bmsql_district GROUP BY d_w_id) d WHERE w.w_id= d.d_w_id ) x WHERE w_ytd != sum_d_ytd; " cc2=" SELECT /*+ no_use_px parallel(8) */ * FROM( SELECT d.d_w_id, d.d_id, d.d_next_o_id, o.max_o_id, no.max_no_o_id FROM bmsql_district d, (SELECT /*+ no_use_px parallel(8) */ o_w_id, o_d_id, MAX(o_id) max_o_id FROM bmsql_oorder GROUP BY o_w_id, o_d_id) o, (SELECT /*+ no_use_px parallel(8) */ no_w_id, no_d_id, MAX(no_o_id) max_no_o_id FROM bmsql_new_order GROUP BY no_w_id, no_d_id) no WHERE d.d_w_id= o.o_w_id AND d.d_w_id= no.no_w_id AND d.d_id= o.o_d_id AND d.d_id= no.no_d_id ) x WHERE d_next_o_id - 1!= max_o_id OR d_next_o_id - 1!= max_no_o_id; " cc3=" SELECT /*+ no_use_px paratLel(8) */ * FROM( SELECT /*+ no_use_px parallel(8) */ no_w_id, no_d_id, MAX(no_o_id) max_no_o_id, MIN(no_o_id) min_no_o_id, COUNT(*) count_no FROM bmsql_new_order GROUP BY no_w_id, no_d_Id ) x WHERE max_no_o_id - min_no_o_id+ 1!= count_no; " cc4=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT o.o_w_id, o.o_d_id, o.sum_o_ol_cnt, ol.count_ol FROM (SELECT /*+ no_use_px parallel(8) */ o_w_id, o_d_id, SUM(o_ol_cnt) sum_o_ol_cnt FROM bmsql_oorder GROUP BY o_w_id, o_d_id) o, (SELECT /*+ no_use_px parallel(8) */ ol_w_id, ol_d_id, COUNT(*) count_ol FROM bmsql_order_line GROUP BY ol_w_id, ol_d_id) ol WHERE o.o_w_id = ol.ol_w_id AND o.o_d_id = ol.ol_d_id ) x WHERE sum_o_ol_cnt != count_ol; " cc5=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_carrier_id, no.count_no FROM bmsql_oorder o, (SELECT /*+ no_use_px parallels) */ no_w_id, no_d_id, no_o_id, COUNT(*) count_no FROM bmsql_new_order GROUP BY no_w_id, no_d_id, no_o_id) no WHERE o.o_w_id = no.no_w_id AND o.o_d_id = no.no_d_id AND o.o_id = no.no_o_id ) x WHERE (o_carrier_id IS NULL AND count_no = 0) OR (o_carrier_id IS NOT NULL AND count_no != 0); " cc6=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_ol_cnt, ol.count_ol FROM bmsql_oorder o, (SELECT /*+ no_use_px parallel(8) */ ol_w_id, ol_d_id, ol_o_id, COUNT(*) count_ol FROM bmsql_order_line GROUP BY ol_w_id, ol_d_id, ol_o_id) ol WHERE o.o_w_id = ol.ol_w_id AND o.o_d_id = ol.ol_d_id AND o.o_id = ol.ol_o_id ) x WHERE o_ol_cnt != count_ol; " cc7=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_ol_cnt, ol.count_ol FROM bmsql_oorder o, (SELECT /*+ no_use_px parallel(8) */ ol_w_id, ol_d_id, ol_o_id, COUNT(*) count_ol FROM bmsql_order_line GROUP BY ol_w_id, ol_d_id, ol_o_id) ol WHERE o.o_w_id = ol.ol_w_id AND o.o_d_id = ol.ol_d_id AND o.o_id = ol.ol_o_id ) x WHERE o_ol_cnt != count_ol; " cc7=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT /*+ no_use_px parallel(8) */ ol.ol_w_id, ol.ol_d_id, ol.ol_o_id, ol.ol_delivery_d, o.o_carrier_id FROM bmsql_order_line ol, bmsql_oorder o WHERE ol.ol_w_id = o.o_w_id AND ol.ol_d_id = o.o_d_id AND ol.ol_o_id = o.o_id ) x WHERE (ol_delivery_d IS NULL AND o_carrier_id IS NOT NULL) OR (ol_delivery_d IS NOT NULL AND o_carrier_id IS NULL); " cc8=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT w.w_id, w.w_ytd, h.sum_h_amount FROM bmsql_warehouse w, (SELECT /*+ no_use_px parallel(8) */ h_w_id, SUM(h_amount) sum_h_amount FROM bmsql_history GROUP BY h_w_id) h WHERE w.w_id = h.h_w_id) x WHERE w_ytd != sum_h_amount; " cc9=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT d.d_w_id, d.d_id, d.d_ytd, h.sum_h_amount FROM bmsql_district d, (SELECT /*+ no_use_px parallel(8) */ h_w_id, h_d_id, SUM(h_amount) sum_h_amount FROM bmsql_history GROUP BY h_w_id, h_d_id) h WHERE d.d_w_id = h.h_w_id AND d.d_id = h.h_d_id ) x WHERE d_ytd != sum_h_amount; " cc_list="$cc1|$cc2|$cc3|$cc4|$cc5|$cc6|$cc7|$cc8|$cc9" oldIFS=$IFS IFS="|" counter=0 for sql in $cc_list do let counter++ echo `date '+%F %X'`" cc$counter start" obclient -Dtpcc -h127.1 -P2883 -utpcc@obbmsql#obdemo -p123456 -A -c -e "$sql" #echo $? if [[ $? -ne 0 ]];then IFS=$oldIFS echo `date '+%F %X'`" cc$counter failed" exit 1 fi echo `date '+%F %X'`" cc$counter finished" done IFS=$oldIFS
BenchmarkSQL TPC-C场景分析
1. E-R模型
2. 场景SQL TPC-C 系统需要处理的交易有以下五种：
| 场景名 | 场景描述 | 交易占比 |
New-Order |
客户输入一笔新的订货交易 | 45% |
Payment |
更新客户账户余额以反应其支付状况 | 43% |
Delivery |
发货（批处理交易） | 4% |
Order-Status |
查询客户最近交易的状态 | 4% |
Stock-Level |
查询仓库库存状况，以便能够及时补货。 | 4% |
场景的比例是在数据库配置文件中定义的。这里是默认值。对于前四种类型的交易，要求响应时间在 5 秒以内；对于库存状况的查询交易，要求响应时间在 20 秒以内。
这五种交易作用在图 1 所示的九张表上，事务操作类型包括更新，插入，删除和取消操作。
下面是我事先通过OceanBase的全量SQL审计抓出的TPCC的事务SQL(做过去重,但可能不全)。
1）场景1：New-Order
SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 778 AND d_id = 5 FOR UPDATE;
SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer JOIN bmsql_warehouse ON (w_id = c_w_id) WHERE c_w_id = 778 AND c_d_id = 5 AND c_id = 2699;
UPDATE bmsql_district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = 778 AND d_id = 5 ;
INSERT INTO bmsql_oorder ( o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (5686, 5, 778, 2699, timestamp '2020-01-04 13:49:34.137', 8, 1);
INSERT INTO bmsql_new_order ( no_o_id, no_d_id, no_w_id) VALUES (5686, 5, 778);
SELECT i_price, i_name, i_data FROM bmsql_item WHERE i_id = 7752 ; -- 循环8次
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 = 778 AND s_i_id = 7752 FOR UPDATE; -- 循环8次
SHOW VARIABLES WHERE Variable_name = 'tx_read_only';
UPDATE bmsql_stock SET s_quantity = 47, s_ytd = s_ytd + 8, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 778 AND s_i_id = 7752; -- 循环8次
SHOW VARIABLES WHERE Variable_name = 'tx_read_only';
INSERT INTO bmsql_order_line ( ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (5686, 5, 778, 1, 7752, 778, 8, 589.36, 'lYvcNHkOvt3iNoBb5W29umGO'); -- 循环8次
COMMIT;
2）场景2：New-Order
SELECT c_id FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 2 AND c_last = 'PRICALLYPRES' ORDER BY c_first;
SELECT c_first, c_middle, c_last, c_balance FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 2 AND c_id = 2694;
SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder WHERE o_w_id = 778 AND o_d_id = 2 AND o_c_id = 2694 AND o_id = ( SELECT max(o_id) FROM bmsql_oorder WHERE o_w_id = 778 AND o_d_id = 2 AND o_c_id = 2694 );
SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 778 AND ol_d_id = 2 AND ol_o_id = 4494 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number;
ROLLBACK;
3）场景3：Payment
UPDATE bmsql_district SET d_ytd = d_ytd + 4806.11 WHERE d_w_id = 778 AND d_id = 10; SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip FROM bmsql_district WHERE d_w_id = 778 AND d_id = 10; UPDATE bmsql_warehouse SET w_ytd = w_ytd + 4806.11 WHERE w_id = 778; SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip FROM bmsql_warehouse WHERE w_id = 778 ; SELECT c_id FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 10 AND c_last = 'ESEBAROUGHT' ORDER BY c_first; SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 10 AND c_id = 502 FOR UPDATE; UPDATE bmsql_customer SET c_balance = c_balance - 4806.11, c_ytd_payment = c_ytd_payment + 4806.11, c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = 778 AND c_d_id = 10 AND c_id = 502; 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_data) VALUES (502, 10, 778, 10, 778, timestamp '2020-01-04 13:49:34.148', 4806.11, 'HfYovpM6 b6aJtf2Xk6'); COMMIT;
4）场景4：
SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 778 AND s_quantity < 10 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 = 778 AND d_id = 1 ) ); ROLLBACK;
5）场景5：
SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 778 AND no_d_id = 1 ORDER BY no_o_id ASC; DELETE FROM bmsql_new_order WHERE no_w_id = 778 AND no_d_id = 1 AND no_o_id = 4488; UPDATE bmsql_oorder SET o_carrier_id = 2 WHERE o_w_id = 778 AND o_d_id = 1 AND o_id = 4488; SELECT o_c_id FROM bmsql_oorder WHERE o_w_id = 778 AND o_d_id = 1 AND o_id = 4488; UPDATE bmsql_order_line SET ol_delivery_d = timestamp '2020-01-04 13:49:34.181' WHERE ol_w_id = 778 AND ol_d_id = 1 AND ol_o_id = 4488; SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = 778 AND ol_d_id = 1 AND ol_o_id = 4488; UPDATE bmsql_customer SET c_balance = c_balance + 3733.14, c_delivery_cnt = c_delivery_cnt + 1 WHERE c_w_id = 778 AND c_d_id = 1 AND c_id = 1260; <---循环8次---> commit
注意：可能还有事务SQL没有找到。
3. TPC-C输出指标
TPC-C 的测试结果主要有两个指标：
- 流量指标（tpmC）：描述了系统在执行 Payment，Order-Status，Delivery，Stock-level 这四种交易的同时，每分钟可以处理的 New-Order交易的数量。流量指标值越大越好。
tpm 是 transactions per minute 的简称；C 指 TPC 中的 C 基准程序。它的定义是每分钟内系统处理的新订单个数。要注意的是，在处理新订单的同时，系统还要按图 1 的要求处理其 它 4 类事务 请求。从图 1 可以看出，新订单请求不可能超出全部事务请求的 45％，因此，当一个系统的性能为 1000tpmC 时，它每分钟实际处理的请求数是 2000 多个。
- 性价比（Price/tpmC）：测试系统价格与流量指标的比值。性价比越小越好。
运行BenchmarkSQL TPC-C测试
1. OceanBase内存冻结与合并
前面加载了大量数据，OceanBase的增量都在内存中，需要做一次major freeze以释放增量内存。这个事件分两步。一是冻结操作，这个很快。二是合并操作，这个跟增量数据量有关，通常要几分钟或者几十分钟。
每次重复测试的时候都建议做一次major freeze事件以释放内存，弊端就是随后测试中内存数据访问又需要一个预热过程。
1）观察内存增量使用情况
select tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb, freeze_cnt, round(total/mem_limit,2) total_pct from `gv$memstore` where tenant_id>1001 order by tenant_id;
2）发起内存major freeze事件
ALTER SYSTEM major freeze;
3） 观察合并进度
观察合并事件
SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip FROM __all_rootservice_event_history WHERE 1 = 1 AND module IN ('daily_merge') ORDER BY gmt_create DESC LIMIT 100;
观察合并进度
select ZONE,svr_ip,major_version,ss_store_count ss_sc, merged_ss_store_count merged_ss_sc, modified_ss_store_count modified_ss_sc, date_format(merge_start_time, "%h:%i:%s") merge_st, date_format(merge_finish_time,"%h:%i:%s") merge_ft, merge_process from `__all_virtual_partition_sstable_image_info` s order by major_version, zone, svr_ip ;
2. 跑TPC-C测试
1）运行测试程序
$./runBenchmark.sh props.ob
2）性能监控
注意：这个监控界面重点关注QPS/TPS、以及相应的RT、增量内存的增量和总量占比等。此外还能看出测试过程中还是有不少物理读IO。
注意：
这个监控界面里的重点看各个节点的QPS和TPS分布，以及远程SQL的数量占总QPS的比例(SRC/SLC)。TPC-C业务定义会有约1%的远程仓库交易事务，在OceanBase里这个交易又有一定概率是分布式事务。
3）TPC-C报告
运行结束后会生成结果。
从图上看，tpmC结果是48204。这个业务租户总资源是20C25G*3。仓库数只有10仓太少了，如果机器好一点，并做10000仓，这个结果应该会更高。
运行同时还生成了一个文件夹
$ll my_result_2020-01-13_175531/ total 16 drwxrwxr-x 2 admin admin 4096 Jan 13 17:55 data -rw-rw-r-- 1 admin admin 5130 Jan 13 18:10 report.html -rwxr-xr-x 1 admin admin 1050 Jan 13 17:55 run.properties
以上就是通过BenchmarkSQL跑TPC-C测试程序的完整过程，感兴趣的同学也可以按照上述步骤体验。
