查看 OceanBase 执行计划

查看 OceanBase 执行计划

文档控制：

目录

1.引言... 2

1.1.       关于此文档... 2

1.2.       参考资料... 2

2.       测试环境说明... 2

2.1.       测试环境配置信息：... 2

2.2.       系统基本检查... 3

2.3.       SQL审计设置... 4

3.       使用BenmarkSQL 运行 TPC-C. 4

3.1.       部署BenmarkSQL. 5

3.1.1.      创建测试数据库和租户... 5

3.1.1.1.      查询系统资源占用情况... 5

3.1.1.2.      查询系统资源分配情况... 5

3.1.1.3.      创建资源单元... 5

3.1.1.4.      创建资源池... 5

3.1.1.5.      创建租户... 6

3.1.1.6.      创建测试数据库... 6

3.1.2.      下载地址... 6

3.1.3.      解压编译... 6

3.1.4.      修改配置文件... 7

3.1.4.1.      创建benchmarksql配置文件... 7

3.1.5.      修改建表语句... 7

3.1.5.1.      初始化表结构... 7

3.1.6.      加载数据... 8

3.1.7.      创建索引... 9

3.1.8.      检查数据... 9

3.2.       压力测试... 10

3.2.1.      运行TPCC进行压测... 10

3.3.       分析TOP SQL. 11

3.3.1.      查看前10条执行计划... 11

3.3.2.      使用sql_id获取前三条SQL语句... 11

3.3.3.      解析前三条执行计划... 12

3.3.4.      查看前三条实际执行计划... 12

1.引言

1.1.   关于此文档

练习目的

本次练习目的掌握 OceanBase 的执行计划查看方法，包括 explain 命令和查看实际执行计划。

练习条件

有 服务器，内存资源至少 12G*1 台，部署有 OceanBase 集群（单副本或三副本都可以）。

练习内容

请记录并分享下列内容：

（必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。

（必选）分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。

（可选）使用 OceanBase 的 Outline 对 其中一条 SQL 进行限流（限制并发为 1 ）。

（可选）导入 TPC-H schema 和数据，数据量不用太大 100M 即可。查看 TPC-H 5条 SQL 的解析执行计划和实际执行计划。

1.2.   参考资料

• 社区版官网-文档-学习中心-入门教程：OceanBase 入门到实战教程
• 社区版官网-博客-入门实战：开源博客

2.     测试环境说明

2.1.  测试环境配置信息：

• 机器信息如下：

• 机器划分如下：

2.2.  系统基本检查

系统版本：

硬盘空间大小：

内存大小：

CPU逻辑核数：

2.3.    SQL审计设置

查看SQL审计是否打开

缺省SQL审计已经打开了，如果没有打开，执行以下SQL语句：

3.     使用BenmarkSQL 运行 TPC-C

3.1.   部署BenmarkSQL

3.1.1.    创建测试数据库和租户

3.1.1.1.     查询系统资源占用情况

MySQL [oceanbase]> select svr_ip,svr_port,cpu_total,mem_total,disk_total,zone from __all_virtual_server_stat;

3.1.1.2.     查询系统资源分配情况

MySQL [oceanbase]> select sum(c.max_cpu),sum(c.max_memory) from __all_resource_pool as a,  __all_unit_config as c where a.unit_config_id=c.unit_config_id;

3.1.1.3.     创建资源单元

如果想把剩下的所有资源全部使用掉，CPU 和内存分别为步骤 2 和步骤 3 得到的值, max_cpu值设置为第二步得到的cpu_total 减去第三步得到的sum(c.max_cpu), 在本例中为9; max_memory和min_memory 设置为第二步的到的mem_total 值 减去 第三步的到的sum(c.max_memory), 在本例中为3,758,096,384。

MySQL [oceanbase]> create resource unit test1 max_cpu = 9, max_memory = 3758096384, min_memory = 3758096384, max_iops = 10000, min_iops = 1280, max_session_num = 3000, max_disk_size = 214748364800;

3.1.1.4.     创建资源池

MySQL [oceanbase]> create resource pool pool1 unit = 'test1',unit_num = 1,zone_list = ('zone1','zone2','zone3');

3.1.1.5.     创建租户

MySQL [oceanbase]> create tenant if not exists test charset = 'utf8mb4', replica_num = 3, zone_list = ('zone1','zone2','zone3'), primary_zone = 'RANDOM', resource_pool_list=('pool1');

修改租户参数：

MySQL [oceanbase]> alter tenant test set variables ob_tcp_invited_nodes='%';

使用test租户登录数据库，初次登录密码为空；

[admin@master ~]$ obclient -h192.168.101.67 -uroot@test -P2881 -c -A -p

3.1.1.6.     创建测试数据库

MySQL [(none)]> create database tpcc;

3.1.2.    下载地址

https://github.com/obpilot/benchmarksql-5.0.git

3.1.3.    解压编译

[admin@master data]$ unzip benchmarksql-5.0-master.zip

[admin@master data]$ cd benchmarksql-5.0-master/

3.1.4.    修改配置文件

3.1.4.1.     创建benchmarksql配置文件

[admin@master run]$ vim props.ob

使用自带的模板，按照测试环境修改用户名和密码即可。

3.1.5.    修改建表语句

tableCreates.sql中建表语句使用了varchar2类型，需要修改为varchar类型。

[admin@master sql.oceanbase]$ sed -i 's/varchar2/varchar/g' tableCreates.sql

3.1.5.1.     初始化表结构

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

出现报错：

解决方式：只有租户下的管理员权限才可以创建TableGroup；给root用户赋权tpcc库的管理员权限即可。

再次创建表结构，查看生成的表：

3.1.6.    加载数据

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

出现报错：

解决方式：

调整事务超时 时间：

MySQL [oceanbase]> set global ob_trx_timeout=36000000000;

MySQL [oceanbase]> set session ob_trx_idle_timeout=120000000;

数据正常加载完成：

3.1.7.    创建索引

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

3.1.8.    检查数据

MySQL [tpcc]> show tables;

3.2.   压力测试

3.2.1.    运行TPCC进行压测

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

出现报错：

解决方式：编辑props.ob，将其中的指定性能测试持续的时间runMins值调整到15即可 .

3.3.   分析TOP SQL

3.3.1.    查看前10条执行计划

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 10;

3.3.2.    使用sql_id获取前三条SQL语句

MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id= '63318F1B497E24831036110EBA75247C'\G

MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id= '436592EC2AE5B631BE1D82197A6558DB'\G

MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id= '055F5A03E1B1FC38DF0F288E5762FBD0'\G

3.3.3.    解析前三条执行计划

MySQL [oceanbase]> EXPLAIN select * from tpcc.bmsql_stock\G

MySQL [oceanbase]> EXPLAIN select * from tpcc.bmsql_item\G

MySQL [oceanbase]> EXPLAIN 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 = '192.168.101.69' AND port=2882 AND plan_id=106\G

3.3.4.    查看前三条实际执行计划

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 = '192.168.101.68' AND port=2882 AND plan_id=2918\G

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 = '192.168.101.68' AND port=2882 AND plan_id=2897\G

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 = '192.168.101.67' AND port=2882 AND plan_id=101\G