Skip site navigation (1) Skip section navigation (2)

Re: Displaying accumulated autovacuum cost

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Displaying accumulated autovacuum cost
Date: 2011-11-16 07:37:43
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
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

Attachment: vacuum_stats_v5.patch
Description: text/x-patch (6.9 KB)

In response to


pgsql-hackers by date

Next:From: Pavel GolubDate: 2011-11-16 07:58:05
Subject: Client library cross-compiling: Win32, Win64, MacOSX. Possible?
Previous:From: Jaime CasanovaDate: 2011-11-16 05:49:49
Subject: Re: patch : Allow toast tables to be moved to a different tablespace

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group