Re: More logging for autovacuum

On Tue, 7 Aug 2007, Decibel! wrote:

> Is logging really the answer for that? ISTM it'd be better to provide
> statistics info so that you could monitor autovacuum activity with
> things like cricket, SNMP, etc.

There are two sides to this. One is that it's difficult to right now to
tell when your performance is being whacked because autovacuum is doing
something. The original version of the checkpoint + other slowdowns
logging patch I submitted included a line of extra detail there so I could
watch the entry/exit to every autovaccum section if the log level was
cranked up to DEBUG2, motivated by the same reasons Greg Stark mentioned.

When you get into a situation where things are inexplicably slow, it's
nice to do a tail on the logs and be able to figure out "oh, it's busy
vacuuming X right now, no wonder". Just logging when you're going through
that is good enough to let you line up the timestamps with other system
activity measurements, and while difficult you can tune based just on
that. And having that information easily available in the logs lets
almost any level of user watch what's going on.

Actually tuning that behavior out of the system altogether is easier done
by collecting statistics, in a similar fashion to how pg_stat_bgwriter
lets you track checkpoints now, but that being helpful presumes a level of
monitoring sophistication that typical new installations (who need this
kind of help the most) don't have. Also, given the way that the stats are
often updated after the work was done, that implementation may not solve
the "why am I slow right now?" side of the problem effectively.

I think both types of instrumentation are ultimately needed, but if you
were only picking one I think the log entries would be the better one to
start with.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD