postgresql - postgresql/Vacuum 中大量的活/死元组不起作用

Question

有一个表，它有 200 行。但是显示的事件元组数量不止于此(大约 60K)。

select count(*) from subscriber_offset_manager;
 count 
-------
   200
(1 row)


 SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |      61453 |          5
(1 row)

但是从 pg_stat_activity 和 pg_locks 可以看出，我们无法跟踪任何打开的连接。

SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity
  USING (pid)
WHERE relation::regclass = 'subscriber_offset_manager'::regclass
  ;
 query | state | locktype | mode 
-------+-------+----------+------
(0 rows)

我也在这张 table 上试过全真空，结果如下:

• 始终没有行被删除
• 有时所有的活元组都会变成死元组。

这是输出。

vacuum FULL VERBOSE ANALYZE subscriber_offset_manager;
INFO:  vacuuming "public.subscriber_offset_manager"
INFO:  "subscriber_offset_manager": found 0 removable, 67920 nonremovable row versions in 714 pages
DETAIL:  67720 dead row versions cannot be removed yet.
CPU 0.01s/0.06u sec elapsed 0.13 sec.
INFO:  analyzing "public.subscriber_offset_manager"
INFO:  "subscriber_offset_manager": scanned 710 of 710 pages, containing 200 live rows and 67720 dead rows; 200 rows in sample, 200 estimated total rows
VACUUM

 SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |        200 |      67749

10秒后

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |      68325 |        132

我们的应用程序如何查询此表。

• 我们的应用程序一般选择一些行，并根据一些业务计算，更新行。

  select query -- 根据一些id进行选择

  select * from subscriber_offset_manager where shard_id=1 ;

  更新查询 -- 为这个选定的分片 ID 更新一些其他列

• 大约 20 个线程并行执行此操作，一个线程仅处理一行。

• 应用程序是用 java 编写的，我们使用 hibernate 进行数据库操作。
• Postgresql 版本为 9.3.24

另一个有趣的观察: - 当我停止我的 java 应用程序然后进行完全真空时，它工作正常(行数和事件元组数变得相等)。因此，如果我们从 java app 不断选择和更新，就会出现问题。 –

问题/问题

这些活元组有时会变成死元组，有时又会活过来。

由于上述行为从表中选择需要时间并增加服务器上的负载，因为那里有很多活/死元组..

Answer 1

我知道阻止 VACUUM 完成其工作的三件事:

• 长时间运行的事务。

• 未提交的已准备事务。

• 过时的复制槽。

参见 my blog post了解详情。