powa-archivist: the powa PostgreSQL extension
Home Page: http://powa.readthedocs.io/
License: PostgreSQL License
This project is the core extension of the PoWA project, a PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graphs to help monitor and tune your PostgreSQL servers.
For more information, please read the PoWA-archivist documentation:
https://powa.readthedocs.io/en/latest/components/powa-archivist/index.html
Hello,
Fist, thanks to all developper for this work on PoWA-archivist.
I would like to suggest a enhancement.
On Cloud databases, it is not always possible to add extensions on database (no access to the host server).
In order to use PoWA with remote architecture, and view queries from pg_statements, i updated functions which called c library => powa_stat_all_rel and powa_stat_user_functions (they return no value).
And, in Powa_collector (PoWA 4.0 architecture), i disabled the check of powa extension.
It work...
It is a POC, but i think it could be very interessant to find a solution for database when there is no access to host server.
Regards
We are planning to implement PG14 wondering if it is supported?
Hi,
I got a feature request for powa, asking for adding hypopg as a dependency for powa:
https://redmine.postgresql.org/issues/2073 (you need a community account to view this)
Do you think it makes sense?
Regards, Devrim
After commit postgres/postgres@13d0072 powa does not work with 17 postgres
Hi,
This is similar to #4 actually, very similar, but it's been there are new versions of everything so I'm opening a new issue.
postgresql-9.6.0
powa-archivist-3.1.0
pg_qualstats-1.0.1
pg_stat_kcache-2.0.3
the query SELECT powa_take_snapshot() runs for hours on end, eating all the memory. Can I stop it with a pg_cancel_backend(pid)?
Seems to have happened under high load.
As you can see, the amount of data in powa_all_relations_history_current is quite substantial, about 20 % of database disk footprint in this case.
First question is, can I cancel it without restarting the database? Will powa.frequency then take effect immediately, so I can stop powa from running in production?
We have had powa running fine for six months after turning off pg_qualstats (not loading it at all). Now after upgrading to postgres-9.6, I wanted to reactive it and expected pg_qualstats to work, as it did in our test environments. Apparently, there is still a problem somewhere with high loads?
Suggestions?
Palle
powa=# SELECT relname, pg_size_pretty(pg_table_size(oid)) FROM pg_class WHERE relkind = 'r' and relname like 'powa%';
relname | pg_size_pretty
--------------------------------------------+----------------
powa_databases | 8192 bytes
powa_last_aggregation | 8192 bytes
powa_last_purge | 8192 bytes
powa_statements_history | 302 MB
powa_statements_history_db | 448 kB
powa_statements_history_current | 108 MB
powa_statements_history_current_db | 80 kB
powa_user_functions_history_current | 704 kB
powa_user_functions_history | 2880 kB
powa_all_relations_history_current | 70 GB
powa_all_relations_history | 251 MB
powa_functions | 16 kB
powa_kcache_metrics | 597 MB
powa_kcache_metrics_db | 312 kB
powa_kcache_metrics_current | 58 MB
powa_kcache_metrics_current_db | 56 kB
powa_qualstats_quals_history_current | 728 kB
powa_statements | 6047 MB
powa_qualstats_quals_history | 20 MB
powa_qualstats_quals | 1872 kB
powa_qualstats_constvalues_history | 33 MB
powa_qualstats_constvalues_history_current | 1136 kB
(22 rows)
SELECT * FROM pg_stat_statements WHERE query like '%powa%snapshot%' and queryid != 105189781;
userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_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 | 3055152042 | 951689334 | SELECT powa_user_functions_snapshot() | 2358 | 141768.738 | 4.416 | 228.313 | 60.1224503816793 | 7.31830802358817 | 2358 | 311776 | 3467 | 3536 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.331 | 0
10 | 3055152042 | 1077229976 | SELECT powa_all_relations_snapshot() | 2357 | 159776.632 | 29.307 | 209.913 | 67.7881340687315 | 25.0953617563583 | 2357 | 13865440 | 343403 | 343444 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1.589 | 0
10 | 3055152042 | 1773443275 | SELECT powa_take_snapshot() | 2357 | 1989114.956 | 145.235 | 41980.686 | 843.91809758167 | 3306.94629951111 | 2357 | 165743477 | 1313125 | 1555277 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1833.96 | 0
10 | 3055152042 | 2950805420 | SELECT powa_qualstats_snapshot() | 2357 | 36807.554 | 0.39 | 164.902 | 15.6162723801442 | 12.616370700035 | 2357 | 5479811 | 7373 | 15672 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.935 | 0
10 | 3055152042 | 2857038487 | SELECT powa_kcache_snapshot() | 2357 | 173026.297 | 4.027 | 232.473 | 73.4095447602885 | 26.4572671045701 | 2357 | 22303108 | 289694 | 289768 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2.084 | 0
10 | 3055152042 | 3588286645 | SELECT powa_statements_snapshot() | 2358 | 689089.514000002 | 21.281 | 1544.558 | 292.234738761663 | 82.7828903193194 | 2358 | 113496013 | 542052 | 585903 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1113.779 | 0
(6 rows)
Hi,
install of pg_qualstats et pg_stat_kcache get those error :
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.u dossier de ce type
make: *** Aucune règle pour fabriquer la cible « /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk ». Arrêt.
after installing libpq-dev i still get an error :
Makefile:23: /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk: Aucun fichier ou dossier de ce type
make: *** Aucune règle pour fabriquer la cible « /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk ». Arrêt.
my data directory is /var/lib/PostgreSQL/9.6/main
my conf file is in /etc/ostgresql/9.6/main/
extension files are in /home/pgsqlextensions/
i guess i forgot an options with the "make" command.
someone got an idea to my problem ?
Thanks.
Maxime Bourget-Mauger.
For now
We should add a powa.purge GUC (default 1), and maybe lower default powa.aggregation GUC.
When the extension pg_profile is installed it causes powa to report wrong values.
The reason is that pg_profile resets after taking a snapshots the postgres internal statistics.
It calls several functions to reset statistics for example it calls pg_stat_statements_reset() to reset the counters in pg_stat_statements.
This causes unexpected data in the powa_statements_history... tables.
Now the values are not continuously increasing anymore.
Suggestion:
Add this "side effect" of pg_profile as a known issue to the powa docu.
It seems, powa_servers should be added here : https://github.com/powa-team/powa-archivist/blob/master/powa--4.0.0beta1.sql#L1464
Naive initial list:
This obviously shouldn't been for all databases and all snapshots, so some heuristics will have to be added to know when to perform that. Some ideas for that:
Hi,
before powa installation the query
SELECT COUNT(counter) AS cc FROM users WHERE viewonline IS TRUE AND last > (NOW() - INTERVAL '4 MINUTES');works without any problem.
Here's a explain verbose output of the query (maybe it can be helpful):
Aggregate (cost=180.28..180.29 rows=1 width=8)
Output: count(counter)
-> Seq Scan on public.users (cost=0.00..180.28 rows=1 width=8)
Output: counter, last, notify_story, private, lang, username, password, name, surname, email, gender, birth_date, board_lang, timezone, viewonline, remote_addr, http_user_agent, regis
tration_time
Filter: ((users.viewonline IS TRUE) AND (users.last > (now() - '00:04:00'::interval)))
(5 rows)After powa installation, the same query output is:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
In addiction, application connected to postgres have logs full of this error:
(here's an example of a php application, conneted via PDO to postgresql).
{main}: SQLSTATE[08006] [7] FATAL: the database system is in recovery mode
Disabling powa (commenting out the 4 lines below) everything comes back to the original state, and everything works well.
shared_preload_libraries='pg_stat_statements,powa,pg_stat_kcache,pg_qualstats'
powa.frequency = 10min
powa.retention = 1d
powa.database = powa
powa.coalesce = 100
I found powa really helpful (tuning the DB its so much easier now) thus I hope you can fix this issue ASAP, so I can re-enable it on my server and continuing the tuning.
Thank you.
P.S: if you want the schema structure (the schema on which I run the query above) you can find it here: nerdz-test-db
My PostgreSQL server version is: 9.4.4
Thank you
Hi Team,
I am using PoWA 4.1.4 on PostgreSQL 14.5. I have faced powa snapshot fail error;
powa_take_snapshot(1): function "powa_statements_snapshot" failed: duplicate key value violates unique constraint "powa_statements_pkey"
I truncated the pow_statements table, it worked for a while, but the error occurred again.
What should I do for the solution?
Thanks in advance for your help.
Firstly, thank you for your help.
Im using PostgreSQL 12.5 on RHEL7.9 with Powa 4.0.1.2 and pg_wait_sampling 1.1.2-1 versions.
The PoWa query is running for more than 30 hours active state in the pg_stat_activity view. Also, added pg_stat_activity view info below.
There is no blocking/blocked transaction in the database. But, when I open the PoWa page I get an error with
"Error while calling public.powa_wait_sampling_src: canceling statement due to lock timeout CONTEXT: PL/pgSQL function powa_wait_sampling_src(integer) line 4 at RETURN QUERY"
datid | 16387
datname | powa
pid | 164496
usesysid | 10
usename | postgres
application_name | PoWA - powa_wait_sampling_snapshot(0)
client_addr |
client_hostname |
client_port |
backend_start | 2021-05-04 15:35:51.532214+03
xact_start | 2021-05-04 15:35:51.535921+03
query_start | 2021-05-04 15:35:51.535921+03
state_change | 2021-05-04 15:35:51.535934+03
wait_event_type | IPC
wait_event | MessageQueueInternal
state | active
backend_xid | 3737818164
backend_xmin | 3734534556
query | SELECT public.powa_take_snapshot()
backend_type | powa
Hello,
It seems there is not entry in CHANGELOG for 3.1.2.
Furthermore, the 3.1.2 release does not exist here : https://github.com/powa-team/powa-archivist/releases
Regards,
The code compiles with PG13, but this doesn't look good:
SELECT powa_take_snapshot();
+WARNING: powa_take_snapshot(0): function "powa_statements_snapshot" failed:
+ state : 42703
+ message: column pgss.total_time does not exist
+ detail :
+ hint :
+ context: PL/pgSQL function powa_statements_src(integer) line 4 at RETURN QUERY
+SQL statement "WITH capture AS(
+ SELECT *
+ FROM powa_statements_src(_srvid)
+ ),
...
HI,
I'm trying to implement powa-archivist in my organisation and in order to get reporting on table activity I would need to have the relname of the tables.
But all I can see is that relid only is stored; I see also that the fetching is done against the C library but relname is not retreived.
Would it be possible to collect also the relname and store it in small table (like dbid, relid, relname) ?
Thanks
When building from sources (master) create extension command returning error:
CREATE EXTENSION
powa=# CREATE EXTENSION powa;
ERROR: "res.checkpoints_timed_per_sec" is not a known variable
LINE 12: res.checkpoints_timed_per_sec = (a.checkpoints_timed - b...
^
QUERY:
DECLARE
res powa_stat_bgwriter_history_rate;
sec integer;
BEGIN
res.sec = extract(EPOCH FROM (a.ts - b.ts));
IF res.sec = 0 THEN
sec = 1;
ELSE
sec = res.sec;
END IF;
res.checkpoints_timed_per_sec = (a.checkpoints_timed - b.checkpoints_timed)::double precision / sec;
res.checkpoints_req_per_sec = (a.checkpoints_req - b.checkpoints_req)::double precision / sec;
res.checkpoint_write_time_per_sec = (a.checkpoint_write_time - b.checkpoint_write_time)::double precision / sec;
res.checkpoint_sync_time_per_sec = (a.checkpoint_sync_time - b.checkpoint_sync_time)::double precision / sec;
res.buffers_checkpoint_per_sec = (a.buffers_checkpoint - b.buffers_checkpoint)::double precision / sec;
res.buffers_clean_per_sec = (a.buffers_clean - b.buffers_clean)::double precision / sec;
res.maxwritten_clean_per_sec = (a.maxwritten_clean - b.maxwritten_clean)::double precision / sec;
res.buffers_backend_per_sec = (a.buffers_backend - b.buffers_backend)::double precision / sec;
res.buffers_backend_fsync_per_sec = (a.buffers_backend_fsync - b.buffers_backend_fsync)::double precision / sec;
res.buffers_alloc_per_sec = (a.buffers_alloc - b.buffers_alloc)::double precision / sec;
return res;
END;
Hi
We use PoWA 3.2 (3.2.0-2) with our PostgreSQL 9.6.x databases.
We update recently to PostgreSQL 9.6.17 and since then we have segmentation Fault during execution of function powa_take_snapshot(), and after little time PostgreSQL entering Recovery mode.
Database Server is on Red Hat 7.6.
PostgreSQL log file extract is :
2020-05-12 00:41:20 CEST [53719]: [1594-1] LOG: 00000: worker process: powa (PID 13153) was terminated by signal 11: Segmentation fault
2020-05-12 00:41:20 CEST [53719]: [1595-1] DETAIL: Failed process was running: SELECT powa_take_snapshot()
2020-05-12 00:41:20 CEST [53719]: [1596-1] LOCATION: LogChildExit, postmaster.c:3581
2020-05-12 00:41:20 CEST [53719]: [1597-1] LOG: 00000: terminating any other active server processes
Then several repeated warnings as :
2020-05-12 00:41:20 CEST [19985]: [5-1] WARNING: 57P02: terminating connection because of crash of another server process
2020-05-12 00:41:20 CEST [19985]: [6-1] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
Thanks by advance for your help.
Hi,
install of pg_qualstats et pg_stat_kcache get those error :
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.u dossier de ce type
make: *** Aucune règle pour fabriquer la cible « /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk ». Arrêt.
after installing libpq-dev i still get an error :
Makefile:23: /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk: Aucun fichier ou dossier de ce type
make: *** Aucune règle pour fabriquer la cible « /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk ». Arrêt.
my data directory is /var/lib/PostgreSQL/9.6/main
my conf file is in /etc/ostgresql/9.6/main/
extension files are in /home/pgsqlextensions/
i guess i forgot an options with the "make" command.
someone got an idea to my problem ?
Thanks.
Maxime Bourget-Mauger.
Hello,
We noticed 80% of our powa database wheight was due to SET xxx statements which are not normalized.
It could be interesting to ignore a pattern as it is done in :
https://github.com/powa-team/powa-archivist/blob/master/powa--4.0.1.sql#L2260
We could add an "options" parameter to all functions of jsonb type and a column to powa_servers. Thus we will be able to give options for specifics server.
It implies to change all functions prototypes, the collector and the UI.
Hi,
running sqlsmith on a database with powa installed produced
crashes on pg_reload_conf(). In the backtrace below the handler
fired in a CriticalSection, where AllocSetContextCreate is
forbidden. There are probably a lot more failure modes. For
example, any elog() would be promoted to a PANIC in a Critical
Section.
IMHO powa must do this like the romans do: Don't do anything in
the signal handler besides setting a flag that a reload is
requested and do the actual work outside the handler where it is
safe to do so.
regards,
Andreas
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:58
58 ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:58
#1 0x00007f455d6b440a in __GI_abort () at abort.c:89
#2 0x000055b745359ec3 in ExceptionalCondition (conditionName=conditionName@entry=0x55b7453dae63 "!(CritSectionCount == 0)", errorType=errorType@entry=0x55b74539d29d "FailedAssertion", fileName=fileName@e$
#3 0x000055b74537f1a2 in MemoryContextCreate (tag=tag@entry=T_AllocSetContext, size=size@entry=200, methods=methods@entry=0x55b7458023e0 <AllocSetMethods>, parent=0x55b745a55dc0, name=name@entry=0x55b745$
#4 0x000055b74537e5a0 in AllocSetContextCreate (parent=<optimized out>, name=name@entry=0x55b74551c03c "config file processing", minContextSize=minContextSize@entry=0, initBlockSize=initBlockSize@entry=8$
#5 0x000055b745379905 in ProcessConfigFile (context=PGC_SIGHUP) at guc-file.l:147
#6 0x00007f455d057dae in powa_sighup (postgres_signal_arg=<optimized out>) at powa.c:342
#7 <signal handler called>
#8 0x00007f455d75bbc0 in __close_nocancel () at ../sysdeps/unix/syscall-template.S:84
#9 0x000055b74504709c in XLogFileClose () at xlog.c:3557
#10 0x000055b74504740d in XLogWrite (WriteRqst=..., flexible=flexible@entry=0 '\000') at xlog.c:2253
#11 0x000055b74504809e in XLogFlush (record=15030149624) at xlog.c:2728
#12 0x000055b74503e6ca in RecordTransactionCommit () at xact.c:1286
#13 CommitTransaction () at xact.c:2039
#14 0x000055b74503f465 in CommitTransactionCommand () at xact.c:2874
#15 0x00007f455d057ff2 in powa_main (main_arg=<optimized out>) at powa.c:293
#16 0x000055b7451e560b in StartBackgroundWorker () at bgworker.c:742
#17 0x000055b7451f1135 in do_start_bgworker (rw=0x55b745a48ba0) at postmaster.c:5612
#18 maybe_start_bgworkers () at postmaster.c:5809
#19 0x000055b7451f14a3 in reaper (postgres_signal_arg=<optimized out>) at postmaster.c:2834
#20 <signal handler called>
#21 0x00007f455d761273 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:84
#22 0x000055b744fb29be in ServerLoop () at postmaster.c:1683
#23 0x000055b7451f3153 in PostmasterMain (argc=3, argv=0x55b745a245b0) at postmaster.c:1327
#24 0x000055b744fb469d in main (argc=3, argv=0x55b745a245b0) at main.c:228
This list probably needs further thoughts.
Per idea from Alexander Kukushkin.
in REL_4_0_0, the file powa--4.0.0.sql has a typo : two missing 's' line 584 and 585
res.seq_can_per_sec = (a.seq_scan - b.seq_scan)::double precision / sec;
res.idx_can_per_sec = (a.idx_scan - b.idx_scan)::double precision / sec;
should be
res.seq_scan_per_sec = (a.seq_scan - b.seq_scan)::double precision / sec;
res.idx_scan_per_sec = (a.idx_scan - b.idx_scan)::double precision / sec;
This was mostly a mistake. We cannot do a lot with this historical data anyway, and it makes things confusing.
The plan is:
So, all powa-archivist extensions were updated to 4.2.0. But now I came to another issue with the collector :
2023-11-09 09:47:15,815 hostname:5432 DEBUG : Calling public.powa_statements_src(0)...
2023-11-09 09:47:15,839 hostname:5432 WARNING: Error while inserting data:
invalid input syntax for type bigint: "t"
CONTEXT: COPY powa_statements_src_tmp, line 1, column queryid: "t"
In the repository DB log :
2023-11-09 09:47:15 CET pid=2545344 : 172.23.101.3(35058) - powa CONTEXT: COPY powa_statements_src_tmp, line 1, column queryid: "t"
2023-11-09 09:47:15 CET pid=2545344 : 172.23.101.3(35058) - powa STATEMENT: COPY powa_statements_src_tmp FROM stdin
To my surprise, this happens alos on PostgreSQL 15 instances.
Function powa_statements_src outputs the toplevel column before queryid, and in powa_statements_src_tmp, toplevel is the last column of the table. Isn't there a mismatch in the column order between the data output function and the integration in the powa repo ?
See :
https://github.com/powa-team/powa-archivist/blob/b5835fe0e5cc122b80309c0d0bcdd07e55621838/powa--4.2.0.sql#L2419C1-L2419C1
And :
powa-archivist/powa--4.2.0.sql
Line 756 in b5835fe
Originally posted by @frost242 in powa-team/powa-collector#18 (comment)
Per idea from Alexander Kukushkin.
The (current) list of views is:
Should we snapshot all of them? Note that they only expose oids, so without a remote database access from UI (or a copy of the remote database's catalog), I'm afraid that it won't be that useful.
Right now, since PG14, all queries that can be ran both top level and on a different context have 2 entries, one with toplevel=true, one with toplevel=false.
Of course it makes a mess of stats on those queries, as their min and max are taken from the 2 different series, so you just get absurd values.
Solution would be either to sum them or be able to collect the toplevel indicator too and separate them in the GUI (but i don't really see the point)
on some environments, i got the mins_in_range/maxs_in_range columns toasted on powa_statement_history
the main purpose of having those 2 columns is to get direct access to them
i worked around this on my production server by doing
alter table powa_statements_history alter mins_in_range set storage main;
alter table powa_statements_history alter maxs_in_range set storage main;
``` (and rebuilding the table)
Maybe it's worth doing systematically ?
Hello, I do not know if it is the correct channel but my hard drive is at 100% for all the information collected from the Powa, I have configured the data retention to 30 days, I lowered it to 20 days thinking that it would be deleted automatically but I see that the disk space is kept at 100%
Is there a way to purge the oldest data from the powa database?
As the image shows that way I have the retention interval registered, is it correct?
Thanks and regards
Hi,
I've noticed a compilation problem with powa.c, at line 71
Seems to be an incorrect " pg_attribute_noreturn()" here.
Because powa background worker run constantly it has memory leak.
I suggest exit background worker periodically (by config, default 1 hour for example), and postgres will restart it itself.
I've tried adding this server twice, and there's always this error. Remote setup, pg14 on Powa server and the monitored server, latest Powa from pgdg.
The Powa server is already monitoring 4 other servers so I think it's set up correctly.
Error in GUI:
db3.cwatch.io: powa_take_snapshot(13): function "powa_statements_snapshot" failed: duplicate key value violates unique constraint "powa_statements_pkey"
Error when I try to execute this function from CLI:
powa=# select powa_take_snapshot(13);
WARNING: powa_take_snapshot(13): function "powa_statements_snapshot" failed:
state : 23505
message: duplicate key value violates unique constraint "powa_statements_pkey"
detail : Key (srvid, queryid, dbid, userid)=(13, 3012963866342342194, 16605, 364028942) already exists.
hint :
context: SQL statement "WITH capture AS(
SELECT *
FROM powa_statements_src(_srvid)
),
mru as (UPDATE powa_statements set last_present_ts = now()
FROM capture
WHERE powa_statements.queryid = capture.queryid
AND powa_statements.dbid = capture.dbid
AND powa_statements.userid = capture.userid
AND powa_statements.srvid = _srvid
),
missing_statements AS(
INSERT INTO public.powa_statements (srvid, queryid, dbid, userid, query)
SELECT DISTINCT _srvid, queryid, dbid, userid, query
FROM capture c
WHERE NOT EXISTS (SELECT 1
FROM powa_statements ps
WHERE ps.queryid = c.queryid
AND ps.dbid = c.dbid
AND ps.userid = c.userid
AND ps.srvid = _srvid
)
),
by_query AS (
INSERT INTO public.powa_statements_history_current
SELECT _srvid, queryid, dbid, userid,
ROW(
ts, calls, total_exec_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,
plans, total_plan_time,
wal_records, wal_fpi, wal_bytes
)::powa_statements_history_record AS record
FROM capture
),
by_database AS (
INSERT INTO public.powa_statements_history_current_db
SELECT _srvid, dbid,
ROW(
ts, sum(calls),
sum(total_exec_time), sum(rows), sum(shared_blks_hit),
sum(shared_blks_read), sum(shared_blks_dirtied),
sum(shared_blks_written), sum(local_blks_hit),
sum(local_blks_read), sum(local_blks_dirtied),
sum(local_blks_written), sum(temp_blks_read),
sum(temp_blks_written), sum(blk_read_time), sum(blk_write_time),
sum(plans), sum(total_plan_time),
sum(wal_records), sum(wal_fpi), sum(wal_bytes)
)::powa_statements_history_record AS record
FROM capture
GROUP BY dbid, ts
)
SELECT count(*) FROM capture"
PL/pgSQL function powa_statements_snapshot(integer) line 15 at SQL statement
SQL statement "SELECT powa_statements_snapshot(13)"
PL/pgSQL function powa_take_snapshot(integer) line 63 at EXECUTE
powa_take_snapshot
--------------------
1
(1 row)
The statements snapshot doesn't have any entries for this server:
powa=# select * from powa_statements where srvid=13;
srvid | queryid | dbid | userid | query | last_present_ts
-------+---------+------+--------+-------+-----------------
(0 rows)
Hi,
I'm glad to see that dalibo is trying to build a tool that is lacking actually on postgresql.
We worked on a similar approach in my company few month ago and added several enhancements that I would like to propose :
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
While viewing statistics via powa-web, I found two sql queries with the same query text, but one of them had constant parameters, the other one had readable constants instead of $1,$2 etc.
I thought that powa takes the query text from the pg_stat_statements view, but I did not find any query with constants there.
For query with constant we have wrong statistics (see screenshot)
powa-archivist needs updating for PostgreSQL 10:
00:00:26.036 gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fdebug-prefix-map=/build/postgresql-10-b8KUP6/postgresql-10-10~beta4=. -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/10/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/mit-krb5 -c -o powa.o powa.c
00:00:26.282 powa.c: In function '_PG_init':
00:00:26.282 powa.c:150:9: error: 'BackgroundWorker {aka struct BackgroundWorker}' has no member named 'bgw_main'; did you mean 'bgw_name'?
00:00:26.282 worker.bgw_main = powa_main;
00:00:26.282 ^~~~~~~~
00:00:26.282 bgw_name
00:00:26.284 powa.c: In function 'powa_main':
00:00:26.284 powa.c:277:4: error: too few arguments to function 'WaitLatch'
00:00:26.284 WaitLatch(&MyProc->procLatch,
00:00:26.284 ^~~~~~~~~
00:00:26.284 In file included from powa.c:17:0:
00:00:26.284 /usr/include/postgresql/10/server/storage/latch.h:174:12: note: declared here
00:00:26.284 extern int WaitLatch(volatile Latch *latch, int wakeEvents, long timeout,
00:00:26.284 ^~~~~~~~~
Hi !
We are facing next issue during using POWA and PostgreSQL 15:
drop database query[2023-01-30 15:01:16.758 UTC][source=postgresql]STATEMENT: drop database testdb_kmlbp_0;
[2023-01-30 15:01:21.760 UTC][source=postgresql]LOG: still waiting for backend with PID 3173 to accept ProcSignalBarrier
process with 3173 is POWA:
postgres 3173 0.0 0.2 333920 54248 ? Ss 14:30 0:01 postgres: patroni: powa idle
I am using local mode of powa with the bgworker
I've found similar issue for timescaledb, may be it related somehow timescale/timescaledb#4838
As requested by Israel Brewster.
Hello,
On a local powa installation, we have upgraded powa from 4.0.0 to 4.1.3, powa-web from 4.0.0 to 4.1.2. Ever since, the snapshots fail to execute, the webui shows an invalid input syntax for type integer: "" error, and data doesn't get collected. Here is the full error from postgresql.conf:
[8827]: [156-1] user=,db=,client= WARNING: powa_take_snapshot(0): function "powa_statements_snapshot" failed:
state : 22P02
message: invalid input syntax for type integer: ""
detail :
hint :
context: PL/pgSQL function powa_statements_src(integer) line 6 at SQL statement
SQL statement "WITH capture AS(
SELECT *
FROM powa_statements_src(_srvid)
),
mru as (UPDATE powa_statements set last_present_ts = now()
FROM capture
WHERE powa_statements.queryid = capture.queryid
AND powa_statements.dbid = capture.dbid
AND powa_statements.userid = capture.userid
AND powa_statements.srvid = _srvid
),
missing_statements AS(
INSERT INTO public.powa_statements (srvid, queryid, dbid, userid, query)
SELECT _srvid, queryid, dbid, userid, min(query)
FROM capture c
WHERE NOT EXISTS (SELECT 1
FROM powa_statements ps
WHERE ps.queryid = c.queryid
AND ps.dbid = c.dbid
AND ps.userid = c.userid
AND ps.srvid = _srvid
)
GROUP BY queryid, dbid, userid
),
by_query AS (
INSERT INTO public.powa_statements_history_current
SELECT _srvid, queryid, dbid, userid,
ROW(
ts, calls, total_exec_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,
plans, total_plan_time,
wal_records, wal_fpi, wal_bytes
)::powa_statements_history_record AS record
FROM capture
),
by_database AS (
INSERT INTO public.powa_statements_history_current_db
SELECT _srvid, dbid,
ROW(
ts, sum(calls),
sum(total_exec_time), sum(rows), sum(shared_blks_hit),
sum(shared_blks_read), sum(shared_blks_dirtied),
sum(shared_blks_written), sum(local_blks_hit),
sum(local_blks_read), sum(local_blks_dirtied),
sum(local_blks_written), sum(temp_blks_read),
sum(temp_blks_written), sum(blk_read_time), sum(blk_write_time),
sum(plans), sum(total_plan_time),
sum(wal_records), sum(wal_fpi), sum(wal_bytes)
)::powa_statements_history_record AS record
FROM capture
GROUP BY dbid, ts
)
SELECT count(*) FROM capture"
PL/pgSQL function powa_statements_snapshot(integer) line 15 at SQL statement
SQL statement "SELECT powa_statements_snapshot(0)"
PL/pgSQL function powa_take_snapshot(integer) line 63 at EXECUTE
SQL statement "SELECT public.powa_take_snapshot()"
Here are the extensions versions :
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
btree_gist | 1.5 | public | support for indexing common datatypes in GiST
hypopg | 1.3.0 | public | Hypothetical indexes for PostgreSQL
pg_qualstats | 2.0.3 | public | An extension collecting statistics about quals
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
powa | 4.1.3 | public | PostgreSQL Workload Analyser-core
Any idea as to why this is occurring?
Thank you!
Just to not forget, I spotted several bugs in $title
Several columns have the same name:
powa-archivist/powa--4.1.3.sql
Line 3896 in 0fb11f3
powa-archivist/powa--4.1.3.sql
Line 3909 in 0fb11f3
powa-archivist/powa--4.1.3.sql
Line 3922 in 0fb11f3
The order by should be ASC
powa-archivist/powa--4.1.3.sql
Line 3891 in 0fb11f3
I try to rewrite this query with window function, I'll address all this issues.
Create extension powa fails as below:
postgres=# create extension powa
postgres-# ;
ERROR: "res.mean_err_estimate_num" is not a known variable
LINE 10: res.mean_err_estimate_num = a.mean_err_estimate_num - b....
^
QUERY:
DECLARE
res powa_qualstats_history_diff;
BEGIN
res.intvl = a.ts - b.ts;
res.occurences = a.occurences - b.occurences;
res.execution_count = a.execution_count - b.execution_count;
res.nbfiltered = a.nbfiltered - b.nbfiltered;
res.mean_err_estimate_ratio = a.mean_err_estimate_ratio - b.mean_err_estimate_ratio;
res.mean_err_estimate_num = a.mean_err_estimate_num - b.mean_err_estimate_num;
return res;
END;
Traced it to the following piece of code in powa--4.0.0beta1.sql (line numbers included for reference) - line 1346 should read mean_err_estimate_num
1339 /* pg_qualstats operator support */
1340 CREATE TYPE powa_qualstats_history_diff AS (
1341 intvl interval,
1342 occurences bigint,
1343 execution_count bigint,
1344 nbfiltered bigint,
1345 mean_err_estimate_ratio double precision,
1346 mean_err_estimate_nun double precision
1347 );
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.