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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
