PG数据库AUTOVACUUM的优化小技巧

最新推荐文章于 2024-08-09 08:00:00 发布
原创 最新推荐文章于 2024-08-09 08:00:00 发布 · 2.3k 阅读 · 33 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #postgresql #dba #运维

本文探讨了在大型PostgreSQL数据库中优化AUTOVACUUM的重要性，特别是通过调整autovacuum_max_workers、maintenance_work_mem和autovacuum_freeze_max_age参数，以及针对特定表的存储参数如vacuum_threshold，以实现高效且对业务影响最小的自动清理过程。监控统计信息也至关重要。

讨论了AUTOVACUUM的必要性，对于大型PostgreSQL数据库来说，AUTOVACUUM时必须的，而且优化好AUTOVACUUM对于大型PostgreSQL数据库来说至关重要。那么该如何来优化AUTOAVCUUM呢？今天我们就讨论一下优化AUTOVACUUM的一些常用的技巧。

首先我们要考虑的是三个基本的参数autovacuum_max_workers、maintenance_work_mem、autovacuum_freeze_max_age，这三个参数设置的合理，大部分AUTOVACUUM的问题都得到了解决。

autovacuum_max_workers指定了autovacuum使用的工作进程的数量，默认为3，对于绝大多数大型PostgreSQL数据库来说，这个值太小了。增加 autovacuum worker的数量意味着更多的进程可用于清理数据，这是面对数量非常多的表时需要考虑的问题。如果仅仅从autovacuum去考虑，理想情况下为每个 CPU 创建一个worker肯定会有最好的效率。因此worker的设置不应超过 CPU 的数量，但过多的worker可能会因 CPU 使用率的激增而对业务产生影响。通常应该将worker的数量设置为3-cpu_count之间。具体如何设置，需要在最大化autovacuum效率与整体系统性能之间进行平衡。如果你不知道该如何设置，那么最先设置为CPU_COUNT的1/2吧，如果在系统高峰期，CPU资源出现了瓶颈，那么适当缩小该值，如果CPU资源还很充足，但是autovacuum的性能不足，那么就增加这个值。

maintenance_work_mem，这个参数的值越大，vacuum的效率越高。不过要注意的是，当超出了每个设定后，增加该参数的效率会出现递减。太大的值会浪费内存资源，如果设置的太大耗尽了内存，那么就会产生十分严重的性能问题。对于较大的Post过热SQL数据库实例，如果物理内存充足，建议maintenance_work_mem至少设置为 1 GB，设置较大的参数将显着提高清理具有大量死元组的表的性能。 1 GB的维护工作内存足以一次处理大约 1.79 亿个死元组，如果你一次性要处理更多的死元组，那么还需要继续加大这个参数。如果这个参数设置不足以一次性

最低0.47元/天 解锁文章
优化PostgreSQL数据库的磁盘I_O性能
2502_91592937的博客
05-23 1074
本文旨在为数据库管理员和开发人员提供一套完整的PostgreSQL磁盘I/O性能优化方法论。PostgreSQL存储引擎架构分析关键配置参数调优索引策略优化表分区技术WAL(预写日志)配置优化查询计划优化文章首先介绍PostgreSQL的存储架构和I/O特性，然后深入探讨各种优化技术，最后通过实际案例展示优化效果。每个优化点都将包含理论解释和实际操作指南。: PostgreSQL的事务日志机制，确保数据持久性和崩溃恢复能力: PostgreSQL维护的内存缓冲区，用于减少磁盘I/O。
PostgreSQL优化实践：从查询到架构的性能提升指南
weixin_47233946的博客
06-01 1198
本文系统总结了PostgreSQL数据库优化策略，涵盖查询优化、索引设计、参数配置、架构扩展和维护监控等关键环节。通过实际代码示例演示了如何分析执行计划、避免类型转换、批量写入等SQL优化技巧，详细介绍了B-tree、GIN等索引的适用场景和复合索引设计原则。在系统配置方面，提供了基于32GB服务器的内存分配建议和写入性能调优参数。此外，还探讨了分区表、读写分离、连接池管理等架构级优化方案，以及自动化维护和关键监控指标。文章强调优化是一个持续过程，建议结合业务特征进行针对性调整，并利用工具进行基准测试和性能
PG Auto Vacuum 学习与性能调优
dnuiking的博客
05-16 659
autovacuum 可以设置开启或关闭，默认是开启，生产环境建议开启。
pg数据库auto vacuum配置
xiaolegeaizy的博客
11-19 3349
autovacuum 是 postgresql 里非常重要的一个服务端进程，能够自动地执行，在一定条件下自动地对 dead tuples 进行清理并对表进行分析 autovacuum参数控制 autovacuum 进程是否打开,默认为 "on" 根据postgresql.conf相关配置，理解autovacuum会在两种情况下会被触发： 1.表上(update,delte 记录) >= autovacuum_vacuum_scale_factor* reltuples(表上记录数) + a.
优化PostgreSQL Autovacuum
PostgreSQL China
12-21 1822
作者：Laurenz Albe是CYBERTEC的高级顾问和支持工程师。自2006年以来，他一直在PostgreSQL上工作并为PostgreSQL做贡献。 译者：类延良，任职于瀚高基础软件股份有限公司，PostgreSQL数据库技术爱好者，10g &11g OCM，OGG认证专家。 在许多PostgreSQL数据库中，您无需考虑或担心调整autovacuum。它会在后台自动运行，并在不妨碍您的情况下进行清理。 但是有时默认配置还不够好，您必须调整autovacuum以使其正常工作。本文介绍了一些典
PostgreSQL autovacuum 优化与调试 (1 触发 autovacuum 的条件)
sql server的专栏
10-11 1141
PostgreSQL 的数据库系统中是需要进行autovacuum 进行表级别的数据清理的。在开始autovacuum 进行调优之前实际上是需要理解为什么需要autovacuum.长话短...
08-PG清理——Autovacuum
qq_29431123的博客
08-03 1014
vacuum 可以清理死元组，但多久调用一次vacuum是很难界定的，对于频繁更新的表，我们需要及时的vacuum，避免积累过多的死元组而致使数据库性能下降，而且死元组过多的积累必将导致下次vacuum执行缓慢；另外，如果表不频繁更新，而频繁的执行vacuum会造成不必要的资源浪费，为此PostgreSQL引入autovacuum机制，由postmaster 拉起一个单独的进程autovacuum来根据表的死元组的阈值来自动vacuum。
汉字笔画笔顺数据库查询优化：性能提升必学技巧
[汉字笔画笔顺数据库查询优化：性能提升必学技巧](https://www.devart.com/dbforge/mysql/studio/images/partitioning-introduction.webp) # 摘要 随着信息技术的快速发展，数据库查询优化已成为提高数据处理效率和...
autovacuum调优的基本知识
liuzhilong的博客
05-19 1127
autovacuum调优的基本知识
PGSQL 设置autovacuum
爷一隐居青楼的博客
12-08 1311
-例如：test表上有1000条记录，当对这个表进行dml操作达到以下阀值是就会进行autovacuum候选列表，等待执行autovacuum操作。--autovacuum会做两件事件：一是vacuum，二是analyze。--analyze=10000*0.05+50=550个tuple。--vacuum=10000*0.1+50=1050个tuple。--autovacuum触发条件。
PostgreSQL（十七）Autovacuum工具
shmily_coco的博客
04-07 2357
Autovacuum是PG的一个后台进程，在9.6版本之后才出现，在PG实例启动时自动启动；每隔一分钟执行一次自动收集需要做Vacuum操作的表；当表的操作记录满足指定条件时，自动执行Vacuum操作；（1）一次可以运行多少个Autovacuum进程autovacuum_max_workers =3 （缺省值）#Autovacuum的工作进程个数参数，意为一次可以运行多少个Autovacuum进程，可以使用此参数，对工作进程进程管理。此参数运用在可能包含多个数据库的实例/群集上；
autovacuum部分相关参数
HighGO
04-19 1343
log_autovacuum_min_duration 如果autovacuum执行的每个操作至少运行了指定的毫秒数，则会被记录。 将其设置为零会记录所有自动清理操作。 -1（默认值）禁用记录autovacuum。 例如，如果将其设置为250ms，则将记录所有运行250ms或更长时间的vacuums 和 analyzes 。 另外，当此参数设置为除-1之外的任何值时，如果由于存在冲突锁而
PG数据库为什么要用autovacuum
DBAIOps的博客
02-04 677
我们可以看到，一个系统中的有些表的死元组比例十分高，有些表则很低。一个比较简单和常用的做法是，首先列出你的系统中的超过1GB的大表（这个阈值根据你的系统的特点去调整，我这里只是举例），然后分析其行数和死元组的数量，比例。甚至在给一个快递公司做一个执行计划稳定的优化的时候，我发现因为每天有几次大批量的数据写入，经常会导致某些时段中的表统计数据必然会导致错误的执行计划出现，因为应用中存在应用中动态拼凑SQL，导致所需的执行计划无法通过HINT固定，而白天业务高峰期经常对超大型的表做分析又风险太大。
PostgreSQL的AutoVacuum进程
文牧之的博客
08-09 1133
AutoVacuum的一个重要功能是防止事务ID回绕（transaction ID wraparound），这是PostgreSQL中一个可能导致数据丢失的问题。在PostgreSQL中，AutoVacuum是一个后台进程，用于自动清理数据库中的冗余数据。它通过定期清理被删除或更新的行来回收存储空间，并更新统计信息，以提高数据库的性能和效率。总之，AutoVacuum是PostgreSQL中一个非常重要的后台进程，它通过自动清理和分析操作，帮助维护数据库的健康和性能。
postgresql autovaccum自动清理
日常博客
07-27 3296
postgresql autovaccum
pg 定时删除_postgresql AutoVacuum系统自动清理进程
weixin_39719127的博客
12-20 842
2.5.5 AutoVacuum系统自动清理进程在PostgreSQL数据库中，对表元组的UPDATE或DELETE操作并未立即删除旧版本的数据，表中的旧元组只是被标识为删除状态，并未立即释放空间。这种处理对于获取多版本并发控制是必要的，如果一个元组的版本仍有可能被其他事务看到，那么就不能删除元组的该版本。当事务提交后，过期元组版本将对事务不再有效，因而其占据的空间必须回收以供其他新元组使用，以...
PgSQL · 源码分析 · AutoVacuum机制之autovacuum launcher
weixin_34354945的博客
12-21 235
背景 根据之前月报的分析，PostgreSQL中的MVCC机制（详见月报）同时存储新旧版本的元组，对于经常更新的表来说，会造成表膨胀的情况。为了解决这个问题，PostgreSQL 引入了VACUUM和ANALYZE命令，并且引入了AutoVacuum自动清理。 在PostgreSQL中，AutoVacuum自动清理操作包括： 删除或重用无效元组的磁...
PostgreSQL 自动Vacuum配置
步行者的专栏
08-02 7121
PostgreSQL的Vacuum由于以下原因需要定期执行。释放,再利用因更新或者删除更新而占用的磁盘空间。更新PostgreSQL查询计划用的统计数据。避免事务ID的重置而引起非常老的数据丢失。VACUUM的标准SQL文的执行和其他的对数据库的实际操作可以并行处理。SELECT、INSERT、UPDATE、DELETE等
PostgreSQL_autovacuum调优
2301_76513555的博客
08-06 1375
注意:对ANALYZE的负面影响更严重一些，因为虽然VACUUM的成本主要与死行数量成正比(当表中死行很少时相当低，使不必要的清理变得便宜)，但ANALYZE实际上必须在每次执行时重新构建统计信息(包括收集随机样本，这可能需要相当多的I/O)。它会获取表上的独占锁，阻止所有操作（包括只读查询），并创建表的新副本，这可能会使使用的磁盘空间量增加一倍（因此，当您的磁盘空间已经用完时，它特别不切实际）。此 MVCC 实现的缺点是，它会留下已删除的行，即使在可能看到这些版本的所有事务完成后也是如此。
pg数据库 autovacuum
最新发布
06-07
### PostgreSQL 自动清理（Autovacuum）功能与配置指南 PostgreSQL 的 `autovacuum` 功能旨在自动执行 VACUUM 和 ANALYZE 操作，以确保数据库的性能和健康状态[^1]。VACUUM 操作的主要目的是回收已删除或更新行占用的空间，而 ANALYZE 则用于更新表的统计信息，从而帮助查询规划器生成更优的执行计划。 以下是对 `autovacuum` 的功能、配置参数以及使用建议的详细介绍： #### 1. Autovacuum 的基本功能 `autovacuum` 是 PostgreSQL 内置的一个后台进程，它会定期检查数据库中的表，并根据预设条件自动执行 VACUUM 和 ANALYZE 操作。通过这种方式，可以避免手动维护带来的不便，同时确保数据库的高效运行[^2]。 #### 2. 关键配置参数 以下是 `autovacuum` 的一些重要配置参数及其作用： - **autovacuum**：启用或禁用自动清理功能。默认值为 `on`。 - **autovacuum_max_workers**：指定可以同时运行的最大 autovacuum 工作线程数。默认值为 3。 - **autovacuum_naptime**：autovacuum 进程在两次扫描之间的休眠时间（单位为秒）。默认值为 60 秒。 - **autovacuum_vacuum_threshold**：触发 VACUUM 操作所需的最小行数变化。默认值为 50 行。 - **autovacuum_analyze_threshold**：触发 ANALYZE 操作所需的最小行数变化。默认值为 50 行。 - **autovacuum_vacuum_scale_factor**：基于表大小的比例因子，用于计算触发 VACUUM 的额外行数。默认值为 0.2（即 20%）。 - **autovacuum_analyze_scale_factor**：基于表大小的比例因子，用于计算触发 ANALYZE 的额外行数。默认值为 0.1（即 10%）。 - **maintenance_work_mem**：控制维护操作（如 VACUUM 和 CREATE INDEX）可用的最大内存量。推荐设置为较大的值，尤其是在大规模数据集上操作时[^2]。 #### 3. 配置示例 以下是一个典型的 `autovacuum` 配置示例，适用于中等规模的生产环境： ```plaintext autovacuum = on autovacuum_max_workers = 5 autovacuum_naptime = 30 autovacuum_vacuum_threshold = 50 autovacuum_analyze_threshold = 50 autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.05 maintenance_work_mem = 2GB ``` #### 4. 使用建议 - 在生产环境中进行任何优化之前，请务必在测试环境中验证效果，并做好数据备份[^1]。 - 根据实际工作负载调整 `autovacuum` 参数。例如，对于高并发写入的表，可能需要降低 `autovacuum_vacuum_scale_factor` 和 `autovacuum_analyze_scale_factor` 的值，以更快地响应变化。 - 使用 `pg_stat_user_tables` 视图监控表的统计信息，评估 `autovacuum` 的运行效果。 - 如果某些表的 `autovacuum` 频率过高，可以通过设置 `autovacuum_enabled = off` 来禁用该表的自动清理功能[^2]。 #### 5. 监控工具 为了更好地监控 `autovacuum` 的运行情况，可以使用以下工具： - **pg_stat_activity**：查看当前正在运行的 `autovacuum` 进程。 - **pg_stat_user_tables**：获取每个表的 VACUUM 和 ANALYZE 统计信息。 - **Grafana + Prometheus**：实现性能监控的可视化[^1]。 ---
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值