Re: [PERFORM] autovacuum on a -mostly- r/o table

[Edoardo Ceccarelli]

Bill Moran wrote: In response to Edoardo Ceccarelli <[EMAIL PROTECTED]>: I have read that autovacuum cannot check to see pg load before launching vacuum but is there any patch about it? that would sort out the problem in a good and simple way. Otherwise, which kind of set of parameters I should put in autovacuum configuration? I am stuck because in our case the table gets mostly read and if I set up things as to vacuum the table after a specific amount of insert/updates, I cannot foresee whether this could happen during daytime when server is under high load. How can I configure the vacuum to run after the daily batch insert/update? It doesn't sound as if your setup is a good match for autovacuum. You might be better off going back to the cron vacuums. That's the beauty of Postgres -- it gives you the choice. If you want to continue with autovac, you may want to experiment with vacuum_cost_delay and associated parameters, which can lessen the impact of vacuuming. The db is constantly monitored during high peak so that we can switch to a backup pg7.3 database that is being vacuumed every night. This is giving me the opportunity to try it so I tried this: vacuum_cost_delay = 200 vacuum_cost_page_hit = 5 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty = 20 vacuum_cost_limit = 100 I know these values affect the normal vacuum process but apparently this means setting #autovacuum_vacuum_cost_delay = -1      # default vacuum cost delay for                                         # autovac, -1 means use                                         # vacuum_cost_delay and #autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for                                         # autovac, -1 means use                                         # vacuum_cost_limit for the rest of them I am currently trying the deafults: #autovacuum_naptime = 60                # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000     # min # of tuple updates before vacuum #autovacuum_analyze_threshold = 500     # min # of tuple updates before analyze #autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before vacuum #autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before analyze Does anybody know which process is actually AUTO-vacuum-ing the db? So that I can check when is running...