Re: Displaying accumulated autovacuum cost

On 10/05/2011 03:02 AM, Greg Smith wrote:
> Presumably you meant to ask if this makes sense to show when cost
> accounting isn't enabled, because the code doesn't do that right now.
> No cost accounting, no buffer usage/write rate data as this was
> submitted.

This is done in the attached update. I just made the page accounting
happen all the time, regardless of whether the costs were being
accumulated. Added a read rate too, which is how fast reads happened
from the OS cache to shared_buffers. Simple test case generates a 600MB
pgbench_accounts database and wipes out enough to take a while to clean
up; it needs log_autovacuum_min_duration = 0 and then:

$ createdb pgbench
$ pgbench -i -s 10 pgbench
$ psql -d pgbench -c "delete from pgbench_accounts where aid<200000"

LOG: automatic vacuum of table "pgbench.public.pgbench_accounts": index
scans: 1
pages: 0 removed, 16394 remain
tuples: 199999 removed, 640011 remain
buffer usage: 13742 hits, 2708 misses, 1058 dirtied
avg read rate: 3.067 MiB/s, avg write rate: 1.198 MiB/s
system usage: CPU 0.05s/0.61u sec elapsed 6.89 sec

> Now that you mention it, people who do a manual, full-speed VACUUM
> would certainly appreciate some feedback on the rate it ran at.

This is more of a pain because this whole code path is only active when
IsAutoVacuumWorkerProcess. I have some larger refactoring in mind to
perhaps make that more feasible. I didn't want to hold this update
aiming at the more valuable autovac case for that though, can always
layer it on later.

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