Claudio Freire <klaussfre...@gmail.com> writes: > On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas <robertmh...@gmail.com> wrote: >> 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. At the time we set the ring buffer size to 256K, the maximum shared_buffers that initdb would configure was 32MB; and you often didn't get that much due to SHMMAX. Now of course it's 128MB, and you'll pretty much always get that. So there's certainly room to argue that it's time to increase vacuum's ring buffer size, but that line of argument doesn't justify more than ~10X increase at most. Like Robert, I'm afraid of changing this number in a vacuum (ahem). If you've got the default number of autovacuum workers going (3), you'd have them thrashing a total of 3/8ths of shared memory by default, which seems like a lot. We do need to look at the impact on foreground processing, and not just at the speed of vacuum itself. One idea for addressing this would be to raise the max values in the switch, but tighten the fraction-of-shared-buffers limit just below. I wouldn't have any objection to a 16MB ring buffer for vacuum when it is coming out of a 1GB arena ... it just seems like a rather large fraction of 128MB to give to a background process, especially to each of several background processes. Maybe the fraction-of-shared-buffers shouldn't be one size fits all, but a different limit for each case? regards, tom lane -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers