环境说明
单机单节点部署
obd cluster display test
+---------------------------------------------+| observer |+-----------+---------+------+-------+--------+| ip | version | port | zone | status |+-----------+---------+------+-------+--------+| 127.0.0.1 | 3.1.1 | 2881 | zone1 | active |+-----------+---------+------+-------+--------+
/etc/sysctl.conf
fs.aio-max-nr = 1048576 ##文件系统最大异步iofs.file-max = 6815744 ##文件系统中文件的最大个数
一、 使用 BenmarkSQL 运行 TPC-C
1.1 准备:
创建tpcc租户（不建议用sys租户）
单机扩容
oceanbase默认sys租户使用了2.5-5个CPU，1G内存.剩余1.5个cpu 3G内存。
根本不够使用的。因此修改配置进行扩容
obd cluster edit-config testobd cluster reload testobd cluster restart test
添加租户
obclient -uroot@sys -h127.0.0.1 -P2881 oceanbase
这里没有obproxy，因此端口不是2883#创建资源单元规格
create resource unit tpcc_unit_2c2g max_cpu=2, min_cpu=2, max_memory='2g', min_memory='2g', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='10g';
#创建资源池(
create resource pool tpcc_pool_test unit = 'tpcc_unit_2c2g', unit_num = 1;
#创建租户：
obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Doceanbasecreate tenant tpcc resource_pool_list=('sys_pool');alter user root identified by '123456';
创建数据库create database tpcc;obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Dtpcc
1.2、操作过程
集群参数调优：无
设置事务超时时间
Worker 001: ERROR: Transaction is timeout
ob_query_timeout 用于设置查询超时时间，单位是微秒。
set global ob_query_timeout=36000000000;
ob_trx_timeout 用于设置事务超时时间，单位为微秒。
set global ob_trx_timeout=36000000000;
1.2 安装benchmarksql
##java环境配置
yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel java -versionopenjdk version "1.8.0_322"
下载benchmarksqlgit clone https://github.com/obpilot/benchmarksql-5.0.git/app/local/2022/benchmarksql-5.0
修改配置：cd /app/local/2022/benchmarksql-5.0/runcat tpcc.props.obconn=jdbc:oceanbase://127.1:2881/tpcc?useUnicode=true&characterEncoding=utf-8#这里没有obproxy jdbc 2283改为2281user=root@tpcc#tpcc租户 root用户password=123456
建立用户和数据文件sh runSQL.sh tpcc.props.ob sql.common/tableCreates.sql
./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sqlWorker 001: Loading Warehouse 1 doneWorker 001: Loading Warehouse 2Worker 001: Loading Warehouse 2 done
查看数据obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Dtpcc
MySQL [tpcc]> show tables;+------------------+| Tables_in_tpcc |+------------------+| bmsql_config || bmsql_customer || bmsql_district || bmsql_history || bmsql_item || bmsql_new_order || bmsql_oorder || bmsql_order_line || bmsql_stock || bmsql_warehouse |+------------------+10 rows in set (0.002 sec)MySQL [tpcc]> select count(*) from bmsql_oorder;+----------+| count(*) |+----------+| 60000 |+----------+1 row in set (0.066 sec)
1.3 运行TPCC测试
更新统计信息
obclient -uroot@sys -h127.0.0.1 -P2881 oceanbasealter system major freeze;
pmC 8.81 ， 96秒一共运行了17个事务
cat runBenchmark.sh
source ./funcs.sh $1sh runBenchmark.sh tpcc.props.obTerm-00, Running Average tpmTOTAL: 14.43 Current tpmTOTAL: 96 Memory Usage: 111MB / 1928MB17:22:44,157 [Thread-2] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 8.8117:22:44,157 [Thread-2] INFO jTPCC : Term-00, Measured tpmTOTAL = 14.4317:22:44,157 [Thread-2] INFO jTPCC : Term-00, Session Start = 2022-02-11 17:21:2917:22:44,157 [Thread-2] INFO jTPCC : Term-00, Session End = 2022-02-11 17:22:4417:22:44,158 [Thread-2] INFO jTPCC : Term-00, Transaction Count = 17
二、分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
2.1 查询TOP3 sql 实际执行计划
obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Doceanbase
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;*********************** 1、row *************************** sql_id: F59A700FA168324279B0DBC25E19760F count(*): 1avg_elapsed_time: 640709 avg_exec_time: 635456 svr_ip: 127.0.0.1 svr_port: 2882 tenant_id: 1002 plan_id: 655*************************** 2、row *************************** sql_id: 482BA7822AE7BE644CEBEB55213E7284 count(*): 1avg_elapsed_time: 633977 avg_exec_time: 632120 svr_ip: 127.0.0.1 svr_port: 2882 tenant_id: 1002 plan_id: 666*************************** 3、row *************************** sql_id: EC66B09D06D688727D0F999BFCFF5348 count(*): 1avg_elapsed_time: 456054 avg_exec_time: 454233 svr_ip: 127.0.0.1 svr_port: 2882 tenant_id: 1002 plan_id: 6673 rows in set (0.028 sec)
EC66B09D06D688727D0F999BFCFF5348 为例子分析
查看sql```select distinct query_sql from gv$sql_audit where sql_id='EC66B09D06D688727D0F999BFCFF5348' G;
OPERATOR Operator 的名称SELECt sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERe ol_w_id = 2 AND ol_d_id = 1 AND ol_o_id = 2101
实际执行计划SELECt ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1002 AND ip = '127.0.0.1' AND port=2882 AND plan_id=667 G;*********************** 1、row *************************** ip: 127.0.0.1 plan_depth: 0plan_line_id: 0 operator: PHY_SCALAR_AGGREGATE 聚合操作 name: NULL rows: 1 cost: 499581 property: NULL*************************** 2、row *************************** ip: 127.0.0.1 plan_depth: 1plan_line_id: 1 operator: PHY_TABLE_SCAN 扫描 name: bmsql_order_line rows: 11 cost: 499579
2.2 执行计划（没有执行）EC66B09D06D688727D0F999BFCFF5348 sql 为例子explain SELECt sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERe ol_w_id = 2 AND ol_d_id = 1 AND ol_o_id = 2101 G;*************************** 1、row ***************************Query Plan: ======================================================|ID|OPERATOR |NAME |EST、ROWS|COST |------------------------------------------------------|0 |SCALAR GROUP BY| |1 |499691||1 | TABLE SCAN |bmsql_order_line|11 |499689|======================================================Outputs & filters:------------------------------------- 0 - output([T_FUN_SUM(bmsql_order_line.ol_amount)]), filter(nil), group(nil), agg_func([T_FUN_SUM(bmsql_order_line.ol_amount)]) 1 - output([bmsql_order_line.ol_amount]), filter([bmsql_order_line.ol_o_id = 2101], [bmsql_order_line.ol_d_id = 1], [bmsql_order_line.ol_w_id = 2]), access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_amount]), partitions(p0)