search

powa-team / powa-archivist

powa-archivist: the powa PostgreSQL extension
http://powa.readthedocs.io/
PostgreSQL License
52 stars 20 forks source link

powa spins at 100% CPU and can only be killed with -9 #4

Closed girgen closed 6 years ago

girgen commented 8 years ago

on a farily busy production server with rougly 50 million queries per day, and about 10000 unique queries, over 1000 tables, one database, powa-archivist seems to not be able to keep up.

I don't really know what it is doing... Any suggestion how to help debugging would be great.

FreeBSD-10.2 powa-archivist-3.0.1 postgresql-9.4.6

girgen commented 8 years ago

I guess a truss says nothing at all... but anyway:

# truss -p 91165
lseek(14,0x0,SEEK_END)               = 161628160 (0x9a24000)
write(14,"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,8192) = 8192 (0x2000)
lseek(15,0x27c000,SEEK_SET)          = 2605056 (0x27c000)
read(15,"\M^N\^T\0\0\0\^\\M-/\M-M\0\0\0\0"...,8192) = 8192 (0x2000)
lseek(14,0x0,SEEK_END)               = 161636352 (0x9a26000)
kill(91161,SIGUSR1)              = 0 (0x0)
write(14,"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,8192) = 8192 (0x2000)
lseek(17,0x406000,SEEK_SET)          = 4218880 (0x406000)
read(17,"\M^N\^T\0\0hp\240\M--\0\0\0\0004"...,8192) = 8192 (0x2000)
lseek(15,0x129a000,SEEK_SET)             = 19505152 (0x129a000)
read(15,"\M^N\^T\0\0xTM\M-p\0\0\0\0004\^A"...,8192) = 8192 (0x2000)
lseek(14,0x0,SEEK_END)               = 161644544 (0x9a28000)
write(14,"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,8192) = 8192 (0x2000)
kill(91161,SIGUSR1)              = 0 (0x0)
lseek(15,0xb9c000,SEEK_SET)          = 12173312 (0xb9c000)
read(15,"\M^N\^T\0\0x 5\M^?\0\0\0\0`\^Ap"...,8192) = 8192 (0x2000)
lseek(14,0x0,SEEK_END)               = 161652736 (0x9a2a000)
write(14,"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,8192) = 8192 (0x2000)
rjuju commented 8 years ago

Hello.

Ouch :(

Do you know what process was PID 91165? (backend, bgworker...)

Can you confirm that only a kill -9 could stop it? (not SIGTERM or other, not pg_terminate_backend(pid) if it was a backend).

Did you check what was the FD 14, 15 and 17 referencing?

Can you share the powa settings you changed, or if you install other extension like pg_qualstats.

If it's possible, attaching on the process through gdb and getting a backtrace could help understand what's happening.

girgen commented 8 years ago

top: 

91165 pgsql          1 103    0 99846M 13433M CPU26  26 775:08 100.00% postgres

ps

91165  -  Rs   775:12,81 postgres: bgworker: powa    (postgres)

i.e. it is the powa bgworker. Killing it with SIGTERM yields nothing at least not within reasonable time.

I relalize I have not restarted postgresql after I ran ALTER EXTENSION powa UPDATE to get from 3.0.0 to 3.0.1. It is a Java application, so with some luck the 3.0.1 fix will actually help. 

(gdb) bt
#0  0x000000000078b5c1 in pfree ()
#1  0x00000000005a02d1 in ExecStoreMinimalTuple ()
#2  0x0000000000795c95 in tuplestore_gettupleslot ()
#3  0x00000000005b2310 in ExecCteScan ()
#4  0x000000000059fb51 in ExecScan ()
#5  0x00000000005995f0 in ExecProcNode ()
#6  0x00000000005b1b23 in ExecSort ()
#7  0x000000000059963f in ExecProcNode ()
#8  0x00000000005abf78 in ExecLimit ()
#9  0x000000000059968f in ExecProcNode ()
#10 0x00000000005a4e32 in ExecAgg ()
#11 0x0000000000599653 in ExecProcNode ()
#12 0x00000000005afddb in ExecNestLoop ()
#13 0x0000000000599617 in ExecProcNode ()
#14 0x00000000005afd37 in ExecNestLoop ()
#15 0x0000000000599617 in ExecProcNode ()
#16 0x00000000005afd37 in ExecNestLoop ()
#17 0x0000000000599617 in ExecProcNode ()
#18 0x00000000005afd37 in ExecNestLoop ()
#19 0x0000000000599617 in ExecProcNode ()
#20 0x00000000005ae6a4 in ExecModifyTable ()
#21 0x0000000000599554 in ExecProcNode ()
#22 0x00000000005970f0 in standard_ExecutorRun ()
#23 0x000000080500c0ea in _PG_init () from /usr/local/lib/postgresql/pg_stat_statements.so
#24 0x00000000005b8e54 in SPI_execute ()
#25 0x00000000005b938c in SPI_execute_plan_with_paramlist ()
#26 0x0000001ebb8cbc3b in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#27 0x0000001ebb8c8885 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#28 0x0000001ebb8c4b1b in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#29 0x0000001ebb8c3d0a in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#30 0x0000001ebb8bf7f8 in plpgsql_call_handler () from /usr/local/lib/postgresql/plpgsql.so
#31 0x000000000059f7f1 in ExecProject ()
#32 0x000000000059ef5d in ExecProject ()
#33 0x00000000005b0f50 in ExecResult ()
#34 0x0000000000599520 in ExecProcNode ()
#35 0x00000000005970f0 in standard_ExecutorRun ()
#36 0x000000080500c0ea in _PG_init () from /usr/local/lib/postgresql/pg_stat_statements.so
#37 0x00000000005b8e54 in SPI_execute ()
#38 0x00000000005b889e in SPI_execute ()
#39 0x0000001ebb8c93f6 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#40 0x0000001ebb8c4c1b in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#41 0x0000001ebb8c7ea4 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#42 0x0000001ebb8cc6bf in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#43 0x0000001ebb8c8394 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#44 0x0000001ebb8c97c9 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#45 0x0000001ebb8c4b1b in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#46 0x0000001ebb8c3d0a in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#47 0x0000001ebb8bf7f8 in plpgsql_call_handler () from /usr/local/lib/postgresql/plpgsql.so
#48 0x000000000059f7f1 in ExecProject ()
#49 0x000000000059ef5d in ExecProject ()
---Type <return> to continue, or q <return> to quit---
#50 0x00000000005b0f50 in ExecResult ()
#51 0x0000000000599520 in ExecProcNode ()
#52 0x00000000005970f0 in standard_ExecutorRun ()
#53 0x000000080500c0ea in _PG_init () from /usr/local/lib/postgresql/pg_stat_statements.so
#54 0x00000000005b8e54 in SPI_execute ()
#55 0x00000000005b889e in SPI_execute ()
#56 0x000000080600b971 in powa_main () from /usr/local/lib/postgresql/powa.so
#57 0x0000000000621ef3 in StartBackgroundWorker ()
#58 0x000000000062eacc in PostmasterMain ()
#59 0x000000000062dfea in PostmasterMain ()
#60 0x0000000800d689aa in pthread_sigmask () from /lib/libthr.so.3
#61 0x0000000800d681a8 in pthread_getspecific () from /lib/libthr.so.3
#62 <signal handler called>
#63 0x000000080266cf5a in _select () from /lib/libc.so.7
#64 0x0000000800d65fb2 in pthread_suspend_all_np () from /lib/libthr.so.3
#65 0x000000000062bdea in PostmasterMain ()
#66 0x00000000005c969f in main ()
(gdb) quit
The```
girgen commented 8 years ago 
shared_preload_libraries = 'pg_stat_statements, pg_qualstats, pg_stat_kcache, powa'

pg_stat_statements.max = 10000 # 5000 is orig value
pg_stat_statements.track = 'all'
pg_qualstats.max = 2000 # 1000 is orig value
rjuju commented 8 years ago

There wasn't any ABI change from 3.0.0 to 3.0.1 so not restarting postgres shouldn't be an issue.

It looks like it's working on a query, via spi_execute() call. Hard to say which part of powa_take_snapshot() it's on. I think pg_qualstats may be part of the problem. Given the number of queries you announced, it can take a significant amount of time. Can you try to disable it temporarily? You can do it by setting pg_qualstats.enabled = false in the postgresql.conf file, and then reload the configuration (FWIW, a sampling parameter has been added recently to avoid consuming too much resources, see https://github.com/dalibo/pg_qualstats/commit/1ab0e68398376d8518b4cd6bfa4eae418762a7d2, it should be released soon).

Can you see in pg_stat_statements the average time of the query "SELECT powa_take_snapshot()"? If it's equal or more than powa.frequency, it'll be called without any sleeping time.

girgen commented 8 years ago

This is after I just restarted postgresql to get rid of pg_qualstat. Reloading didn't really help, I powa was already beyond contact. I removed pg_qualstat from the library list, stopped postgresql which of course didn't happen, until I killed the power-bgworker -9.

I'll check it again tomorrow morning for better stats regarding the powa_take_snapshot query.

Thanks!

pp=# select * from pg_stat_statements where query like '%powa_take_snapshot%';
 userid |    dbid    |  queryid   |            query            | calls | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time 
--------+------------+------------+-----------------------------+-------+------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
     10 | 2791984724 | 1557599846 | SELECT powa_take_snapshot() |     1 |     44.521 |    1 |            1743 |              224 |                  14 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |          6.39 |              0
(1 row)

new config:

shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache, powa' #pg_qualstats, 
pg_stat_statements.max = 10000 # 5000 is orig value
pg_stat_statements.track = 'all'
pg_qualstats.max = 2000 # 1000 is orig value
girgen commented 8 years ago

It is now at 100% steady again. but it seems to me the SELECT powa_take_snapshot() is still running regularly 

pp=# select * from pg_stat_statements where query like '%powa_take_snapshot%';
 userid |    dbid    |  queryid   |            query            | calls | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time 
--------+------------+------------+-----------------------------+-------+------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
     10 | 2791984724 | 1557599846 | SELECT powa_take_snapshot() |    99 | 434286.159 |   99 |        56611327 |           120554 |              574980 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |      2112.571 |              0
(1 row)
girgen commented 8 years ago

powa stopped taking snapshots after 99 snaps. now there are no traces left of it in pg_stat_statements.

It still starts spinning at 100% cpu once the server gets working in the morning.

So far I've tried updating to powa-3.0.1 and removing pg_qualstats. Any suggestions for debugging this? can I run the archivist with more debugging perhaps?

rjuju commented 8 years ago

According to pg_stat_statements, powa_take_snapshot() has an average runtime of more than 7 min, which is more than powa.frequency. In this case, it'll be called again and again without any sleep time. It shouldn't stop after 99 snapshots, but should do an "aggregate" operation on the 100th execution (powa.coalesce setting).

Next step is to find which snapshot function is/are most time consuming. Can you return the result of these queries:

SELECT * FROM pg_stat_satements WHERE query like '%powa%snapshot%'; SELECT relname, pg_size_pretty(pg_table_size(oid)) FROM pg_class WHERE relkind = 'r' and relname like 'powa%';

and if you enabled track_functions:

SELECT * FROM pg_stat_user_functions WHERE funcname like 'powa%snapshot';

girgen commented 8 years ago

I believe total_time is the total time spent? i.e. each SELECT powa_take_snapshot() takes in average total_time / calls, which is around four seconds?

SELECT * FROM pg_stat_statements WHERE query like '%powa%snapshot%'; gives no results anymore, it seems that pg_stat_statements was hit by retention.

pg_stat_user_functions has no powa:

powa=# SELECT * FROM pg_stat_user_functions;
   funcid   | schemaname |      funcname      | calls | total_time | self_time 
------------+------------+--------------------+-------+------------+-----------
 2791985136 | public     | pg_stat_statements |     2 |    150.397 |   150.397
(1 row)

the SELECT relname, pg_size_pretty(pg_table_size(oid)) FROM pg_class WHERE relkind = 'r' and relname like 'powa%'; is still running after 10 minutes... we'll if it eventually finishes...

rjuju commented 8 years ago

Oups yes you're right I forgot to divide by number of calls, so yes 4 seconds.

When has the statistics been reinitialized?

select pg_stat_get_db_stat_reset_time(oid) from pg_database where datname = 'powa';

I wonder if the average time wasn't longer before. If powa-ui if working, you could use it to check how long was the powa_take_snasphot() calls (or nested *snapshot functions) when the issue happened.

girgen commented 8 years ago

I don't know why the stats where reinitialised, I certainly didn't do anything to make it happen...? 

powa=# select pg_stat_get_db_stat_reset_time(oid) from pg_database where datname = 'powa';
 pg_stat_get_db_stat_reset_time 
--------------------------------
 2016-02-24 00:51:17.570055+01
(1 row)

Note that this was 23 hours ago when I last restarted the database. I got stats from pg_stat_statemets 13 hours ago? Something is rotten in the state of Denmark...

powa web gui is hung.

rjuju commented 8 years ago

If you didn't call the reset function, it's probably due to unclean shutdown. Can you compare the reset time to the output of pg_postmaster_start_time() ?

Could you get the size of the tables? If it's really too long, I guess you could start by truncating the tables related to pg_qualstats since you disabled it. The list is:

girgen commented 8 years ago

It not that many tuples...

powa=# select count(*) from powa_qualstats_quals_history ;
 count 
-------
 21382
(1 row)

powa=# select count(*) from powa_qualstats_quals_history_current ;
  count  
---------
 4100739
(1 row)

powa=# select count(*) from powa_qualstats_constvalues_history ;
 count 
-------
 21382
(1 row)

powa=# select count(*) from powa_qualstats_quals;
  count  
---------
 2873510
(1 row)

powa=# select count(*) from powa_qualstats_constvalues_history_current;
  count   
----------
 13928977
(1 row)

powa=# truncate powa_qualstats_quals_history; 
TRUNCATE TABLE

powa=# truncate powa_qualstats_quals_history_current;

It does not like truncating powa_qualstats_quals_history_current:

pp=# select now() - pg_stat_activity.query_start AS qtime,now() - pg_stat_activity.xact_start AS xacttime, pg_stat_activity.pid, pg_stat_activity.datname, pg_stat_activity.application_name,pg_stat_activity.waiting AS w,pg_stat_activity.state,pg_stat_activity.query FROM pg_stat_activity WHERE datname = 'powa';
      qtime      |        xacttime        |  pid  | datname | application_name | w | state  |                     query                      
-----------------+------------------------+-------+---------+------------------+---+--------+------------------------------------------------
                 | 2 days 05:20:56.584426 | 87700 | powa    | POWA collector   | f |        | 
 03:24:08.392414 | 03:24:08.392414        | 11727 | powa    | psql             | t | active | truncate powa_qualstats_quals_history_current;
(2 rows)
rjuju commented 8 years ago

The TRUNCATE statements is waiting for a lock (column w to true). If these are the only two queries running on the database, I assume that the powa collector has a lock on this table. However, the powa bgworker should not keep a transaction open while it's not executing a snapshot (see https://github.com/dalibo/powa-archivist/blob/master/powa.c#L215). So either the snapshot is running since 2 days, or there's something really weird. Can you attach again with gdb and see if it's still running an spi_execute()?

Can you also try to see which relations are locked by this process?

girgen commented 8 years ago

Seems like it is still in SPI_execute, yes:

(gdb) bt
#0  0x000000080266d6e2 in memcpy () from /lib/libc.so.7
#1  0x000000000046db91 in heap_copy_minimal_tuple ()
#2  0x0000000000795c81 in tuplestore_gettupleslot ()
#3  0x00000000005b2310 in ExecCteScan ()
#4  0x000000000059fb51 in ExecScan ()
#5  0x00000000005995f0 in ExecProcNode ()
#6  0x00000000005b1b23 in ExecSort ()
#7  0x000000000059963f in ExecProcNode ()
#8  0x00000000005abf78 in ExecLimit ()
#9  0x000000000059968f in ExecProcNode ()
#10 0x00000000005a4e32 in ExecAgg ()
#11 0x0000000000599653 in ExecProcNode ()
#12 0x00000000005afddb in ExecNestLoop ()
#13 0x0000000000599617 in ExecProcNode ()
#14 0x00000000005ae6a4 in ExecModifyTable ()
#15 0x0000000000599554 in ExecProcNode ()
#16 0x00000000005970f0 in standard_ExecutorRun ()
#17 0x000000080500c0ea in _PG_init () from /usr/local/lib/postgresql/pg_stat_statements.so
#18 0x00000000005b8e54 in SPI_execute ()
#19 0x00000000005b938c in SPI_execute_plan_with_paramlist ()
#20 0x0000001ebb8cbc3b in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#21 0x0000001ebb8c8885 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#22 0x0000001ebb8c4b1b in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#23 0x0000001ebb8c3d0a in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#24 0x0000001ebb8bf7f8 in plpgsql_call_handler () from /usr/local/lib/postgresql/plpgsql.so
#25 0x000000000059f7f1 in ExecProject ()
#26 0x000000000059ef5d in ExecProject ()
#27 0x00000000005b0f50 in ExecResult ()
#28 0x0000000000599520 in ExecProcNode ()
#29 0x00000000005970f0 in standard_ExecutorRun ()
#30 0x000000080500c0ea in _PG_init () from /usr/local/lib/postgresql/pg_stat_statements.so
#31 0x00000000005b8e54 in SPI_execute ()
#32 0x00000000005b889e in SPI_execute ()
#33 0x0000001ebb8c93f6 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#34 0x0000001ebb8c4c1b in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#35 0x0000001ebb8c7ea4 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#36 0x0000001ebb8cc6bf in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#37 0x0000001ebb8c8394 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#38 0x0000001ebb8c97c9 in plpgsql_subxact_cb () from /usr/local/lib/postgresql/plpgsql.so
#39 0x0000001ebb8c4b1b in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#40 0x0000001ebb8c3d0a in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so
#41 0x0000001ebb8bf7f8 in plpgsql_call_handler () from /usr/local/lib/postgresql/plpgsql.so
#42 0x000000000059f7f1 in ExecProject ()
#43 0x000000000059ef5d in ExecProject ()
#44 0x00000000005b0f50 in ExecResult ()
#45 0x0000000000599520 in ExecProcNode ()
#46 0x00000000005970f0 in standard_ExecutorRun ()
#47 0x000000080500c0ea in _PG_init () from /usr/local/lib/postgresql/pg_stat_statements.so
#48 0x00000000005b8e54 in SPI_execute ()
---Type <return> to continue, or q <return> to quit--- 
#49 0x00000000005b889e in SPI_execute ()
#50 0x000000080600b971 in powa_main () from /usr/local/lib/postgresql/powa.so
#51 0x0000000000621ef3 in StartBackgroundWorker ()
#52 0x000000000062eacc in PostmasterMain ()
#53 0x000000000062dfea in PostmasterMain ()
#54 0x0000000800d689aa in pthread_sigmask () from /lib/libthr.so.3
#55 0x0000000800d681a8 in pthread_getspecific () from /lib/libthr.so.3
#56 <signal handler called>
#57 0x000000080266cf5a in _select () from /lib/libc.so.7
#58 0x0000000800d65fb2 in pthread_suspend_all_np () from /lib/libthr.so.3
#59 0x000000000062bdea in PostmasterMain ()
#60 0x00000000005c969f in main ()
rjuju commented 8 years ago

Executing a tuplestore_gettupleslot, I think it's querying an SRF. You said you disabled pg_qualstats, did you restart after that? What is "SHOW shared_preload_libraries;" returning?

Can you get the name of object with granted locked through pg_locks?

select l.*, c.* from pg_locks l left join pg_class c on c.oid = l.relation left join pg_database d on d.oid = l.database where d.datname = 'powa';
girgen commented 8 years ago

hmmm, apparently your right, I probably didn't restart properly after removing pg_qualstats. weird...

postgres=# SHOW shared_preload_libraries;
                shared_preload_libraries                
--------------------------------------------------------
 pg_stat_statements, pg_qualstats, pg_stat_kcache, powa
(1 row)
powa=# select l.*, c.* from pg_locks l left join pg_class c on c.oid = l.relation left join pg_database d on d.oid = l.database where d.datname = 'powa';
 locktype |  database  |  relation  | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath |                             relname                             | relnamespace |  reltype   | reloftype | relowner | relam | relfilenode | reltablespace | relpages |  reltuples  | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relispopulated | relreplident | relfrozenxid | relminmxid |                   relacl                    | reloptions 
----------+------------+------------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------+-----------------------------------------------------------------+--------------+------------+-----------+----------+-------+-------------+---------------+----------+-------------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------------+--------------+--------------+------------+---------------------------------------------+------------
 relation | 2791984724 |       3455 |      |       |            |               |         |       |          | 8/3607084          | 95238 | AccessShareLock     | t       | t        | pg_class_tblspc_relfilenode_index                               |           11 |          0 |         0 |       10 |   403 |           0 |             0 |        5 |         386 |             0 |             0 | f           | f           | p              | i       |        2 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 |       2663 |      |       |            |               |         |       |          | 8/3607084          | 95238 | AccessShareLock     | t       | t        | pg_class_relname_nsp_index                                      |           11 |          0 |         0 |       10 |   403 |           0 |             0 |        7 |         386 |             0 |             0 | f           | f           | p              | i       |        2 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 |       2662 |      |       |            |               |         |       |          | 8/3607084          | 95238 | AccessShareLock     | t       | t        | pg_class_oid_index                                              |           11 |          0 |         0 |       10 |   403 |           0 |             0 |        5 |         386 |             0 |             0 | f           | f           | p              | i       |        1 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 |       1259 |      |       |            |               |         |       |          | 8/3607084          | 95238 | AccessShareLock     | t       | t        | pg_class                                                        |           11 |         83 |         0 |       10 |     0 |           0 |             0 |       12 |         386 |            12 |             0 | t           | f           | p              | r       |       29 |         0 | t          | f          | f           | f              | f              | t              | n            |    763474463 |    4904332 | {=r/pgsql}                                  | 
 relation | 2791984724 |      11187 |      |       |            |               |         |       |          | 8/3607084          | 95238 | AccessShareLock     | t       | t        | pg_locks                                                        |           11 |      11188 |         0 |       10 |     0 |       11187 |             0 |        0 |           0 |             0 |             0 | f           | f           | p              | v       |       15 |         0 | f          | f          | t           | f              | f              | t              | n            |            0 |          0 | {=r/pgsql}                                  | 
 relation | 2791984724 | 2792431046 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_qualstats_constvalues_history                              |         2200 | 2792431048 |         0 |       10 |     0 |  2792431046 |         16428 |     2333 |       21382 |          2333 |    2792431049 | t           | f           | p              | r       |        9 |         0 | f          | f          | f           | t              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431014 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_qualstats_quals                                            |         2200 | 2792431016 |         0 |       10 |     0 |  2792431014 |         16428 |    13813 |      819764 |         13813 |    2792431017 | t           | f           | p              | r       |        5 |         0 | f          | t          | f           | t              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431014 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_qualstats_quals                                            |         2200 | 2792431016 |         0 |       10 |     0 |  2792431014 |         16428 |    13813 |      819764 |         13813 |    2792431017 | t           | f           | p              | r       |        5 |         0 | f          | t          | f           | t              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2793992831 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | pg_qualstats_by_query                                           |         2200 | 2793992833 |         0 |       10 |     0 |  2793992831 |             0 |        0 |           0 |             0 |             0 | f           | f           | p              | v       |       10 |         0 | f          | f          | t           | f              | f              | t              | n            |            0 |          0 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430949 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_all_relations_history                                      |         2200 | 2792430951 |         0 |       10 |     0 |  2792430949 |         16428 |    17605 |      100815 |         17605 |    2792430952 | t           | f           | p              | r       |        6 |         0 | f          | f          | f           | f              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430936 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_user_functions_history                                     |         2200 | 2792430938 |         0 |       10 |     0 |  2792430936 |         16428 |      182 |         930 |           182 |    2792430939 | t           | f           | p              | r       |        6 |         0 | f          | f          | f           | f              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430909 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_statements_userid_idx                                      |         2200 |          0 |         0 |       10 |   403 |  2792430909 |         16428 |    13138 | 3.77443e+06 |             0 |             0 | f           | f           | p              | i       |        1 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430908 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_statements_dbid_idx                                        |         2200 |          0 |         0 |       10 |   403 |  2792430908 |         16428 |    13181 | 3.77443e+06 |             0 |             0 | f           | f           | p              | i       |        1 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430906 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_statements_pkey                                            |         2200 |          0 |         0 |       10 |   403 |  2792430906 |         16428 |    18928 | 3.77443e+06 |             0 |             0 | f           | f           | p              | i       |        3 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430917 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_statements_history_db                                      |         2200 | 2792430919 |         0 |       10 |     0 |  2792430917 |         16428 |        3 |          29 |             3 |    2792430920 | t           | f           | p              | r       |        5 |         0 | f          | f          | f           | f              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430910 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_statements_history                                         |         2200 | 2792430912 |         0 |       10 |     0 |  2792430910 |         16428 |   142046 |      623509 |        142046 |    2792430913 | t           | f           | p              | r       |        7 |         0 | f          | f          | f           | f              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430900 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_statements                                                 |         2200 | 2792430902 |         0 |       10 |     0 |  2792430900 |         16428 |   518616 | 3.77827e+06 |        518616 |    2792430903 | t           | f           | p              | r       |        4 |         0 | f          | t          | f           | t              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430900 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_statements                                                 |         2200 | 2792430902 |         0 |       10 |     0 |  2792430900 |         16428 |   518616 | 3.77827e+06 |        518616 |    2792430903 | t           | f           | p              | r       |        4 |         0 | f          | t          | f           | t              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 |      11150 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | pg_roles                                                        |           11 |      11151 |         0 |       10 |     0 |       11150 |             0 |        0 |           0 |             0 |             0 | f           | f           | p              | v       |       13 |         0 | f          | f          | t           | f              | f              | t              | n            |            0 |          0 | {=r/pgsql}                                  | 
 relation | 2791984724 | 2791985137 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | pg_stat_statements                                              |         2200 | 2791985139 |         0 |       10 |     0 |  2791985137 |             0 |        0 |           0 |             0 |             0 | f           | f           | p              | v       |       19 |         0 | f          | f          | t           | f              | f              | t              | n            |            0 |          0 | {pgsql=arwdDxt/pgsql,=r/pgsql,powa=r/pgsql} | 
 relation | 2791984724 | 2792430964 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_functions                                                  |         2200 | 2792430966 |         0 |       10 |     0 |  2792430964 |         16428 |        1 |          22 |             1 |    2792430970 | f           | f           | p              | r       |        5 |         1 | f          | f          | f           | f              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2824443070 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_databases_pkey                                             |         2200 |          0 |         0 |    16385 |   403 |  2824443070 |         16428 |        1 |           0 |             0 |             0 | f           | f           | p              | i       |        1 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2824443070 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_databases_pkey                                             |         2200 |          0 |         0 |    16385 |   403 |  2824443070 |         16428 |        1 |           0 |             0 |             0 | f           | f           | p              | i       |        1 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2824443067 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | t        | powa_databases                                                  |         2200 | 2824443069 |         0 |    16385 |     0 |  2824443067 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | r       |        3 |         0 | f          | t          | f           | f              | f              | t              | d            |    818741012 |    9620658 |                                             | 
 relation | 2791984724 | 2824443067 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | t        | powa_databases                                                  |         2200 | 2824443069 |         0 |    16385 |     0 |  2824443067 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | r       |        3 |         0 | f          | t          | f           | f              | f              | t              | d            |    818741012 |    9620658 |                                             | 
 relation | 2791984724 | 2792431057 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_qualstats_constvalues_history_current                      |         2200 | 2792431059 |         0 |       10 |     0 |  2808985675 |         16428 |    77360 | 4.50335e+06 |         77360 |    2792431060 | f           | f           | p              | r       |        9 |         0 | f          | f          | f           | t              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431057 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_qualstats_constvalues_history_current                      |         2200 | 2792431059 |         0 |       10 |     0 |  2808985675 |         16428 |    77360 | 4.50335e+06 |         77360 |    2792431060 | f           | f           | p              | r       |        9 |         0 | f          | f          | f           | t              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431057 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | powa_qualstats_constvalues_history_current                      |         2200 | 2792431059 |         0 |       10 |     0 |  2808985675 |         16428 |    77360 | 4.50335e+06 |         77360 |    2792431060 | f           | f           | p              | r       |        9 |         0 | f          | f          | f           | t              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431038 |      |       |            |               |         |       |          | 18/1973005         | 11727 | AccessExclusiveLock | f       | f        | powa_qualstats_quals_history_current                            |         2200 | 2792431040 |         0 |       10 |     0 |  2808985678 |         16428 |    13422 | 1.26631e+06 |         13385 |             0 | f           | f           | p              | r       |        8 |         0 | f          | f          | f           | t              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430933 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | pg_toast_2792430930                                             |           99 | 2792430934 |         0 |       10 |     0 |  2808984983 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
 relation | 2791984724 | 2792430933 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430930                                             |           99 | 2792430934 |         0 |       10 |     0 |  2808984983 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
 relation | 2791984724 | 2792430993 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_kcache_metrics_current                                     |         2200 | 2792430995 |         0 |       10 |     0 |  2808986073 |         16428 |    25946 | 1.93243e+06 |         25946 |    2792430996 | f           | f           | p              | r       |        4 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430962 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_coalesce_sequence                                          |         2200 | 2792430963 |         0 |       10 |     0 |  2792430962 |             0 |        1 |           1 |             0 |             0 | f           | f           | p              | S       |       10 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792431020 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_qualstats_quals_pkey                                       |         2200 |          0 |         0 |       10 |   403 |  2792431020 |         16428 |     5516 |      819764 |             0 |             0 | f           | f           | p              | i       |        4 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430943 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_user_functions_history_current                             |         2200 | 2792430945 |         0 |       10 |     0 |  2808985006 |         16428 |      217 |       18936 |           217 |    2792430946 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430943 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_user_functions_history_current                             |         2200 | 2792430945 |         0 |       10 |     0 |  2808985006 |         16428 |      217 |       18936 |           217 |    2792430946 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430943 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | powa_user_functions_history_current                             |         2200 | 2792430945 |         0 |       10 |     0 |  2808985006 |         16428 |      217 |       18936 |           217 |    2792430946 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430943 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | powa_user_functions_history_current                             |         2200 | 2792430945 |         0 |       10 |     0 |  2808985006 |         16428 |      217 |       18936 |           217 |    2792430946 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430959 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | pg_toast_2792430956                                             |           99 | 2792430960 |         0 |       10 |     0 |  2808985658 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
 relation | 2791984724 | 2792430959 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430956                                             |           99 | 2792430960 |         0 |       10 |     0 |  2808985658 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
 relation | 2791984724 | 2792430929 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430924_index                                       |           99 |          0 |         0 |       10 |   403 |  2808984978 |         16428 |        1 |           0 |             0 |             0 | f           | f           | p              | i       |        2 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430956 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_all_relations_history_current                              |         2200 | 2792430958 |         0 |       10 |     0 |  2808985657 |         16428 |    14730 |      576799 |         14730 |    2792430959 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430956 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_all_relations_history_current                              |         2200 | 2792430958 |         0 |       10 |     0 |  2808985657 |         16428 |    14730 |      576799 |         14730 |    2792430959 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430956 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | powa_all_relations_history_current                              |         2200 | 2792430958 |         0 |       10 |     0 |  2808985657 |         16428 |    14730 |      576799 |         14730 |    2792430959 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430956 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | powa_all_relations_history_current                              |         2200 | 2792430958 |         0 |       10 |     0 |  2808985657 |         16428 |    14730 |      576799 |         14730 |    2792430959 | f           | f           | p              | r       |        3 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431070 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_qualstats_quals_queryid_idx                                |         2200 |          0 |         0 |       10 |   403 |  2792431070 |         16428 |     3052 |      819764 |             0 |             0 | f           | f           | p              | i       |        1 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430930 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_statements_history_current_db                              |         2200 | 2792430932 |         0 |       10 |     0 |  2808984982 |         16428 |       26 |        1092 |            26 |    2792430933 | f           | f           | p              | r       |        2 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430930 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_statements_history_current_db                              |         2200 | 2792430932 |         0 |       10 |     0 |  2808984982 |         16428 |       26 |        1092 |            26 |    2792430933 | f           | f           | p              | r       |        2 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430930 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | powa_statements_history_current_db                              |         2200 | 2792430932 |         0 |       10 |     0 |  2808984982 |         16428 |       26 |        1092 |            26 |    2792430933 | f           | f           | p              | r       |        2 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430930 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | powa_statements_history_current_db                              |         2200 | 2792430932 |         0 |       10 |     0 |  2808984982 |         16428 |       26 |        1092 |            26 |    2792430933 | f           | f           | p              | r       |        2 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430900 |      |       |            |               |         |       |          | 2/102              | 87700 | RowShareLock        | t       | f        | powa_statements                                                 |         2200 | 2792430902 |         0 |       10 |     0 |  2792430900 |         16428 |   518616 | 3.77827e+06 |        518616 |    2792430903 | t           | f           | p              | r       |        4 |         0 | f          | t          | f           | t              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431038 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_qualstats_quals_history_current                            |         2200 | 2792431040 |         0 |       10 |     0 |  2808985678 |         16428 |    13422 | 1.26631e+06 |         13385 |             0 | f           | f           | p              | r       |        8 |         0 | f          | f          | f           | t              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431038 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | powa_qualstats_quals_history_current                            |         2200 | 2792431040 |         0 |       10 |     0 |  2808985678 |         16428 |    13422 | 1.26631e+06 |         13385 |             0 | f           | f           | p              | r       |        8 |         0 | f          | f          | f           | t              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431096 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_qualstats_aggregate_constvalues_current                    |         2200 | 2792431098 |         0 |       10 |     0 |  2792431096 |             0 |        0 |           0 |             0 |             0 | f           | f           | p              | v       |        9 |         0 | f          | f          | t           | f              | f              | t              | n            |            0 |          0 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792431068 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_qualstats_constvalues_hi_queryid_qualid_coalesce_range_idx |         2200 |          0 |         0 |       10 |   783 |  2792431068 |         16428 |     2481 |       21382 |             0 |             0 | f           | f           | p              | i       |        3 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430946 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | pg_toast_2792430943                                             |           99 | 2792430947 |         0 |       10 |     0 |  2808985007 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
 relation | 2791984724 | 2792430946 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430943                                             |           99 | 2792430947 |         0 |       10 |     0 |  2808985007 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
 relation | 2791984724 | 2792431069 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_qualstats_constvalues_history_qualid_queryid_idx           |         2200 |          0 |         0 |       10 |   403 |  2792431069 |         16428 |     1083 |       21382 |             0 |             0 | f           | f           | p              | i       |        2 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430935 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430930_index                                       |           99 |          0 |         0 |       10 |   403 |  2808984984 |         16428 |        1 |           0 |             0 |             0 | f           | f           | p              | i       |        2 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430961 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430956_index                                       |           99 |          0 |         0 |       10 |   403 |  2808985659 |         16428 |        1 |           0 |             0 |             0 | f           | f           | p              | i       |        2 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792431014 |      |       |            |               |         |       |          | 2/102              | 87700 | RowShareLock        | t       | f        | powa_qualstats_quals                                            |         2200 | 2792431016 |         0 |       10 |     0 |  2792431014 |         16428 |    13813 |      819764 |         13813 |    2792431017 | t           | f           | p              | r       |        5 |         0 | f          | t          | f           | t              | f              | t              | d            |    797765425 |    9090442 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430948 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430943_index                                       |           99 |          0 |         0 |       10 |   403 |  2808985008 |         16428 |        1 |           0 |             0 |             0 | f           | f           | p              | i       |        2 |         0 | f          | f          | f           | f              | f              | t              | n            |            0 |          0 |                                             | 
 relation | 2791984724 | 2792430924 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessShareLock     | t       | f        | powa_statements_history_current                                 |         2200 | 2792430926 |         0 |       10 |     0 |  2808984976 |         16428 |    50340 | 2.04814e+06 |         50340 |    2792430927 | f           | f           | p              | r       |        4 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430924 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_statements_history_current                                 |         2200 | 2792430926 |         0 |       10 |     0 |  2808984976 |         16428 |    50340 | 2.04814e+06 |         50340 |    2792430927 | f           | f           | p              | r       |        4 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430924 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | powa_statements_history_current                                 |         2200 | 2792430926 |         0 |       10 |     0 |  2808984976 |         16428 |    50340 | 2.04814e+06 |         50340 |    2792430927 | f           | f           | p              | r       |        4 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430924 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | powa_statements_history_current                                 |         2200 | 2792430926 |         0 |       10 |     0 |  2808984976 |         16428 |    50340 | 2.04814e+06 |         50340 |    2792430927 | f           | f           | p              | r       |        4 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430999 |      |       |            |               |         |       |          | 2/102              | 87700 | RowExclusiveLock    | t       | f        | powa_kcache_metrics_current_db                                  |         2200 | 2792431001 |         0 |       10 |     0 |  2808986079 |         16428 |       14 |        1085 |            14 |    2792431002 | f           | f           | p              | r       |        2 |         0 | f          | f          | f           | f              | f              | t              | d            |    808365371 |    9368711 | {pgsql=arwdDxt/pgsql,powa=r/pgsql}          | 
 relation | 2791984724 | 2792430927 |      |       |            |               |         |       |          | 2/102              | 87700 | ShareLock           | t       | f        | pg_toast_2792430924                                             |           99 | 2792430928 |         0 |       10 |     0 |  2808984977 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
 relation | 2791984724 | 2792430927 |      |       |            |               |         |       |          | 2/102              | 87700 | AccessExclusiveLock | t       | f        | pg_toast_2792430924                                             |           99 | 2792430928 |         0 |       10 |     0 |  2808984977 |         16428 |        0 |           0 |             0 |             0 | t           | f           | p              | t       |        3 |         0 | f          | t          | f           | f              | f              | t              | n            |    808365371 |    9368711 |                                             | 
(69 rows)
girgen commented 8 years ago

the config file has

shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache, powa' #pg_qualstats,

but pg_qualstats is still there. I disabled it by setting .enabled = false now:

postgres=# show pg_qualstats.enabled ;
 pg_qualstats.enabled 
----------------------
 off
(1 row)

postgres=# show shared_preload_libraries;
                shared_preload_libraries                
--------------------------------------------------------
 pg_stat_statements, pg_qualstats, pg_stat_kcache, powa
(1 row)
rjuju commented 8 years ago

The previous query must be run on the powa database to have the name of the relations, sorry for ommitting this.

the pg_qualstats.enabled option basically disable the extension while it's still loaded. You can also bypass the qualstats snapshot (even if it should now be no-op) by updating powa_functions table, like this:

UPDATE powa_functions SET enabled = false WHERE module = 'pg_qualstats';

girgen commented 8 years ago

Sorry, should have realized this... I updated the above post running on the powa database.

rjuju commented 8 years ago

Seeing the result of the query, the truncate is still running, and still blocked. Except the locks due do executing the query, everything seems to be hold by the bgworker. Looking at the table name, it looks like it correspond to an aggregate operation.

Also, you said that the powa_qualstats_constvalues_history_current table has more than 13 millions record. This table is supposed to be aggregated every 100 snapshot, so by default every ~ 8.5 hours. This still seems quite big, but aggregating this should of course not take 2 days. You can start by cancelling the truncate statement, disable pg_qualstats in powa_functions and check if the powa sequence is changing on the powa database:

SELECT * FROM powa_coalesce_sequence ;

This should change every ~ powa.frequency. If it's not changing after 2 or 3 powa.frequency, then either it's stuck or it's really aggregating since two days (the gdb backtrace seems to indicate it's working). Can you confirm which situation it is?

girgen commented 8 years ago

The powa_coalesce_sequence is fixed at 4300.

I get a gut feeling that ~8.5 hours is right about as long as the powa-archivist bgworker is functional after postgresql is started. That aggregation thing after ~8.5 h might well be what's fails. After about 8.5 hours it starts spinning at 100% cpu.

girgen commented 8 years ago

I just restarted the production server to reset powa. Had to kill it -9 "didn't wait more than 10 seconds, but it is not susceptable any other signals...

So anyway, now powa is really running with pg_qualstats off. We'll see tomorrow morning if it's any better than before.

rjuju commented 8 years ago

If the sequence is stuck at a % 100 value, it seems that the aggregate part is guilty. Let's hope that the (too) many quals where sloing it down, and it'll perform well now that pg_qualstats is disabled.

Please keep me informed.

Also, I confirm that none of the current signal handlers can help you in this situation :( I'll look into this shortly to fix this issue.

girgen commented 8 years ago

Hello again,

Well, I killed the maverick process and restarted. Now without pg_qualstats, and yes, it seems to be working much better. It is still running smoothly.

Of course, the feature that pg_qualstats supplies, that is getting parameter values, is of course of great value, could it be made working.

I'll be glad to help find a way to get it into a working state.

Palle

rjuju commented 8 years ago

I bet that storing statistics for every constant of every qual will always be too expensive for your workload.

Using the new sampling parameter should allow you to use the features pg_qualstats supplies with much less penalties, and a probably good enough overview of the constant used.

I also fixed the powa-archivist bgworker code to allow stopping it without a kill -9.

girgen commented 8 years ago

sounds great. looking forward to 3.0.2 then, I see you're working on a release.

Once it is working well for me, I'll commit a port for FreeBSD as well. :-)

Thanks a lot for all your help. I would probably have had to abandon powa without it, there wouldn't have been enough time to solve the issues by ourselves.

Where can I read more about the new sampling parameters?

rjuju commented 8 years ago

Thanks!

Fow now, the sampling parameter is pg_qualstats.sample_ratio, a float between 0 and 1 expressing the percentage of queries to look at (if a query is sampled, all its quals will be sampled). It's been introduced in commit https://github.com/dalibo/pg_qualstats/commit/1ab0e68398376d8518b4cd6bfa4eae418762a7d2.

We must make sure that by default pg_qualstats isn't too much aggressive. We'll probably make for this parameter a special default value for "auto tuned value", to sample something like "1/max_connections" % of queries, which should be safe enough for highly concurrent environnments. We'll document it in a proper way the new release will be out.

girgen commented 8 years ago

Sounds great. So you're planning a new tag/patch for pg_qualstats as well?

rdunklau commented 8 years ago

I just did that to pg_qualstats. I implemented rjuju's suggestion to tune it to 1 / max_connections by default, let me know how it goes.

girgen commented 8 years ago

Hi,

Are you sure max_connections is a good value here? I tend to always keep it around 50-100 no matter how busy the server is, and use a connection pool in front of PostgreSQL.

But I can set the value of choice I guess?

2 mars 2016 kl. 08:05 skrev Ronan Dunklau notifications@github.com:

I just did that to pg_qualstats. I implemented rjuju's suggestion to tune it to 1 / max_connections by default, let me know how it goes.

— Reply to this email directly or view it on GitHub.

rdunklau commented 8 years ago

Yes, you can set whatever you want in the 0..1 range.

1 / max_connections was to prevent concurrency issues: statiscally speaking, we shouldn't have too much contention with that value. It should also probably help you.

Just start with 0.001 (1 out of 1000 queries) and see where that leads you.

rjuju warned me of some bugs of the current approach (with how you can't explicitly write "= -1" in the config file) so expect another release soon.

rjuju commented 8 years ago

The 0.0.9 release of pg_qualstats with all this is now available.

rjuju commented 6 years ago

@girgen I'm closing this issue since it should be fixed now. Please tell me if it's not the case.

Thank a lot for reporting it!