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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Jan 22, 2010, at 12:42 PM, fkater@xxxxxxxxxxxxxx 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.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux