We are getting DB connections count for temporal user above 60 (Limit is 70 per user in our setup) sometimes it goes above 100 as well, and there is a pattern to it. A lot of queries are being blocked and in long running for more than an hour and whenever I flush the blocked queries connections count decreases and comes back to normal.
Query to check long running queries:
SELECT pid,
now() - pg_stat_activity.query_start AS duration,
usename,
datname,
state,
wait_event_type,
wait_event,
query
FROM pg_stat_activity
WHERE datname = 'temporal'
AND (now() - pg_stat_activity.query_start) > interval '1 hour'
AND state != 'idle'
ORDER BY duration DESC;
Query to check long blocked queries:
SELECT a.pid, a.query, a.query_start, b.pid AS blocking_pid, b.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_locks l1 ON a.pid = l1.pid
JOIN pg_locks l2 ON l1.locktype = l2.locktype
AND l1.database IS NOT DISTINCT FROM l2.database
AND l1.relation IS NOT DISTINCT FROM l2.relation
AND l1.page IS NOT DISTINCT FROM l2.page
AND l1.tuple IS NOT DISTINCT FROM l2.tuple
AND l1.virtualxid IS NOT DISTINCT FROM l2.virtualxid
AND l1.transactionid IS NOT DISTINCT FROM l2.transactionid
AND l1.classid IS NOT DISTINCT FROM l2.classid
AND l1.objid IS NOT DISTINCT FROM l2.objid
AND l1.objsubid IS NOT DISTINCT FROM l2.objsubid
AND a.pid <> l2.pid
JOIN pg_stat_activity b ON b.pid = l2.pid
WHERE NOT l1.granted
AND a.datname = 'temporal'
AND a.pid <> pg_backend_pid();
Is this a normal behaviour? Can we solve this somehow?
We are running temporal version 1.24.3.

