Re: Detrimental performance impact of ringbuffers on performance

On Wed, Apr 13, 2016 at 12:08 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> On 2016-04-12 14:29:10 -0400, Robert Haas wrote:
> > On Wed, Apr 6, 2016 at 6:57 AM, Andres Freund <andres(at)anarazel(dot)de>
wrote:
> > > While benchmarking on hydra
> > > (c.f.
http://archives.postgresql.org/message-id/20160406104352.5bn3ehkcsceja65c%40alap3.anarazel.de
),
> > > which has quite slow IO, I was once more annoyed by how incredibly
long
> > > the vacuum at the the end of a pgbench -i takes.
> > >
> > > The issue is that, even for an entirely shared_buffers resident scale,
> > > essentially no data is cached in shared buffers. The COPY to load data
> > > uses a 16MB ringbuffer. Then vacuum uses a 256KB ringbuffer. Which
means
> > > that copy immediately writes and evicts all data. Then vacuum reads &
> > > writes the data in small chunks; again evicting nearly all buffers.
Then
> > > the creation of the ringbuffer has to read that data *again*.
> > >
> > > That's fairly idiotic.
> > >
> > > While it's not easy to fix this in the general case, we introduced
those
> > > ringbuffers for a reason after all, I think we at least should add a
> > > special case for loads where shared_buffers isn't fully used yet. Why
> > > not skip using buffers from the ringbuffer if there's buffers on the
> > > freelist? If we add buffers gathered from there to the ringlist, we
> > > should have few cases that regress.
> >
> > That does not seem like a good idea from here. One of the ideas I
> > still want to explore at some point is having a background process
> > identify the buffers that are just about to be evicted and stick them
> > on the freelist so that the backends don't have to run the clock sweep
> > themselves on a potentially huge number of buffers, at perhaps
> > substantial CPU cost. Amit's last attempt at this didn't really pan
> > out, but I'm not convinced that the approach is without merit.
>

Yeah and IIRC, I observed that there was lot of contention in dynahash
table (when data doesn't fit in shared buffers) due to which the
improvement hasn't shown measurable gain in terms of TPS. As now in 9.6,
we have reduced the contention (spinlocks) in dynahash tables, it might be
interesting to run the tests again.

> FWIW, I've posted an implementation of this in the checkpoint flushing
> thread; I saw quite substantial gains with it. It was just entirely
> unrealistic to push that into 9.6.
>

Sounds good. I remember last time you mentioned that such an idea could
benefit bulk load case when data doesn't fit in shared buffers, is it the
same case where you saw benefit or other cases like read-only and
read-write tests as well.

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