Comments (12)
What I found on my powa repository DB
powa_kcache_metrics_current was as big as 133Gb
and SELECT powa_kcache_aggregate(13)
threw an error:
ERROR: duplicate key value violates unique constraint "powa_kcache_metrics_pkey" Detail: Key (srvid, coalesce_range, queryid, dbid, userid, top)=(13, ["2021-03-16 12:19:31.162797-07","2021-03-16 16:46:16.570921-07"], -9206572327289564196, 862035, 861727, t) already exists.
PL/pgSQL function powa_kcache_aggregate(integer) line 12 at SQL statement
and my fix was adding ON CONFLICT DO NOTHING;
I don't know if it is acceptable solution
`CREATE OR REPLACE FUNCTION public.powa_kcache_aggregate(_srvid integer)
RETURNS void
LANGUAGE plpgsql
AS
DECLARE
result bool;
v_funcname text := 'powa_kcache_aggregate(' || _srvid || ')';
v_rowcount bigint;
BEGIN
PERFORM powa_log(format('running %I', v_funcname));
PERFORM powa_prevent_concurrent_snapshot(_srvid);
-- aggregate metrics table
INSERT INTO public.powa_kcache_metrics (coalesce_range, srvid, queryid,
top, dbid, userid, metrics,
mins_in_range, maxs_in_range)
SELECT tstzrange(min((metrics).ts), max((metrics).ts),'[]'),
srvid, queryid, top, dbid, userid, array_agg(metrics),
ROW(min((metrics).ts),
min((metrics).plan_reads), min((metrics).plan_writes),
min((metrics).plan_user_time), min((metrics).plan_system_time),
min((metrics).plan_minflts), min((metrics).plan_majflts),
min((metrics).plan_nswaps),
min((metrics).plan_msgsnds), min((metrics).plan_msgrcvs),
min((metrics).plan_nsignals),
min((metrics).plan_nvcsws), min((metrics).plan_nivcsws),
min((metrics).exec_reads), min((metrics).exec_writes),
min((metrics).exec_user_time), min((metrics).exec_system_time),
min((metrics).exec_minflts), min((metrics).exec_majflts),
min((metrics).exec_nswaps),
min((metrics).exec_msgsnds), min((metrics).exec_msgrcvs),
min((metrics).exec_nsignals),
min((metrics).exec_nvcsws), min((metrics).exec_nivcsws)
)::powa_kcache_type,
ROW(max((metrics).ts),
max((metrics).plan_reads), max((metrics).plan_writes),
max((metrics).plan_user_time), max((metrics).plan_system_time),
max((metrics).plan_minflts), max((metrics).plan_majflts),
max((metrics).plan_nswaps),
max((metrics).plan_msgsnds), max((metrics).plan_msgrcvs),
max((metrics).plan_nsignals),
max((metrics).plan_nvcsws), max((metrics).plan_nivcsws),
max((metrics).exec_reads), max((metrics).exec_writes),
max((metrics).exec_user_time), max((metrics).exec_system_time),
max((metrics).exec_minflts), max((metrics).exec_majflts),
max((metrics).exec_nswaps),
max((metrics).exec_msgsnds), max((metrics).exec_msgrcvs),
max((metrics).exec_nsignals),
max((metrics).exec_nvcsws), max((metrics).exec_nivcsws)
)::powa_kcache_type
FROM powa_kcache_metrics_current
WHERE srvid = _srvid
GROUP BY srvid, queryid, top, dbid, userid
**ON CONFLICT DO NOTHING;**
`
from powa-archivist.
Hi,
Do you have some monitoring to see how is the growth factor like? If it's a constant and regular growth then it's likely due to inadequate autovacuum tuning.
For the ON CONFLICT, it's a workaround for a bug. So sure for now you can keep it as is, but it's a sign that there's something wrong going on here, and the root problem should be fixed instead.
That being said I have no clue how that could be happening. It seems that the function is aggregating the same data twice, but that shouldn't be possible because the function removes the data it just aggregated. As the function is atomic, you should either have both aggregated the data and remove the source OR done nothing.
Ideally what should be done is removing the ON CONFLICT cause, wait to see if the error happens again and if it does check if the problematic record exists in powa_kcache_metrics table.
from powa-archivist.
Unfortunately, I don't have any monitoring in place..
I monitor 6 clusters (253 DBs), retention policy looks like:
DBs Retention
4 3 days
4 7 days
245 2 days
Table was bloated, after vacuum full it became: 12 GB (133Gb before)
I use default auto vacuum setting on postgres and nothing special on that table.
To find the root cause, I am going to remove ON CONFLICT and start monitoring it carefully, to understand what causes the issue.
Thank you for support.
from powa-archivist.
Found what is wrong with auto vacuum on my powa repository DB - I have powa_take_snapshot constantly running. auto vacuum doesn't have enough time to complete between snapshots.
from powa-archivist.
What does those values actually mean?
But even if there's always one or multiple powa_take_snapshot() running, it shouldn't prevent autovacuum from working. It only means that you'll have an amount of bloat equal to the maximum number of snapshot being performed between two autovacuum runs. It can be a bit high, but it should stay relatively constant. And I don't think that it should represent 120GB.
Note also that if you have pg_qualstats setup and a lot of databases and/or users and/or different normalized queries, the aggregation can be quite expensive. We have a fix for that (682b753) but it's not released yet. If you have pg_stat_statements enabled (and maybe a local powa) on your repository server you could check if you could benefit from this patch.
from powa-archivist.
from powa-archivist.
Hi @banlex73, any news on this issue?
from powa-archivist.
from powa-archivist.
Ok, thanks!
from powa-archivist.
All good now, thank you
from powa-archivist.
All good now, thank you
from powa-archivist.
Thanks a lot for the confirmation!
from powa-archivist.
Related Issues (20)
- Add possibility to ignore query HOT 1
- PG13: powa_take_snapshot(0): function "powa_statements_snapshot" failed: column pgss.total_time does not exist HOT 7
- Snapshot pg_stat_progres_* views? HOT 3
- Work without create extension HOT 1
- Bugs in powa_qualstats_aggregate_constvalues_current, suggested rewrite HOT 2
- powa_wait_sampling_snapshot(0) query is running by active state for a long time HOT 5
- does powa support PG14? HOT 6
- hard disk full HOT 2
- memory leak HOT 11
- duplicate key value violates unique constraint "powa_statements_pkey" HOT 12
- Extension pg_profile influences extension powa HOT 3
- powa_take_snapshot fails following powa upgrade to 4.1.3 HOT 20
- "powa_statements_snapshot" failed HOT 4
- DROP queries are hanged if powa is enabled HOT 5
- we need to take toplevel into account in pg_stat_statements HOT 2
- Pg 17 support HOT 3
- Problem with remote collection since toplevel was added HOT 15
- Unparameterized sql query in powa_statements table HOT 16
- make the min/max fields stay in the main table HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 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.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
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.
from powa-archivist.