CTE recursive慢

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.1.2.7
【问题描述】mysql 8迁移ob4.2.1.7, 有with recursive表达式的SQL都慢，mysql8上执行很快
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

1 个赞
#3

根因分析场景: SQL性能问题
根因分析命令: obdiag rca run --scene=sql_performance_issue
日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

推荐使用 obdiag 工具进行根因分析和日志采集，以便确认问题所在并提供优化建议。上述命令将帮助我们收集 SQL 查询性能相关的诊断信息。

请您按照以下步骤操作，并将采集到的日志压缩包提供给我们：

  1. 执行 obdiag gather scene run --scene=observer.perf_sql 命令时，请确保替换其中的连接参数（如主机、端口、用户名、密码和数据库名）为实际使用的值。
  2. 在执行性能较差的 SQL 时，请记录对应的 trace_id，并将其填入上述命令中以精准定位问题。

另外，为了更好地帮助您解决问题，请您提供以下信息：

  1. 是否可以提供具体的 SQL 查询语句？如果涉及敏感信息，是否可以对其进行脱敏处理后提供？
  2. 在 OceanBase 和 MySQL 中执行该查询时，各自的执行计划是否有显著差异？如果有，能否提供两者的执行计划？
  3. 当前 OceanBase 集群的硬件配置和负载情况如何？是否存在其他高负载任务影响查询性能？
  4. 是否尝试过调整 OceanBase 的相关参数（如内存分配或优化器设置）来改善性能？如果有，请说明具体调整了哪些参数。
  5. 数据量在 OceanBase 和 MySQL 中是否一致？数据分布和索引设计是否有差异？

附上敏捷诊断工具 obdiag 使用帮助链接

1 个赞
#4

具体的语句发一下 ob上的统计信息 是否收集
obdiag收集一下
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002015243

1 个赞
#5

执行计划发下，看下执行计划是否有问题

1 个赞
#6

4.3.2执行很快

image
image1003×622 42.1 KB

4.2.1.7执行很慢
image
image876×552 33.7 KB

#7

根据您提供的信息，CTE（Common Table Expressions）递归查询在 OceanBase 4.2.1.7 版本中执行较慢，而在 4.3.2 版本中执行较快。以下是可能的原因分析和解决建议：

可能原因分析

  1. 优化器行为差异
    不同版本的 OceanBase 数据库可能使用了不同的优化器策略或规则。从您的执行计划来看，4.2.1.7 和 4.3.2 的执行计划存在显著差异，尤其是在 TEMP TABLE TRANSFORMATION 和 HASH JOIN 等操作上的时间分配。

  2. 递归 CTE 的实现方式
    在 4.2.1.7 版本中，递归 CTE 的实现可能依赖于临时表（TEMP TABLE ACCESS），而这种实现方式可能导致性能瓶颈。而在 4.3.2 中，可能对递归 CTE 的处理进行了优化，减少了中间结果集的存储和访问开销。

  3. 统计信息不准确
    如果表的统计信息未及时更新，可能导致优化器选择次优的执行计划。这在递归查询中尤为明显，因为优化器需要估算递归深度和每层的结果集大小。

  4. 硬件或配置差异
    如果两个环境的硬件配置或数据库参数设置不同，也可能导致性能差异。例如，内存分配、并发线程数等参数可能影响递归查询的执行效率。

解决建议

1. 检查并更新统计信息

确保相关表的统计信息是最新的。可以使用以下命令更新统计信息：

ANALYZE TABLE table_name COMPUTE STATISTICS;

如果递归 CTE 涉及分区表，还需检查分区级别的统计信息是否准确。

2. 对比执行计划

请提供两个版本的完整执行计划（可以通过 EXPLAIN EXTENDED 获取），以便进一步分析差异。重点关注以下几点：

  • 是否存在不必要的 TEMP TABLE TRANSFORMATION。
  • 递归部分的执行时间和成本估算是否有显著差异。
  • 是否有更高效的联接顺序或索引使用。

3. 调整优化器参数

尝试调整以下优化器相关参数，观察是否能改善性能：

  • ob_optimize_level：控制优化器的优化级别，默认值为 2。可以尝试设置为 3，启用更多优化规则。
    SET GLOBAL ob_optimize_level = 3;
  • enable_recursive_cte_in_memory：控制递归 CTE 是否在内存中执行。默认值为 ON，可以尝试显式开启。
    SET GLOBAL enable_recursive_cte_in_memory = ON;

4. 升级到更高版本

如果问题的根本原因是 4.2.1.7 版本中的优化器缺陷，建议升级到 4.3.2 或更高版本。新版本通常会对递归 CTE 的执行性能进行优化。

5. 使用提示（Hints）

如果无法立即升级，可以尝试通过 SQL 提示（Hints）引导优化器选择更优的执行计划。例如：

WITH RECURSIVE cte_name AS (
    -- 递归定义
)
SELECT /*+ NO_TEMP_TABLE_TRANSFORMATION */ * FROM cte_name;

上述提示会禁用临时表转换，可能减少中间结果集的存储开销。

进一步诊断建议

如果您希望更深入地分析问题，可以使用以下工具和方法：

  1. obdiag 工具
    使用 obdiag rca run --scene=sql_performance_issue 收集 SQL 性能相关的诊断信息。

    obdiag rca run --scene=sql_performance_issue --env "{db_connect='-h127.0.0.1 -P2881 -utest@test -p****** -Dtest', trace_id='Yxx'}"

  2. 慢查询日志
    检查慢查询日志，确认递归查询的具体耗时和资源使用情况。

  3. 性能监控
    使用 OCP 或其他监控工具，查看递归查询运行期间的系统资源使用情况（如 CPU、内存、I/O 等）。

总结

根据目前的信息，递归 CTE 查询在 4.2.1.7 版本中性能较差可能是由于优化器行为或实现方式的差异导致的。建议按照上述步骤逐步排查，并考虑升级到更高版本以获得更好的性能表现。如果仍有疑问，请提供更多上下文信息（如具体 SQL 语句、完整执行计划等），我们将进一步协助您解决问题。

具体信息可参考以下文档:

  1. oceanbase.DBA_OB_BACKUP_TASK_HISTORY
  2. SQL 执行计划简介

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

#8

版本不一样 执行的也会有差别 后面的版本是有优化的 如果使用的话 尽量使用OLTPob425和OLAPob435 这两个是LTS版本

#9

:rofl:使用版本个人做不了主