二月 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()::numeric * 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
现在,查询性能好了很多。我们将执行时间缩短了三倍以上!