On Jun 16, 2010, at 1:53 PM, Alvaro Herrera wrote:
> Excerpts from Tom Lane's message of lun jun 14 23:57:11 -0400 2010:
>> Scott Carey <scott@richrelevance.com> writes:
>>> Great points. There is one other option that is decent for the WAL:
>>> If splitting out a volume is not acceptable for the OS and WAL -- absolutely split those two out into their own
partitions. It is most important to make sure that WAL and data are not on the same filesystem, especially if ext3 is
involved.
>>
>> Uh, no, WAL really needs to be on its own *spindle*. The whole point
>> here is to have one disk head sitting on the WAL and not doing anything
>> else except writing to that file.
>
> However, there's another point here -- probably what Scott is on about:
> on Linux (at least ext3), an fsync of any file does not limit to
> flushing that file's blocks -- it flushes *ALL* blocks on *ALL* files in
> the filesystem. This is particularly problematic if you have pgsql_tmp
> in the same filesystem and do lots of disk-based sorts.
>
> So if you have it in the same spindle but on a different filesystem, at
> least you'll avoid that extra fsync work, even if you have to live with
> the extra seeking.
yes, especially with a battery backed up caching raid controller the whole "own spindle" thing doesn't really matter,
theWAL log writes fairly slowly and linearly and any controller with a damn will batch those up efficiently.
By FAR, the most important thing is to have WAL on its own file system. If using EXT3 in a way that is safe for your
data(data = ordered or better), even with just one SATA disk, performance will improve a LOT if data and xlog are
separatedinto different file systems. Yes, an extra spindle is better.
However with a decent RAID card or caching storage, 8 spindles for it all in one raid 10, with a partition for xlog and
onefor data, is often better performing than a mirrored pair for OS/xlog and 6 for data so long as the file systems are
separated. With a dedicated xlog and caching reliable storage, you can even mount it direct to avoid polluting OS page
cache.
>
> --
> Álvaro Herrera <alvherre@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support