Thread

1. autovacuum_vacuum_cost_delay

   Shenavai, Manuel <manuel.shenavai@sap.com> — 2024-07-09T11:38:40Z

   Hi everyone,
   
   We are doing some tests with different autovacuum settings.
   
   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?
   
   Thanks in advance &
   Best regards,
   Manuel
   

2. Re: autovacuum_vacuum_cost_delay

   Muhammad Ikram <mmikram@gmail.com> — 2024-07-09T13:42:11Z

   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:
   >
   >
   > 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?
   >
   >
   >
   > Thanks in advance &
   > Best regards,
   >
   > Manuel
   >
   
   
   -- 
   Muhammad Ikram
   

3. Re: autovacuum_vacuum_cost_delay

   Christoph Moench-Tegeder <cmt@burggraben.net> — 2024-07-09T16:23:50Z

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