search

powa-team / powa-archivist

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

Create powa--4.0.1--4.0.2.sql #32

Closed banlex73 closed 4 years ago

banlex73 commented 4 years ago

powa_statements clean up functionality New column: created_ts timestamptz NULL DEFAULT now(); and FUNCTION powa_statements_purge was updated to delete obsolete records from powa_statements

rjuju commented 4 years ago

Thanks for working on this!

If I understand correctly, the new behavior would be to drop statements that were created "powa_retention" days before the purge function is called, whether they were used afterwards or not. This should work well with workloads that implies a lot of new queryid, but wouldn't that be problematic for workload with stable set of queries?

For those, the powa_statements rows will get deleted when the purge function is called, and inserted again during the next snapshot right? It also means that if you use the UI after the call, you won't be able to get the underlying query text.

Have you considered implementing a last_present_ts instead, that would be updated during snapshot every time the queryid still exists in pg_stat_statements output? This should ensure that we don't drop the powa_statements lines too early. This will add some more activity on powa_statements table, but autovacuum should be able to keep bloat under control.

banlex73 commented 4 years ago

good point! I will implement your suggestions. PR will be updated later next week - need time to test it in a real world.

On Sat, Jun 20, 2020, 23:13 Julien Rouhaud notifications@github.com wrote:

Thanks for working on this!

If I understand correctly, the new behavior would be to drop statements that were created "powa_retention" days before the purge function is called, whether they were used afterwards or not. This should work well with workloads that implies a lot of new queryid, but wouldn't that be problematic for workload with stable set of queries?

For those, the powa_statements rows will get deleted when the purge function is called, and inserted again during the next snapshot right? It also means that if you use the UI after the call, you won't be able to get the underlying query text.

Have you considered implementing a last_present_ts instead, that would be updated during snapshot every time the queryid still exists in pg_stat_statements output? This should ensure that we don't drop the powa_statements lines too early. This will add some more activity on powa_statements table, but autovacuum should be able to keep bloat under control.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-archivist/pull/32#issuecomment-647084093, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYDX3WWY6FUYJ6CTTX3RXWQHFANCNFSM4ODR22BQ .

banlex73 commented 4 years ago

just commited new update. added last_present_ts to store MRU as advised added performance index on last_present_ts updated powa_statements_snapshot and powa_statements_purge

сб, 20 черв. 2020 о 23:13 Julien Rouhaud notifications@github.com пише:

Thanks for working on this!

If I understand correctly, the new behavior would be to drop statements that were created "powa_retention" days before the purge function is called, whether they were used afterwards or not. This should work well with workloads that implies a lot of new queryid, but wouldn't that be problematic for workload with stable set of queries?

For those, the powa_statements rows will get deleted when the purge function is called, and inserted again during the next snapshot right? It also means that if you use the UI after the call, you won't be able to get the underlying query text.

Have you considered implementing a last_present_ts instead, that would be updated during snapshot every time the queryid still exists in pg_stat_statements output? This should ensure that we don't drop the powa_statements lines too early. This will add some more activity on powa_statements table, but autovacuum should be able to keep bloat under control.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-archivist/pull/32#issuecomment-647084093, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYDX3WWY6FUYJ6CTTX3RXWQHFANCNFSM4ODR22BQ .

rjuju commented 4 years ago

Thanks! I merged the PR as is, I'll handle the rest of the required modifications.