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
