AI生成的图像
大家好！在这篇文章里，我将聊聊 work_mem（和 maintenance_work_mem）与 max_connections 参数之间的关系。这两个设置在内存使用和整体性能方面起着关键作用，是任何为达到未来性能目标所做的优化工作的核心。虽然 PostgreSQL 还有一些跟内存相关的参数，但这篇文章主要聊聊 work_mem 和 max_connections 怎么相互影响并影响系统性能。
这篇博客的主要内容如下。
- work_mem和max_connections的定义
- 两个GUC设置之间的关系
- 配置work_mem和max_connections以获得更可靠的PostgreSQL
- 使用不同设置的基准测试
这些定义可以在官方 PostgreSQL 文档页面中找到。简单来说，work_mem 设置了查询操作（如排序或哈希表）在写入临时磁盘文件之前所使用的最大内存基础。如果没有指定单位，默认以千字节为单位。
另外，max_connections 参数决定了数据库服务器可以同时接受的最大连接数。默认情况下通常是 100 个连接，但如果内核设置不允许多于该数量，则可能会少一些（在初始化时决定）。此参数只能在启动服务器时设置。PostgreSQL 根据 max_connections 的值直接依据该参数分配某些资源。增加其值会增加这些资源的分配量，包括共享内存的分配。
我不会讨论如何更改这些参数，但很快我们可以访问PostgreSQL配置文件或在数据库上执行以下命令。请记住，更改max_connection参数后需要重启PostgreSQL服务。
ALTER SYSTEM SET work_mem TO '2MB'; // 将工作内存设置为2MB
ALTER SYSTEM SET max_connections TO 500; // 将最大连接数设置为500谈到工作内存（work_mem）参数时，它会影响查询性能和I/O性能。如果它设置得不够大，无法存储临时信息，包括排序和分组的数据，这些操作将会在磁盘上执行。
当 PostgreSQL 在排序或分组时更倾向于使用磁盘而非内存时，这会导致性能问题。换句话说，由于不必要的磁盘排序操作，查询性能可能会受到影响。另一方面，因为 work_mem 是为每个操作在每个连接中分配的，因此总的潜在内存消耗会随着活跃连接数的增加而增大。实际上，这意味着：
总内存使用（约为）= max_connections x work_mem（取决于查询复杂度和并发程度）。
例如，如果将 work_mem 设为 16 MB 并且将 max_connections 设为 100，那么数据库有可能仅在执行活动操作时就消耗高达 1.6 GB 的内存。
为了设定一个更可持续和高效的最大连接数，我们可以考虑以下几点。
- 服务器中的内存（MB）：实例的总可用内存。
- 工作内存配置（MB）：为每个查询操作分配的内存（MB），我们假设每个 PostgreSQL 实例至少分配 1MB。
- 共享缓冲配置（MB）：共享缓冲设置为总 RAM 的 25%，这是典型的 PostgreSQL 配置。
- 基础开销（MB）：基于 PostgreSQL 内部结构和连接数的固定开销（MB）。
- 缓冲百分比（整数）：预留总 RAM 的 15% 供操作系统和其他非 PostgreSQL 进程使用。
- 维护缓冲（整数）：预留总 RAM 的 10% 用于维护操作，例如真空处理和索引构建。
配置这些参数正确与否很大程度上取决于具体需求和工作负载。我编写了一个脚本来自动化这个过程，脚本位于本节末尾，可供参考。这个脚本提供了一种公式化的方法来评估内存限制并识别潜在瓶颈。它计算出安全和可持续的估计值，帮助避免在高负载下耗尽可用内存的配置。
解释，重点在于公式及其评估过程该脚本提供了一种系统化的方式来评估内存分配情况，指导用户平衡设置max_connections和work_mem参数。它考虑到系统开销及特定的PostgreSQL需求，基于可用RAM来安全估算内存使用量。以下是脚本计算如何指导配置：
- 用例场景和工作负载依赖关系
最优的 max_connections 和 work_mem 参数值与工作负载特性紧密相关。高并发的 OLTP 系统可能需要将 max_connections 设置得更高，而将 work_mem 设置得更低，而 OLAP 系统可能受益于较少的连接数和每个连接更高的 work_mem 设置。
此脚本提供了一些基础的推荐设置，用户可以根据自己工作量的需求进行调整。
- 记忆估算：公式
脚本计算当前配置下的总内存使用量（CURRENT_MEMORY_OVERHEAD_TOTAL）。这个值代表在达到最大连接数时PostgreSQL将占用的内存总量。
通过将这个总数与可用内存（AVAILABLE_RAM_MB）比较，脚本评估当前配置是否在系统限制范围内，或者是否需要调整。
以下针对 max_connections 和 work_mem 的建议配置（选项 1 和 2）基于系统资源给出了具体的限制，确保系统不会超出安全的内存使用范围。
- PostgreSQL内存分配指导
该脚本使用公式方法来确定内存分配，考虑了OS、共享缓冲区和维护内存需求。它在固定work_mem的情况下，计算max_connections的可行配置，以及在固定max_connections的情况下，计算work_mem的可行配置，根据用户优先考虑的参数提供灵活性以适应不同的需求。
通过根据这个公式调整 max_connections 和 work_mem 参数，用户可以达到与系统容量相匹配的最佳配置。这也将满足工作负载的需求。
这种方法是数据库管理员有效利用内存的一个起点。在实际环境中测试这些推荐值并监控PostgreSQL的运行情况，这对于根据实际工作负载调整优化至关重要。
#!/bin/bash
# 常量值（假设在计算前已经提供了 RAM 和相关配置）
TOTAL_RAM_MB=8000
CURRENT_CONNECTION_LIMIT=1000
CURRENT_WORK_MEM_MB=16
BASE_OVERHEAD_MB=8
HASH_MEM_MULTIPLIER=2
OS_MEMORY_USAGE_PERCENTAGE=25
MAINTENANCE_OS_MEMORY_USAGE_PERCENTAGE=15
# 根据总 RAM 和使用百分比计算内存分配
BUFFER_MB=$(( TOTAL_RAM_MB * OS_MEMORY_USAGE_PERCENTAGE / 100 ))
SHARED_BUFFERS_MB=$(( TOTAL_RAM_MB / 4 ))
MAINTENANCE_BUFFER_MB=$(( TOTAL_RAM_MB * MAINTENANCE_OS_MEMORY_USAGE_PERCENTAGE / 100 ))
AVAILABLE_RAM_MB=$(( TOTAL_RAM_MB - BUFFER_MB - SHARED_BUFFERS_MB - MAINTENANCE_BUFFER_MB ))
# 显示当前配置
echo "当前工作内存设置: ${CURRENT_WORK_MEM_MB} MB"
echo "当前最大连接数设置: ${CURRENT_CONNECTION_LIMIT}"
# 计算每个连接的内存分配和总内存开销
MEMORY_PER_CONNECTION_MB=$(( (CURRENT_WORK_MEM_MB * HASH_MEM_MULTIPLIER) + BASE_OVERHEAD_MB ))
CURRENT_MEMORY_OVERHEAD_TOTAL=$(( MEMORY_PER_CONNECTION_MB * CURRENT_CONNECTION_LIMIT ))
# 检查当前配置是否适合可用内存
if (( AVAILABLE_RAM_MB > CURRENT_MEMORY_OVERHEAD_TOTAL )); then
echo "当前最大连接数和工作内存设置是可持续的。假设可用内存为 $AVAILABLE_RAM_MB MB"
else
echo "PostgreSQL 在 ${CURRENT_CONNECTION_LIMIT} 个并发连接下无法扩展，消耗 ${CURRENT_MEMORY_OVERHEAD_TOTAL} MB。请考虑调整你的 max_connections 和 work_mem 参数。"
fi
# 根据可用内存和当前工作内存计算建议的最大连接数
SUGGESTED_MAX_CONNECTION=$(( AVAILABLE_RAM_MB / MEMORY_PER_CONNECTION_MB ))
echo "选项 1 -> 最大连接数的最大值为 ${SUGGESTED_MAX_CONNECTION}，假设工作内存为 ${CURRENT_WORK_MEM_MB} MB。"
# 根据当前连接限制计算建议的工作内存
SUGGESTED_WORK_MEM_CURRENT=$(( AVAILABLE_RAM_MB / CURRENT_CONNECTION_LIMIT ))
echo "选项 2 -> 工作内存的最大值为 ${SUGGESTED_WORK_MEM_CURRENT} MB，假设最大连接数为 ${CURRENT_CONNECTION_LIMIT}。"最后，我可以解释并合理化两个常数值。
- HASH_MEM_MULTIPLIER：PostgreSQL 中 hash_mem_multiplier 参数的值。这是一个技术参数。
- BASE_OVERHEAD_MB：这是一个估算/假设，单个连接可能最多占用 8MB 内存。此值会根据您的 hugepages 设置和其他内核配置而有所不同，但我尽量多加了一些 MB 以确保安全。
例如，假设我们运行了前面的脚本并获得了一些关于我们当前配置的建议。在执行脚本之前，我们提供了当前的work_mem和max_connections值。这样一来，我们就可以期待脚本给出有关max_connections或work_mem的限制。
当前 work_mem 配置：16 MB
当前 max_connections 配置：1000
PostgreSQL 在 1000 个并发连接的情况下可能无法正常扩展，这将消耗 40000 MB 的内存。请考虑调整 max_connections 和 work_mem 参数的值。
选项 1 -> 如果 work_mem 设置为 16 MB，那么 max_connections 的最大推荐值是 70。
选项 2 -> 如果 max_connections 设置为 1000，那么 work_mem 的最大推荐值是 2 MB如果实例中的可用内存约为8GB，最大连接数为1000，每个连接的工作内存为16MB，脚本会有一个相当明显的警告，指出如果连接被允许使用工作内存的话，PostgreSQL可能无法处理1000个并发连接，因为当前的work_mem值太小。
因此可以评估两个不同的选项。第一个选项建议将 max_connections 减少到大约 70，如果 work_mem 需要设置为 16MB。
另外，如果把 max_connection 设置成 1000，建议把 work_mem 调小到 2MB。
总结起来，这些建议并不是适用于每个PostgreSQL实例的唯一正确值。此脚本和方法的主要目的是了解我们在PostgreSQL实例中的限制、配置以及资源使用状况。
基准和测试结果我也创建了一些基准测试来衡量配置的影响。例如，我尝试了多种不同的配置组合，使用不同的工作内存和连接设置来展示内存消耗和事务处理速率的变化。
CREATE TABLE `订单` (
`订单编号` SERIAL PRIMARY KEY,
`客户编号` INT NOT NULL,
`订单下单日期` DATE NOT NULL,
`订单状态` VARCHAR(20)
);
-- 将随机数据插入订单表（100万行）
INSERT INTO `订单` (`客户编号`, `订单下单日期`, `订单状态`)
SELECT
(RANDOM() * 100000)::INT, -- 随机客户编号
CURRENT_DATE - (RANDOM() * 365)::INT, -- 过去一年内的随机日期
CASE WHEN RANDOM() > 0.5 THEN '完成' ELSE '未处理' END
FROM
generate_series(1, 1000000);
explain analyze (select * from `订单` where `订单状态`='未处理' order by `客户编号`);为了测量影响，我准备了两个不同的基准文件，一个使用了 512kB 的 work_mem，另一个使用了 16MB。
将 work_mem 设置为 '512kB';
select * from orders where status='pending' 按 customer_id 排序;
将 work_mem 设置为 '16MB';
select * from orders where status='pending' 按 customer_id 排序;之后我写了一个简单的 bash 脚本，用来模拟任务负载并收集测试结果。
#!/bin/bash
truncate -s 0 raw_pgbench_results.log
low_benchmark_1(){
AVG_LATENCY=$(pgbench -f workmem_low.sql -C -c 100 -j 2 -T 120 benchmark_v2)
AVG_LATENCY_2=$(echo $AVG_LATENCY | grep 'latency average' | awk '{print $45}')
echo "-------16kB 工作内存，100个连接-------------" >> raw_pgbench_results.log
echo "$AVG_LATENCY_2" >> raw_pgbench_results.log
}
low_benchmark_2(){
AVG_LATENCY=$(pgbench -f workmem_low.sql -C -c 150 -j 2 -T 120 benchmark_v2)
AVG_LATENCY_2=$(echo $AVG_LATENCY | grep 'latency average' | awk '{print $45}')
echo "-------16kB 工作内存，150个连接-------------" >> raw_pgbench_results.log
echo "$AVG_LATENCY_2" >> raw_pgbench_results.log
}
high_benchmark_1(){
AVG_LATENCY=$(pgbench -f workmem_high.sql -C -c 100 -j 2 -T 120 benchmark_v2)
AVG_LATENCY_2=$(echo $AVG_LATENCY | grep 'latency average' | awk '{print $45}')
echo "-------16MB 工作内存，100个连接-------------" >> raw_pgbench_results.log
echo "$AVG_LATENCY_2" >> raw_pgbench_results.log
}
high_benchmark_2(){
AVG_LATENCY=$(pgbench -f workmem_high.sql -C -c 150 -j 2 -T 120 benchmark_v2)
AVG_LATENCY_2=$(echo $AVG_LATENCY | grep 'latency average' | awk '{print $45}')
echo "-------16MB 工作内存，150个连接-------------" >> raw_pgbench_results.log
echo "$AVG_LATENCY_2" >> raw_pgbench_results.log
}
for i in {1..3}
do
echo "开始pgbench基准测试"
low_benchmark_1
sleep 120
low_benchmark_2
sleep 120
high_benchmark_1
sleep 120
high_benchmark_2
done简单来说，基准场景和细节如下。
- 创建了一个示例表，填充了数据，并定义了一个查询，该查询将需要使用工作内存空间进行排序。
- 创建了两个不同的 .sql 文件。其中一个在执行查询之前将 work_mem 设置为 512kB，另一个在执行之前设置为 16MB。
- 创建了一个样本基准 bash 脚本。该脚本包含四个不同的基准测试项。简单来说，我尝试使用两种不同的连接数来执行两个不同的基准 .sql 文件。
因此，我得到了一些结果，并尝试根据这些结果记录一些事实。_对于某些工作量（查询），增加work_mem值可能会提升性能，但这种改变可能会影响资源消耗。以下图表显示，当内存使用增加时，延迟有所减少。_
其次，增加并发连接来提高TPS可能会导致查询性能下降，因为这会增加内存消耗，原因是work_mem的增加。如第二张图所示，当我们增加了50个并发连接后，延迟时间实际上增加了。简单来说，这并不意味着增加并发连接和work_mem一定会提升查询性能。
100个连接的内存消耗和延迟时间
在有150个连接时的内存使用和延迟。
总之，在 PostgreSQL 中调整 work_mem 和 max_connections 可以在某些条件下提升查询性能，但这些调整需要仔细权衡内存资源和工作负载特性。结果显示，增加 work_mem 可以减少特定查询的延迟，通过增加内存处理来提高性能。然而，更高的 work_mem 设置也会增加整个系统的内存使用量，从而导致资源竞争，尤其是在高并发时。此外，增加并发连接以提高每秒事务数 (TPS) 可能会适得其反，导致性能下降，这是由于内存压力导致的。这表明更多的连接和更高的 work_mem 设置并不能自动带来更好的性能。有效的调整需要根据系统的容量和工作负载需求来调整这些参数，以实现最佳性能而不超出内存限制。
通过LinkedIn联系我，在Twitter或Superpeer上与我建立联系。让我们共同努力，优化数据库，提高效率，就像《星球大战》中的"愿原力与你同在"一样。愿原力指引我们优化数据库，使其更加高效。
德米尔
共同学习，写下你的评论
评论加载中...
作者其他优质文章
