Thread: RAID + PostgreSQL?

RAID + PostgreSQL?

From
"MG"
Date:
Hello,
 
we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
 
When we do a big SELECT-query the whole maschine becomes very very very slowly or stands.
The maschine has 3 GB RAM, so we suppose it`s the RAID.
 
Has anyone some experience with RAID + PostgreSQL?
 
Where does PostgreSQL saves his temporary files? Perhaps these are the reason for the bad performance.
 
Greetings
 
Michaela

Re: RAID + PostgreSQL?

From
Tony Caduto
Date:
MG wrote:
> Hello,
>
> we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
>
> When we do a big SELECT-query the whole maschine becomes very very
> very slowly or stands.
> The maschine has 3 GB RAM, so we suppose it`s the RAID.
>
> Has anyone some experience with RAID + PostgreSQL?
>
> Where does PostgreSQL saves his temporary files? Perhaps these are the
> reason for the bad performance.
>
> Greetings
>
> Michaela
What kind of RAID?  I know if you have it set up to mirror it becomes
slow as pond water.
I have a server that was a hand me down so I did not have a choice in
the RAID and it was set up to mirror with two drives and
the performance on large selects was very bad.  The performance would
increase by 50 or more percent if
fsync = off    is set in the postgresql.conf file.

Do a search of the mailing list archives, there is a lot of talk about
RAID and battery backed write caches etc.


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration


Re: RAID + PostgreSQL?

From
"Joshua D. Drake"
Date:
On Monday 26 June 2006 00:36, MG wrote:
> Hello,
>
> we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.

I'm sorry.

>
> When we do a big SELECT-query the whole maschine becomes very very very
> slowly or stands. The maschine has 3 GB RAM, so we suppose it`s the RAID.

Or your database structure, a badly written query, lack of statistics, too
many dead rows, lack of vacuuming, bad postgresql.conf settings, or using an
aggregate without a where clause over millions of rows.

>
> Has anyone some experience with RAID + PostgreSQL?

Lots...    What type of raid? How many drives?

>
> Where does PostgreSQL saves his temporary files? Perhaps these are the
> reason for the bad performance.

I would suggest looking at the above half a dozen possibilities as well.

Sincerely,

Joshua D. Drake



>
> Greetings
>
> Michaela

Re: RAID + PostgreSQL?

From
Scott Marlowe
Date:
On Mon, 2006-06-26 at 08:59, Tony Caduto wrote:
> MG wrote:
> > Hello,
> >
> > we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
> >
> > When we do a big SELECT-query the whole maschine becomes very very
> > very slowly or stands.
> > The maschine has 3 GB RAM, so we suppose it`s the RAID.
> >
> > Has anyone some experience with RAID + PostgreSQL?
> >
> > Where does PostgreSQL saves his temporary files? Perhaps these are the
> > reason for the bad performance.
> >
> > Greetings
> >
> > Michaela
> What kind of RAID?  I know if you have it set up to mirror it becomes
> slow as pond water.

I have to say this has NOT been my experience.  With a pair of U320
drives on an LSI-Megaraid with battery backed cache (256M or 512M, not
sure which it was, it's been a few years) our pg server was noticeable
faster with a mirror set than with a single IDE drive (with cache
disabled) on the same machine.  And faster than a single SCSI drive with
no RAID controller as well.


> I have a server that was a hand me down so I did not have a choice in
> the RAID and it was set up to mirror with two drives and
> the performance on large selects was very bad.

Wow, something's wrong then.  normally, selects are much faster on
mirror sets rather than on a single drive.  Do you have a lot of sorts
spilling onto disc?

> The performance would
> increase by 50 or more percent if
> fsync = off    is set in the postgresql.conf file.

Of selects?  Or performance in general?  I can see if for performance in
general, but selects really shouldn't be greatly affected by fsync.

In another vein, I agree with Joshua.  There could be LOTS of causes of
poor performance.  I wouldn't just assume it's RAID until it's been
proven to be the cause of the problem.

Re: RAID + PostgreSQL?

From
Guy Fraser
Date:
On Mon, 2006-26-06 at 11:08 -0500, Scott Marlowe wrote:
> On Mon, 2006-06-26 at 08:59, Tony Caduto wrote:
> > MG wrote:
> > > Hello,
> > >
> > > we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
> > >
> > > When we do a big SELECT-query the whole maschine becomes very very
> > > very slowly or stands.
> > > The maschine has 3 GB RAM, so we suppose it`s the RAID.
> > >
> > > Has anyone some experience with RAID + PostgreSQL?
> > >
> > > Where does PostgreSQL saves his temporary files? Perhaps these are the
> > > reason for the bad performance.
> > >
> > > Greetings
> > >
> > > Michaela
> > What kind of RAID?  I know if you have it set up to mirror it becomes
> > slow as pond water.
>
> I have to say this has NOT been my experience.  With a pair of U320
> drives on an LSI-Megaraid with battery backed cache (256M or 512M, not
> sure which it was, it's been a few years) our pg server was noticeable
> faster with a mirror set than with a single IDE drive (with cache
> disabled) on the same machine.  And faster than a single SCSI drive with
> no RAID controller as well.
>
>
> > I have a server that was a hand me down so I did not have a choice in
> > the RAID and it was set up to mirror with two drives and
> > the performance on large selects was very bad.
>
> Wow, something's wrong then.  normally, selects are much faster on
> mirror sets rather than on a single drive.  Do you have a lot of sorts
> spilling onto disc?
>
> > The performance would
> > increase by 50 or more percent if
> > fsync = off    is set in the postgresql.conf file.
>
> Of selects?  Or performance in general?  I can see if for performance in
> general, but selects really shouldn't be greatly affected by fsync.
>
> In another vein, I agree with Joshua.  There could be LOTS of causes of
> poor performance.  I wouldn't just assume it's RAID until it's been
> proven to be the cause of the problem.
>

Confusion Abounds.

Mirroring does not improve performance. Mirroring writes the same data
to two sets of identically sized partitions/slices, and reads the data
from both and verifies the integrity of the returned data. A good RAID
controller will add some latency on first read, but with caching can
improve the speed of some drive operations. Large selects may very
well produce more data than the cache on the controller can hold so
the speed will be limited by the sustained throughput of the drive and
controller with a number of other factors that can cause lower levels
of performance. Some performance improvements can be made using RAID
methods that distribute the data over multiple drives like striping,
but even with striping large selects can still be larger than the
cache, but the data can usually be accessed more quickly than with
a single drive, or straight mirroring.

The main advantage mirroring has is that it can provide redundancy
in the event of premature drive failure, as is usually the least
expensive data redundancy solution.




Re: RAID + PostgreSQL?

From
Tony Caduto
Date:
Scott Marlowe wrote:
> On Mon, 2006-06-26 at 08:59, Tony Caduto wrote:
>
>> MG wrote:
>>
>>> Hello,
>>>
>>> we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
>>>
>>> When we do a big SELECT-query the whole maschine becomes very very
>>> very slowly or stands.
>>> The maschine has 3 GB RAM, so we suppose it`s the RAID.
>>>
>>> Has anyone some experience with RAID + PostgreSQL?
>>>
>>> Where does PostgreSQL saves his temporary files? Perhaps these are the
>>> reason for the bad performance.
>>>
>>> Greetings
>>>
>>> Michaela
>>>
>> What kind of RAID?  I know if you have it set up to mirror it becomes
>> slow as pond water.
>>
>
> I have to say this has NOT been my experience.  With a pair of U320
> drives on an LSI-Megaraid with battery backed cache (256M or 512M, not
> sure which it was, it's been a few years) our pg server was noticeable
> faster with a mirror set than with a single IDE drive (with cache
> disabled) on the same machine.  And faster than a single SCSI drive with
> no RAID controller as well.
>
>
Hi Scott,

We are just using the compaq hardware SCSI //raid included with the
server and it does not have a
battery backed cache.  Actually my test DB running the same queries on a
cheap IDE drive (on a Athlon 2200+ where 50% faster than the compaq
running the mirrored
SCSI drives.  The compaq system was also a Dual 2.4 gzh Xeon with 2.5 GB
of memory and the Athlon had 512mb.  Some of the performance on the
Athlon could be attributed to the CPU, but mostly the IDE drive just
blew away the mirrored SCSI drive
On the Compaq system setting Fsync to false increased the speed of all
select statements, not just complex ones with sorting.

I didn't set up the hardware, so I don't know if the system admin guy
screwed anything up in the raid setup.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration


Re: RAID + PostgreSQL?

From
Scott Marlowe
Date:
On Mon, 2006-06-26 at 12:10, Guy Fraser wrote:
> On Mon, 2006-26-06 at 11:08 -0500, Scott Marlowe wrote:
> > On Mon, 2006-06-26 at 08:59, Tony Caduto wro
> >
> > I have to say this has NOT been my experience.  With a pair of U320
> > drives on an LSI-Megaraid with battery backed cache (256M or 512M, not
> > sure which it was, it's been a few years) our pg server was noticeable
> > faster with a mirror set than with a single IDE drive (with cache
> > disabled) on the same machine.  And faster than a single SCSI drive with
> > no RAID controller as well.
> >
> >
>
> Confusion Abounds.
>
> Mirroring does not improve performance. Mirroring writes the same data
> to two sets of identically sized partitions/slices, and reads the data
> from both and verifies the integrity of the returned data.

I know of no RAID controllers that do that with mirroring during reads.
The linux kernel RAID module also does not do this.  While all writes go
to all drives, reads are normally interleaved between drives.  You can
even use > 2 drives in a mirror set for improved read performance of
things like a data warehouse, where the loads are in bulk during off
hours and then many many users read different sections of the same data
sets at the same time.

If you've an example of a hardware or software RAID implementation that
reads from both drives and compares them to be sure the data has
integrity, I'd be interested in seeing them, as there are some uses for
that kind of setup, although you'd really need three disks, at least, to
assure a good read, since a failure in a two drive mirror would result
in a state where you knew the data was corrupted, but without check
digits no way of knowing which drive is failing.

Re: RAID + PostgreSQL?

From
"Alex Turner"
Date:
Compaq RAID controllers are known to be slow under linux.

Alex.

On 6/26/06, Tony Caduto <tony_caduto@amsoftwaredesign.com > wrote:
Scott Marlowe wrote:
> On Mon, 2006-06-26 at 08:59, Tony Caduto wrote:
>
>> MG wrote:
>>
>>> Hello,
>>>
>>> we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
>>>
>>> When we do a big SELECT-query the whole maschine becomes very very
>>> very slowly or stands.
>>> The maschine has 3 GB RAM, so we suppose it`s the RAID.
>>>
>>> Has anyone some experience with RAID + PostgreSQL?
>>>
>>> Where does PostgreSQL saves his temporary files? Perhaps these are the
>>> reason for the bad performance.
>>>
>>> Greetings
>>>
>>> Michaela
>>>
>> What kind of RAID?  I know if you have it set up to mirror it becomes
>> slow as pond water.
>>
>
> I have to say this has NOT been my experience.  With a pair of U320
> drives on an LSI-Megaraid with battery backed cache (256M or 512M, not
> sure which it was, it's been a few years) our pg server was noticeable
> faster with a mirror set than with a single IDE drive (with cache
> disabled) on the same machine.  And faster than a single SCSI drive with
> no RAID controller as well.
>
>
Hi Scott,

We are just using the compaq hardware SCSI //raid included with the
server and it does not have a
battery backed cache.  Actually my test DB running the same queries on a
cheap IDE drive (on a Athlon 2200+ where 50% faster than the compaq
running the mirrored
SCSI drives.  The compaq system was also a Dual 2.4 gzh Xeon with 2.5 GB
of memory and the Athlon had 512mb.  Some of the performance on the
Athlon could be attributed to the CPU, but mostly the IDE drive just
blew away the mirrored SCSI drive
On the Compaq system setting Fsync to false increased the speed of all
select statements, not just complex ones with sorting.

I didn't set up the hardware, so I don't know if the system admin guy
screwed anything up in the raid setup.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Re: RAID + PostgreSQL?

From
Franz.Rasper@izb.de
Date:
I have here an Smart Array 6i with 192 MB Battery Backup Write Cache with several 146 MB U320 SCSI HDs
and I get read/write speed till 120 MB per secound (HP ProLiant DL 380G4) under Debian Linux with
Kernel 2.4.32 an Postgresql 7.4.13.
 
Greetings,
 
-Franz
-----Ursprüngliche Nachricht-----
Von: Alex Turner [mailto:armtuk@gmail.com]
Gesendet: Dienstag, 27. Juni 2006 03:50
An: Tony Caduto
Cc: Scott Marlowe; MG; pgsql general
Betreff: Re: [GENERAL] RAID + PostgreSQL?

Compaq RAID controllers are known to be slow under linux.

Alex.

On 6/26/06, Tony Caduto <tony_caduto@amsoftwaredesign.com > wrote:
Scott Marlowe wrote:
> On Mon, 2006-06-26 at 08:59, Tony Caduto wrote:
>
>> MG wrote:
>>
>>> Hello,
>>>
>>> we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
>>>
>>> When we do a big SELECT-query the whole maschine becomes very very
>>> very slowly or stands.
>>> The maschine has 3 GB RAM, so we suppose it`s the RAID.
>>>
>>> Has anyone some experience with RAID + PostgreSQL?
>>>
>>> Where does PostgreSQL saves his temporary files? Perhaps these are the
>>> reason for the bad performance.
>>>
>>> Greetings
>>>
>>> Michaela
>>>
>> What kind of RAID?  I know if you have it set up to mirror it becomes
>> slow as pond water.
>>
>
> I have to say this has NOT been my experience.  With a pair of U320
> drives on an LSI-Megaraid with battery backed cache (256M or 512M, not
> sure which it was, it's been a few years) our pg server was noticeable
> faster with a mirror set than with a single IDE drive (with cache
> disabled) on the same machine.  And faster than a single SCSI drive with
> no RAID controller as well.
>
>
Hi Scott,

We are just using the compaq hardware SCSI //raid included with the
server and it does not have a
battery backed cache.  Actually my test DB running the same queries on a
cheap IDE drive (on a Athlon 2200+ where 50% faster than the compaq
running the mirrored
SCSI drives.  The compaq system was also a Dual 2.4 gzh Xeon with 2.5 GB
of memory and the Athlon had 512mb.  Some of the performance on the
Athlon could be attributed to the CPU, but mostly the IDE drive just
blew away the mirrored SCSI drive
On the Compaq system setting Fsync to false increased the speed of all
select statements, not just complex ones with sorting.

I didn't set up the hardware, so I don't know if the system admin guy
screwed anything up in the raid setup.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Re: RAID + PostgreSQL?

From
Franz.Rasper@izb.de
Date:
I have here HP DL 380 G3 und HP DL 380 G4. (here Smart Array 5i and Smart
Array 6i)
Maybe there are problems with old linux kernel (but as far as i know >
2.4.27)
I dont know about such performance problems, maybe the conrollers are faster
under windows.

The battery backup write cache are very import for inserts.

You should use for performance test with dd under linux larger blocksizes,
but you should compare postgresql under linux and windows (selects, update,
inserts, etc.)

Greetings,

-Franz

-----Ursprüngliche Nachricht-----
Von: Bruno Almeida do Lago [mailto:teolupus@gmail.com]
Gesendet: Dienstag, 27. Juni 2006 14:28
An: Franz.Rasper@izb.de
Betreff: RE: [GENERAL] RAID + PostgreSQL?


I've heard from other lists that Compaq Smart Array controllers are slower
under Linux.

I could test this since I've some DL380 available with Linux and Windows.

For Linux I could use dd, but I wonder what can be used with Windows so we
could compare the results?

Best Regards,
   Bruno

________________________________________
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Franz.Rasper@izb.de
Sent: Tuesday, June 27, 2006 4:00 AM
To: armtuk@gmail.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] RAID + PostgreSQL?

I have here an Smart Array 6i with 192 MB Battery Backup Write Cache with
several 146 MB U320 SCSI HDs
and I get read/write speed till 120 MB per secound (HP ProLiant DL 380G4)
under Debian Linux with
Kernel 2.4.32 an Postgresql 7.4.13.
 
Greetings,
 
-Franz


Re: RAID + PostgreSQL?

From
Scott Marlowe
Date:
On Tue, 2006-06-27 at 08:05, Franz.Rasper@izb.de wrote:
> I have here HP DL 380 G3 und HP DL 380 G4. (here Smart Array 5i and Smart
> Array 6i)
> Maybe there are problems with old linux kernel (but as far as i know >
> 2.4.27)
> I dont know about such performance problems, maybe the conrollers are faster
> under windows.
>
> The battery backup write cache are very import for inserts.
>
> You should use for performance test with dd under linux larger blocksizes,
> but you should compare postgresql under linux and windows (selects, update,
> inserts, etc.)

Note that having the latest driver usually helps a lot too.

The LSI megaraid was solid but a mediocre performer with the older 1.18
driver, but the 2.x series driver was much faster when I tested it.

Re: RAID + PostgreSQL?

From
Joseph Shraibman
Date:
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

Re: RAID + PostgreSQL?

From
Glen Parker
Date:
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
>
>


Re: RAID + PostgreSQL?

From
Franz.Rasper@izb.de
Date:
How much I/O Performance do you need ?

READ Performance ? Write Performance ?

I need an fast and reliable RAID Controller (harddisks have to be hot plug,
automatic rebuild etc.)
and I have to say that the HP DL 380 G 4 with Battery Backup Write Cache ,
FAST U320 HDs,
some gigs ram (chipkill is a nice feature), redundant fans and power
supplies is a good database server.
Unfortunately I had no chance to test the HP DL 385.

Greetings,

-Franz

-----Ursprüngliche Nachricht-----
Von: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Gesendet: Dienstag, 27. Juni 2006 17:45
An: Franz.Rasper@izb.de
Cc: teolupus@gmail.com; pgsql general
Betreff: Re: [GENERAL] RAID + PostgreSQL?


On Tue, 2006-06-27 at 08:05, Franz.Rasper@izb.de wrote:
> I have here HP DL 380 G3 und HP DL 380 G4. (here Smart Array 5i and Smart
> Array 6i)
> Maybe there are problems with old linux kernel (but as far as i know >
> 2.4.27)
> I dont know about such performance problems, maybe the conrollers are
faster
> under windows.
>
> The battery backup write cache are very import for inserts.
>
> You should use for performance test with dd under linux larger blocksizes,
> but you should compare postgresql under linux and windows (selects,
update,
> inserts, etc.)

Note that having the latest driver usually helps a lot too.

The LSI megaraid was solid but a mediocre performer with the older 1.18
driver, but the 2.x series driver was much faster when I tested it.

---------------------------(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

Re: RAID + PostgreSQL?

From
"Alex Turner"
Date:
Of course if you really want good performance, 6 drives is a little light, and U320 is definatley not the way to go as you are limited to 320MB/sec per channel (an the DL380 comes with all drives on one channel) (not that you could saturate that with only 6 drives in RAID 10 anyway).  Given that this chap has 13 disks already, a DL380 is probably not what he is looking for.  Consider SAS for better performance.

Alex

On 6/28/06, Franz.Rasper@izb.de <Franz.Rasper@izb.de> wrote:
How much I/O Performance do you need ?

READ Performance ? Write Performance ?

I need an fast and reliable RAID Controller (harddisks have to be hot plug,
automatic rebuild etc.)
and I have to say that the HP DL 380 G 4 with Battery Backup Write Cache ,
FAST U320 HDs,
some gigs ram (chipkill is a nice feature), redundant fans and power
supplies is a good database server.
Unfortunately I had no chance to test the HP DL 385.

Greetings,

-Franz

-----Ursprüngliche Nachricht-----
Von: Scott Marlowe [mailto: smarlowe@g2switchworks.com]
Gesendet: Dienstag, 27. Juni 2006 17:45
An: Franz.Rasper@izb.de
Cc: teolupus@gmail.com; pgsql general
Betreff: Re: [GENERAL] RAID + PostgreSQL?


On Tue, 2006-06-27 at 08:05, Franz.Rasper@izb.de wrote:
> I have here HP DL 380 G3 und HP DL 380 G4. (here Smart Array 5i and Smart
> Array 6i)
> Maybe there are problems with old linux kernel (but as far as i know >
> 2.4.27)
> I dont know about such performance problems, maybe the conrollers are
faster
> under windows.
>
> The battery backup write cache are very import for inserts.
>
> You should use for performance test with dd under linux larger blocksizes,
> but you should compare postgresql under linux and windows (selects,
update,
> inserts, etc.)

Note that having the latest driver usually helps a lot too.

The LSI megaraid was solid but a mediocre performer with the older 1.18
driver, but the 2.x series driver was much faster when I tested it.

---------------------------(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 6: explain analyze is your friend

Re: RAID + PostgreSQL?

From
"Alex Turner"
Date:
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@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

Re: RAID + PostgreSQL?

From
Franz.Rasper@izb.de
Date:
If you need so much performance and space you will need an external storage extensions (how about SAN)
or HP ML 570 G4 (up to 18 hpt plug SFF Drives) with Intel 7041 CPUs ?
 
Greetings,
 
-Franz
-----Ursprüngliche Nachricht-----
Von: Alex Turner [mailto:armtuk@gmail.com]
Gesendet: Dienstag, 4. Juli 2006 04:10
An: Franz.Rasper@izb.de
Cc: smarlowe@g2switchworks.com; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] RAID + PostgreSQL?

Of course if you really want good performance, 6 drives is a little light, and U320 is definatley not the way to go as you are limited to 320MB/sec per channel (an the DL380 comes with all drives on one channel) (not that you could saturate that with only 6 drives in RAID 10 anyway).  Given that this chap has 13 disks already, a DL380 is probably not what he is looking for.  Consider SAS for better performance.

Alex

On 6/28/06, Franz.Rasper@izb.de <Franz.Rasper@izb.de> wrote:
How much I/O Performance do you need ?

READ Performance ? Write Performance ?

I need an fast and reliable RAID Controller (harddisks have to be hot plug,
automatic rebuild etc.)
and I have to say that the HP DL 380 G 4 with Battery Backup Write Cache ,
FAST U320 HDs,
some gigs ram (chipkill is a nice feature), redundant fans and power
supplies is a good database server.
Unfortunately I had no chance to test the HP DL 385.

Greetings,

-Franz

-----Ursprüngliche Nachricht-----
Von: Scott Marlowe [mailto: smarlowe@g2switchworks.com]
Gesendet: Dienstag, 27. Juni 2006 17:45
An: Franz.Rasper@izb.de
Cc: teolupus@gmail.com; pgsql general
Betreff: Re: [GENERAL] RAID + PostgreSQL?


On Tue, 2006-06-27 at 08:05, Franz.Rasper@izb.de wrote:
> I have here HP DL 380 G3 und HP DL 380 G4. (here Smart Array 5i and Smart
> Array 6i)
> Maybe there are problems with old linux kernel (but as far as i know >
> 2.4.27)
> I dont know about such performance problems, maybe the conrollers are
faster
> under windows.
>
> The battery backup write cache are very import for inserts.
>
> You should use for performance test with dd under linux larger blocksizes,
> but you should compare postgresql under linux and windows (selects,
update,
> inserts, etc.)

Note that having the latest driver usually helps a lot too.

The LSI megaraid was solid but a mediocre performer with the older 1.18
driver, but the 2.x series driver was much faster when I tested it.

---------------------------(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 6: explain analyze is your friend

Re: RAID + PostgreSQL?

From
"Joshua D. Drake"
Date:
On Tuesday 04 July 2006 00:40, Franz.Rasper@izb.de wrote:
> If you need so much performance and space you will need an external storage
> extensions (how about SAN)
> or HP ML 570 G4 (up to 18 hpt plug SFF Drives) with Intel 7041 CPUs ?

Or you could be a little more thrifty and go with the ml30 which has 14 hot
plug scsi drives. Throw in a second raid controller for 800 bucks and your
set.

Joshua D. Drake


>
> Greetings,
>
> -Franz
>
> -----Ursprüngliche Nachricht-----
> Von: Alex Turner [mailto:armtuk@gmail.com]
> Gesendet: Dienstag, 4. Juli 2006 04:10
> An: Franz.Rasper@izb.de
> Cc: smarlowe@g2switchworks.com; pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] RAID + PostgreSQL?
>
>
> Of course if you really want good performance, 6 drives is a little light,
> and U320 is definatley not the way to go as you are limited to 320MB/sec
> per channel (an the DL380 comes with all drives on one channel) (not that
> you could saturate that with only 6 drives in RAID 10 anyway).  Given that
> this chap has 13 disks already, a DL380 is probably not what he is looking
> for. Consider SAS for better performance.
>
> Alex
>
>
> On 6/28/06, Franz.Rasper@izb.de <mailto:Franz.Rasper@izb.de>
> <Franz.Rasper@izb.de <mailto:Franz.Rasper@izb.de> > wrote:
>
> How much I/O Performance do you need ?
>
> READ Performance ? Write Performance ?
>
> I need an fast and reliable RAID Controller (harddisks have to be hot plug,
> automatic rebuild etc.)
> and I have to say that the HP DL 380 G 4 with Battery Backup Write Cache ,
> FAST U320 HDs,
> some gigs ram (chipkill is a nice feature), redundant fans and power
> supplies is a good database server.
> Unfortunately I had no chance to test the HP DL 385.
>
> Greetings,
>
> -Franz
>
> -----Ursprüngliche Nachricht-----
> Von: Scott Marlowe [mailto:  <mailto:smarlowe@g2switchworks.com>
> smarlowe@g2switchworks.com]
> Gesendet: Dienstag, 27. Juni 2006 17:45
> An: Franz.Rasper@izb.de <mailto:Franz.Rasper@izb.de>
> Cc: teolupus@gmail.com <mailto:teolupus@gmail.com> ; pgsql general
> Betreff: Re: [GENERAL] RAID + PostgreSQL?
>
>
> On Tue, 2006-06-27 at 08:05, Franz.Rasper@izb.de
>
> <mailto:Franz.Rasper@izb.de>  wrote:
> > I have here HP DL 380 G3 und HP DL 380 G4. (here Smart Array 5i and Smart
> > Array 6i)
> > Maybe there are problems with old linux kernel (but as far as i know >
> > 2.4.27)
> > I dont know about such performance problems, maybe the conrollers are
>
> faster
>
> > under windows.
> >
> > The battery backup write cache are very import for inserts.
> >
> > You should use for performance test with dd under linux larger
> > blocksizes, but you should compare postgresql under linux and windows
> > (selects,
>
> update,
>
> > inserts, etc.)
>
> Note that having the latest driver usually helps a lot too.
>
> The LSI megaraid was solid but a mediocre performer with the older 1.18
> driver, but the 2.x series driver was much faster when I tested it.
>
> ---------------------------(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 6: explain analyze is your friend

Re: RAID + PostgreSQL?

From
Joseph Shraibman
Date:
Alex Turner wrote:

> 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

Well we have a hot standby disk.

> 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.

Oh it is using indexes, but it is still painfully slow.