a verbose option for autovacuum
Hi all I was surprised to see that there's no way to get `VACUUM VERBOSE`-like output from autovacuum. Is there any interest in enabling this? Additionally, is there any interest in exposing more vacuum options to be run by autovac? Right now it runs FREEZE and ANALYZE, which leaves the VERBOSE, SKIP_LOCKED, INDEX_CLEANUP, and TRUNCATE unconfigurable. I skipped FULL in that list because I'm assuming no one would ever want autovac to run VACUUM FULL. Tommy
Re: a verbose option for autovacuum
Hey Tom > Seems like that would very soon feel like log spam. What would be the > use case for having this on? If you want one-off results you could > run VACUUM manually. In my case I have a fairly large, fairly frequently updated table with a large number of indexes where autovacuum's runtime can fluctuate between 12 and 24 hours. If I want to investigate why autovacuum today is running many hours longer than it did last week, the only information I have to go off is from pg_stat_progress_vacuum, which reports only progress based on the number of blocks completed across _all_ indexes. VACUUM VERBOSE's output is nice because it reports runtime per index, which would allow me to see if a specific index has bloated more than usual. I also have autovacuum throttled much more aggressively than manual vacuums, so information from a one-off manual VACUUM isn't comparable. As for log spam, I'm not sure it's a problem as long as the verbose option is disabled by default. Tommy On Fri, Jan 22, 2021 at 2:33 PM Tom Lane wrote: > Tommy Li writes: > > I was surprised to see that there's no way to get `VACUUM VERBOSE`-like > > output from autovacuum. Is there any interest in enabling this? > > Seems like that would very soon feel like log spam. What would be the > use case for having this on? If you want one-off results you could > run VACUUM manually. > > > Additionally, is there any interest in exposing more vacuum options to be > > run by autovac? Right now it runs FREEZE and ANALYZE, which leaves the > > VERBOSE, SKIP_LOCKED, INDEX_CLEANUP, and TRUNCATE unconfigurable. > > To the extent that any of these make sense in autovacuum, I'd say they > ought to be managed automatically. I don't see a strong argument for > users configuring this. (See also nearby thread about allowing index > AMs to control some of this.) > > regards, tom lane >
Re: a verbose option for autovacuum
Hi Stephen > ... can set vacuum options on a table level which autovacuum should respect, > such as vacuum_index_cleanup and vacuum_truncate. For skip locked, > autovacuum already will automatically release it's attempt to acquire a > lock if someone backs up behind it for too long. This is good information, I wasn't aware that autovacuum respected those settings. In that case I'd like to focus specifically on the verbose aspect. My first thought was a new boolean configuration called "autovacuum_verbose". I'd want it to behave similarly to autovacuum_vacuum_cost_limit in that it can be set globally or on a per-table basis. Thoughts? Tommy
Re: a verbose option for autovacuum
Hi Masahiko > If we set > it per-table basis, it’s useful when the user already knows which > tables are likely to take a long time for autovacuum I would assume that's the default case, most apps I've seen are designed around a small number of large tables that take up most of the maintenance effort > Regarding when to log, we can have autovacuum emit index vacuum log > after each lazy_vacuum/cleanup_index() end like VACUUM VERBOSE does, > but I’m not sure how it could work together with > log_autovacuum_min_duration. I do like having this rolled into the existing configuration. This might be an absurd idea, but what if the autovacuum process accumulates the per-index vacuum information until that threshold is reached, and then spits out the logs all at once? And after the min duration is passed, it just logs the rest of the index vacuum information as they occur. That way the information is more likely to be available to investigate an abnormally long running vacuum while it's still happening. Tommy
