search

powa-team / powa

PostgreSQL Workload Analyzer
http://powa.readthedocs.io/
PostgreSQL License
777 stars 57 forks source link

powa_statements table keeps growing #133

Closed banlex73 closed 4 years ago

banlex73 commented 4 years ago

I noticed that powa_statements table keeps growing constantly and it causes performance issue. Expected that powa_statements_purge will clean it up but it deletes obsolete records from powa_statements_history and powa_statements_history_db only

rjuju commented 4 years ago

Hello.

Yes, that's expected behavior for now. This is based on the assumption that a single database will have a finite set of normalized queries. I'm assuming that your workload is causing this assumption to break. Do you know if for instance you're using a lot of temporary tables, or if you're dropping and creating table frequently?

banlex73 commented 4 years ago

Bonjour Julien Thank you for your swift response. I noticed this issue on remote repository. We have tons of databases to monitor and this is the problem for us. here's some stats: Total servers: 22 Tatal databases: 1032 Retention: 3 days Frequency: 60 sec mostly (4 NP clusters have 600 sec) Probably, we need to add a timestamp to the powa_statements and run a purge frequently.

Best regards Andriy

пн, 8 черв. 2020 о 11:36 Julien Rouhaud notifications@github.com пише:

Hello.

Yes, that's expected behavior for now. This is based on the assumption that a single database will have a finite set of normalized queries. I'm assuming that your workload is causing this assumption to break. Do you know if for instance you're using a lot of temporary tables, or if you're dropping and creating table frequently?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa/issues/133#issuecomment-640800611, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYBWAHB4ZGW5B4K2EL3RVUVSPANCNFSM4NYVDXQQ .

rjuju commented 4 years ago

You mean some kind of LRU for the statements? That's probably the easiest way to enable a cheap purge for those rows and make sure those are also automatically cleaned up.

Note that the only FK that exist for powa_statements are for powa_qualstats_quals, so you can probably run a massive purge by checking the rows not reference in that tables, run a vacuum or vacuum full, and if any statements is still active on the remote servers they'll be added back at the next snapshot.

BTW, do you have pg_qualstats enabled? It seems that you can run into the issue with powa_qualstats_quals table.

banlex73 commented 4 years ago

yes, LRU is the way to go. and yes, I have pg_qualstats enabled on all instances. I am implementing here:

ALTER TABLE public.powa_statements ADD ts timestamptz NULL DEFAULT now(); ----powa_statements_purge ---

*DELETE* *FROM* powa_statements *WHERE* ts < (*now*() - v_retention) *AND* srvid = _srvid; *GET* *DIAGNOSTICS* v_rowcount = ROW_COUNT; *perform* powa_log(format('%I (powa_statements) - rowcount: %s', v_funcname, v_rowcount)); *END*; *$function$* ; If everything works fine I can create a PR. Also I use pg_repack daily to keep repository database healthy пн, 8 черв. 2020 о 12:05 Julien Rouhaud пише: > You mean some kind of LRU for the statements? That's probably the easiest > way to enable a cheap purge for those rows and make sure those are also > automatically cleaned up. > > Note that the only FK that exist for powa_statements are for > powa_qualstats_quals, so you can probably run a massive purge by checking > the rows not reference in that tables, run a vacuum or vacuum full, and if > any statements is still active on the remote servers they'll be added back > at the next snapshot. > > BTW, do you have pg_qualstats enabled? It seems that you can run into the > issue with powa_qualstats_quals table. > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > , or > unsubscribe > > . >
rjuju commented 4 years ago

Thank you so much!

banlex73 commented 4 years ago

PR was created. Please review when time permits

пн, 8 черв. 2020 о 12:47 Julien Rouhaud notifications@github.com пише:

Thank you so much!

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa/issues/133#issuecomment-640849269, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYHMLCR5KTCCAT7FAADRVU55FANCNFSM4NYVDXQQ .

rjuju commented 4 years ago

Thanks a lot for the PR! I merged it, and added the required boilerplate code required for a new extension version (see https://github.com/powa-team/powa-archivist/commit/8a50508de3ad106264839066c66daba9f51306e1).

banlex73 commented 4 years ago

Thank you Julien!

https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail Virus-free. www.avast.com https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

сб, 4 лип. 2020 о 10:12 Julien Rouhaud notifications@github.com пише:

Thanks a lot for the PR! I merged it, and added the required boilerplate code required for a new extension version (see powa-team/powa-archivist@ 8a50508 https://github.com/powa-team/powa-archivist/commit/8a50508de3ad106264839066c66daba9f51306e1 ).

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa/issues/133#issuecomment-653790180, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYE2BKYI2ZZ6BVGFVJLRZ5PHDANCNFSM4NYVDXQQ .