单条 SQL 内存使用超限和写临时文件超限报错分析方法

问题现象

问题1：SQL 执行报错 Exceed query memory limit（-11049） 应用执行 SQL 时报错，完整报错信息如下：

Caused by: java.sql.SQLException: Exceed query memory limit (mem_limit=3382286745, mem_hold=3382636544), please check whether the query_memory_limit_percentage configuration item is reasonable.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)

SQL AUDIT 信息：

SELECT * FROM gv$ob_sql_audit WHERE sql_id = '63CB94990FCB456DFE3A1FDAB3EAEE28'\G;

查询结果（关键字段）如下：

RET_CODE: -11049
ELAPSED_TIME: 6861792
EXECUTE_TIME: 6861499
SSSTORE_READ_ROW_COUNT: 12795172
BLOCKSCAN_ROW_CNT: 11905371
REQUEST_MEMORY_USED: 3135510001

关键日志（简化）：

grep 'Exceed query memory limit' observer.log*
[2025-10-20 13:58:55.365467] WDIAG [errcode=-11049] Exceed query memory limit (mem_limit=3382286745, mem_hold=3382636544), please check whether the query_memory_limit_percentage configuration item is reasonable.
[2025-10-20 13:58:55.371524] WDIAG execute query fail (ret=-11049)

执行日志中还可看到 sort 算子触发 dump 落盘的记录（trace sort need dump），说明内存不足时排序中间结果需要落盘。 问题2：SQL 中间结果写入临时文件达到存储层可用总大小限制报错 SQL 计算中间结果需要写临时文件，当写入量达到存储层能使用的总大小限制时报错，日志中大量出现 fail to alloc_page，ret="OB_ALLOCATE_TMP_FILE_PAGE_FAILED"（错误码 -9124）。 关键日志（简化）：

grep 'fail to alloc_page' observer.log*
[2025-10-27 14:16:23.624115] WDIAG [errcode=-9124] fail to write continuous pages (ret="OB_ALLOCATE_TMP_FILE_PAGE_FAILED")
[2025-10-27 14:16:23.630629] WDIAG [errcode=-9124] fail to alloc_page (ret="OB_ALLOCATE_TMP_FILE_PAGE_FAILED")

关键诊断信息

触发条件

问题1：

• 单条 SQL 执行所需内存超过 query_memory_limit_percentage 配置项允许的上限（租户内存 × 百分比）。例如 32C70G 规格的租户，配置项值为 5% 时，单条 SQL 可用内存约 3.5G，而该 SQL 原计划执行需要 6G 以上内存（正常执行需输出约 3300 万行数据）。
• SQL 中包含分组、排序等内存消耗较大的算子，且 sort 算子触发 dump 落盘，进一步推高内存使用。 问题2：
• SQL 计算中间结果（如 hash join、sort 等算子）需要写临时文件（tempfile）。
• 临时文件可使用的内存默认限制为租户内存的 1%（_temporary_file_io_area_size 默认值为 1）。小内存租户（如 2C3G）下，tempfile 可用内存仅约 30MB，写入少量数据即需下刷到磁盘，磁盘占用被放大（可达 100 多倍），更容易触发该问题。

事前巡检

• 检查 query_memory_limit_percentage 配置项当前值：

SHOW PARAMETERS LIKE 'query_memory_limit_percentage';

• 检查 _temporary_file_io_area_size 配置项当前值：

SHOW PARAMETERS LIKE '_temporary_file_io_area_size';

• 结合租户规格评估大 SQL 的内存需求（结果集行数、涉及的算子），确认单条 SQL 可用内存是否充足。

事后诊断

问题1：

1. 应用报错信息：应用侧报错 Exceed query memory limit，错误信息提示检查 query_memory_limit_percentage 配置项。
2. SQL AUDIT：通过 gv$ob_sql_audit 查询该 SQL 的执行记录，关注返回码 RET_CODE: -11049，以及单条 SQL 内存使用 REQUEST_MEMORY_USED 是否超过 query_memory_limit_percentage 比例限制：

SELECT * FROM gv$ob_sql_audit WHERE sql_id = '63CB94990FCB456DFE3A1FDAB3EAEE28'\G;

3. 关键日志：在 observer 日志中检索报错关键字：

grep 'Exceed query memory limit' observer.log*

日志特征为 Exceed query memory limit (mem_limit=..., mem_hold=...)，且伴随 sort 算子 trace sort need dump 的落盘记录。 问题2：

1. 关键日志：在 observer 日志中检索报错关键字：

grep 'fail to alloc_page' observer.log*

日志特征为 fail to alloc_page、ret="OB_ALLOCATE_TMP_FILE_PAGE_FAILED"（错误码 -9124）。 2. 查询配置项 _temporary_file_io_area_size 当前值，确认是否过小。

问题原因

问题1： query_memory_limit_percentage 配置项用于指定单条 SQL 可使用的租户内存百分比，当单条 SQL 的内存使用超过该阈值后，系统报错 -11049 并中断该 SQL 的执行。示例场景中，租户规格为 32C70G，配置项值为 5%（单条 SQL 约 3.5G 内存），而该 SQL 原计划执行需要 6G 以上内存（正常执行需输出约 3300 万行数据，涉及分组、排序等算子），内存不足导致 SQL 执行报错中断。 该配置项的功能说明与引入版本如下：

配置项功能描述
query_memory_limit_percentage 用于指定单条 SQL 可使用的租户内存百分比。当内存使用超过指定的阈值后，系统会报错并中断该 SQL 的执行。
引入版本说明
对于 V4.3.x 版本，该配置项从 V4.3.5 版本开始引入。
对于 V4.2.x 版本，该配置项从 V4.2.5 版本开始引入。

问题2： SQL 执行过程中，计算中间结果需要写入临时文件（tempfile）。临时文件可使用的内存默认受 _temporary_file_io_area_size 配置项限制（默认值为租户内存的 1%，带单位时为大小，不带单位时为租户内存的比例）。当临时文件写入量达到存储层允许使用的总大小限制时，系统报错 -9124（OB_ALLOCATE_TMP_FILE_PAGE_FAILED）并中断该 SQL 的执行。小内存租户（如 2C3G）下 tempfile 可用内存很小（约 30MB），写入少量数据即需将内存中的数据下刷到磁盘，磁盘占用被放大（可达 100 多倍），更容易触发该问题。

问题的风险及影响

• SQL 执行失败，可能导致相关业务中断或延迟。
• 小内存租户下，临时文件频繁下刷磁盘会造成磁盘占用放大，影响磁盘空间使用。

影响租户

影响版本

解决方法

问题1：SQL 执行报错 Exceed query memory limit（-11049）

1. 调大 query_memory_limit_percentage，提高单条 SQL 最大可使用的租户内存百分比。示例中该配置项原为 5%，可按需调大（默认值为 50，请结合租户规格设置合理值）：

ALTER SYSTEM SET query_memory_limit_percentage = 50;

2. 在 SQL 中加入 Hint /*+parallel(2) */ 开启并行执行，避免中间结果写入临时文件。 问题2：SQL 中间结果写临时文件超限报错
3. 使用 Hint /*+parallel(2) */ 开启并行执行，避免中间结果写入临时文件。
4. 调整 SQL 中间结果（如 hash join、sort 等算子）在存储层可使用的临时文件总大小比例（在 MySQL 租户下执行；带单位时为大小，不带单位时为租户内存的比例）：

ALTER SYSTEM SET _temporary_file_io_area_size = 20;

规避方式

问题1：

• 提前评估大 SQL 的内存需求，合理设置 query_memory_limit_percentage，避免单条 SQL 内存使用超过限制。
• 对中间结果较大的 SQL，使用 Hint /*+parallel(2) */ 开启并行执行，避免中间结果写入临时文件。 问题2：
• 使用 Hint /*+parallel(2) */ 避免中间结果写入临时文件。
• 适当调大 _temporary_file_io_area_size，提高中间结果在存储层可使用的临时文件总大小。