On Wed, May 08, 2019 at 10:08:03AM -0400, Robert Haas wrote:
>On Tue, May 7, 2019 at 4:16 PM Andres Freund <andres@anarazel.de> wrote:
>> Just to attach some numbers for this. On my laptop, with a pretty fast
>> disk (as in ~550MB/s read + write, limited by SATA, not the disk), I get
>> these results.
>>
>> [ results showing ring buffers massively hurting performance ]
>
>Links to some previous discussions:
>
>http://postgr.es/m/8737e9bddb82501da1134f021bf4929a@postgrespro.ru
>http://postgr.es/m/CAMkU=1yV=Zq8sHviv5Nwajv5woWOvZb7bx45rgDvtxs4P6W1Pw@mail.gmail.com
>
>> We probably can't remove the ringbuffer concept from these places, but I
>> think we should allow users to disable them. Forcing bulk-loads, vacuum,
>> analytics queries to go to the OS/disk, just because of a heuristic that
>> can't be disabled, yielding massive slowdowns, really sucks.
>
>The discussions to which I linked above seem to suggest that one of
>the big issues is that the ring buffer must be large enough that WAL
>flush for a buffer can complete before we go all the way around the
>ring and get back to the same buffer. It doesn't seem unlikely that
>the size necessary for that to be true has changed over the years, or
>even that it's different on different hardware. When I did some
>benchmarking in this area many years ago, I found that there as you
>increase the ring buffer size, performance improves for a while and
>then more or less levels off at a certain point. And at that point
>performance is not much worse than it would be with no ring buffer,
>but you maintain some protection against cache-trashing. Your
>scenario assumes that the system has no concurrent activity which will
>suffer as a result of blowing out the cache, but in general that's
>probably not true.
>
>It seems to me that it might be time to bite the bullet and add GUCs
>for the ring buffer sizes. Then, we could make the default sizes big
>enough that on normal-ish hardware the performance penalty is not too
>severe (like, it's measured as a percentage rather than a multiple),
>and we could make a 0 value disable the ring buffer altogether.
>
IMO adding such GUC would be useful for testing, which is something we
should probably do anyway, and then based on the results we could either
keep the GUC, modify the default somehow, or do nothing.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services