526互联

KingbaseES V8R6 等待事件之IO类BufFileRead BufFileWrite

发布时间 2023-09-19 19:22:46作者: KINGBASE研究院

等待事件含义

当数据库创建临时文件时，会发生IO:BufFileRead和IO:BufFileWrite等待事件。当操作需要的内存比当前定义的work_mem内存参数更多时，会将临时数据写入磁盘永久存储。这种操作有时被称为“溢出到磁盘”。

IO:BufFileRead和IO:BufFile Write涉及work_mem内存区和maintenance_work_mem内存区。

work_mem的默认值为4 MB。如果一个会话并行执行操作，则每个处理并行性的工作进程将使用4MB的内存。因此，需要谨慎设置work_mem。如果将该值增加得太多，则运行多个会话可能会消耗太多内存。如果设置的值太低，数据库会在本地存储中创建临时文件。这些临时文件的磁盘I/O消耗可能会降低性能。

如果观察到以下事件，则数据库可能正在生成临时文件：

1、数据库存储性能突然急剧下降，然后快速恢复。

2、执行计划中可以看到sort disk字样，表示work_mem不够用了，排序已经溢出到磁盘排序。

可能导致等待增加的原因

IO:BufFileRead和IO:BufFileWrite等待事件的常见原因包括：

需要使用更多work_mem内存的查询

具有以下特征的查询使用work_mem区域：

  • Hash joins
  • ORDER BY clause
  • GROUP BY clause
  • DISTINCT
  • Window functions
  • CREATE TABLE AS SELECT
  • Materialized view refresh

需要使用更多maintenance_work_mem内存的语句

以下语句使用到维护工作区域：

CREATE INDEX,VACUUM, ALTER TABLE ADD FOREIGN KEY,CLUSTER

解决方法

定位问题

当我们在kwr报告中发现这类IO等待事件，说明很可能有sql已经内存溢出，需要用到磁盘排序。这时需要查看有无可疑的top sql

或者设置参数log_temp_files。此参数记录生成超过阈值KB的临时文件的所有查询。如果值为0，数据库记录所有临时文件。如果值为1024，数据库会记录所有生成大于1MB的临时文件的查询。

避免使用DISTINCT

如果可能，请避免使用DISTINCT操作来删除重复的行。查询返回的不必要和重复的行越多，DISTINCT操作的成本就越高。如果可能，请在WHERE子句中添加过滤条件，这可以提高性能并减少资源使用。

如果需要对同一表的多行使用DISTINCT，请考虑创建一个复合索引。将索引中的多列分组可以缩短计算不同行的时间。

检查ORDER BY和GROUP BY查询

ORDER BY子句可能会导致临时文件过多。考虑以下指导原则：

仅当需要对ORDER BY子句中的列进行排序时才将其包括在内。对于在ORDER BY子句中返回数千行并指定许多列的查询，此准则尤其重要。

当ORDER BY子句与具有相同升序或降序的列匹配时，考虑创建索引以加速ORDER BY语句。较小的索引读取和遍历速度更快。

如果可能，通过筛选结果集来减少需要排序的行数。如果使用WITH子句语句或子查询，请记住内部查询会生成一个结果集并将其传递给外部查询。查询可以筛选出的行越多，查询需要执行的排序就越少。

如果您不需要获得完整的结果集，请使用LIMIT子句。例如，如果您只想要前五行，那么使用LIMIT子句的查询不会一直生成结果。通过这种方式，查询所需的内存和临时文件更少。

GROUP BY自己也可以产生临时文件，使用GROUP BY查询的函数汇总：

  • COUNT
  • AVG
  • MIN
  • MAX
  • SUM
  • STDDEV

考虑使用窗口函数代替GROUP BY

使用GROUP BY可以更改结果集，然后检索聚合结果。使用窗口函数，可以在不更改结果集的情况下聚合数据。窗口函数使用OVER子句在查询定义的集合之间执行计算，将一行与另一行关联起来。

可以使用窗口函数中的所有GROUP BY函数，也可以使用以下函数：

  • RANK
  • ARRAY_AGG
  • ROW_NUMBER
  • LAG
  • LEAD

若要最大限度地减少窗口函数生成的临时文件的数量，请在需要两个不同的聚合时删除同一结果集的重复数据。请考虑以下查询。

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM a;

可以改写为窗window子句，如下。

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM a
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

物化视图和CTAS语句

当刷新物化视图时，它会运行一个查询，此查询可能包含GROUP BY、ORDER BY或DISTINCT等操作。在刷新过程中，可能会看到大量的临时文件以及等待事件IO:BufFileWrite和IO:BufFileRead。类似地，当基于SELECT语句创建表时，create table语句会运行查询。要减少所需的临时文件，请优化查询。

当cluster tables时，增加maintenance_work_mem

CLUSTER命令根据index_name指定的现有索引对table_name指定的表进行重新聚类。此方法以物理方式重新创建表，以匹配给定索引的顺序。

如果运行CLUSTER命令并观察到等待事件IO:BufFileWrite和IO:BufFileRead，请调优maintenance_work_mem参数。将内存大小增加到一定的数量。

增加work_mem

在某些情况下，您唯一的选择是增加会话使用的内存。如果您的查询语句书写正确，并且使用了正确的联接键，请考虑增加work_mem值。
若要了解查询生成的临时文件数，请将log_temp_files设置为0。如果将work_mem值增加到日志中标识的最大值，则不会在查询生成临时文件。但是，需要注意，如果数据库有5000个连接，并且每个连接使用256MB内存，则数据库实例需要1.2TiB的RAM。因此，可能会出现oom的情况。

为共享缓冲池保留足够的内存

数据库还使用共享缓冲池之类的内存区域，而不仅仅是工作内存区域。在增加work_mem之前，还需考虑这些额外内存区域的需求。

例如，假设你的数据库实例有64GB的内存。默认情况下，75%的内存保留给共享缓冲池。减去分配给共享内存区域的后，剩余16GB左右。不要将剩余内存全部分配给工作内存区域，因为操作系统也需要内存。

管理连接数

假设数据库实例有5000个连接同时并发。每个连接至少使用4MB的work_mem。连接的高内存消耗可能会降低性能。可以选择以下措施：

1、升级到更大的硬件内存。

2、通过使用连接代理或连接池程序来减少同时连接数据库的数量。

3、当数据库连接较少时，可以通过计算数据库内存总消耗以便增加work_mem的值。通过这种方式，可以减少IO:BufFileRead和IO:BufFileWrite等待事件的发生。