崔鹏，海能达PostgreSQL高级DBA,PG爱好者。
吴聪，PostgreSQL技术爱好者。熟悉oracle、PostgreSQL、mysql等数据库，现主要从事oracle和Greenplum相关的工作。
VACUUM 和 ANALYZE 是两个最重要的 PostgreSQL 数据库维护操作。VACUUM用于恢复表中“死元组”占用的空间。当记录被删除或更新（删除后插入）时，会创建一个死元组。PostgreSQL 不会从表中物理删除旧行，而是在其上放置一个“标记”，以便查询不会返回该行。当vacuum进程运行时，这些死元组占用的空间被标记为可被其他元组重用。“analyze”操作正如其名——它分析数据库表的内容并收集有关每个表的每一列中值分布的统计信息。PostgreSQL 查询引擎使用这些统计信息来寻找最佳查询计划。随着在数据库中插入、删除和更新行，列统计信息也会发生变化。ANALYZE——要么由 DBA 手动运行，要么在 autovacuum 后由 PostgreSQL 自动运行——确保统计数据是最新的。虽然听起来相对简单，但vacuum和analyze是两个复杂的过程。幸运的是，DBA 不必太担心他们的内部结构。但是，他们经常对手动运行这些进程或设置配置参数的最佳值感到困惑。在本文中，我们将分享一些 VACUUM 和 ANALYZE 的最佳实践。提示 1：不要无故运行手动 VACUUM 或 ANALYZEPostgreSQL 清理（自动清理或手动清理）可最大限度地减少表膨胀并防止事务 ID 回卷。Autovacuum 不会恢复死元组占用的磁盘空间。但是，运行VACUUM FULL命令会这样做。不过，VACUUM FULL 有其性能含义。目标表在操作期间被独占锁定，甚至阻止对表的读取。该进程还会制作表的完整副本，这在运行时需要额外的磁盘空间。我们建议不要运行 VACUUM FULL，除非膨胀率非常高，并且查询受到严重影响。我们还建议使用最低数据库活动期。最好不要在整个数据库上过于频繁地运行手动清理；目标数据库可能已经通过 autovacuum 过程进行了最佳清理。因此，手动清理可能不会删除任何死元组，但会导致不必要的 I/O 负载或 CPU 峰值。如有必要，手动清理应仅在需要时逐表运行，例如活动行与死行的比率较低，或自动清理之间的间隙较大。此外，应在用户活动最少时进行手动vacuum。Autovacuum 还使表的数据分布统计信息保持最新（它不会重建它们）。手动运行时，ANALYZE命令实际上重建这些统计信息而不是更新它们。同样，当统计信息已经通过常规自动清理进行最佳更新时重建统计信息可能会对系统资源造成不必要的压力。您必须手动运行 ANALYZE 的时间是在将数据批量加载到目标表之后。现有表中的大量（甚至数百）新行将显着扭曲其列数据分布。新行将导致任何现有列统计信息过时。当查询优化器使用此类统计信息时，查询性能可能会非常缓慢。在这些情况下，在数据加载后立即运行 ANALYZE 命令以完全重建统计信息是比等待自动清理启动更好的选择。检查或调整 autovacuum 并分析postgresql.conf文件或单个表属性中的配置参数以在 autovacuum 和性能增益之间取得平衡至关重要。PostgreSQL 使用两个配置参数来决定何时启动自动清理：•autovacuum_vacuum_threshold ：默认值为 50•autovacuum_vacuum_scale_factor ：默认值为 0.2当表中的死行数超过该表中的行数乘以比例因子加上vacuum阈值时，这些参数告诉 PostgreSQL 启动自动清理。换句话说，PostgreSQL 将在以下情况下对表启动 autovacuum：pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor ) + autovacuum_vacuum_threshold对于中小型表，这可能就足够了。例如，一个有 10,000 行的表，在自动清理开始之前，死行的数量必须超过 2,050 ((10,000 x 0.2) + 50)。并非数据库中的每个表都会经历相同的数据修改率。通常，一些大表会频繁修改数据，因此会出现更多的死行。默认值可能不适用于此类表。例如，使用默认值，一个有 100 万行的表在自动清理开始之前需要有超过 200,050 个死行 ((1000,000 x 0.2) + 50)。这可能意味着 autovacuum 之间的间隔更长，autovacuum 时间越来越长，更糟糕的是，如果 table 上的活动事务正在锁定它，autovacuum 根本不会运行。因此，目标应该是将这些阈值设置为最佳值，以便自动清理可以定期发生并且不会花费很长时间（并影响用户会话），同时保持死行数相对较低。一种方法是使用一个或另一个参数。因此，如果我们将 autovacuum_vacuum_scale_factor 设置为 0，而是将 autovacuum_vacuum_threshold 设置为 5,000，那么当一个表的死行数超过 5,000 时，该表将被自动清理。与 autovacuum 类似，autoanalyze 也使用两个参数来决定 autovacuum 何时也触发自动分析：•autovacuum_analyze_threshold ：默认值为 50•autovacuum_analyze_scale_factor ：默认值为 0.1与 autovacuum 一样，autovacuum_analyze_threshold 参数可以设置为一个值，该值指示在自动分析开始之前表中插入、删除或更新的元组数。我们建议在大型和高事务表上单独设置此参数。表配置将覆盖 postgresql.conf 值。下面的代码片段显示了用于修改表的 autovacuum_analyze_threshold 设置的 SQL 语法。ALTER TABLE <table_name> SET (autovacuum_analyze_threshold = <threshold rows>)技巧 4：微调 Autovacuum WorkerDBA 经常忽略的另一个参数是autovacuum_max_workers ，它的默认值为 3。Autovacuum 不是单个进程，而是多个并行运行的独立vacuum线程。指定多个 worker 的原因是确保清理大表不会阻碍清理较小的表和用户会话。autovacuum_max_workers 参数告诉 PostgreSQL 增加 autovacuum 工作线程的数量来进行清理。PostgreSQL DBA 的一个常见做法是增加最大工作线程的数量，以期加快 autovacuum 的速度。这不起作用，因为所有线程共享相同的autovacuum_vacuum_cost_limit，其默认值为 200。使用以下公式为每个 autovacuum 线程分配一个成本限制：单个线程的 cost_limit = autovacuum_vacuum_cost_limit autovacuum_max_workersautovacuum 线程完成的工作成本是使用三个参数计算的：•vacuum_cost_page_hit : 默认值为 1•vacuum_cost_page_miss：默认值为 10•vacuum_cost_page_dirty : 默认值为 20•当一个vacuum线程在共享缓冲区中找到它应该清理的数据页时，成本为 1。•如果数据页不在共享缓冲区中，而是在操作系统缓存中，则成本为 10。•如果由于vacuum线程必须删除死行而必须将页面标记为脏，则成本将为 20。工作线程数量的增加将降低每个线程的成本限制。由于每个线程被分配了一个较低的成本限制，它会更频繁地进入睡眠状态，因为很容易达到成本阈值，最终导致整个vacuum过程运行缓慢。我们建议将 autovacuum_vacuum_cost_limit 增加到更高的值，例如 2000，然后调整最大工作线程数。更好的方法是仅在必要时为单个表调整这些参数。例如，如果大型事务表的 autovacuum 耗时太长，则该表可能会临时配置为使用自己的vacuum 成本限制和成本延迟。成本限制和延迟将覆盖在 postgresql.conf 中设置的系统范围的值。ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>)ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>)使用第一个参数将确保分配给表的 autovacuum 线程在进入睡眠之前执行更多工作。降低autovacuum_vacuum_cost_delay也意味着线程休眠的时间更少。如您所见，更改vacuum和analyze的配置参数很简单，但首先需要仔细观察。每个数据库在大小、流量模式和事务率方面都不同。我们建议 DBA 在更改参数或推出手动vacuum/analyze机制之前先收集有关其数据库的足够信息。此类信息可能是：· 手动vacuum/analyze后相同查询的性能从这里，DBA 可以选择几个“试点”表开始优化。他们可以开始更改表的vacuum/analyze属性并检查性能。PostgreSQL 是一个智能的数据库引擎——DBA 通常会发现最好让 PostgreSQL 进行清理和分析，而不是手动进行。PostgreSQL中文社区欢迎广大技术人员投稿
投稿邮箱：press@postgres.cn
最后修改时间：2021-09-30 06:19:57
文章转载自
PostgreSQL中文社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
