Postgresql Alter Table 卡住 DB(CPU 被某些 Select 高负载)

Question

PostgreSQL 9.6

我愿意

ALTER TABLE "Users"
   ADD COLUMN "someField" BOOLEAN NULL DEFAULT NULL;

数据库卡住超过 10 分钟。

表"Users"有 5,000 行，但数据库非常大(150+ Gb)。

马上，20+ SELECT用户表上的查询出现，(检查):

SELECT query FROM pg_stat_activity
WHERE state = 'active' and query LIKE 'SELECT%'

(之前，没有查询)

这些 SELECT查询占用所有 CPU。

我尝试重新启动数据库并尝试执行 VACUUM ANALYZE在 "Users"表:

INFO:  vacuuming "public.Users"
INFO:  index "Users_pkey" now contains 5556 row versions in 86 pages
DETAIL:  0 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.12 sec.
INFO:  index "users_subscription_canceled" now contains 5028 row versions in 508 pages
DETAIL:  0 index row versions were removed.
8 index pages have been deleted, 8 are currently reusable.
CPU 0.00s/0.00u sec elapsed 1.54 sec.
INFO:  index "users_shopper_id" now contains 5556 row versions in 205 pages
DETAIL:  0 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.64 sec.
INFO:  index "users_referrer" now contains 5556 row versions in 586 pages
DETAIL:  0 index row versions were removed.
4 index pages have been deleted, 4 are currently reusable.
CPU 0.00s/0.00u sec elapsed 1.80 sec.
INFO:  index "users_referral_code" now contains 5556 row versions in 84 pages
DETAIL:  0 index row versions were removed.
2 index pages have been deleted, 2 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.27 sec.
INFO:  "Users": found 0 removable, 2137 nonremovable row versions in 803 out of 2780 pages
DETAIL:  445 dead row versions cannot be removed yet.
There were 25647 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 6.91 sec.
INFO:  "Users": stopping truncate due to conflicting lock request
INFO:  vacuuming "pg_toast.pg_toast_26460"
INFO:  index "pg_toast_26460_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_26460": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.Users"
INFO:  "Users": scanned 2780 of 2780 pages, containing 5539 live rows and 459 dead rows; 5539 rows in sample, 5539 estimated total rows
VACUUM

如果我这样做 DROP COLUMN或 RENAME COLUMN ，同样的第 4 点和第 5 点发生 - 并且数据库卡住。

问题:

这是数据库的某种错误吗？添加 Nullable 字段应该非常快。

任何建议都非常受欢迎，我厌倦了谷歌搜索和调试:)

Answer 1

这是正常行为；问题是你的工作量。

你说得对ALTER TABLE ... ADD COLUMN是一个非常快的操作。那不是问题。问题是这样的ALTER TABLE需要短ACCESS EXCLUSIVE锁定表，因为它修改了表结构。

这样的ACCESS EXCLUSIVE锁与 ACCESS SHARE 不兼容锁定一个 SELECT声明放在 table 上。这就是目的:应该如何SELECT如果表在运行时发生更改，则语句的行为？

现在的问题是您的查询需要很长时间，或者有人忘记关闭对表有锁定的事务。

你可以检查这个

SELECT pid, a.state, a.xact_start
FROM pg_locks AS l
   JOIN pg_stat_activity AS a USING (pid)
WHERE l.relation = 'Users'::regclass;

这将显示所有在表上有锁定的事务以及它们何时开始。

现在您的 ALTER TABLE必须等到所有这些交易完成，以及所有的短 SELECT稍后发出的语句必须排在 ALTER TABLE 后面。 .

尽快 ALTER TABLE获得它需要的锁，它会很快完成并释放表上的锁。现在，所有其他排队的语句将同时松散，并在您的机器上造成高负载。

解决方案由两部分组成:

修复应用程序，使其立即关闭事务。

减少 max_connections尽可能使用连接池。那么可以被阻塞的语句数量是有限的，机器过载的危险就更小了。