Thread: autovacuum_vacuum_cost_delay
Hi everyone,
We are doing some tests with different autovacuum settings.
Looking at autovacuum_vacuum_cost_delay:
Can someone help to understand what a high or low value of this setting really means? Would it be OK to set this to 0? If not, why not?
Thanks in advance &
Best regards,
Manuel
Hi Shenvail,
Based on what I could understand,
Lower value will cause more frequent run of Autovacuum. Will have less delay between processing data chunks but will put more load on the I/O system. Autovacuum tasks will complete faster. Cleanup of dead tuples and blots will be quicker thus increasing performance in the cases where tables are heavily updated/deleted.
Higher values will result in longer delays between processing data chunks thus slowing down the completion of autovacuum tasks. Autovacuum will have less I/O load for its tasks. Database performance will be degraded if operations are more update/delete intensive. Dead tuples and bloat will take longer to be cleaned up.
It can be beneficial for performance of other database operations, especially if your system is I/O-bound. Can also be helpful when system resources are less
Regards,
Muhammad Ikram
Bitnine Global.
On Tue, Jul 9, 2024 at 4:38 PM Shenavai, Manuel <manuel.shenavai@sap.com> wrote:
Hi everyone,
We are doing some tests with different autovacuum settings.
Looking at autovacuum_vacuum_cost_delay:
Can someone help to understand what a high or low value of this setting really means? Would it be OK to set this to 0? If not, why not?
Thanks in advance &
Best regards,Manuel
Muhammad Ikram
## Shenavai, Manuel (manuel.shenavai@sap.com): > Looking at autovacuum_vacuum_cost_delay: > https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY > > Can someone help to understand what a high or low value of this setting really means? Would it be OK to set this to 0?If not, why not? The autovacuum settings link to the docs of the regular vacuum settings: https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST which explains the parameters at least as good as I could do here. You can set autovacuum_vacuum_cost_delay to 0 - that would give you "unlimited" autovacuum bandwidth (limited by whatever your system and your other configuration can give you). That might create very noticeable performance impact on the actual workload, which is most often rather undesirable. In short: it's possible to set that parameter to 0, but that could well be a not-so-good idea. Regards, Christoph -- Spare Space