Re: SCSI vs SATA - Mailing list pgsql-performance

On Wed, 2007-04-04 at 09:12, jason@ohloh.net wrote:
> On Apr 3, 2007, at 6:54 PM, Geoff Tolley wrote:
>

> > But what's likely to make the largest difference in the OP's case
> > (many inserts) is write caching, and a battery-backed cache would
> > be needed for this. This will help mask write latency differences
> > between the two options, and so benefit SATA more. Some 3ware cards
> > offer it, some don't, so check the model.
>
> The servers are hooked up to a reliable UPS. The battery-backed cache
> won't hurt but might be overkill (?).

Just had to mention that the point of battery backed cache on the RAID
controller isn't the same as for a UPS on a system.

With drives that properly report fsync(), your system is limited to the
rpm of the drive( subsystem) that the pg_xlog sits upon.  With battery
backed cache, the controller immediately acknowledges an fsync() call
and then commits it at its leisure.  Should the power be lost, either
due to mains / UPS failure or internal power supply failure, the
controller hangs onto those data for several days, and upon restart
flushes them out to the drives they were heading for originally.

battery backed cache is the best way to get both good performance and
reliability from a system without breaking the bank.  I've seen 2 disk
RAID-1 setups with BBU beat some pretty big arrays that didn't have a
BBU on OLTP work.


> > How the drives are arranged is going to be important too - one big
> > RAID 10 is going to be rather worse than having arrays dedicated to
> > each of pg_xlog, indices and tables, and on that front the SATA
> > option is going to grant more flexibility.
>
> I've read some recent contrary advice. Specifically advising the
> sharing of all files (pg_xlogs, indices, etc..) on a huge raid array
> and letting the drives load balance by brute force.

The other, at first almost counter-intuitive result was that putting
pg_xlog on a different partition on the same array (i.e. one big
physical partition broken up into multiple logical ones) because the OS
overhead of writing all the data to one file system caused performance
issues.  Can't remember who reported the performance increase of the top
of my head.

Note that a lot of the advantages to running on multiple arrays etc...
are somewhat negated by having a good RAID controller with a BBU.