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 |
Message-ID: | 8737e9bddb82501da1134f021bf4929a@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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: https://postgrespro.ru
The Russian Postgres Company
Attachment | Content-Type | Size |
---|---|---|
0001-Set-vacuum-ring-buffer-16MB.patch | text/x-diff | 1.0 KB |
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 |