On 2016-06-03 15:17:06 -0400, Robert Haas wrote:
> On Fri, Jun 3, 2016 at 2:20 PM, Andres Freund <andres@anarazel.de> wrote:
> >> I've always heard that guideline as "roughly 1/4, but not more than
> >> about 8GB" - and the number of people with more than 32GB of RAM is
> >> going to just keep going up.
> >
> > I think that upper limit is wrong. But even disregarding that:
>
> Many people think the upper limit should be even lower, based on good,
> practical experience. Like I've seen plenty of people recommend
> 2-2.5GB.
Which largely imo is because of the writeback issue. And the locking
around buffer replacement, if you're doing it highly concurrently (which
is now mostly solved).
> > To hit the issue in that case you have to access more data than
> > shared_buffers (8GB), and very frequently re-dirty already dirtied
> > data. So you're basically (on a very rough approximation) going to have
> > to write more than 8GB within 30s (256MB/s). Unless your hardware can
> > handle that many mostly random writes, you are likely to hit the worst
> > case behaviour of pending writeback piling up and stalls.
>
> I'm not entire sure that this is true, because my experience is that
> the background writing behavior under Linux is not very aggressive. I
> agree you need a working set >8GB, but I think if you have that you
> might not actually need to write data this quickly, because if Linux
> decides to only do background writing (as opposed to blocking
> processes) it may not actually keep up.
But that's *bad*. Then a checkpoint comes around and latency and
throughput is shot to hell while the writeback from the fsyncs is
preventing any concurrent write activity. And if it's not keeping up
before, it's now really bad.
> And in fact I
> think what the testing shows so far is that when they can't achieve
> locality, backend flush control sucks.
FWIW, I don't think that's generally enough true. For pgbench
bigger-than-20%-of-avail-memory there's pretty much no locality, and
backend flushing helps considerably,
Andres
