ob使用函数执行sql耗时是mysql的10倍和2倍

宠辱不惊m · 2025 年4 月 27 日 17:53

@论坛小助手对比执行计划没看出什么问题，还需要对比内存和并行的参数吗？还有哪些方法可以排查性能问题？

论坛小助手 · 2025 年4 月 27 日 17:54

根因分析场景: SQL性能问题
根因分析命令: 无（当前 obdiag RCA 场景未直接覆盖函数性能差异）
日志采集场景: 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 执行过程中可能存在的性能瓶颈。上述命令将收集与 SQL 性能相关的诊断信息，包括执行计划、资源消耗等。请您运行该命令并将生成的日志压缩包提供给我们。

为了更好地帮助您排查问题，请您提供以下信息：

是否已调整过 OceanBase 的内存参数或并行执行参数？如果调整过，请提供具体的参数设置。
能否提供具体的 SQL 语句以及在 OceanBase 和 MySQL 中的执行时间对比数据？
在 OceanBase 中执行 SHOW PARAMETERS，查看与并行执行和内存管理相关的参数配置（如 parallel_degree, memory_limit_percentage 等）。
是否可以尝试在 OceanBase 中禁用并行执行（通过设置 parallel_degree = 1），然后重新测试 SQL 性能？
是否有其他类似的 SQL 函数也表现出性能差异，还是仅限于 CONVERT_TZ 和 date_format？

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

淇铭 · 2025 年4 月 27 日 18:00

可以根据论坛小助手的sql收集一下

宠辱不惊m · 2025 年4 月 27 日 18:50

1.未调整任何参数
2.执行sql：
select * from oc_order o where CONVERT_TZ(o.date_modified, ‘America/Los_Angeles’, ‘Asia/Tokyo’) = ‘2025-04-01’;
select * from oc_order o where date_format(o.date_modified,’%Y-%m-%d’) = ‘2025-04-01’;
执行时间：
convert_tz函数：mysql是0.03s，ob是0.326s；
date_format函数：mysql是0.06s，ob是0.103s；
3.查看并行执行和内存管理相关参数

4.禁用和开启并行执行没有显著效果

5.count函数和sum函数也更慢，sum函数计算结果怎么和mysql的不一致？
count和sum函数

论坛小助手 · 2025 年4 月 27 日 19:04

根据您提供的信息，以下是针对问题的详细分析和可能的解决方案：

1. 关于 `CONVERT_TZ` 和 `DATE_FORMAT` 函数性能差异

已知信息：

在 MySQL 中，CONVERT_TZ 的执行时间为 0.03 秒，而 OceanBase 中为 0.326 秒。
在 MySQL 中，DATE_FORMAT 的执行时间为 0.06 秒，而 OceanBase 中为 0.103 秒。

分析：

函数实现差异：OceanBase 社区版对某些 MySQL 兼容函数（如 CONVERT_TZ 和 DATE_FORMAT）的实现可能存在性能优化空间。例如，CONVERT_TZ 需要加载时区文件并进行复杂的时区转换计算，这可能导致额外开销。
内存参数配置：从您提供的 SHOW PARAMETERS 结果来看，未发现明显的内存限制问题。但可以进一步检查是否启用了向量化执行（Vectorized Execution），因为向量化执行可能对复杂函数的性能有显著影响。

建议：

启用向量化执行：尝试设置以下参数以启用向量化执行：
```
ALTER SYSTEM SET enable_vectorized_execution = TRUE;
```
然后重新测试 SQL 性能。
减少时区文件加载开销：确保 OceanBase 的时区文件已正确加载。可以通过以下命令验证：
```
SHOW VARIABLES LIKE 'time_zone';
```
如果时区文件未加载，可以参考官方文档加载时区文件。
使用索引优化查询：如果 o.date_modified 列上有索引，可以尝试将函数移出 WHERE 子句，避免全表扫描。例如：
```
SELECT * FROM oc_order o WHERE o.date_modified >= '2025-04-01T00:00:00' AND o.date_modified < '2025-04-02T00:00:00';
```

2. 关于 `COUNT` 和 `SUM` 函数性能及结果不一致

已知信息：

在 MySQL 中，COUNT(*) 执行时间为 0.094 秒，SUM(id) 结果为 103310196100。
在 OceanBase 中，COUNT(*) 执行时间为 0.046 秒，SUM(id) 结果为 83305451780。

分析：

性能差异：OceanBase 的 COUNT(*) 执行时间略低于 MySQL，这可能是由于 OceanBase 的分布式架构优化了统计操作。
结果不一致：SUM(id) 的结果差异可能由以下原因导致：
1. 数据一致性问题：OceanBase 和 MySQL 中的数据可能不完全一致。请确认两者的 oc_order 表数据是否完全相同。
2. 数据类型差异：OceanBase 和 MySQL 对整数类型的处理可能存在差异。例如，MySQL 的 BIGINT 类型在 OceanBase 中可能被映射为其他类型，导致计算结果不同。
3. 分区表的影响：如果 oc_order 是分区表，OceanBase 的分区裁剪逻辑可能与 MySQL 不同，导致扫描的行数不同。

建议：

验证数据一致性：
- 在 OceanBase 和 MySQL 中分别执行以下查询，确认数据是否一致：
```
SELECT COUNT(*), MIN(id), MAX(id) FROM oc_order;
```
- 如果数据不一致，请检查数据导入或同步过程是否存在问题。
检查数据类型：
- 查看 oc_order.id 列的数据类型：
```
DESC oc_order;
```
- 如果类型不同，请调整为一致。
排查分区表影响：
- 如果 oc_order 是分区表，请检查 OceanBase 的分区裁剪逻辑是否正确。可以通过以下命令查看执行计划：
```
EXPLAIN SELECT SUM(id) FROM oc_order;
```

3. 关于并行执行的效果

已知信息：

您提到禁用和开启并行执行对性能没有显著影响。

分析：

并行执行的效果取决于查询的复杂度和数据分布。对于简单的聚合查询（如 COUNT 和 SUM），并行执行可能不会带来明显提升。
如果查询涉及大量数据扫描或复杂计算，并行执行可能会更有效。

建议：

尝试设置更高的并行度，观察性能变化：
```
ALTER SESSION SET parallel_degree = 16;
```
如果仍无显著效果，可以考虑优化查询逻辑或数据分布。

4. 总结

短期建议：
- 启用向量化执行以优化函数性能。
- 验证数据一致性，确保 OceanBase 和 MySQL 中的数据完全一致。
- 调整查询逻辑，减少函数使用，充分利用索引。
长期建议：
- 关注 OceanBase 社区版的后续版本更新，部分函数性能问题可能在未来的版本中得到优化。
- 如果问题持续存在，建议联系 OceanBase 技术支持团队，提供详细的诊断日志以获取进一步帮助。

希望以上建议对您有所帮助！如果有更多问题，请随时提问。

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

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

淇铭 · 2025 年4 月 28 日 09:55

1、按照上面小助手提示 obdiag收集一下sql性能看看有什么问题
obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

2、##### 集群 server 级资源分配情况
select zone,concat(SVR_IP,’:’,SVR_PORT) observer,
cpu_capacity_max cpu_total,cpu_assigned_max cpu_assigned,
cpu_capacity-cpu_assigned_max as cpu_free,
round(memory_limit/1024/1024/1024,2) as memory_total,
round((memory_limit-mem_capacity)/1024/1024/1024,2) as system_memory,
round(mem_assigned/1024/1024/1024,2) as mem_assigned,
round((mem_capacity-mem_assigned)/1024/1024/1024,2) as memory_free,
round(log_disk_capacity/1024/1024/1024,2) as log_disk_capacity,
round(log_disk_assigned/1024/1024/1024,2) as log_disk_assigned,
round((log_disk_capacity-log_disk_assigned)/1024/1024/1024,2) as log_disk_free,
round((data_disk_capacity/1024/1024/1024),2) as data_disk,
round((data_disk_in_use/1024/1024/1024),2) as data_disk_used,
round((data_disk_capacity-data_disk_in_use)/1024/1024/1024,2) as data_disk_free
from oceanbase.gv$ob_servers;
3、集群租户级资源分配和磁盘使用情况
select a.zone,a.svr_ip,b.tenant_name,b.tenant_type, a.max_cpu, a.min_cpu,
round(a.memory_size/1024/1024/1024,2) memory_size_gb,
round(a.log_disk_size/1024/1024/1024,2) log_disk_size,
round(a.log_disk_in_use/1024/1024/1024,2) log_disk_in_use,
round(a.data_disk_in_use/1024/1024/1024,2) data_disk_in_use
from oceanbase.gv$ob_units a join oceanbase.dba_ob_tenants b on a.tenant_id=b.tenant_id order by b.tenant_name;
4、配置项查一下
show parameters where name in (‘memory_limit’,‘memory_limit_percentage’,‘system_memory’,‘log_disk_size’,‘log_disk_percentage’,‘datafile_size’,‘datafile_disk_percentage’);