Re: Increase Vacuum ring buffer.

On Tue, Jul 18, 2017 at 6:09 AM, Sokolov Yura
<funny(dot)falcon(at)postgrespro(dot)ru> wrote:
> I investigated autovacuum performance, and found that it suffers a lot
> from small ring buffer. It suffers in a same way bulk writer suffered
> before Tom Lane's commit 6382448cf96:
>
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 2009-06-23 00:04:28
>> For bulk write operations (eg COPY IN), use a ring buffer of 16MB
>> instead of the 256KB limit originally enforced by a patch committed
>> 2008-11-06. Per recent test results, the smaller size resulted in an
>> undesirable decrease in bulk data loading speed, due to COPY
>> processing frequently getting blocked for WAL flushing. This area
>> might need more tweaking later, but this setting seems to be good
>> enough for 8.4.
>
> It is especially noticable when database doesn't fit in shared_buffers
> but fit into OS file cache, and data is intensively updated (ie OLTP
> load). In this scenario autovacuum with current 256kB (32 pages) ring
> buffer lasts 3-10 times longer than with increased to 16MB ring buffer.
>
> I've tested with synthetic load with 256MB or 1GB shared buffers and
> 2-6GB (with indices) tables, with different load factor and with/without
> secondary indices on updated columns. Table were randomly updated with
> hot and non-hot updates. Times before/after buffer increase (depending
> on load) were 7500sec/1200sec, 75000sec/11500sec. So benefit is
> consistently reproducible.
>
> I didn't tested cases when database doesn't fit OS file cache. Probably
> in this case benefit will be smaller cause more time will be spent in
> disk read.
> I didn't test intensively OLAP load. I've seen once that increased
> buffer slows a bit scanning almost immutable huge table, perhaps cause
> of decreased CPU cache locality. But given such scan is already fast,
> and autovacuum of "almost immutable table" runs rarely, I don't think
> it is very important.
>
> Initially I wanted to make BAS_BULKWRITE and BAS_VACUUM ring sizes
> configurable, but after testing I don't see much gain from increasing
> ring buffer above 16MB. So I propose just 1 line change.

I think the question for this patch is "so, why didn't we do it this
way originally?".

It's no secret that making the ring buffer larger will improve
performance -- in fact, not having a ring buffer at all would improve
performance even more. But it would also increase the likelihood that
the background work of vacuum would impact the performance of
foreground operations, which is already a pretty serious problem that
we probably don't want to make worse. I'm not certain what the right
decision is here, but I think that a careful analysis of possible
downsides is needed.

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