我在 Heroku 上托管一个采用标准 0 计划的 Postgresql 数据库。
我的数据库有一个名为 的表transactions
,其中包含约 1800 万行:
SELECT COUNT(*) FROM transactions;
count
----------
17927768
(1 row)
在过去的几个月里,我注意到数据库变得越来越慢。我现在正处于应用程序超时的状态,因为(即使是简单的)查询花费的时间超过 30 秒。
在试图找出发生了什么时,我发现了一些奇怪的事情:
在托管服务器上,一个简单的查询如下:
EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
Finalize Aggregate (cost=405691.73..405691.74 rows=1 width=8) (actual time=34941.061..34961.256
rows=1 loops=1)
-> Gather (cost=405691.63..405691.73 rows=1 width=8) (actual time=34940.913..34961.247 rows=2
loops=1)
Workers Planned: 1
Workers Launched: 1
-> Partial Aggregate (cost=404691.63..404691.63 rows=1 width=8) (actual time=34924.080.
.34924.081 rows=1 loops=2)
-> Parallel Seq Scan on transactions (cost=0.00..400083.56 rows=9216145 width=0)
(actual time=77.981..34179.970 rows=7801236 loops=2)
Filter: (partner_id = 1)
Rows Removed by Filter: 1164606
Planning Time: 0.755 ms
JIT:
Functions: 10
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1.912 ms, Inlining 0.000 ms, Optimization 30.606 ms, Emission 119.538 ms, To
tal 152.057 ms
Execution Time: 35190.328 ms
(14 rows)
最多需要35 秒。
但是,当我将生产转储下载到我的机器(较旧的 thinkpad)时,查询只需要不到一秒的时间:
EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
Finalize Aggregate (cost=251757.89..251757.90 rows=1 width=8)
(actual time=669.234..674.362 rows=1 loops=1)
-> Gather (cost=251757.67..251757.88 rows=2 width=8) (actua
l time=669.008..674.348 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=250757.67..250757.68 rows=
1 width=8) (actual time=638.447..638.448 rows=1 loops=3)
-> Parallel Index Only Scan using index_transact
ions_on_partner_id on transactions (cost=0.44..234528.06 rows=6
491844 width=0) (actual time=0.061..405.148 rows=5199597 loops=3
)
Index Cond: (partner_id = 1)
Heap Fetches: 0
Planning Time: 0.231 ms
JIT:
Functions: 11
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 3.109 ms, Inlining 0.000 ms, Optimization 0.826 ms, Emission 11.406 ms, Total 15.342 ms
Execution Time: 676.047 ms
(14 rows)
我们还可以看到托管的 Postgresql 使用并行 seq 扫描,而本地实例使用并行索引扫描。
这怎么可能?我需要做什么才能在托管服务器上获得接近此性能的性能?
编辑1:有关“膨胀”的更多信息
我尝试调查可能的膨胀,并收到了交易表的信息:
type | schemaname | object_name | bloat | waste
-------+------------+--------------+-------+------------
table | public | transactions | 1.3 | 571 MB
和这个:
schema | table | last_vacuum | last_autovacuum | rowcount | dead_rowcount | autovacuum_threshold | expect_autovacuum
--------+--------------------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
public | transactions | | | 17,949,072 | 600 | 3,589,864 |
这些查询由 Herokus 内置工具生成,用于分析膨胀,如此处所述。
dead rowcount
与 1700 万行相比,600 行看起来可以忽略不计 - 但为什么浪费如此之多(570MB)?这可能是问题的根源吗?似乎从未进行过真空处理。
“浪费”看起来每个元组只有 32 个字节,这似乎并不是特别严重。我不会据此采取任何行动或得出任何结论。他们自己的文档说超过 10 的东西值得研究,你只在 1.3。
他们的真空统计脚本似乎已经过时了。它不包括对 autovacuum_vacuum_insert_scale_factor 的引用,这应该是驱动表清理的因素。该参数是在 v13 中实现的,专门用于满足以插入为主的表的需求,这似乎就是您所拥有的。除了从脚本中排除这一点之外,也许他们还用一些无用的东西覆盖了默认设置 0.2,这可以解释缺乏真空清理,这反过来又解释了缺乏仅索引扫描,这可能反过来解释了缓慢的原因。
手动对桌子进行真空吸尘,看看是否确实解决了问题。另外,检查 autovacuum_vacuum_insert_scale_factor 的默认设置,看看这是否解释了为什么 autovacuum 不处理表,因此需要手动真空。