> > If you use linux, try the following settings:
> > 1. Decrease /proc/sys/vm/dirty_ratio and dirty_background_ratio.
You will need to pair this with bgwriter_* settings, else too few
pages are written to the os inbetween checkpoints.
> > 2. Increase wal_buffers to redule WAL flushing.
You will want the possibility of single group writes to be able to reach
256kb. The default is thus not enough when you have enough RAM.
You also want enough, so that new txns don't need to wait for an empty
buffer (that is only freed by a write).
> > 3. Set wal_sync_method to open_sync; O_SYNC is faster then fsync().
O_SYNC's only advantage over fdatasync is that it saves a system call,
since it still passes through OS cache, but the disadvantage is that it
does not let the OS group writes. Thus it is more susceptible to too
few WAL_buffers. What you want is O_DIRECT + enough wal_buffers to allow
256k writes.
> > 4. Separate data and WAL files into different partitions or disks.
While this is generally suggested, I somehow doubt the validity when you
only have few disk spindles. If e.g. you only have 2-3 (mirrored) disks
I
wouldn't do it (at least on the average 70/30 read write systems).
Andreas