我有一个接收大量数据的端点。它将数据插入名为ingress的表中。我还编写了几个解析器,循环遍历ingress表中的消息并将其解析到其他各种表中。
我们的Postgres集群最近一直存在着性能问题,我无法找到原因。所以我首先开始查看pg_stat_activity
表,以查看哪些查询需要花费很长时间。在那里,我找到了这个:
postgres=> select pid, query_start, age(clock_timestamp(), query_start) as age, state, wait_event_type, wait_event, LEFT(query, 40)
from pg_stat_activity where state like '%idle%' and datname = 'mydatabase'
order by query_start asc limit 5;
pid | query_start | age | state | wait_event_type | wait_event | left
------+-------------------------------+-----------------+-------+-----------------+------------+------------------------------------------
9429 | 2021-08-04 12:20:55.790618+02 | 00:05:29.874102 | idle | Client | ClientRead | INSERT INTO "ingress_message" ("created_
9551 | 2021-08-04 12:21:42.384146+02 | 00:04:43.280586 | idle | Client | ClientRead | INSERT INTO "ingress_message" ("created_
9776 | 2021-08-04 12:23:37.849208+02 | 00:02:47.815526 | idle | Client | ClientRead | select 1
9922 | 2021-08-04 12:25:02.207894+02 | 00:01:23.456841 | idle | Client | ClientRead | INSERT INTO "ingress_message" ("created_
9891 | 2021-08-04 12:25:02.378745+02 | 00:01:23.285992 | idle | Client | ClientRead | INSERT INTO "ingress_message" ("created_
(5 rows)
您可以看到,最长运行的查询已经运行了5分钟以上(!!),并且由“ClientRead”引起。这对我来说似乎很奇怪。为什么读取会阻止写入?或者这是否意味着某个进程正在锁定整个表格进行读取?
我检查了pg_locks表,它向我展示了下面的结果。我一直在研究pg_locks,但我无法真正理解这些信息告诉我的内容。
我主要想知道的是:我能否找出哪个查询导致了ClientRead锁定,从而导致插入操作如此缓慢?
postgres=> select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fas
tpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----
------
relation | 82586 | 11645 | | | | | | | | 93/129 | 3764 | AccessShareLock | t | t
virtualxid | | | | | 93/129 | | | | | 93/129 | 3764 | ExclusiveLock | t | t
relation | 82586 | 22442205 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 9898413 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 9898449 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 12134578 | | | | | | | | 42/6323 | 3141 | AccessShareLock | t | t
relation | 82586 | 12134578 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 12103296 | | | | | | | | 42/6323 | 3141 | AccessShareLock | t | t
relation | 82586 | 12103296 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 12103295 | | | | | | | | 42/6323 | 3141 | AccessShareLock | t | t
relation | 82586 | 12103295 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 12102372 | | | | | | | | 42/6323 | 3141 | AccessShareLock | t | t
relation | 82586 | 12102372 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 12102338 | | | | | | | | 42/6323 | 3141 | AccessShareLock | t | t
relation | 82586 | 12102338 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
relation | 82586 | 12102331 | | | | | | | | 42/6323 | 3141 | AccessShareLock | t | t
relation | 82586 | 12102331 | | | | | | | | 42/6323 | 3141 | RowShareLock | t | t
relation | 82586 | 12102331 | | | | | | | | 42/6323 | 3141 | RowExclusiveLock | t | t
virtualxid | | | | | 42/6323 | | | | | 42/6323 | 3141 | ExclusiveLock | t | t
relation | 19825 | 12429831 | | | | | | | | 31/7218 | 3128 | AccessShareLock | t | t
relation | 19825 | 12429819 | | | | | | | | 31/7218 | 3128 | AccessShareLock | t | t
relation | 19825 | 12429818 | | | | | | | | 31/7218 | 3128 | AccessShareLock | t | t
relation | 19825 | 12429770 | | | | | | | | 31/7218 | 3128 | AccessShareLock | t | t
relation | 19825 | 12429751 | | | | | | | | 31/7218 | 3128 | AccessShareLock | t | t
relation | 19825 | 12429744 | | | | | | | | 31/7218 | 3128 | AccessShareLock | t | t
virtualxid | | | | | 31/7218 | | | | | 31/7218 | 3128 | ExclusiveLock | t | t
relation | 19825 | 12429831 | | | | | | | | 30/6218 | 3127 | AccessShareLock | t | t
relation | 19825 | 12429819 | | | | | | | | 30/6218 | 3127 | AccessShareLock | t | t
relation | 19825 | 12429818 | | | | | | | | 30/6218 | 3127 | AccessShareLock | t | t
relation | 19825 | 12429770 | | | | | | | | 30/6218 | 3127 | AccessShareLock | t | t
relation | 19825 | 12429751 | | | | | | | | 30/6218 | 3127 | AccessShareLock | t | t
relation | 19825 | 12429744 | | | | | | | | 30/6218 | 3127 | AccessShareLock | t | t
virtualxid | | | | | 30/6218 | | | | | 30/6218 | 3127 | ExclusiveLock | t | t
relation | 19825 | 12429831 | | | | | | | | 29/5284 | 3126 | AccessShareLock | t | t
relation | 19825 | 12429819 | | | | | | | | 29/5284 | 3126 | AccessShareLock | t | t
relation | 19825 | 12429818 | | | | | | | | 29/5284 | 3126 | AccessShareLock | t | t
relation | 19825 | 12429770 | | | | | | | | 29/5284 | 3126 | AccessShareLock | t | t
relation | 19825 | 12429751 | | | | | | | | 29/5284 | 3126 | AccessShareLock | t | t
relation | 19825 | 12429744 | | | | | | | | 29/5284 | 3126 | AccessShareLock | t | t
virtualxid | | | | | 29/5284 | | | | | 29/5284 | 3126 | ExclusiveLock | t | t
relation | 19825 | 12429831 | | | | | | | | 28/5964 | 3123 | AccessShareLock | t | t
relation | 19825 | 12429819 | | | | | | | | 28/5964 | 3123 | AccessShareLock | t | t
relation | 19825 | 12429818 | | | | | | | | 28/5964 | 3123 | AccessShareLock | t | t
relation | 19825 | 12429770 | | | | | | | | 28/5964 | 3123 | AccessShareLock | t | t
relation | 19825 | 12429751 | | | | | | | | 28/5964 | 3123 | AccessShareLock | t | t
relation | 19825 | 12429744 | | | | | | | | 28/5964 | 3123 | AccessShareLock | t | t
virtualxid | | | | | 28/5964 | | | | | 28/5964 | 3123 | ExclusiveLock | t | t
relation | 29251 | 31001 | | | | | | | | 71/415 | 10820 | AccessShareLock | t | t
relation | 29251 | 30988 | | | | | | | | 71/415 | 10820 | AccessShareLock | t | t
relation | 29251 | 30981 | | | | | | | | 71/415 | 10820 | AccessShareLock | t | t
virtualxid | | | | | 71/415 | | | | | 71/415 | 10820 | ExclusiveLock | t | t
relation | 29251 | 30981 | | | | | | | | 72/645 | 10821 | AccessShareLock | t | t
virtualxid | | | | | 72/645 | | | | | 72/645 | 10821 | ExclusiveLock | t | t
transactionid | | | | | | 1762434479 | | | | 42/6323 | 3141 | ExclusiveLock | t | f
(54 rows)