Growing table powa_statements_src_tmp

[Alexise23]

Hi,
I have powa v 4 configured with 4 Postgresql servers and dedicated repository database server. The powa_statements_src_tmp table is growing by a size of 44 GB, how can I reduce the size? , can I manually truncate?.

Thanks in advance

[banlex73]

Hi Alexise23 1. Find the root cause in postgres log on powa repository or UI shows you it. 2. Try to fix it 3. Yes, you can manually run * truncate table powa_statements_src_tmp * Let me know if you need any help ср, 2 груд. 2020 о 12:14 Alexise23 <notifications@github.com> пише:

Hi, I have powa v 4 configured with 4 Postgresql servers and dedicated repository database server. The powa_statements_src_tmp table is growing by a size of 44 GB, how can I reduce the space? , can I manually truncate?. Thanks in advance — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#119>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYE4ZZZGOJKL2R2OAYLSS2N37ANCNFSM4UK7U2RA> .

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

[Alexise23]

no, thank you very much

[rjuju]

Is the table still growing, or did the size stabilized? How much retention did you configure for the servers?

Do you see any logs on the repository server and/or powa-collector?

This table should almost always be empty, as the data is only there the time needed to perform a snapshot, and should probably not even be visible outside of the powa-collector transaction. Can you give the result of the following query?

select srvid, ts, count(*) from powa_statements_src_tmp group by 1, 2;

If needed you can truncate this table, the worse that could happen is that you lose data for a single snapshot, for one or multiple remote servers.

[Alexise23]

Hi rjuju,
The size table stabilized, another question , can I truncate manually the table powa_all_relations_src_tmp?

Thanks in advance

[rjuju]

Hi @Alexise23. As I said nothing really bad should happen by truncating this table, but if you can provide the result of the mentioned query, I'll know if there's a bug (like leaked rows or something) that need to be fixed, or just autovacuum concerns that should be properly documented.

[Alexise23]

Hi rjuju , The result of the mentioned query is zero rows, but there is another table that is growing: powa_all_relations_src_tmp , can I truncate it manually ?

[rjuju]

Oh right, I'm sorry I didn't realize it was a different table. It's also safe to truncate that table, but it would be nice to know if there are any rows, something like:

select srvid, ts, count(*) from powa_all_relations_src_tmp group by 1, 2;

[Alexise23]

Hi rjuju,
The result the mentioned query is zero rows.

Thanks

[rjuju]

Thanks a lot for checking!

So, since there is no apparently leakage of orphan rows, you could even go with a VACUUM FULL, that should be almost instant, without any risk of removing unwanted rows.

It also seem that autovacuum is definitely not doing its job fast enough given the amount of bloat. If you don't change anything this is likely to happen again. Can you try to make autovacuum more aggressive, and/or lower the threshold for the powa tables?

Also, do you have an estimation of the number of rows for the pg_stat_all_tables and pg_stat_all_indexes on all remote servers? And do you have any idea on how fast the table size grew on the *_src_tmp tables?

[Alexise23]

Hi rjuju ,
Set up a vacuum aggressive and analyze for two tables that have bloat, the result of rows for pg_stat_all_tables. = 181 rows and for the pg_stat_all_indexes = 229 rows, after of FULL VACUUM for table powa_statements_src_tmp grow to size 10 GB of bloat in less 24 hours, then forces me to perform vacuum aggressive because the bloat is caused by constant DELETE of tuples and this was filling the storage of server.

Thanks rjuju

[banlex73]

I have setup pg_repack daily to get rid of bloated objects. вт, 5 січ. 2021 о 15:37 Alexise23 <notifications@github.com> пише:

…

Hi rjuju , Set up a vacuum aggressive and analyze for two tables that have bloat, the result of rows for pg_stat_all_tables. = 181 rows and for the pg_stat_all_indexes = 229 rows, after of FULL VACUUM for table powa_statements_src_tmp grow to size 10 GB of bloat in less 24 hours, then forces me to perform vacuum aggressive because the bloat is caused by constant DELETE of tuples and this was filling the storage of server. Thanks juju — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#119 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYG6QVMT677UMRRD4R3SYOPDRANCNFSM4UK7U2RA> .

[rjuju]

@Alexise23 sorry for the late answer. What exact configuration did you use for more aggressive vacuum on those 2 tables? Did you notice any message in the logs for autovacuum on those tables (like regularly canceled task due to conflicting lock for instance)?

[banlex73]

powa_statements_src_tmp as you see from its name is a temporary (by its nature) table. If no snapshots are running, there should be no records at all. When you noticed it grew up to 10Gb, were there any records? if yes there's nothing to do with vacuum, check postgres log or *powa_snapshot_metas* column *errors *for error details. First of all you need to fix that error.. пт, 5 лют. 2021 о 23:14 Julien Rouhaud <notifications@github.com> пише:

…

@Alexise23 <https://github.com/Alexise23> sorry for the late answer. What exact configuration did you use for more aggressive vacuum on those 2 tables? Did you notice any message in the logs for autovacuum on those tables (like regularly canceled task due to conflicting lock for instance)? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#119 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYDBC7TXPQRFISF664TS5TT4JANCNFSM4UK7U2RA> .

[rjuju]

@banlex73 true, but if I understand correctly this wasn't the number of rows in the table but the estimated number of rows stored in statistics, so there may not be an issue on that side.

[rjuju]

Since there's no activity here anymore I'm closing this issue. Feel free to reopen if needed.