powa_statements table keeps growing

[banlex73]

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]

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]

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 <#133 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYBWAHB4ZGW5B4K2EL3RVUVSPANCNFSM4NYVDXQQ> .

[rjuju]

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]

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 --- <SKIPPED> *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 <notifications@github.com> пише:

…

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 <#133 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYBZZCCUD4L5TE5VNRLRVUY67ANCNFSM4NYVDXQQ> .

[rjuju]

Thank you so much!

[banlex73]

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 <#133 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYHMLCR5KTCCAT7FAADRVU55FANCNFSM4NYVDXQQ> .

[rjuju]

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).

[banlex73]

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 <powa-team/powa-archivist@8a50508> ). — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#133 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYE2BKYI2ZZ6BVGFVJLRZ5PHDANCNFSM4NYVDXQQ> .