PostgreSQL VACUUM 和 ANALYZE 最佳实践技巧

最新推荐文章于 2024-08-06 09:48:29 发布

TracyGao01

最新推荐文章于 2024-08-06 09:48:29 发布

阅读量8.3k

点赞数 4

分类专栏： Postgresql 文章标签： postgresql 数据库

原文链接：https://mp.weixin.qq.com/s/jJTdnuHyCrfaY7qY82SPGw

版权

Postgresql 专栏收录该内容

42 篇文章 8 订阅

订阅专栏

文章目录

简介

VACUUM 和 ANALYZE 是两个最重要的 PostgreSQL 数据库维护操作。
VACUUM用于恢复表中“死元组”占用的空间。当记录被删除或更新（删除后插入）时，会创建一个死元组。PostgreSQL 不会从表中物理删除旧行，而是在其上放置一个“标记”，以便查询不会返回该行。当vacuum进程运行时，这些死元组占用的空间被标记为可被其他元组重用。
“analyze”操作正如其名——它分析数据库表的内容并收集有关每个表的每一列中值分布的统计信息。PostgreSQL 查询引擎使用这些统计信息来寻找最佳查询计划。随着在数据库中插入、删除和更新行，列统计信息也会发生变化。ANALYZE——要么由 DBA 手动运行，要么在 autovacuum 后由 PostgreSQL 自动运行——确保统计数据是最新的。
虽然听起来相对简单，但vacuum和analyze是两个复杂的过程。幸运的是，DBA 不必太担心他们的内部结构。但是，他们经常对手动运行这些进程或设置配置参数的最佳值感到困惑。
在本文中，我们将分享一些 VACUUM 和 ANALYZE 的最佳实践。

技巧 1：不要无故运行手动 VACUUM 或 ANALYZE

PostgreSQL 清理（自动清理或手动清理）可最大限度地减少表膨胀并防止事务 ID 回卷。Autovacuum 不会恢复死元组占用的磁盘空间。但是，运行VACUUM FULL命令会这样做。不过，VACUUM FULL 有其性能含义。目标表在操作期间被独占锁定，甚至阻止对表的读取。该进程还会制作表的完整副本，这在运行时需要额外的磁盘空间。我们建议不要运行 VACUUM FULL，除非膨胀率非常高，并且查询受到严重影响。我们还建议使用最低数据库活动期。
最好不要在整个数据库上过于频繁地运行手动清理；目标数据库可能已经通过 autovacuum 过程进行了最佳清理。因此，手动清理可能不会删除任何死元组，但会导致不必要的 I/O 负载或 CPU 峰值。如有必要，手动清理应仅在需要时逐表运行，例如活动行与死行的比率较低，或自动清理之间的间隙较大。此外，应在用户活动最少时进行手动vacuum。
Autovacuum 还使表的数据分布统计信息保持最新（它不会重建它们）。手动运行时，ANALYZE命令实际上重建这些统计信息而不是更新它们。同样，当统计信息已经通过常规自动清理进行最佳更新时重建统计信息可能会对系统资源造成不必要的压力。
您必须手动运行 ANALYZE 的时间是在将数据批量加载到目标表之后。现有表中的大量（甚至数百）新行将显着扭曲其列数据分布。新行将导致任何现有列统计信息过时。当查询优化器使用此类统计信息时，查询性能可能会非常缓慢。在这些情况下，在数据加载后立即运行 ANALYZE 命令以完全重建统计信息是比等待自动清理启动更好的选择。

技巧 2：微调 Autovacuum 阈值

检查或调整 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 时，该表将被自动清理。

技巧 3：微调自动分析阈值

与 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 Worker

DBA 经常忽略的另一个参数是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_workers
autovacuum 线程完成的工作成本是使用三个参数计算的：
 - 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>)