查看 OceanBase 执行计划

一、目标

（必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
（必选）分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
参考：
https://open.oceanbase.com/docs/tutorials/quickstart/V1.0.0/6-4-how-do-i-run-the-tpc-c-test#title-or8-l3z-7wc
https://blog.csdn.net/lichx123/article/details/122717891等

二、环境准备

三、实施

1、 创建测试数据库

登录ob数据库

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

创建测试数据库和用户

[admin@oceanbase01 ~]$ obclient -h127.1 -uroot@obmysql#obcluster -P2883 -c -A test
MySQL [test]> create database tpcc;
MySQL [tpcc]> create user tpcc identified by ‘tpcc’;
Query OK, 0 rows affected (0.030 sec)
MySQL [tpcc]> grant all privileges on tpcc.* to tpcc ;
Query OK, 0 rows affected (0.018 sec)
MySQL [tpcc]> alter user root identified by ‘123456’;

2、安装BenchmarkSQL运行TPCC

BenchmarkSQL下载
BenchmarkSQL 是开源的项目，官方下载地址为：https://sourceforge.net/projects/benchmarksql/

为了节省测试时间，OceanBase 团队对这个程序进行了修改，支持导入数据报错时，只针对报错的仓库进行补充加载，而不是所有数据重新导入。该版本可以向 OceanBase 技术支持人员获取。或者直接访问链接（https://github.com/obpilot/benchmarksql-5.0）下载。

安装jdk1.8

[admin@oceanbase01]$ yum -y install java1.8*
[admin@oceanbase01]$ java -version
java version “1.8.0_291”
Java™ SE Runtime Environment (build 1.8.0_291-b10)
Java HotSpot™ 64-Bit Server VM (build 25.291-b10, mixed mode)

解压benchmarksql

[admin@oceanbase01]$ unzip ~/benchmarksql-5.0.zip

配置参数

[admin@oceanbase01 run]$ cd ~/benchmarksql-5.0-master/run/
[admin@oceanbase01 run]$ vim props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@obmysql#obcluster
//user=tpcc@oracle0_85#obv22_stable
password=tpcc

warehouses=2
loadWorkers=5

terminals=5
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=1
//Number of total transactions per minute
limitTxnsPerMin=0

//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true

//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1

下载库文件
OceanBase 数据库提供自己的驱动文件 oceanbase-client-x.x.x.jar 文件。无论是测试 MySQL 租户还是 Oracle 租户，都可以使用这个驱动文件。 驱动文件的读取是脚本 run/func.sh 里的逻辑。
因此，只要把 OceanBase 数据库的驱动放到 lib/ 目录或 lib/oracle 下即可。

[admin@oceanbase01 lib]$ cp /tmp/oceanbase-client-1.1.10.jar /lib/

数据准备

建表

[admin@oceanbase01 run]$ ./runSQL.sh props.ob sql.common/tableCreates.sql

加载数据

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

执行过程出现了Transaction超时错误：

需要调整事务超时参数：
MySQL [oceanbase]> show variables like ‘%timeout%’;
±--------------------±-----------------+
| Variable_name | Value |
±--------------------±-----------------+
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout | 10000000 |
| ob_trx_idle_timeout | 120000000 |
| ob_trx_lock_timeout | -1 |
| ob_trx_timeout | 100000000 |
| wait_timeout | 28800 |
±--------------------±-----------------+
11 rows in set (0.021 sec)

MySQL [oceanbase]> set global ob_trx_timeout=10000000000;
Query OK, 0 rows affected (0.056 sec)

MySQL [oceanbase]> set global ob_query_timeout=1000000

清空后重新执行加载表数据成功

检查数据

[admin@oceanbase01 run]$ obclient -h172.17.0.13 -uroot@obmysql#obcluster -P2883 -c -A tpcc -p123456
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

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.001 sec)

MySQL [tpcc]> select count(*) from bmsql_config;
±---------+
| count() |
±---------+
| 4 |
±---------+
1 row in set (0.002 sec)

MySQL [tpcc]> select count(*) from bmsql_customer;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.206 sec)

MySQL [tpcc]> select count(*) from bmsql_customer ;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.163 sec)

MySQL [tpcc]> select count(*) from bmsql_history ;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.057 sec)

MySQL [tpcc]> select count(*) from bmsql_item ;
±---------+
| count() |
±---------+
| 100000 |
±---------+
1 row in set (0.084 sec)

MySQL [tpcc]> select count(*) from bmsql_oorder ;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.047 sec)

MySQL [tpcc]> select count(*) from bmsql_order_line;
±---------+
| count() |
±---------+
| 601529 |
±---------+
1 row in set (0.461 sec)

MySQL [tpcc]> select count(*) from bmsql_stock ;
±---------+
| count() |
±---------+
| 200000 |
±---------+
1 row in set (0.680 sec)

创建索引

当数据初始化完后再补充两个索引。

[root@obce-0000 run]# cat 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;

(可选)删除表

删除表就是删除所有的表和表分组，在需要修改表结构分区数或重新导入的时候执行。运行如下命令：

bash runSQL.sh props.ob sql.common/tableDrops.sql

3、查看执行计划

性能测试

测试前调整租户参数，否则会出现超时错误:

[admin@oceanbase01 ]$ obclient -h127.0.0.1 -P 2883 -u root@my_tenant -p -A -c
set global autocommit=ON;
set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
set global max_allowed_packet=67108864;
set global ob_sql_work_area_percentage=100;
set global parallel_max_servers=800;
set global parallel_servers_target=800;

运行性能测试：

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

查看执行计划

查看top10 sql

SELECT /*+ PARALLEL(15) */ avg_exe_usec,tenant_id, svr_ip, svr_port, sql_id, plan_id FROM oceanbase.gv$plan_cache_plan_stat WHERE tenant_id=1001 ORDER BY avg_exe_usec DESC LIMIT 10 ;

根据topsql的sql_id获取sql语句

select query_sql from gv$plan_cache_plan_stat where sql_id=‘F59A700FA168324279B0DBC25E19760F’;

使用gv$plan_cache_plan_explain查看sql实际执行计划：

select plan_line_id,operator,name,rows,cost from oceanbase.gv$plan_cache_plan_explain where ip =‘172.17.0.13’ and port=‘2882’ and tenant_id=‘1001’ and plan_id=‘77’;

使用explain查看解析执行计划：

explain 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 = 2 AND s_i_id = 25350 FOR UPDATE;

再查看一条top3 sql的执行计划：

MySQL [tpcc]>select plan_line_id,operator,name,rows,cost from oceanbase.gv$plan_cache_plan_explain where ip =‘172.17.0.13’ and port=‘2882’ and tenant_id=‘1001’ and plan_id=‘79’;

使用explain查询：

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 < 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 = 2 AND d_id = 3 ) );

注：查看更详细的执行计划可以使用explain extended。

结果一致。

参数调整参考

sys:
alter system set writing_throttling_trigger_percentage=100 tenant=xxx;
alter system set writing_throttling_maximum_duration=‘1h’;
alter system set memstore_limit_percentage = 80;
alter system set freeze_trigger_percentage = 30;
alter system set large_query_threshold = ‘200s’;
alter system set trx_try_wait_lock_timeout = ‘0ms’;
alter system set cpu_quota_concurrency = 4;
alter system set minor_warm_up_duration_time = 0;
alter system set minor_freeze_times=500;
alter system set minor_compact_trigger=3;
alter system set sys_bkgd_io_high_percentage = 90;
alter system set sys_bkgd_io_low_percentage = 70;
alter system set minor_merge_concurrency =20;
alter system set builtin_db_data_verify_cycle = 0;
alter system set trace_log_slow_query_watermark = ‘10s’;
alter system set gts_refresh_interval=‘500us’;
alter system set server_permanent_offline_time=‘36000s’;
alter system set weak_read_version_refresh_interval=0;
alter system set _ob_get_gts_ahead_interval = ‘5ms’;
alter system set bf_cache_priority = 10;
alter system set user_block_cache_priority=5;
alter system set merge_stat_sampling_ratio = 0;
alter system set enable_sql_audit=false;
alter system set _cache_wash_interval = ‘1m’;

tpcc租户：
set global ob_sql_work_area_percentage=80;
set global optimizer_use_sql_plan_baselines = true;
set global optimizer_capture_sql_plan_baselines = true;
alter system set ob_enable_batched_multi_statement=‘true’;

set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
set global max_allowed_packet=67108864;
set global secure_file_priv="";

set global parallel_max_servers=260;
set global parallel_servers_target=624;