On 2014-10-10 12:28:13 +0530, Amit Kapila wrote:
> On Fri, Oct 10, 2014 at 1:08 AM, Andres Freund <andres@2ndquadrant.com>
> wrote:
> > On 2014-10-09 16:01:55 +0200, Andres Freund wrote:
> > >
> > > I don't think OLTP really is the best test case for this. Especially not
> > > pgbench with relatilvely small rows *and* a uniform distribution of
> > > access.
> > >
> > > Try parallel COPY TO. Batch write loads is where I've seen this hurt
> > > badly.
> >
> >
> > just by switching shared_buffers from 1 to 8GB. I haven't tried, but I
> > hope that with an approach like your's this might become better.
> >
> > psql -f /tmp/prepare.sql
> > pgbench -P5 -n -f /tmp/copy.sql -c 8 -j 8 -T 100
>
> Thanks for providing the scripts. You haven't specified how much data
> is present in 'large' file used in tests.
I don't think it matters much. It should be small enough that you get a
couple TPS over all backends.
> I have tried with different set of
> rows, but I could not see the dip that is present in your data when you
> increased shared buffers from 1GB to 8GB, also I don't see any difference
> with patch.
Interesting. I wonder whether that's because the concurrency wasn't high
enough for that machine to show the problem. I ran the test on my
workstation which has 8 actual cores.
> BTW, why do you think that for such worklaods this patch can
> be helpful, according to my understanding it can be helpful mainly for
> read mostly workloads when all the data doesn't fit in shared buffers.
The performance dip comes from all the backends performing the clock
sweep. As the access is pretty uniform all the buffers start with some
usage count (IIRC 3 in this example. Much worse if 5). Due to the
uniform usagecount the clock sweep frequently has to go several times
through *all* the buffers. That leads to quite horrible performance in
some cases.
I had hoped that bgreclaimer can make that workload les horrible by
funneling most of the accesses through the freelist.
Greetings,
Andres Freund
-- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services