database - 插入和更新在 postgres 的表上被阻止

Question

我们最近遇到了这样一种情况，即 Postgres 中一个特定表上的 DML（我们只使用插入和更新）不时被阻塞。所有其他表上的查询运行得很好，尽管由于增加了负载而增加了一些延迟。

我们注意到 autovacuum 在与相关表相关的 toasts 表中的某些期间处于活动状态。

在其中一次，我们试图查看所有被卡住的查询，我们可以看到该特定表上的所有更新和插入都在等待彼此：

SELECT blocked_locks.pid         AS blocked_pid, 
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid        AS blocking_pid, 
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement, 
       blocking_activity.query   AS current_statement_in_blocking_process 
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid 
JOIN pg_catalog.pg_locks blocking_locks            ON blocking_locks.locktype = blocked_locks.locktype 
     AND blocking_locks.DATABASE      IS NOT DISTINCT FROM blocked_locks.DATABASE
     AND blocking_locks.relation      IS NOT DISTINCT FROM blocked_locks.relation
     AND blocking_locks.page          IS NOT DISTINCT FROM blocked_locks.page 
     AND blocking_locks.tuple         IS NOT DISTINCT FROM blocked_locks.tuple 
     AND blocking_locks.virtualxid    IS NOT DISTINCT FROM blocked_locks.virtualxid 
     AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid 
     AND blocking_locks.classid       IS NOT DISTINCT FROM blocked_locks.classid 
     AND blocking_locks.objid         IS NOT DISTINCT FROM blocked_locks.objid 
     AND blocking_locks.objsubid      IS NOT DISTINCT FROM blocked_locks.objsubid
     AND blocking_locks.pid != blocked_locks.pid 
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid 
WHERE NOT blocked_locks.GRANTED;

结果：

-[ RECORD 1 ]-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
blocked_pid                           | 28526
blocked_user                          | user
blocking_pid                          | 29151
blocking_user                         | user
blocked_statement                     | UPDATE "table_in_question" SET "response" = $1, "status_id" = $2, "updated_at" = $3 WHERE "table_in_question"."id" = $4 current_statement_in_blocking_process | INSERT INTO "table_in_question" ("params_json", "test_email", "callback_url", "mail_templates_version_id", "template_name", "queue_time", "send_time", "email_ids", "status_id", "created_at", "updated_at", "uuid") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING "id"
-[ RECORD 2 ]-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
blocked_pid                           | 28526
blocked_user                          | user
blocking_pid                          | 31778
blocking_user                         | user
blocked_statement                     | UPDATE "table_in_question" SET "response" = $1, "status_id" = $2, "updated_at" = $3 WHERE "table_in_question"."id" = $4 current_statement_in_blocking_process | UPDATE "table_in_question" SET "params_json" = $1, "updated_at" = $2 WHERE "table_in_question"."id" = $3

查询似乎被彼此阻止而不是 autovacuum。虽然，我在这里没有看到任何自然循环的东西。

有问题的表是数据库中较大的表之一。尽管考虑到今天的标准，这并不算太大。

SELECT relname as "Table", 
       pg_size_pretty(pg_total_relation_size(relid)) As "Size",
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" 
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

结果：

Table              |  Size   | External Size 
---------------------------------+---------+---------------
table_in_question  | 37 GB   | 10 GB

Postgres 版本：

select version();
     version                                                  
------------------------
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

标签： databasepostgresql