postgreql中的活元组/死元组数量多/真空不工作

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)

我也尝试了全真空在这张桌子上，以下是结果：

• 所有时间都没有删除行
• 有时所有活元组都变成死元组。

这是输出。

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

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

> < li>

我们的应用程序通常选择一些行，并根据一些业务计算来更新这些行。

select查询--基于某些id进行选择

从shard_id＝1的subscriber_offset_manager中选择*；

update query--为此选定的碎片id更新其他列

大约 20 个线程并行执行此操作，一个线程仅在一行上工作。

一个更有趣的观察：-当我停止我的java应用程序，然后做全真空，它工作正常（行数和活元组变得相等）。所以如果我们从java应用程序中连续选择和更新，就有问题。-

问题/问题

这些活元组有时会变成死元组，过了一段时间又会活起来。

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

Answer 1

毕竟可能存在锁，您的查询可能会误导：

SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE relation = 'subscriber_offset_manager'::regclass

pg_locks.pid 可以为 NULL，则连接将消除行。Postgres 9.3 的手册：

持有或等待此锁的服务器进程的进程ID，如果锁由准备好的事务持有，则为空

大胆强调我的。（在第 10 页中仍然相同。

这个简单的查询有什么收获吗？

SELECT * FROM pg_locks
WHERE relation = 'subscriber_offset_manager'::regclass;

这可以解释为什么VACUUM抱怨：

DETAIL:  67720 dead row versions cannot be removed yet.

这反过来会导致应用程序逻辑/查询出现问题，锁定的行数超过了需要。

我的第一个想法是长时间运行的事务，其中甚至一个简单的< code>SELECT(获得一个低级的< code>ACCESS SHARE锁)就可以阻止< code>VACUUM完成它的工作。并行的20个线程可能会连锁并无限期地锁定< code>VACUUM。让您的事务(及其锁)尽可能简短。并确保您的查询得到优化，不要锁定不必要的行。

还有一点需要注意：事务隔离级别＜code＞SERIALIZABLE＜/code＞或＜code＞REPEATABLE READ＜/code＞使＜code＞VACUUM＜/code>更难清理。默认的＜code＞READ COMMITTED＜/code＞模式限制性较小，但＜code＞VACUUM＜/code＞仍可以被阻止，如所讨论的。

相关：

• 不结束数据库事务的后果是什么
• Postgres更新…限制1
• VACUUM VERBOSE输出，不可删除的“死区版本还不能删除”

Answer 2

我把问题☺。

为了理解这个问题，请考虑以下流程：

线程1 -

< li >打开Hibernate会话 < li >对表A进行一些查询 < li >从subscriber_offset_manager中选择 < li >更新subscriber_offset_manager。 < li >关闭会话。

许多Thread-1型线程并行运行。

线程 2 -

• 这些类型的线程并行运行。
• 打开Hibernate会话
• 对表A进行一些选择查询
• 不关闭会话。（会话泄漏。）

临时解决方案-如果我使用pg_cancel_backend关闭所有由Thread-2建立的连接，则真空开始工作。

此外，我们已经重新创建了这个问题很多次，并尝试了这个解决方案，它起作用了。

现在，有以下疑问仍未得到解答。

< li >为什么postgres不显示任何与表“subscriber_offset_manager”相关的数据。 < li >如果我们使用psql对表A运行select，而不是运行Thread-2，则不会再次出现此问题。 < li >为什么postgres以这种方式使用jdbc。

还有一些令人振奋的观察：

1. 事件，如果我们在不同的会话中对“subscriber_offset_manager”运行查询，那么也会发出问题;
2. 我们在这里发现了许多实例，其中线程 2 正在处理第三个表“Table-C”并且问题即将到来
3. 所有这些类型的 OD 事务在 pg_stat_activity 中状态为“idle_in_transaction ”。

@Erwin Brandstetter和@Laurenz Albe，如果您知道有与postgres/jdbc相关的错误。

Answer 3

我知道有三件事阻碍了VACUUM的工作：

> 长时间运行的交易。

未提交的已准备好的事务。

过时的复制插槽。

有关详细信息，请参阅我的博客文章。