Increase Vacuum ring buffer.

From: Sokolov Yura <funny(dot)falcon(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Increase Vacuum ring buffer.
Date: 2017-07-18 10:09:41
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Good day, every one.

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.

With regards,
Sokolov Yura aka funny_falcon
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
0001-Set-vacuum-ring-buffer-16MB.patch text/x-diff 1.0 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2017-07-18 10:10:59 Re: Proposal for CSN based snapshots
Previous Message Kyotaro HORIGUCHI 2017-07-18 09:12:13 Re: PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING