Re: Inserting 8MB bytea: just 25% of disk perf used?

Scott Carey:

> > (2) The tests:
> >
> > Note: The standard speed was about 800MB/40s, so 20MB/s.
> >
> >
> > a)
> > What I changed: fsync=off
> > Result: 35s, so 5s faster.
> >
> >
> > b) like a) but:
> > checkpoint_segments=128 (was 3)
> > autovacuum=off
> >
> > Result: 35s (no change...?!)
> >
>
> yes, more checkpoint_segments will help if your
> shared_buffers is larger, it won't do a whole lot
> otherwise. Generally, I like to keep these roughly equal
> sized as a starting point for any small to medium sized
> configuration. So if shared_buffers is 1GB, that takes 64
> checkpoint segments to hold for heavy write scenarios.

(1)

Ok, that's what I tested: 1024 MB shared_buffers, 64
checkpoint segments.

Unfortunatelly I could not run it on the same hardware
anymore: The data is written to a single disk now, not raid
anymore. So with the default shared_buffers of 8 MB (?) we
should expect 45s for writing the 800 MB. With the large
shared_buffers and checkpoints (mentioned above) I got this:

1. run (right after postgres server (re-)start): 28s (!)
2. run: 44s
3. run: 42s

So, roughly the same as with small buffers.

(2)
Then I switched again from 8.2.4 to 8.4.2:

1. run (after server start): 25s.
2. run: 38s
3. run: 38s

So, 8.4 helped a bit over 8.2.

(3) All in all

By (1) + (2) the performance bottleneck has, however,
changed a lot (as shown here by the performance monitor):

Now, the test system is definitly disk bound. Roughly
speaking, at the middle of the whole test, for about 40-50%
of the time, the 'data' disk was at 100% (and the 'WAL' at
20%), while before and after that the 'WAL' disk had a lot
of peaks at 100% (and 'data' disk at 30%).

The average MB/s of the 'data' disk was 40 MB/s (WAL:
20MB/s) -- while the raw performance is 800MB/40s = 20MB/s,
so still *half* what the disk does.

So, this remains as the last open question to me: It seems
the data is doubly written to the 'data' disk, although WAL
is written to the separate 'WAL' disk.

> > Ok, I've managed to use 8.4 here. Unfortunatelly: There was
> > nearly no improvement in speed. For example test 2d)
> > performed in 35s.
> >
>
> With a very small shared_buffers the improvements to
> Postgres' shared_buffer / checkpoint interaction can not
> be utilized.

See above.

Thank You
Felix