PostgreSQL 教程: 优化 work_mem 设置

二月 3, 2024

摘要:在本教程中,您将学习如何在 PostgreSQL 中优化work_mem设置。

什么是 work_mem?

在 PostgreSQL 中,work_mem用于设置进行排序和哈希处理等查询操作时,在写入到磁盘中的临时文件之前要使用的最大内存量。这对于 ORDER BY、DISTINCT 和 JOIN 等操作至关重要。默认情况下,work_mem设置为 4MB。它可以在 postgresql.conf 文件中设置,也可以在会话级别设置。

set work_mem to '64MB';

PostgreSQL 会为超出work_mem的操作,在PGDATA/base/pgsql_tmp目录内分配临时文件。work_mem是针对单个操作进行分配的,而不是整个查询。例如,如果单个查询有 8 个排序操作,则将分配 8 次work_mem,每个排序操作一次。

PostgreSQL 中的并行工作进程允许使用多个进程处理单个查询,从而划分任务以提高性能。这些工作进程通过共享内存共享中间结果进行协作。如果 2 个并行工作进程正在处理具有排序操作的查询,则每个并行工作进程会单独使用各自的work_mem

何时该调整 work_mem?

在许多情况下,优化work_mem可以提高性能:

  • 当您在运行涉及大量排序或哈希操作的查询时。
  • 在磁盘上创建了大量临时文件时。
  • 当您在 EXPLAIN ANALYZE 的计划输出中看到external merge disk操作时。

调大 work_mem 的好处

调大work_mem参数有很多好处。例如,调大work_mem可以:

  • 对大型结果集进行排序是一项常见的数据库任务,适当的设置work_mem可以显著加快这一过程。它允许在内存中对更多数据进行排序,从而减少对缓慢的磁盘操作的需要。
  • 哈希连接用于在查询执行期间合并来自各种表的数据。较大的work_mem值可以更多的使用内存进行数据存储,最小化磁盘 I/O 操作的需要,来增强哈希连接的性能。
  • 通过更快的排序和连接,查询可以更快地完成执行。这也让数据库性能表现更快速,用户响应时间更短。
  • 适当调整work_mem可提升并行能力,并充分利用多核处理器,进一步提高查询效率。

注意:必须注意的是,work_mem设置得太高,会导致内存使用过多和潜在的内存竞争问题。因此,为您的特定工作负载找到合适的平衡点很关键。

优化 work_mem 示例

让我们通过一个示例,来更好地了解优化work_mem如何带来实质性的性能改进。

假设我们有一个名为sales_data的表:

CREATE TABLE sales_data (
  id SERIAL PRIMARY KEY,
  date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

要为sales_data表生成数百万行示例数据,您可以使用 PostgreSQL 中的generate_series函数。下面是一个示例,说明如何将 100 万行示例数据插入到sales_data表中。

下面是一个示例 SQL 查询,用于生成数据:

INSERT INTO sales_data (date, amount)
SELECT
generate_series('2020–01–01'::date, '2022–12–31'::date, '1 minute'::interval) AS date,
round(random() * 1000 + 50, 2) AS amount
FROM generate_series(1, 20);

在此查询中:

1. 我们用generate_series创建了从 2020 年 1 月 1 日到 2022 年 12 月 31 日的一系列日期,每个日期之间间隔一分钟。这将创建大约 150 万个唯一的时间戳。

2. 对于每个生成的日期,我们将创建 20 行,随机数额介于 50 到 1050 之间,四舍五入到小数点后两位。您可以更改这些限制,以填充想要填充的任意数量的行。

3. 然后使用查询的INSERT INTO sales_data (date, amount)部分,将生成的数据插入到sales_data表中。

请根据需要调整日期范围、间隔和每个时间戳的行数。如果需要超过 100 万行,可以调整generate_series函数的参数,或增加每个时间戳生成的行数。

我们已经用数百万行的示例数据填充了此表。

现在,假设我们要按日期排序检索总的销售额:

EXPLAIN ANALYZE SELECT date, SUM(amount) FROM sales_data GROUP BY date ORDER BY date;
                     QUERY PLAN
----------------------------------------------------
 ...
 Sort Method: external merge Disk: 31528kB
 Execution Time: 1426.504 ms

使用默认的work_mem设置时,您可能会注意到 PostgreSQL 正在使用基于磁盘的排序操作,这可能会很慢。

现在,让我们将work_mem调大到 64MB,看看它如何影响性能:

SET work_mem = '64MB';

EXPLAIN ANALYZE SELECT date, SUM(amount) FROM sales_data GROUP BY date ORDER BY date;
                     QUERY PLAN
----------------------------------------------------
 ...
 Sort Method: quicksort Memory: 52428kB
 Execution Time: 438.372 ms

现在,查询性能好了很多。我们将执行时间缩短了三倍以上!