Autovacuum 是 PostgreSQL 最强大的功能之一，旨在通过自动执行日常维护任务来维护数据库健康并优化性能。但是，配置不当可能会导致性能瓶颈、因资源效率低下而导致的成本过高或不受控制的表膨胀。本博客探讨了 autovacuum 是什么、它在性能优化和成本降低方面的作用，以及配置其参数的最佳实践。
什么是 Autovacuum？
Autovacuum 是 PostgreSQL 中的一个后台进程，负责通过执行两个关键任务来维护表的健康状况：
1.Vacuuming
删除死元组（已被更新或删除但不再可见的行）。
释放空间以供重复使用，以防止表膨胀并降低存储成本。
2.分析
更新查询计划器使用的表统计信息以优化执行计划，提高查询性能。
如果没有自动清理，死元组就会累积，从而导致：
- 表膨胀：磁盘使用量的增加会增加存储成本并降低查询性能。
- 事务 ID 回绕：一种强制系统进入“安全模式”的情况，阻止非超级用户事务以保护数据完整性。如果不解决，这可能会导致数据库无法使用，从而导致停机并增加运营成本。
通过自动执行这些任务，自动真空可确保数据库性能的一致性并最大限度地减少不必要的成本。
Autovacuum 参数和性能优化与成本降低的最佳实践
正确调整 autovacuum 涉及在postgresql.conf中配置几个参数。下面，我将讨论每个参数、其含义以及在设置其值时要考虑的因素，以最大限度地提高性能并最大限度地降低成本。
- autovacuum
- 描述：启用或禁用自动清理进程。
- 默认值： on
- 建议：始终保持启用状态。禁用自动清理可能会导致严重的性能下降、存储成本增加以及由于事务 ID 回绕问题而导致的停机。
- autovacuum_max_workers
- 描述：设置自动清理工作进程的最大数量。
- 默认值： 3
- 推荐：
每 8 到 16 个 CPU 核心分配 1 个工作器，以平衡性能和资源使用情况。
确保每个工作程序都有足够的内存（autovacuum_work_mem ），以避免过度交换，从而增加成本。
- autovacuum_naptime
- 描述：对需要维护的表进行检查的时间间隔。
- 默认值： 1 分钟
- 推荐：
对于高写入工作负载，减少到30 秒以防止膨胀并优化查询性能。
对于低写入环境，默认值足以平衡成本和性能。
- autovacuum_vacuum_threshold
- 描述：触发真空的最小死元组数量。
- 默认值： 50
- 推荐：
对于大型表，增加到100-200以减少不必要的 vacuum 操作并控制成本。
此设置应与autovacuum_vacuum_scale_factor一起决定。
- autovacuum_analyze_threshold
- 描述：触发分析操作的插入或更新元组的最小数量。
- 默认值： 50
- 推荐：
对于高流失表，减少到20-50以确保准确的查询计划和更短的查询执行时间。
对于低流失表，默认值足以最大限度地减少不必要的成本。
此设置应与autovacuum_analyze_scale_factor一起决定。
- autovacuum_vacuum_scale_factor
- 描述：表大小的分数，用于计算触发真空所需的死元组的数量。
- 默认值： 0.2 （20%）
- 推荐：
对于大写入表，减少至0.01-0.05，以确保及时清理并降低存储成本。
- autovacuum_analyze_scale_factor
- 描述：表大小的分数，用于计算触发分析所需的更新或插入的元组的数量。
- 默认值： 0.1 （10%）
- 推荐：
对于高写入表，减少到0.05以保持最佳查询性能。
对于低写入表，默认值足以平衡维护成本和性能。
- autovacuum_freeze_max_age
- 描述：在强制进行真空处理以防止回绕之前，表的事务 ID 的最大使用期限。
- 默认值： 2亿
- 推荐：
对于高写入工作负载，减少到1.5 亿，以主动防止停机并避免相关成本。
对于低写入工作负载，保留默认值以最大限度地减少维护开销。
- autovacuum_multixact_freeze_max_age
- 描述：强制清理之前表的多事务 ID 的最大使用期限。
- 默认值： 4亿
- 推荐：
对于高多事务使用率（例如外键锁），减少到2 亿到 3 亿以避免延迟并最大限度地降低运营成本。
对于较低的多事务使用率，默认值就足够了。
10.自动真空_真空_成本_延迟
- 描述：真空操作之间的暂停持续时间（以毫秒为单位）以限制 I/O 影响。
- 默认值： 20ms
- 推荐：
对于快速 I/O 系统（例如 SSD），减少到10ms以加快维护速度并降低与膨胀相关的成本。
对于高 I/O 争用，增加到30-50ms以平衡运营成本和性能。
- autovacuum_vacuum_cost_limit
- 描述：真空吸尘器暂停前的成本限制，影响其工作积极性。
- 默认值： -1 （继承vacuum_cost_limit ）。
- 推荐：
对于高写入工作负载，增加到2000-4000以更有效地处理大表并防止长期与膨胀相关的成本。
对于低写入工作负载，请保持在500-1000以避免过多的资源使用。
- log_autovacuum_min_duration
- 描述：记录超过指定持续时间的自动清理操作。
- 默认值： -1 （禁用）。
- 推荐：
对于调试和成本分析，请设置为0 （记录所有操作）。请注意，这可能会在高写入系统中创建大量日志。
对于生产，设置为5000-10000ms以识别昂贵的长时间运行的任务。
13.自动真空工作内存
- 描述：分配给每个自动清理工作者进行处理的内存。
- 默认值： -1 （继承maintenance_work_mem ，通常为64MB ）。
- 推荐：
确保自动清理的总内存不超过可用资源，防止因内存争用而产生不必要的成本。
计算方法是取 20% 的系统内存，然后除以autovacuum_max_workers即可得到此值。请注意，这只是一个指导原则，应根据观察到的使用模式进行验证。
监控和调优的最佳实践
监控 Autovacuum 活动：使用pg_stat_user_tables跟踪 vacuum 并分析操作：
从 pg_stat_user_tables中选择
relname 作为 table_name、
n_dead_tup、
last_autovacuum、
last_autoanalyze ，其中 schemaname ='public'；
除了使用pg_stat_user_tables跟踪自动清理活动外，PostgreSQL 还通过pg_stat_activity和pg_stat_progress_vacuum提供实时监控功能。这些视图为正在进行的维护操作提供了宝贵的见解：
- pg_stat_activity ：此视图显示数据库中所有活动进程，包括自动清理工作进程。您可以使用以下查询筛选自动清理进程：
从 pg_stat_activity 中选择 pid、state、query、query_start，
其中查询类似于'％autovacuum％'，
按 query_start DESC 排序；复制到剪贴板
此查询有助于识别长时间运行的自动清理任务及其相关表。
- pg_stat_progress_vacuum ：此视图提供有关活动真空操作（包括自动真空）的详细进度信息。例如：
从 pg_stat_progress_vacuum 中选择 pid、
relname、
phase、
heap_blks_total、
heap_blks_scanned、
heap_blks_vacuumed ；
此输出突出显示清理过程的当前阶段以及已处理的表的百分比。它对于监控自动清理可能需要大量时间的大型表特别有用。
通过结合这些视图，您可以更深入地了解自动真空操作并主动解决性能瓶颈。
调整自动清理配置时，请牢记以下准则：
- 迭代调整：从保守值开始，并根据观察到的性能、死元组积累和成本效率进行调整。
- 使用日志记录：启用log_autovacuum_min_duration来识别具有频繁或长时间运行的自动清理过程的表，帮助查明成本密集型操作。
- 平衡内存使用情况：确保autovacuum_work_mem和其他内存参数（例如shared_buffers 、work_mem ）适合总系统内存，从而优化经济高效的性能。
物理磁盘空间恢复注意事项
Autovacuum 通过将死元组标记为表中的可重复使用空间来优化数据库性能，但它不会减少磁盘上表的物理大小。随着时间的推移，特别是在高写入环境中，如果旧数据未被物理删除，表仍然可能膨胀。
要回收物理磁盘空间，您需要执行如下操作：
- VACUUM FULL ：重写整个表，压缩并减少其物理大小。但是，它会在操作期间锁定表，因此不适合生产中经常访问的表。
- pg_repack ：一种扩展，允许通过创建表的新副本并将其交换到位来进行表压缩而无需长锁。这是VACUUM FULL的更适合生产的替代方案。
了解自动真空的逻辑空间重用与这些手动操作之间的区别可确保有效的数据库维护，同时平衡停机时间和资源使用情况。
结论
Autovacuum 对于维护 PostgreSQL 性能和控制运营成本至关重要。通过适当的配置，您可以最大限度地减少表膨胀，优化查询性能并减少资源浪费。通过了解每个参数、监控活动和反复微调设置，您可以确保 PostgreSQL 数据库得到良好维护，从而平衡性能和成本效率。
实施这些最佳实践，以释放 PostgreSQL 的全部潜力，同时控制运营成本。
原文地址：https://stormatics.tech/blogs/leveraging-autovacuum-in-postgresql-to-optimize-performance-and-reduce-costs
原文作者：Umair Shahid
