如何在Postgresql ClientRead pg_stat_activity中查找wait_event的原因？
如何在Postgresql ClientRead pg_stat_activity中查找wait_event的原因？
提问于 2021-08-04 10:35:56
我有一个端点，它接收大量的数据。它把它插入到一个叫做入口的桌子里。我还编写了几个解析器，它们循环遍历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)回答 2
Stack Overflow用户
发布于 2021-08-04 11:07:30
Stack Overflow用户
发布于 2022-06-07 00:23:37
这些都是闲置的连接。插入已经完成。您看到的时间是连接空闲的时间。如果state = ' active‘，则它意味着查询是活动的，如果存在等待事件，则在这种情况下您正在等待。(如果没有等待事件，并且您是活动的，那么您就可以在CPU上运行)
凯尔
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68649726
复制相关文章
相似问题
腾讯云开发者
