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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers