Re: RAID + PostgreSQL?

Please note that your single Fibre Channel card is limiting you to 256MB/sec
read speed from your array, which is not enough for a 13 drive RAID 5.

First test would be to run a basic bonnie++ benchmark against your drive
array and see what kind of throughput you are getting for linear read,
linear write. If it's much under 256MB/sec, particularly for read, the
problem may lie with your controller or your array. A 13 disk RAID 5 should
easily max out the fibre channel card for raw throughput.

As an aside note, I would consider a 13 disk RAID 5 a high risk solution.
If you loose just two drives of 13 at the same time, your data is all gone.
If you loose one drive, your array goes into degraded mode and your read and
write performance goes to hell, and your machine might as well be down as
all queries will suddenly take a great deal longer (with 13 drives, it would
be more than 12 times as long as you will have to read and then XOR all 12
other blocks together to get one block back) (see
http://en.wikipedia.org/wiki/Redundant_array_of_independent_disks for more
info on RAID levels).

Second thing might be to post an explain analyze so that we can have a look
at the query and see what's up. May be some of the tunning gurus can give
good hints on things missing, like indexes and analyzingness, or general
query structure.

Alex.

On 6/27/06, Glen Parker <glenebob(at)nwlink(dot)com> wrote:
>
> My experience has been that RAID5 + Postgres = counter productive. I've
> been happier with a 4-way software mirror (Linux).
>
> In the past, Postgres has been very drive seek intensive, because of the
> way index scans were performed. RAID5 does *NOTHING* to help this. Raw
> disk throughput is essentially a non-issue with Postgres, for the sort
> of use I'm used to putting it to.
>
> My tests have shown *HUGE* improvement in index scans in 8.1. The
> bitmap index read was one of the most profound improvements I've seen in
> Postgres yet. I suspect 8.1 would help you quite a lot.
>
> -Glen
>
> Joseph Shraibman wrote:
> > I'm running 8.0.8 on a raid 5 over 13 disks, and select performance on a
> > query that needs to join two large tables is very bad. top shows pg
> > using 2 to 4 percent cpu. Doing a query on one big table uses 30 to 45
> > percent cpu.
> >
> > This is RHEL 4 running kernel 2.6.9-22.ELsmp, using an LSI fiber channel
> > adapter. The disk shelf is one of these:
> > http://iqstor.com/products/iq1000.htm
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>