Re: Optimizing Postgresql server and FreeBSD for heavy read and writes

On Thu, 4 Feb 2010, Amitabh Kant wrote:
> On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <> wrote:
>> If you can, add another 2 drives in RAID 1 and move+symlink the pg_xlog
>> directory to the new array.
>
> Can't do anything about this server now, but would surely keep in mind
> before upgrading other servers. Would you recommend the same speed
> drives(15K SAS) for RAID 1, or would a slower drive also work here (10K SAS
> or even SATA II)?

The performance requirements for the WAL are significantly lower than for
the main database. This is for two reasons - firstly the WAL is
write-only, and has no other activity. The WAL only gets read again in the
event of a crash. Secondly, writes to the WAL are sequential writes, which
is the fastest mode of operation for a disc, whereas the main database
discs will have to handle random access.

The main thing you need to make sure of is that the WAL is on a disc
system that has a battery-backed up cache. That way, it will be able to
handle the high rate of fsyncs that the WAL generates, and the cache will
convert that into a simple sequential write. Otherwise, you will be
limited to one fsync every 5ms (or whatever the access speed of your WAL
discs is).

If you make sure of that, then there is no reason to get expensive fast
discs for the WAL at all (assuming they are expensive enough to not lie
about flushing writes properly).

Matthew

--
So, given 'D' is undeclared too, with a default of zero, C++ is equal to D.
  mnw21, commenting on the "Surely the value of C++ is zero, but C is now 1"
  response to "No, C++ isn't equal to D. 'C' is undeclared [...] C++ should
  really be called 1" response to "C++ -- shouldn't it be called D?"