我有一个接收大量数据的端点。它将其插入到一个名为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锁，而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)
3条答案
按热度按时间du7egjpx1#
我不认为你有锁定或服务器端的问题。来自文档：
ClientRead：等待从客户端读取数据。
在我看来，服务器正在等待客户端为INSERT查询提供数据。也许是网络问题？
lvmkulzt2#
这些是空闲连接。插入已完成。您看到的时间是连接空闲的时间。如果state = 'active'，则意味着查询是活动的，如果存在等待事件，则在这种情况下您正在等待。（如果没有等待事件，并且您处于活动状态，则您可以在CPU上运行）
凯尔
hec6srdp3#
ClientRead背后的原因可能是持久性上下文内存变满，垃圾收集器开始处理自己，所以它不会从数据库中获取结果。所以尝试冲洗检查这篇文章