On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Jul 18, 2017 at 6:09 AM, Sokolov Yura > <funny.fal...@postgrespro.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 <t...@sss.pgh.pa.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.
IIRC, originally, the default shared_buffers settings was tiny. -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers