Re: RAID + PostgreSQL?
| От | Alex Turner |
|---|---|
| Тема | Re: RAID + PostgreSQL? |
| Дата | |
| Msg-id | 33c6269f0607031924k2b600012w4a726b2dcf17e313@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: RAID + PostgreSQL? (Glen Parker <glenebob@nwlink.com>) |
| Ответы |
Re: RAID + PostgreSQL?
|
| Список | pgsql-general |
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.
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@nwlink.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
В списке pgsql-general по дате отправления: