Re: 8.4 open item: copy performance regression?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Huh, that's bizarre. I can see that increasing shared_buffers
> should make no difference in this test case (we're not using them
> all anyway). But why should increasing wal_buffers make it slower?
> I forget the walwriter's control algorithm at the moment ... maybe
> it works harder when wal buffers are full?

I created a postgresql.conf file with the options from the default
file, and then tried that by itself again, and with each of three
other options:

<none>
0m24.540s
0m24.630s
0m23.778s

checkpoint_segments = 100
0m30.251s
0m29.474s
0m26.604s

wal_buffers = 16MB
0m24.487s
0m23.939s
0m23.557s

shared_buffers = 256MB
0m25.885s
0m25.654s
0m24.025s

So the big hit seems to come from boosting checkpoint_segments,
although boosting shared_buffers seems to cause a slight slowdown.
Boosting wal_buffers seemed to help a little. Both of these last two,
though, are within the noise, so low confidence on those without a lot
more tests.

The checkpoint_segments seems dramatic enough to be real. I wonder if
the test is short enough that it never got around to re-using any of
them, so it was doing extra writes for the initial creation during the
test?

-Kevin