我在亚马逊上有生产和登台 RDS 实例,登台的数据是生产的直接副本,因此两个实例都有重复数据。
这样做的EXPLAIN ANALYZE SELECT * from my_table WHERE my_col=true;
结果是:
Seq Scan on my_table (cost=0.00..142,775.73 rows=1 width=1,436) (actual time=18,170.294..18,170.294 rows=0 loops=1) Filter: my_col Rows Removed by Filter: 360275
在生产中,它是:
Seq Scan on my_table (cost=0.00..62,145.88 rows=1 width=1,450) (actual time=282.487..282.487 rows=0 loops=1) Filter: my_col Rows Removed by Filter: 366442
跑步时select pg_total_relation_size('my_table'::regclass);
我发现舞台的大小几乎是制作的两倍。根据我的阅读,我看到 postgresql 的 MVCC 对此负责,因为它保留了多个版本的行。我手动运行VACUUM FULL
,然后看到舞台的大小减少了 2/3。现在运行相同的解释分析显示:
Seq Scan on my_table (cost=0.00..56094.75 rows=1 width=1436) (actual time=1987.340..1987.340 rows=0 loops=1) Filter: my_col Rows Removed by Filter: 360287 Total runtime: 1987.547 ms
这很好——但我不明白的是,文档建议自动吸尘器应该启动并清理这些死行,但显然这并没有发生。
我读过几个地方谈论“不要让你的索引膨胀”,我不太明白 1)索引如何膨胀,2)如何防止索引膨胀。
我怎样才能防止这种情况在未来再次发生?
更新
这是我的自动真空设置:
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline
-------------------------------------+-----------+------+------------+-------------------------------------------------------------------------------------------+------------+------------+---------+---------+-----------+------------+----------+-----------+-----------+------------+------------
autovacuum | on | | Autovacuum | Starts the autovacuum subprocess. | | sighup | bool | default | | | | on | on | |
autovacuum_analyze_scale_factor | 0.1 | | Autovacuum | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. | | sighup | real | default | 0 | 100 | | 0.1 | 0.1 | |
autovacuum_analyze_threshold | 50 | | Autovacuum | Minimum number of tuple inserts, updates, or deletes prior to analyze. | | sighup | integer | default | 0 | 2147483647 | | 50 | 50 | |
autovacuum_freeze_max_age | 200000000 | | Autovacuum | Age at which to autovacuum a table to prevent transaction ID wraparound. | | postmaster | integer | default | 100000000 | 2000000000 | | 200000000 | 200000000 | |
autovacuum_max_workers | 3 | | Autovacuum | Sets the maximum number of simultaneously running autovacuum worker processes. | | postmaster | integer | default | 1 | 8388607 | | 3 | 3 | |
autovacuum_multixact_freeze_max_age | 400000000 | | Autovacuum | Multixact age at which to autovacuum a table to prevent multixact wraparound. | | postmaster | integer | default | 10000000 | 2000000000 | | 400000000 | 400000000 | |
autovacuum_naptime | 60 | s | Autovacuum | Time to sleep between autovacuum runs. | | sighup | integer | default | 1 | 2147483 | | 60 | 60 | |
autovacuum_vacuum_cost_delay | 20 | ms | Autovacuum | Vacuum cost delay in milliseconds, for autovacuum. | | sighup | integer | default | -1 | 100 | | 20 | 20 | |
autovacuum_vacuum_cost_limit | -1 | | Autovacuum | Vacuum cost amount available before napping, for autovacuum. | | sighup | integer | default | -1 | 10000 | | -1 | -1 | |
autovacuum_vacuum_scale_factor | 0.2 | | Autovacuum | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. | | sighup | real | default | 0 | 100 | | 0.2 | 0.2 | |
autovacuum_vacuum_threshold | 50 | | Autovacuum | Minimum number of tuple updates or deletes prior to vacuum. | | sighup | integer | default | 0 | 2147483647 | | 50 | 50 | |
自动吸尘最终应该可以清理它(假设您没有禁用它),但它可能不会因为您的目的而尽快解决。有许多设置可以控制自动吸尘以及如何/何时完成,这可能很有趣:这里和这里。
对于高流失率的表尤其如此。也就是说,具有大量插入和删除的表。长时间运行和空闲的事务也可能是这里的一个因素,因为MVCC将启动并防止死元组被回收。手动执行 a
VACUUM
释放死元组的事实表明,这对您而言并非如此,它可能是前一个问题。一般来说,不建议这样做
VACUUM FULL
,因为这会取消表排他锁,特别是当表中的大多数行已被更新/删除时。从文档:
您的使用模式是否会出现这种情况?您确实提到了涉及“直接复制”,但尚不清楚具体是如何完成的。
我遇到过高流失表的情况,其中默认的自动清理率还不够,即使是相对少量的死元组也会极大地影响查询速度(这是在一个经常查询的大表中,在哪里查询需要非常快,因此受到死元组的高度影响)。
为了帮助解决这个问题,我
VACUUM ANALYZE
在设置为每 5 分钟运行一次的 cron 作业中设置了一个表手册(这样它既可以释放元组,又可以通过更新统计信息来帮助查询规划器)。由于没有那么多死元组,所以VACUUM
速度非常快,并且不断的清理使死元组计数保持在足够低的水平,从而保持对该表的快速查询。编辑以回应OP的评论:
在VACUUM 文档中,它说:
然后医生说(强调我的):
所以它肯定会回收死元组。
文件表明应该启动自动吸尘器并清理这些死行,但显然这并没有发生。
要了解 autovacuum 是否处理表,请查看
pg_catalog.pg_stat_user_tables.last_autovacuum
.您似乎认为 autovacuum 没有运行,因为手动
VACUUM FULL
缩小了表格而 autovacuum 没有。但这很正常,因为 autovacuum 不应该收缩表,它只会将死行占用的空间标记为可重用。1) 索引如何膨胀,以及 2) 如何防止索引膨胀。
一旦发生写入,索引膨胀就会发生,这是无法防止的。需要检查的是膨胀是否无限增长或保持稳定以及它有多严重。
postgres wiki 提供了一个查询: https ://wiki.postgresql.org/wiki/Show_database_bloat