Re: Displaying accumulated autovacuum cost

On 08/22/2011 05:54 PM, Jim Nasby wrote:
> I know folks have talked about progress, but I haven't seen anything
> specific... could you add info about what table/index vacuum is
> working on, and how far along it is? I realize that's not very close
> to an actual % completion, but it's far better than what we have right
> now.

Due to complaints about the mechanism the first version used to inform
the user of the progress, I'm yanking that from the next patch
altogether. The goal for now is to get a good report into the logs, and
then maybe that gets extended later with a progress report. (All of the
proposed alternate mechanisms are way more complicated than anything I
have time to do right now)

> FWIW, the number I end up caring about isn't so much write traffic as read. Thanks to a good amount of battery-backed write cache (and possibly some iSCSI misconfiguration), our writes are generally much cheaper than our reads.
>

VACUUM can't really know its true read rate from what's inside the
database. I can add a summary of the accumulated read amounts into the
logs, in more useful figures than what is provided so far, which is
better than nothing. But those will be kind of deceptive, which is one
reason I wasn't so focused on them yet. If the relation is largely in
the OS cache, but not the PostgreSQL one, the summary can show a read
rate even when that isn't actually doing any reads at all. That was
exactly the case in the sample data I posted. VACUUM thought it was
reading anywhere from 2.5 to 6MB/s. But at the OS level, it was
actually reading zero bytes, since the whole thing was in cache already.

What you actually want is a count of the accumulated read counters at
the OS level. I've recently figured out how to track those, too, but
that code is something that lives outside the database. If this is
something useful to you, I think you're about to sign up to be my next
beta tester for that program.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us