死元组会损害PostgreSQL数据库的性能，因为它们会继续占用空间并导致膨胀。我们在较早的博客文章中介绍了VACUUM和膨胀。但是，现在是时候看看postgres的autovacuum了，以及为维护苛刻的应用程序所需的高性能PostgreSQL数据库而需要了解的内部知识。
什么是自动真空？
Autovacuum是启动PostgreSQL时自动启动的后台实用程序进程之一。如下面的日志所示，pid 2862的postmaster（父PostgreSQL进程）已使用pid 2868启动了autovacuum启动器进程。要启动autovacuum，必须将参数autovacuum设置为ON。实际上，在生产系统中，除非您100％确定自己的工作及其含义，否则不要将其设置为OFF。Shell
| 1234 | avi@percona:~$ps -eaf | egrep “/post|autovacuum”postgres 2862 1 0 Jun17 pts/0 00:00:11 /usr/pgsql-10/bin/postgres -D /var/lib/pgsql/10/datapostgres 2868 2862 0 Jun17 ? 00:00:10 postgres: autovacuum launcher process postgres 15427 4398 0 18:35 pts/1 00:00:00 grep -E –color=auto /post|autovacuum |
为什么需要自动真空？
我们需要VACUUM来删除死元组，以便该表可以将死元组所占用的空间重新用于以后的插入/更新。要了解有关死元组和膨胀的更多信息，请阅读我们 以前的博客文章。我们还需要在表上进行ANALYZE 来更新表统计信息，以便优化程序可以为SQL语句选择最佳执行计划。postgres中的自动真空负责执行真空和对表进行分析。
postgres中存在另一个称为后台的过程， Stats Collector 该过程可跟踪使用情况和活动信息。自动真空启动器使用此过程收集的信息来标识自动真空的候选表列表。PostgreSQL会识别需要清理或自动分析的表，但仅在启用了autovacuum的情况下。这样可以确保postgres能够自我修复并阻止数据库发展更多的膨胀/碎片。
在PostgreSQL中启用自动清理所需的参数是：Shell
| 12 | autovacuum = on # ( ON by default )track_counts = on # ( ON by default ) |
track_counts 由统计信息收集器使用。没有适当的设置，自动清理将无法访问候选表。
记录自动真空
最终，您可能希望记录自动真空花费更多时间的表。在这种情况下，请将参数设置 log_autovacuum_min_duration为一个值（默认为毫秒），以便将运行时间超过此值的任何自动真空记录到PostgreSQL日志文件中。这可能有助于适当调整表级别的自动真空设置。Shell
| 12 | # Setting this parameter to 0 logs every autovacuum to the log file.log_autovacuum_min_duration = ‘250ms’ # Or 1s, 1min, 1h, 1d |
这是自动抽真空和分析的示例日志Shell
| 1234567 | < 2018-08-06 07:22:35.040 EDT > LOG: automatic vacuum of table “vactest.scott.employee”: index scans: 0pages: 0 removed, 1190 remain, 0 skipped due to pins, 0 skipped frozentuples: 110008 removed, 110008 remain, 0 are dead but not yet removablebuffer usage: 2402 hits, 2 misses, 0 dirtiedavg read rate: 0.057 MB/s, avg write rate: 0.000 MB/ssystem usage: CPU 0.00s/0.02u sec elapsed 0.27 sec< 2018-08-06 07:22:35.199 EDT > LOG: automatic analyze of table “vactest.scott.employee” system usage: CPU 0.00s/0.02u sec elapsed 0.15 sec |
PostgreSQL何时在表上运行autovacuum？
如前所述，postgres中的autovacuum指自动VACUUM和ANALYZE，而不仅仅是VACUUM。根据以下数学方程式，在表格上运行自动抽真空或分析。
有效表级自动抽空阈值的计算公式为：Shell
| 1 | Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold |
通过上面的等式，可以清楚地看到，如果表中的实际死元组数超过此有效阈值，由于更新和删除，该表将成为自动真空清除的候选者。Shell
| 1 | Autovacuum ANALYZE threshold for a table = autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold |
上式表示，自上次分析以来，插入/删除/更新总数超过此阈值的任何表都可以进行自动真空分析。
让我们详细了解这些参数。
autovacuum_vacuum_scale_factor或autovacuum_analyze_scale_factor：将添加到公式中的表记录的分数。例如，值0.2等于表记录的20％。autovacuum_vacuum_threshold或autovacuum_analyze_threshold：触发自动清理所需的最少记录或dml数。
让我们考虑一个表：percona.employee，具有1000条记录和以下autovacuum参数。Shell
| 1234 | autovacuum_vacuum_scale_factor = 0.2autovacuum_vacuum_threshold = 50autovacuum_analyze_scale_factor = 0.1autovacuum_analyze_threshold = 50 |
以上述数学公式为参考，Shell
| 12 | Table : percona.employee becomes a candidate for autovacuum Vacuum when,Total number of Obsolete records = (0.2 * 1000) + 50 = 250 |
Shell
| 12 | Table : percona.employee becomes a candidate for autovacuum ANALYZE when,Total number of Inserts/Deletes/Updates = (0.1 * 1000) + 50 = 150 |
在PostgreSQL中调整Autovacuum
我们需要了解这些是全局设置。这些设置适用于实例中的所有数据库。这意味着，不管表的大小如何，如果达到上述公式，则表可以进行自动真空抽真空或分析。
这有问题吗？
考虑具有10条记录的表与具有100万条记录的表。即使具有一百万条记录的表可能会更频繁地参与事务，但是对于只有十条记录的表，自动执行吸尘或分析的频率可能会更大。
因此，PostgreSQL允许您配置绕过全局设置的单个表级自动真空设置。Shell
| 1 | ALTER TABLE scott.employee SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100); |
Shell
| 12345678 | Output Log———-avi@percona:~$psql -d perconapsql (10.4)Type “help” for help. percona=# ALTER TABLE scott.employee SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100);ALTER TABLE |
仅当存在100条以上的过时记录时，以上设置才会在表scott.employee上运行autovacuum vacuum。
我们如何确定需要调整其自动真空设置的表？
为了分别调整表的自动真空度，您必须知道某个时间间隔内表上的插入/删除/更新次数。您也可以查看postgres目录视图：pg_stat_user_tables以获得该信息。Shell
| 1234567 | percona=# SELECT n_tup_ins as “inserts”,n_tup_upd as “updates”,n_tup_del as “deletes”, n_live_tup as “live_tuples”, n_dead_tup as “dead_tuples”FROM pg_stat_user_tablesWHERE schemaname = ‘scott’ and relname = ’employee’; inserts | updates | deletes | live_tuples | dead_tuples ———+———+———+————-+————- 30 | 40 | 9 | 21 | 39(1 row) |
如上述日志所示，在一定时间间隔内对此数据进行快照应有助于您了解每个表上DML的频率。反过来，这应该可以帮助您调整各个表的自动真空设置。
一次可以运行多少个自动真空过程？
在autovacuum_max_workers 可能包含多个数据库的实例/群集中，一次运行的自动清理进程的数量不能 超过一个。Autovacuum启动器后台进程为需要真空或分析的表启动工作进程。如果有四个数据库的autovacuum_max_workers设置为3，则第4个数据库必须等待，直到现有工作进程之一空闲为止。
在启动下一个自动真空之前，它将等待 autovacuum_naptime，大多数版本的默认值为1分钟。如果您有三个数据库，则下一个自动真空等待60/3秒。因此，开始下一个自动清理之前的等待时间始终为（autovacuum_naptime / N），其中N是实例中数据库的总数。
仅增加autovacuum_max_workers是否会增加可以并行运行的autovacuum进程的数量？
没有。在接下来的几行中将对此进行更好的解释。
VACUUM IO是否密集？
自动真空可以被视为清理。如前所述，每个表有1个工作进程。 Autovacuum 从磁盘读取表的8KB（默认为block_size）页，并对包含无效元组的页进行修改/写入。这涉及读取和写入IO。因此，当在高峰交易时间内，在具有许多死元组的巨大表上运行自动清理时，这可能是IO密集型操作。为避免此问题，我们设置了一些参数，以最大程度地减少真空对IO的影响。
以下是用于调整自动真空IO的参数
- autovacuum_vacuum_cost_limit ：autovacuum可以达到的总成本限制（由所有autovacuum作业组合）。
- autovacuum_vacuum_cost_delay ：完成清理达到autovacuum_vacuum_cost_limit成本后，autovacuum会休眠许多毫秒。
- vacuum_cost_page_hit ：读取已在共享缓冲区中并且不需要读取磁盘的页面的成本。
- vacuum_cost_page_miss ：获取不在共享缓冲区中的页面的成本。
- vacuum_cost_page_dirty ：发现死元组时写入每一页的成本。
Shell
| 1234567 | Default Values for the parameters discussed above.——————————————————autovacuum_vacuum_cost_limit = -1 (So, it defaults to vacuum_cost_limit) = 200autovacuum_vacuum_cost_delay = 20msvacuum_cost_page_hit = 1vacuum_cost_page_miss = 10vacuum_cost_page_dirty = 20 |
考虑在表percona.employee上运行的autovacuum VACUUM。
让我们想象一下在一秒钟内会发生什么。（1秒= 1000毫秒）
在读取延迟为0毫秒的最佳情况下，自动唤醒可以唤醒并进入睡眠状态50次（1000毫秒/ 20毫秒），因为唤醒之间的延迟需要为20毫秒。Shell
| 1 | 1 second = 1000 milliseconds = 50 * autovacuum_vacuum_cost_delay |
由于每次读取shared_buffers中的页面相关的成本为1，因此每次唤醒可读取200页，而在50次唤醒中可读取50 * 200页。
如果在共享缓冲区中找到所有带有死元组的页面，并且autovacuum_vacuum_cost_delay为20ms，则它可以vacuum_cost_page_hit在每个回合中读取：（（200 / ）* 8）KB，需要等待autovacuum_vacuum_cost_delay一段时间。
因此，最多可以读取一个autovacuum：每秒50 * 200 * 8 KB = 78.13 MB（如果在shared_buffers中已经找到块），则将block_size视为8192字节。
如果这些块不在共享缓冲区中并且需要从磁盘中提取，则自动清理可以读取：50 *（（200 / vacuum_cost_page_miss）* 8）KB =每秒7.81 MB。
我们上面看到的所有信息都是针对读取IO的。
现在，为了从页面/块中删除死元组，写操作的开销为： vacuum_cost_page_dirty，默认情况下设置为20。
最多，自动清理可以写入/清除：50 *（（200 / vacuum_cost_page_dirty）* 8）KB =每秒3.9 MB。
通常，此成本平均分配给autovacuum_max_workers 实例中运行的所有自动 真空进程数。因此，增加 autovacuum_max_workers 可能会延迟当前正在运行的自动真空工作器的自动真空执行。并且增加 autovacuum_vacuum_cost_limit 可能会导致IO瓶颈。需要注意的重要一点是，可以通过设置单个表的存储参数来覆盖此行为，该参数随后将忽略全局设置。Shell
| 1234567891011 | postgres=# alter table percona.employee set (autovacuum_vacuum_cost_limit = 500);ALTER TABLEpostgres=# alter table percona.employee set (autovacuum_vacuum_cost_delay = 10);ALTER TABLEpostgres=# postgres=# \d+ percona.employeeTable “percona.employee”Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ——–+———+———–+———-+———+———+————–+————-id | integer | | | | plain | | Options: autovacuum_vacuum_threshold=10000, autovacuum_vacuum_cost_limit=500, autovacuum_vacuum_cost_delay=10 |
因此，在繁忙的OLTP数据库上，始终有一种策略可以在低峰值时段内对经常被DML击中的表实施手动VACUUM。在设置相关的autovacuum_ *设置后手动运行时，您可能拥有尽可能多的并行真空作业。因此，总是建议您使用预定的手动真空作业以及经过微调的自动真空设置。
您可能还喜欢
ProxySQL查询缓存可以很好地扩展并帮助您的数据库显着提高性能。但是，查询缓存并非没有局限性。阅读我们的博客以了解有关ProxySQL查询缓存，其配置，其工作方式以及当前已知限制的更多信息。
迁移到云并不会降低DBA的价值和重要性。当某些任务被自动化时，工作的其他方面（例如数据建模和数据安全性）只会增长。我们的白皮书讨论了贵公司的DBA员工如何适应新的云数据库环境等。