Deadlock (or TOO LONG query)

[tarkhil]

On ONE of my servers, I'm getting an error

cld-bi-dwh02d: Error retrieving datasource data pg_wait_sampling: canceling statement due to lock timeout CONTEXT: SQL statement " 
-- the various background processes report wait events but don't have 
-- associated queryid. Gather them all under a fake 0 dbid 
SELECT now(), COALESCE(pgss.dbid, 0) AS dbid, s.event_type, s.event, s.queryid, sum(s.count) as count FROM public.pg_wait_sampling_profile s 
-- pg_wait_sampling doesn't offer a per (userid, dbid, queryid) view, 
-- only per pid, but pid can be reused for different databases or users 
-- so we cannot deduce db or user from it. However, queryid should be 
-- unique across differet databases, so we retrieve the dbid this way. 
-- Note that the same queryid can exists for multiple entries if 
-- multiple users execute the query, so it's critical to retrieve a 
-- single row from pg_stat_statements per (dbid, queryid) 
LEFT JOIN (SELECT DISTINCT s2.dbid, s2.queryid FROM public.pg_stat_statements(false) s2 ) pgss ON pgss.queryid = s.queryid WHERE s.event_type IS NOT NULL AND s.event IS NOT NULL AND COALESCE(pgss.dbid, 0) NOT IN ( SELECT oid FROM public.powa_databases WHERE dropped IS NOT NULL ) GROUP BY pgss.dbid, s.event_type, s.event, s.queryid 
" PL/pgSQL function public.powa_wait_sampling_src(integer) line 17 at RETURN QUERY

Postgres 14.20 there (can't upgrade right now).

Exploring postgres got me

postgres=# SELECT pid, age(clock_timestamp(), query_start), usename, query, state
postgres-# FROM pg_stat_activity
postgres-# WHERE state not like 'idle%' AND query NOT ILIKE '%pg_stat_activity%'
postgres-# ORDER BY query_start desc;
   pid   |          age          | usename  |                               query                                | state
---------+-----------------------+----------+--------------------------------------------------------------------+--------
 1055561 | 1 day 15:39:54.16436  | postgres | SET search_path TO pg_catalog;SELECT public.powa_take_snapshot()   | active
 1055537 | 1 day 15:40:01.829876 | powa     | COPY (SELECT 2, * FROM public.powa_wait_sampling_src(0)) TO stdout | active
(2 rows)

What could cause the problem?

[tarkhil]

Update:

 1245242 | 00:38:18.692282 | postgres | SET search_path TO pg_catalog;SELECT public.powa_take_snapshot() | active

0% CPU usage

[rjuju]

is it a remote server? it seems that it gets both local snapshot (so powa in shared_preload_libraries) and remove snapshot (so powa collector connecting to it)

[tarkhil]

So I need to remove powa extension and remove powa from shared_preload_libraries?

[rjuju]

You definitely need the powa extension installed on all servers, both the repository and remote, in the powa-dedicated database.

Having powa in shared_preload_libraries means that you aggregate data locally for the local server (which could be either for the local server of a repository server or of a remote server), which doesn't seem like something you need (I assume you don't have a powa-web running on each remote server to get the local stats on top of the repository one). If indeed you do not want that then you should remove powa from shared_preload_libraries on all the servers for which you do not want local metrics. If you keep both then you should be prepared for occasional conflicts or deadlocks.

[tarkhil]

Okay, I'll check that, reread manuals and maybe rewrite them)

[rjuju]

any work on improving the documentation is highly welcome 😊

[tarkhil]

Well, I've removed powa from shared libraries

Unfortunately, it did not help a lot.

 1674998 | 7 days 23:10:51.167163 | powa    | COPY (SELECT 2, * FROM public.powa_wait_sampling_src(0)) TO stdout | active

select count(*) from public.pg_wait_sampling_profile seems to take infinite time. What can be done with it?

[rjuju]

I realised that I didn't mention that you need to restart after changing shared_preload_libraries, although the snapshot on the local instance doesn't appear in your output so I assume it either finished or you did restart 8 days ago.

does that function return something (maybe with a count(*) instead to avoid emitting all the rows)? if yes, how many rows are returned?

note that per https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_wait_sampling.html#configuration we recommend to disable pg_wait_sampling.profile_pid as it will amplify the number of records and the cost of sampling that datasource. if needed you could also call the reset function for that extension in case there are just too many records after disabling profile_pid. there will be a drop on one point on the graphs and then it will go back to normal.