Andreas,
> Josh wrote:
> >> With a large database you may even think about
> >> shifting individual tables or indexes to seperate disks.
>
> OK, I admit it was a bit provoking. It was my intention to stir things up a
> little bit ;-) IMHO, thinking about locating data on dedicated files is a
> waste of time on small servers. Let the hardware do the job for you! It is
> "good enough".
Aha, by "large databases" I mean "several million records". In the odd case
where you have a database which has one or two tables which are larger than
the rest of the database combined, you can get a performance boost by putting
those tables, and/or their indexes, on a seperate spindle.
Frankly, for small servers, a pair of mirrored IDE drives is adequate. And,
of course, if you have a RAID 1+0 controller, that's better than trying to
directly allocate different files to different disks ... except the WAL log.
> I don't think solid state disks are a way out (unless you don't know where
> to bury your money :-). Maybe the gurus can tell more about PostgreSQL's
> caching, but for my opinion if enough RAM is available after some time all
> of the DB should be in cache eliminating the need to access the disks for
> read access. For writing, which is typically less than 10 % of total load,
> an optimizing caching disk controller should be sufficient.
Depends on the database. I've worked on DBs where writes were 40% of all
queries ... and 90% of the system resource load. For those databases,
moving the WAL log to a RAMdisk might mean a big boost.
Also, I'm currently working to figure out a solution for some 1U machines
which don't have any *room* for an extra drive for WAL. A PCI ramdisk would
be just perfect ...
--
Josh Berkus
Aglio Database Solutions
San Francisco