OceanBase 并行执行学习笔记 6 —— 并行执行诊断及调优技巧-每日运维

诊断并行执行问题，可以从两个大的方面入手。首先从系统整体上判断，比如确认网络、磁盘 IO、CPU 是不是被打满；然后从具体 SQL 着手，找到问题 SQL 在哪里，它的内部状态如何。

并行执行系列的内容分为以下七篇博客，本篇是其中的第六篇。


第一篇	并行执行概念
第二篇	设定并行度
第三篇	并发控制与排队
第四篇	并行执行分类
第五篇	并行执行控制参数
第六篇	并行执行诊断及调优技巧
第七篇	并行执行 PoC QuickStart

6.1 系统诊断

在业务比较繁忙的系统重出现性能问题时，首先需要在系统层面做初步诊断。一般有两种途径：

OCP（OceanBase Cloud Platform），支持可视化观测系统性能
tsar 等命令行系统工具，支持查询网络、磁盘、CPU等的历史监控数据

tsar 是一个系统监控和性能分析工具，它可以提供关于 CPU、磁盘、网络等方面的详细信息。以下是 tsar 命令的几个常见用法：

tsar --cpu

tsar --io

tsar --traffic

除了以上示例外，tsar 还支持其他选项和参数，例如通过参数-d 2 可以查出两天前到现在的数据，-i 1 表示以每次1分钟作为采集显示。

tsar -n 2 -i 1 --cpu

如果出现磁盘或网络打爆，则优先从硬件容量过小或并发压力过大角度解决问题。

6.2 SQL 诊断

当遇到并行执行问题时，可以从 SQL 层面、并行执行线程层面、算子层面逐层检查。

6.2.1 确认 SQL 还在执行

确认 SQL 在正常执行：关注 TIME 字段，每次查询 GV$OB_PROCESSLIST 视图 TIME 字段都在增长，并且 STATE 为 ACTIVE，说明 Query 还在执行。

确认 SQL 是否在反复重试：如果 SQL 是因为反复重试导致没有返回结果，RETRY_CNT、RETRY_INFO 字段会有相关信息。其中 RETRY_CNT 是表示重试了多少次了，RETRY_INFO 是最后一次重试的原因。没有重试发生的时候，RETRY_CNT 为 0。TOTAL_TIME 字段表示包含每次重试在内的累计执行时间。如果发现 SQL 在反复重试，则根据 RETRY_INFO 中给出的错误码判断是否需要干预。OBServer v4.1 之前，最常见的一个错误是 -4138（OB_SNAPSHOT_DISCARDED），遇到这种情况，按照本文中《4 并行执行分类》中的 4.2.4 节指示，调大 undo_retention 值即可解决。对于其它错误，如 -4038（OB_NOT_MASTER）等，无需任何处理，一般可以自动重试成功。如果重试次数总是大于1，并且确认系统整体状态平稳，可以联系 OceanBase 研发做进一步判断。

-- MySQL 模式
SELECT
  TENANT,INFO,TRACE_ID,STATE,TIME,TOTAL_TIME,RETRY_CNT,RETRY_INFO
FROM
  oceanbase.GV$OB_PROCESSLIST;

如果发现 GV$OB_PROCESSLIST 里还有对应的 SQL，但状态被标记为 SESSION_KILLED，并且一直没有退出，那么需要联系 OceanBase 研发，报告 bug。这可能是因为：

有逻辑没有正确检测 SESSION KILLED 状态，未能及时退出执行流程

6.2.2 确认 SQL 还在执行并行查询

OBServer 集群中，所有活跃的并行执行线程都可以通过 GV$OB_PX_WORKER_STAT视图查看到。

-- MySQL 模式
OceanBase(admin@oceanbase)>select * from oceanbase.GV$OB_PX_WORKER_STAT;
SESSION_ID: 3221520411
 TENANT_ID: 1002
    SVR_IP: 192.168.0.1
  SVR_PORT: 19510
  TRACE_ID: Y4C360B9E1F4D-0005F9A76E9E66B2-0-0
     QC_ID: 1
    SQC_ID: 0
 WORKER_ID: 0
    DFO_ID: 0
START_TIME: 2023-04-23 17:29:17.372461

-- Oracle 模式
OceanBase(root@SYS)>select * from SYS.GV$OB_PX_WORKER_STAT;
SESSION_ID: 3221520410
 TENANT_ID: 1004
    SVR_IP: 192.168.0.1
  SVR_PORT: 19510
  TRACE_ID: Y4C360B9E1F4D-0005F9A76E9E66B1-0-0
     QC_ID: 1
    SQC_ID: 0
 WORKER_ID: 0
    DFO_ID: 0
START_TIME: 2023-04-23 17:29:15.372461

结合从 GV$OB_PROCESSLIST 拿到的 TRACE_ID，通过这个视图可以看到 SQL 当前正在执行哪些 DFO，执行了多久等信息。

如果这个视图里什么也查不到，但 GV$OB_PROCESSLIST 里依然可以看到相应 SQL，可能的原因包括：

所有 DFO 均已执行完成，结果集较大，当前正处在向客户端吐数据阶段
除了最顶层 DFO 外，其余所有 DFO 均已执行完成

6.2.3 确认每个算子的执行状况

通过 oceanbase.GV$SQL_PLAN_MONITOR (MySQL） 或 SYS.GV$SQL_PLAN_MONITOR（Oracle）可以查看每个并行工作线程中每个算子的执行状态。从 OBServer v4.1 起，GV$SQL_PLAN_MONITOR包含两部分数据：

已经执行完成的算子。所谓执行完成是指这个算子已经调用过 close 接口，在当前线程中不再处理任何数据。
正在执行的算子。所谓正在执行是指这个算子还没有调用 close 接口，正在处理数据过程中。读取这部分算子的数据，需要在查询 GV$SQL_PLAN_MONITOR 视图的 where 条件中指定 request_id < 0。在使用 request_id < 0 条件查询本视图时，我们也称为访问 “Realtime SQL PLAN MONITOR”。本访问接口未来可能会变化。

OBServer 4.1 之前，仅支持查看已经执行完成的算子状态。

GV$SQL_PLAN_MONITOR中有几个重要的域：

TRACE_ID：它唯一标识了一条 SQL
PLAN_LINE_ID：算子在执行计划中的编号，对应于通过 explain 语句查看到的编号
PLAN_OPERATION：算子名称，如 TABLE SCAN、HASH JOIN
OUTPUT_ROWS：当前算子已经输出的行数
FIRST_CHANGE_TIME：算子吐出首行数据时间
LAST_CHANGE_TIME：算子吐出最后一行数据时间
FIRST_REFRESH_TIME：算子开始监控时间
LAST_REFRESH_TIME：算子结束监控时间

根据上面几个域，基本就能刻画出一个算子处理数据的主要动作了。举例几个场景：

查看一个已经执行完成的 SQL，每个算子使用了多少个线程来执行：

SELECT PLAN_LINE_ID, PLAN_OPERATION, COUNT(*) THREADS
FROM GV$SQL_PLAN_MONITOR
WHERE TRACE_ID = 'YA1E824573385-00053C8A6AB28111-0-0'
GROUP BY PLAN_LINE_ID, PLAN_OPERATION
ORDER BY PLAN_LINE_ID;

+--------------+------------------------+---------+
| PLAN_LINE_ID | PLAN_OPERATION         | THREADS |
+--------------+------------------------+---------+
|            0 | PHY_PX_FIFO_COORD      |       1 |
|            1 | PHY_PX_REDUCE_TRANSMIT |       2 |
|            2 | PHY_GRANULE_ITERATOR   |       2 |
|            3 | PHY_TABLE_SCAN         |       2 |
+--------------+------------------------+---------+
4 rows in set (0.104 sec)

查看正在执行的 SQL，当前正在执行哪些算子，使用了多少线程，已经吐出了多少行：

SELECT PLAN_LINE_ID, CONCAT(LPAD('', PLAN_DEPTH, ' '), PLAN_OPERATION) OPERATOR, COUNT(*) THREADS, SUM(OUTPUT_ROWS) ROWS
FROM GV$SQL_PLAN_MONITOR
WHERE TRACE_ID = 'YA1E824573385-00053C8A6AB28111-0-0' AND REQUEST_ID < 0
GROUP BY PLAN_LINE_ID, PLAN_OPERATION, PLAN_DEPTH
ORDER BY PLAN_LINE_ID;

查看一个已经执行完成的 SQL，每个算子处理了多少行数据，吐出了多少行数据：

SELECT PLAN_LINE_ID, CONCAT(LPAD('', PLAN_DEPTH, ' '), PLAN_OPERATION) OPERATOR, SUM(OUTPUT_ROWS) ROWS
FROM GV$SQL_PLAN_MONITOR
WHERE TRACE_ID = 'Y4C360B9E1F4D-0005F9A76E9E6193-0-0'
GROUP BY PLAN_LINE_ID, PLAN_OPERATION, PLAN_DEPTH
ORDER BY PLAN_LINE_ID;
+--------------+-----------------------------------+------+
| PLAN_LINE_ID | OPERATOR                          | ROWS |
+--------------+-----------------------------------+------+
|            0 | PHY_PX_MERGE_SORT_COORD           |    2 |
|            1 |  PHY_PX_REDUCE_TRANSMIT           |    2 |
|            2 |   PHY_SORT                        |    2 |
|            3 |    PHY_HASH_GROUP_BY              |    2 |
|            4 |     PHY_PX_FIFO_RECEIVE           |    2 |
|            5 |      PHY_PX_DIST_TRANSMIT         |    2 |
|            6 |       PHY_HASH_GROUP_BY           |    2 |
|            7 |        PHY_HASH_JOIN              | 2002 |
|            8 |         PHY_HASH_JOIN             | 2002 |
|            9 |          PHY_JOIN_FILTER          | 8192 |
|           10 |           PHY_PX_FIFO_RECEIVE     | 8192 |
|           11 |            PHY_PX_REPART_TRANSMIT | 8192 |
|           12 |             PHY_GRANULE_ITERATOR  | 8192 |
|           13 |              PHY_TABLE_SCAN       | 8192 |
|           14 |          PHY_GRANULE_ITERATOR     | 8192 |
|           15 |           PHY_TABLE_SCAN          | 8192 |
|           16 |         PHY_GRANULE_ITERATOR      | 8192 |
|           17 |          PHY_TABLE_SCAN           | 8192 |
+--------------+-----------------------------------+------+
18 rows in set (0.107 sec)

为了展示美观，上面使用了一个域 PLAN_DEPTH来做缩进处理，PLAN_DEPTH 表示这个算子在算子树中的深度。

注：

尚未调度的 DFO 的算子信息，不会出现在 GV$SQL_PLAN_MONITOR 中。
在一个 PL 中如果包含多条 SQL，它们的 TRACE_ID 相同

6.3 并行执行调优技巧

本章介绍一些基础的 OceanBase 并行执行调优技巧。调优是一个永无止境的话题，本章内容也会与时俱进，不断更新。

6.3.1 手动收集统计信息

如果优化器中保存的统计信息陈旧，可能导致生成的计划不优。OBServer v3.2 和 OBServer v4.1 分别提供了手动收集统计信息的接口：OceanBase 优化器统计信息 (4.x 版本)

OBServer v4.1 手动收集主表、索引表的语法如下：

-- 收集用户TEST的表T1的全局级别的统计信息，所有列的桶个数设定为auto策略:
call dbms_stats.gather_table_stats('TEST', 'T1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
-- 收集用户TEST下表T1的索引IDX的索引统计信息，并行度4(IDX不唯一，需指定表名称)
call dbms_stats.gather_index_stats('TEST', 'IDX', degree=>4, tabname=>'T1');

6.3.2 修改分区方式使用 Partition Wise Join

PoC 场景中，如果有大表 JOIN，并且在业务允许的前提下，可以让大表使用相同的分区方式，并且将他们绑定到同一个表组上，这样可以实现性能最佳的 partition wise join。使用 partition wise join 时，并行度也要调整得和分区数相适应，这样可以获得最佳性能。

6.3.3 并行度与分区数适配

一般来说，并行度与分区数符合一定的整比例关系，能得到较好的性能。详细论述参考之前发布的一篇博客《并行执行学习笔记 1 —— 并行执行概念》中的《1.6 通过均衡负载来优化性能》一节。

6.3.4 创建索引

创建合适的索引，能减少数据的扫描量，可以提高并行执行性能。在哪些表、哪些列上建索引，没有一个通用的方案，需要基于具体 SQL 具体分析。创建索引的基础技巧可以参考之前发布的一篇博客《SQL 性能调优学习笔记 1 —— 索引调优》。

6.3.5 创建复制表

OBServer v4.2 及之后版本，通过创建复制表，能减少数据重分布，可以提高并行执行性能，详见 OceanBase 官方文档中创建表的《创建复制表》章节。基本语法举例如下：

create table dup_t1(c1 int) duplicate_scope = 'cluster';

OceanBase 并行执行学习笔记 6 —— 并行执行诊断及调优技巧

6.1 系统诊断

6.2 SQL 诊断

6.2.1 确认 SQL 还在执行

6.2.2 确认 SQL 还在执行并行查询

6.2.3 确认每个算子的执行状况

6.3 并行执行调优技巧

6.3.1 手动收集统计信息

6.3.2 修改分区方式使用 Partition Wise Join

6.3.3 并行度与分区数适配

6.3.4 创建索引

6.3.5 创建复制表

相关文章

发布评论取消回复

剑圣无痕

百度搜索：蓝易云 – centos7系统手动安装ceph教程

出行365：用国产数据库为国人出行“护航”

tidb8.1的磁盘选择，关于网络ssd，和本地ssd的选择对性能影响很大，差距60倍。

OceanBase 并行执行学习笔记 6 —— 并行执行诊断及调优技巧

6.1 系统诊断

6.2 SQL 诊断

6.2.1 确认 SQL 还在执行

6.2.2 确认 SQL 还在执行并行查询

6.2.3 确认每个算子的执行状况

6.3 并行执行调优技巧

6.3.1 手动收集统计信息

6.3.2 修改分区方式使用 Partition Wise Join

6.3.3 并行度与分区数适配

6.3.4 创建索引

6.3.5 创建复制表

相关文章

发布评论 取消回复

剑圣无痕

百度搜索：蓝易云 – centos7系统手动安装ceph教程

出行365：用国产数据库为国人出行“护航”

tidb8.1的磁盘选择，关于网络ssd，和本地ssd的选择对性能影响很大，差距60倍。

发布评论取消回复