From: Gregory Stark <stark@enterprisedb.com>
To: <lists@on-track.ca>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Adding disks/xlog & index
In-Reply-To: <1645.69.31.174.216.1180129421.squirrel@webmail.ctgameinfo.com>
	(lists@on-track.ca's message of "Fri, 25 May 2007 14:43:41 -0700
	(PDT)")
Organization: EnterpriseDB
References: <1645.69.31.174.216.1180129421.squirrel@webmail.ctgameinfo.com>
Date: Sat, 26 May 2007 01:35:43 +0100
Message-ID: <87ps4ojuao.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

<lists@on-track.ca> writes:

> Some questions on this:
> 1. Can the database survive loss/corruption of the xlog and indexes in a
> recoverable way? To save money (and because I won't need the throughput as
> much), I am thinking on making this index/wal/xlog drive a single cheap
> sata drive (or maybe a non-raided 15k scsi for 60% more money). However
> without the redundancy of a mirror I am concerned about drive failure.
> Loss of several mins of recent transactions in a serious crash is
> acceptable to be, but full/serious database corruption (the likes of fsync
> off) is not.

Losing any WAL that the database has fsynced is exactly like having fsync off.

> 2. Is there any point using a high performance (ie scsi) disk for this, or
> would the mirror containing the majority of the data still be the major
> bottleneck causing the disk usage to not exceed sata performance anyway?

Well that depends on your database traffic. In most databases the volume of
WAL traffic is substantially less than the i/o traffic to the data drives. So
you usually don't need to be able to sustain high i/o bandwidth to the WAL
drive.

However in some database loads the latency to the WAL drive does matter. This
is especially true if you're executing a lot of short transactions and
response time is critical. Especially if you aren't executing many such
transactions in parallel. So for example if you're processing a serial batch
of short transactions and committing each one as a separate transaction. In
that case you would want a drive that can fsync fast which either means a
battery backed cache or 15kRPM drive. It doesn't necessarily mean you need a
bit raid array though.


-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com