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

Re: Experimental patch for inter-page delay in VACUUM

From: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
To: Ang Chin Han <angch(at)bytecraft(dot)com(dot)my>
Cc: Christopher Browne <cbbrowne(at)acm(dot)org>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Experimental patch for inter-page delay in VACUUM
Date: 2003-11-04 16:30:40
Message-ID: 3FA7D430.201@zeut.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Ang Chin Han wrote:

> Christopher Browne wrote:
>
>> Centuries ago, Nostradamus foresaw when "Stephen" 
>> <jleelim(at)xxxxxxx(dot)com> would write:
>>
>>> As it turns out. With vacuum_page_delay = 0, VACUUM took 1m20s (80s)
>>> to complete, with vacuum_page_delay = 1 and vacuum_page_delay = 10,
>>> both VACUUMs completed in 18m3s (1080 sec). A factor of 13 times! 
>>> This is for a single 350 MB table.
>>
>> While it is unfortunate that the minimum quanta seems to commonly be
>> 10ms, it doesn't strike me as an enormous difficulty from a practical
>> perspective.
>
> If we can't lower the minimum quanta, we could always vacuum 2 pages 
> before sleeping 10ms, effectively sleeping 5ms.

Right, I think this is what Jan has done already.

> What would be interesting would be pg_autovacuum changing these values 
> per table, depending on current I/O load.
>
> Hmmm. Looks like there's a lot of interesting things pg_autovacuum can 
> do:
> 1. When on low I/O load, running multiple vacuums on different, 
> smaller tables on full speed, careful to note that these vacuums will 
> increase the I/O load as well.
> 2. When on high I/O load, vacuum big, busy tables slowly.

I'm not sure how practacle any of this is.  How will pg_autovacuum 
surmise the current I/O load of the system?  Keeping in mind that 
postgres is not the only cause of I/O.  Also, the optimum delay for a 
long running vacuum might change dramatically while it's running.  If 
there is a way to judge the current I/O load, it might be better for 
vacuum to auto-tune itself while it's running, perhaps based on some 
hints given to it by pg_autovacuum or manually by a user.   For example, 
a delay hint of 0 should always be zero no matter what.  A delay hint of 
1 will scale up slower than a delay hint of 2 which would scale up 
slower than 5 etc.... 

Of course this is all wild conjecture if there isn't an easy way to 
surmise the system I/O load.  Thoughts?




In response to

pgsql-hackers by date

Next:From: Gaetano MendolaDate: 2003-11-04 16:35:42
Subject: Re: 7.4RC1 tag'd, branched and bundled ...
Previous:From: Jan WieckDate: 2003-11-04 16:28:10
Subject: Re: Experimental patch for inter-page delay in VACUUM

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