Thread: Foreground vacuum and buffer access strategy

Foreground vacuum and buffer access strategy

From
Jeff Janes
Date:
If I invoke vacuum manually and do so with VacuumCostDelay == 0, I
have basically declared my intentions to get this pain over with as
fast as possible even if it might interfere with other processes.

Under that condition, shouldn't it use BAS_BULKWRITE rather than
BAS_VACUUM?  The smaller ring size leads to a lot of synchronous WAL
flushes which I think can slow the vacuum down a lot.

Cheers,

Jeff


Re: Foreground vacuum and buffer access strategy

From
Robert Haas
Date:
On Fri, May 25, 2012 at 4:06 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> If I invoke vacuum manually and do so with VacuumCostDelay == 0, I
> have basically declared my intentions to get this pain over with as
> fast as possible even if it might interfere with other processes.
>
> Under that condition, shouldn't it use BAS_BULKWRITE rather than
> BAS_VACUUM?  The smaller ring size leads to a lot of synchronous WAL
> flushes which I think can slow the vacuum down a lot.

Of course, an autovacuum of a really big table could run too slowly,
too, even though it's not a foreground task.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Foreground vacuum and buffer access strategy

From
Jeff Janes
Date:
Reviving a very old thread, because I've run into the issue again.

On Tue, May 29, 2012 at 11:58 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, May 25, 2012 at 4:06 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> If I invoke vacuum manually and do so with VacuumCostDelay == 0, I
>> have basically declared my intentions to get this pain over with as
>> fast as possible even if it might interfere with other processes.
>>
>> Under that condition, shouldn't it use BAS_BULKWRITE rather than
>> BAS_VACUUM?  The smaller ring size leads to a lot of synchronous WAL
>> flushes which I think can slow the vacuum down a lot.
>
> Of course, an autovacuum of a really big table could run too slowly,
> too, even though it's not a foreground task.

True.  But almost by definition, an autovacuum is not trying to run
inside a maintenance window.

Would it be reasonable to upgrade the ring buffer size whenever
VacuumCostDelay is zero, regardless of whether it is a manual or an
auto vac?  One thing I worry about is that many people may have
changed autovacuum_vacuum_cost_delay from 20 directly to 0 or -1, and
the accidental throttling on WAL syncs might be the only thing
preventing their system from falling over each time autovac of a large
table kicks in.

Cheers,

Jeff



Re: Foreground vacuum and buffer access strategy

From
Robert Haas
Date:
On Mon, Aug 12, 2013 at 11:47 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> Reviving a very old thread, because I've run into the issue again.
> On Tue, May 29, 2012 at 11:58 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Fri, May 25, 2012 at 4:06 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>> If I invoke vacuum manually and do so with VacuumCostDelay == 0, I
>>> have basically declared my intentions to get this pain over with as
>>> fast as possible even if it might interfere with other processes.
>>>
>>> Under that condition, shouldn't it use BAS_BULKWRITE rather than
>>> BAS_VACUUM?  The smaller ring size leads to a lot of synchronous WAL
>>> flushes which I think can slow the vacuum down a lot.
>>
>> Of course, an autovacuum of a really big table could run too slowly,
>> too, even though it's not a foreground task.
>
> True.  But almost by definition, an autovacuum is not trying to run
> inside a maintenance window.
>
> Would it be reasonable to upgrade the ring buffer size whenever
> VacuumCostDelay is zero, regardless of whether it is a manual or an
> auto vac?  One thing I worry about is that many people may have
> changed autovacuum_vacuum_cost_delay from 20 directly to 0 or -1, and
> the accidental throttling on WAL syncs might be the only thing
> preventing their system from falling over each time autovac of a large
> table kicks in.

I'm not sure what the right thing to do here is, but I definitely
agree there's a problem.  There are definitely cases where people want
or indeed need to vacuum as fast as possible, and using a small ring
buffer is not the way to do that.  Now, tying that to VacuumCostDelay
doesn't seem right, because setting that to 0 shouldn't suddenly
change the behavior in other ways, as well.

In general, the approach we've taken so far has been to try to hide
the ring-buffer behavior from users and not make it tunable, but I'm
not sure we can really get away with that in this case.  Increasing
the ring-buffer size has system-wide performance implications which
could be very good (less bloat) or very bad (I/O starvation of
concurrent activity).  I don't think the system knows enough to guess
which one will be better in any particular case.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Foreground vacuum and buffer access strategy

From
Greg Stark
Date:
On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> I'm not sure what the right thing to do here is, but I definitely
> agree there's a problem.  There are definitely cases where people want
> or indeed need to vacuum as fast as possible, and using a small ring
> buffer is not the way to do that.

I'm not convinced using a ring buffer is necessarily that bad even if
you want to vacuum as fast as possible. The reason we use a small ring
buffer is to avoid poisoning the entire cache with vacuum pages, not
to throttle the speed of vacuum by introducing synchronous wal
flushes.

I think we should increase the size of the ring buffer if we hit a
synchronous wal buffer flush and there is less than some amount of wal
pending. That amount is the relevant thing people might want to limit
to avoid slowing down other transaction commits. The walwriter might
even provide a relevant knob already for how much wal should be the
maximum pending.



-- 
greg



Re: Foreground vacuum and buffer access strategy

From
Amit Kapila
Date:
On Wed, Aug 14, 2013 at 1:41 AM, Greg Stark <stark@mit.edu> wrote:
> On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>
>> I'm not sure what the right thing to do here is, but I definitely
>> agree there's a problem.  There are definitely cases where people want
>> or indeed need to vacuum as fast as possible, and using a small ring
>> buffer is not the way to do that.
>
> I'm not convinced using a ring buffer is necessarily that bad even if
> you want to vacuum as fast as possible. The reason we use a small ring
> buffer is to avoid poisoning the entire cache with vacuum pages, not
> to throttle the speed of vacuum by introducing synchronous wal
> flushes.
>
> I think we should increase the size of the ring buffer if we hit a
> synchronous wal buffer flush and there is less than some amount of wal
> pending.  It will be better if the decision to increase ring buffer also
consider other activity, otherwise  it can lead to more I/O due to buffer replacements by backend.  I am not sure
currentlythere is any way to check that, but if we
 
maintain buffers on free list, then  it can be used to check the current activity (if there are enough
buffers on free list, then ring size can be increased as it  is an indication that the system is relatively less
busy).

> That amount is the relevant thing people might want to limit
> to avoid slowing down other transaction commits. The walwriter might
> even provide a relevant knob already for how much wal should be the
> maximum pending.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com



Re: Foreground vacuum and buffer access strategy

From
Robert Haas
Date:
On Tue, Aug 13, 2013 at 4:11 PM, Greg Stark <stark@mit.edu> wrote:
> I'm not convinced using a ring buffer is necessarily that bad even if
> you want to vacuum as fast as possible. The reason we use a small ring
> buffer is to avoid poisoning the entire cache with vacuum pages, not
> to throttle the speed of vacuum by introducing synchronous wal
> flushes.

Right, but the DBA, being God, is entitled to override that.  A
regular user should not be able to change system policy here, but if a
superuser wants to do it, who are we to say no?

> I think we should increase the size of the ring buffer if we hit a
> synchronous wal buffer flush and there is less than some amount of wal
> pending. That amount is the relevant thing people might want to limit
> to avoid slowing down other transaction commits. The walwriter might
> even provide a relevant knob already for how much wal should be the
> maximum pending.

I doubt that would work out; the amount of WAL pending is going to
change extremely rapidly.  You can't increase the size of the ring
buffer for a vacuum that might run for another 24 hours on the basis
of an instantaneous measurement whose value might be completely
different a few milliseconds later or earlier.

Also, if there is a lot of WAL pending, then the system is likely I/O
saturated, which might be exactly the wrong time to allow more cache
poisoning.

Auto-tuning is nice when we can do it, but you can't
auto-guess-what-the-human-wants.

...Robert