On Fri, Apr 29, 2016 at 7:08 AM, Bruce Momjian <br...@momjian.us> wrote: > On Wed, Apr 6, 2016 at 12:57:16PM +0200, Andres Freund wrote: >> While benchmarking on hydra >> (c.f. >> http://archives.postgresql.org/message-id/20160406104352.5bn3ehkcsceja65c%40alap3.anarazel.de), >> which has quite slow IO, I was once more annoyed by how incredibly long >> the vacuum at the the end of a pgbench -i takes. >> >> The issue is that, even for an entirely shared_buffers resident scale, >> essentially no data is cached in shared buffers. The COPY to load data >> uses a 16MB ringbuffer. Then vacuum uses a 256KB ringbuffer. Which means >> that copy immediately writes and evicts all data. Then vacuum reads & >> writes the data in small chunks; again evicting nearly all buffers. Then >> the creation of the ringbuffer has to read that data *again*. >> >> That's fairly idiotic. >> >> While it's not easy to fix this in the general case, we introduced those >> ringbuffers for a reason after all, I think we at least should add a >> special case for loads where shared_buffers isn't fully used yet. Why >> not skip using buffers from the ringbuffer if there's buffers on the >> freelist? If we add buffers gathered from there to the ringlist, we >> should have few cases that regress. >> >> Additionally, maybe we ought to increase the ringbuffer sizes again one >> of these days? 256kb for VACUUM is pretty damn low. > > Is this a TODO?
I think we are in agreement that some changes may be needed, but I don't think we necessarily know what the changes are. So you could say something like "improve VACUUM ring buffer logic", for example, but I think something specific like "increase size of the VACUUM ring buffer" will just encourage someone to do it as a beginner project, which it really isn't. Maybe others disagree, but I don't think this is a slam-dunk where we can just change the behavior in 10 minutes and expect to have winners but no losers. -- 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