Re: Detrimental performance impact of ringbuffers on performance

On Thu, Apr 14, 2016 at 10:22 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>
> On Tue, Apr 12, 2016 at 11:38 AM, Andres Freund <andres(at)anarazel(dot)de>
wrote:
> >> And, on the other hand, if we don't do something like that, it will be
> >> quite an exceptional case to find anything on the free list. Doing it
> >> just to speed up developer benchmarking runs seems like the wrong
> >> idea.
> >
> > I don't think it's just developer benchmarks. I've seen a number of
> > customer systems where significant portions of shared buffers were
> > unused due to this.
> >
> > Unless you have an OLTP system, you can right now easily end up in a
> > situation where, after a restart, you'll never fill shared_buffers.
> > Just because sequential scans for OLAP and COPY use ringbuffers. It sure
> > isn't perfect to address the problem while there's free space in s_b,
> > but it sure is better than to just continue to have significant portions
> > of s_b unused.
>
> I agree that the ringbuffer heuristics are rather unhelpful in many
> real-world scenarios. This is definitely a real problem that we should
> try to solve soon.
>
> An adaptive strategy based on actual cache pressure in the recent past
> would be better. Maybe that would be as simple as not using a
> ringbuffer based on simply not having used up all of shared_buffers
> yet. That might not be good enough, but it would probably still be
> better than what we have.
>

I think that such a strategy could be helpful in certain cases, but not
sure every time using it can be beneficial. There could be cases where we
extend ring buffers to use unused buffers in shared buffer pool for bulk
processing workloads and immediately after that there is a demand for
buffers from other statements. Not sure, but I think an idea of different
kind of buffer pools can be helpful for some such cases. Different kind
of buffer pools could be ring buffers, extended ring buffers (relations
associated with such buffer pools can bypass ring buffers and use unused
shared buffers), retain or keep buffers (relations that are frequently
accessed can be associated with this kind of buffer pool where buffers can
stay for longer time) and a default buffer pool (all relations by default
will be associated with default buffer pool where the behaviour will be
same as current).

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com