On Wed, May 08, 2019 at 10:08:03AM -0400, Robert Haas wrote:
On Tue, May 7, 2019 at 4:16 PM Andres Freund <and...@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/8737e9bddb82501da1134f021bf49...@postgrespro.ru
http://postgr.es/m/CAMkU=1yV=zq8shviv5nwajv5wowovzb7bx45rgdvtxs4p6w...@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
