Re: how to configure my new server

On Thu, 06 Feb 2003 22:43:23 +0100 in message <3E42D6FB(dot)9000605(at)web(dot)de>, Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de> wrote:
> I do not agree with the advice to dedicate one disc to every table.
>
> Imagine 10 disks, 10 tables, and 10 users accessing the same table. This
> would mean 1 disk really busy and 9 idling all the way. If you use a
> RAID array, the random access is hopefully split to all disks, giving a
> much better performance. There are suggestions about direct disk access
> (using O_DIRECT file flags), versus using the OS' caching mechanisms.
> This is quite the same, in hardware. Today's hardware is designed to
> make the best of it, give it a chance!

Unfortunately, today's hardware still has rotational latency. You aren't goign to get much more than 300 seeks per sec on the best single drive. Putting them together in a way that requires half to all of them to seek for a given read or write is a performance killer. The only way around this is high end raid cards with backup batteries and ram.

I've been doing some tests using pgbench (which aren't written up yet) on the topic of low budget performance. So far, using linux 2.4.20 md software raid where applicable, I've seen against a baseline of one ide disk:

running on a rocketraid card (kernel thinks it's scsi) is faster than onboard controllers
mirrored is negligably slower
striped is much slower
splitting WAL and Data on two drives gives a 40+% speed boost
having data in ram cache is good for ~ 100% speed boost. Essentially, disk activity goes from evenly split reading and writing to all writing

The only pg settings that show any correlation with pgbench performance are the # of WAL logs, generally corresponding to the interval between flushing wal logs to the data store. Buffers don't change much over a 64-8192 range, Sort mem doesn't change much. (Note that that may be due to the query types in this benchmark. My app certainly needs the sortmem)

As a somewhat on topic thought, it would be really neat to have a pci card that was one slot for ram, one for compact flash, a memory/ide controller and battery. Fill the ram and cf with identical sized units, and use it as a disk for WAL. if the power goes off, dump the ram to cf. Should be able to do thousands of writes per sec, effectivley moving the bottleneck somewhere else. It's probably $20 worth of chips for the board, but it would probably sell for thousands.

eric