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: 4EC36847.208@2ndQuadrant.com (view raw or flat)
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  www.2ndQuadrant.us


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

In response to

Responses

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-2014 The PostgreSQL Global Development Group