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

On Jan 22, 2010, at 12:42 PM, fkater(at)googlemail(dot)com wrote:
>
> 'Writing twice': That is the most interesting point I
> believe. Why is the data disk doing 40 MB/s *not* including
> WAL, however, having 20 MB/s write thoughput in fact. Seems
> like: 20 MB for data, 20 MB for X, 20 MB for WAL.
>

There are a few things that can do this for non-TOAST stuff. The other comment that TOAST writes all zeros first might be related too.

> Although that questions is still unanswered: I verified
> again that I am disk bound by temporarily replacing the
> raid-0 with slower solution: a singly attached sata disk
> of the same type: This *did* slow down the test a lot
> (approx. 20%). So, yes, I am disk bound but, again, why
> that much...
>

Sometimes disk bound (as the graphs show). I suspect that if you artificially slow your CPU down (maybe force it into power saving mode with a utility) it will also be slower. The I/O seems to be the most significant part though.

>
> (1) First, the most important 8.2.4 defaults (for you to
> overlook):
>
> #shared_buffers=32MB

Try 200MB for the above
> #temp_buffers=8MB

You tried making this larger, which helped some.

> #bgwriter_delay=200ms
> #bgwriter_lru_percent=1.0
> #bgwriter_lru_maxpages=5
> #bgwriter_all_percent=0.333
> #bgwriter_all_maxpages=5
> #checkpoint_segments=3
> #checkpoint_timeout=5min
> #checkpoint_warning=30s

Check out this for info on these parameters
http://wiki.postgresql.org/wiki/User:Gsmith (Is there a better link Greg?)

> #fsync=on
Changing this probably helps the OS spend less time flushing to disk.

>
> (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.

>
> c) like b) but:
> temp_buffers=200MB (was 8)
> wal_sync_method=open_datasync (was fsync)
> wal_buffers=1024kB (was 64)
>
> Result:
> The best ever, it took just 29s, so 800MB/29s = 27.5MB/s.
> However, having autovacuum=off probably means that deleted
> rows will occupy disk space? And I also fear that
> checkpoint_segments=128 mean that at some point in the
> future there will be a huge delay then (?).

I am curious which of the two helped most. I don't think temp_buffers should do anything (it is for temp tables afaik).

> d) also like b) but:
> temp_buffers=1000MB
> wal_buffers=4096kB
> checkpoint_segments=3
> autovacuum=on
>
> Result: Again slower 36s
>

Try changing shared_buffers. This is where uncommitted data needs to avoid overflowing before a commit. If this was non-TOAST data, i would suspect this is the cause of any double-writing. But I don't know enough about TOAST to know if the same things happen here.

> 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.