pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Article about PostgreSQL and RAID in Brazil
10+ messages / 6 participants
[nested] [flat]

* Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 17:50  Josh Berkus <josh@agliodbs.com>
  0 siblings, 3 replies; 10+ messages in thread

From: Josh Berkus @ 2004-09-16 17:50 UTC (permalink / raw)
  To: ricardo@sqlmagazine.com.br; +Cc: pgsql-performance; br@postgresql.org

Ricardo,

Hello.   I've moved your query to a more appropriate mailing list; on 
PERFORMANCE we discuss RAID all the time.  If you don't mind wading through a 
host of opinions, you'll get plenty here.  I've also cc'd our Brazillian 
PostgreSQL community.

Everyone, please note that Ricardo is NOT subscribed so cc him on your 
responses.

Here's Ricardo's question.   My response is below it.

===============================================
Let me introduce, I'm Ricardo Rezende and I'm SQL Magazine subeditor, from 
Brazil (http://www.sqlmagazine.com.br.).

My goal in this first contact is to solve a doubt about PostgreSQL RDBMS.

I'm writing an article about redundant storage technology, called RAID. 
The first part of the article can be found in 
http://www.sqlmagazine.com.br/colunistas.asp?artigo=Colunistas/RicardoRezende/06_Raid_P1.asp

My ideia is to put, in the end of the article, a note about the better 
configuration of RAID to use with PostgreSQL and the reasons, including 
the reference to the autor/link to this information.

Could you send me this information?

Our magazine is being a reference between DBAs and Database Developers in 
Brazil and that is the reason to write "oficial" papers about PostgreSQL

Thank you very much and I'm waiting for a return of this e-mail.
=========================================================

The first and most important step for RAID performance with PostgreSQL is to 
get a card with onboard battery back-up and enable the write cache for the 
card.   You do not want to enable the write cache *without* battery back-up 
because of the risk of data corruption after a power failure.

If you can't afford this hardware, I would advise using software RAID over 
using a cheaper (< $300US) RAID card.

The second step is to have lots of disks; 5 drives is a minimum for really 
good performance.   3-drive RAID5, in particular, is a poor performer for 
PostgreSQL, often resulting in I/O that is 40% or less as efficient as a 
single disk due to extremely slow random seeks and little parallelization.

Once you have 6 drives or more, opinions are divided on whether RAID 10 or 
RAID 5 is better.   I think it partly depends on your access pattern.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 18:10  Daniel Ceregatti <daniel@omnis.com>
  parent: Josh Berkus <josh@agliodbs.com>
  2 siblings, 2 replies; 10+ messages in thread

From: Daniel Ceregatti @ 2004-09-16 18:10 UTC (permalink / raw)
  To: pgsql-performance; +Cc: ricardo@sqlmagazine.com.br


>The first part of the article can be found in 
>http://www.sqlmagazine.com.br/colunistas.asp?artigo=Colunistas/RicardoRezende/06_Raid_P1.asp
>  
>
The site seems to be down. I was looking forward to reading it. :(

>The first and most important step for RAID performance with PostgreSQL is to 
>get a card with onboard battery back-up and enable the write cache for the 
>card.   You do not want to enable the write cache *without* battery back-up 
>because of the risk of data corruption after a power failure.
>  
>
Here is a small example of the performance difference with write cache:

http://sh.nu/bonnie.txt

-- 

Daniel Ceregatti - Programmer
Omnis Network, LLC

A little suffering is good for the soul.
		-- Kirk, "The Corbomite Maneuver", stardate 1514.0




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 18:57  Steinar H. Gunderson <sgunderson@bigfoot.com>
  parent: Daniel Ceregatti <daniel@omnis.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Steinar H. Gunderson @ 2004-09-16 18:57 UTC (permalink / raw)
  To: pgsql-performance

On Thu, Sep 16, 2004 at 11:10:13AM -0700, Daniel Ceregatti wrote:
> Here is a small example of the performance difference with write cache:
> 
> http://sh.nu/bonnie.txt

Am I missing something here? I can't find any tests with the same machine
showing the difference between writeback and write-through -- one machine
always uses write-through and the other always uses writeback. (Yes, the
hardware looks more or less the same, but the kernels and systems are way
different.)

/* Steinar */
-- 
Homepage: http://www.sesse.net/



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 19:00  Josh Berkus <josh@agliodbs.com>
  parent: Daniel Ceregatti <daniel@omnis.com>
  1 sibling, 2 replies; 10+ messages in thread

From: Josh Berkus @ 2004-09-16 19:00 UTC (permalink / raw)
  To: Daniel Ceregatti <daniel@omnis.com>; +Cc: pgsql-performance

Primer,

> The site seems to be down. I was looking forward to reading it. :(

I didn't have a problem.   The site *is* in Portuguese, though.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 19:01  Daniel Ceregatti <daniel@omnis.com>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Daniel Ceregatti @ 2004-09-16 19:01 UTC (permalink / raw)
  To: pgsql-performance

Josh Berkus wrote:

>Primer,
>
>  
>
>>The site seems to be down. I was looking forward to reading it. :(
>>    
>>
>
>I didn't have a problem.   The site *is* in Portuguese, though.
>
>  
>
Yes, it came up finally. Fortunately I'm Brazilian. :)

-- 

Daniel Ceregatti - Programmer
Omnis Network, LLC

Too clever is dumb.
		-- Ogden Nash

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 20:43  Qing Zhao <qzhao@supplyfx.com>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Qing Zhao @ 2004-09-16 20:43 UTC (permalink / raw)
  To: pgsql-performance


Hi, there,

I am running PostgreSQL 7.3.4 on MAC OS X G5 with dual processors and
8GB memory. The shared buffer was set as 512MB.

The database has been running great until about 10 days ago when our
developers decided to add some indexes to some tables to speed up
certain uploading ops.

Now the CPU usage reaches 100% constantly when there are a few users
accessing their information by SELECT tables in databases. If I REINEX
all the indexes, the database performance improves a bit but before 
long,
it goes back to bad again.

My suspicion is that since  now a few indexes are added, every ops are
run by PostgreSQL with the indexes being used when calculating cost.
This leads to the downgrade of performance.

What do you think of this? What is the possible solution?

Thanks!

Qing

The following is the output from TOP command:

Processes:  92 total, 4 running, 88 sleeping... 180 threads            
13:09:18
Load Avg:  2.81, 2.73, 2.50     CPU usage:  95.2% user, 4.8% sys, 0.0% 
idle
SharedLibs: num =  116, resident = 11.5M code, 1.66M data, 4.08M 
LinkEdit
MemRegions: num = 12132, resident =  148M + 2.82M private,  403M shared
PhysMem:   435M wired, 5.04G active, 2.22G inactive, 7.69G used,  316M 
free
VM: 32.7G + 81.5M   5281127(13) pageins, 8544145(0) pageouts

   PID COMMAND      %CPU   TIME   #TH #PRTS #MREGS RPRVT  RSHRD  RSIZE  
VSIZE
27314 postgres    92.2%  2:14.75   1     9    49  12.8M+  396M  75.0M+  
849M
26099 postgres    91.1% 19:28.04   1     9    67  15.9M+  396M   298M+  
850M
24754 top          2.8%  4:48.33   1    29    26   272K   404K   648K  
27.1M
     0 kernel_tas   1.9%  2:12:05  40     2  8476  67.1M     0K   281M  
1.03G
   294 hwmond       0.5%  2:26:34   8    75    57   240K   544K  1.09M  
31.0M
   347 lookupd      0.3%  1:52:28   2    35    73  3.05M   648K  3.14M  
33.6M
    89 configd      0.1% 53:05.16   3   126   151   304K   644K   832K  
29.2M
26774 servermgrd   0.1%  0:02.93   1    10    40   344K- 1.17M+ 1.86M  
28.2M
   170 coreservic   0.1%  0:09.04   1    40    93   152K   532K  2.64M  
28.5M
   223 DirectoryS   0.1% 19:42.47   8    84   135   880K+ 1.44M  4.60M+ 
37.1M+
   125 dynamic_pa   0.0%  0:26.79   1    12    17    16K   292K    28K  
17.7M
    87 kextd        0.0%  0:01.23   2    17    21     0K   292K    36K  
28.2M
   122 update       0.0% 14:27.71   1     9    15    16K   300K    44K  
17.6M
     1 init         0.0%  0:00.03   1    12    16    28K   320K    76K  
17.6M
     2 mach_init    0.0%  3:36.18   2    95    18    76K   320K   148K  
18.2M
    81 syslogd      0.0%  0:19.96   1    10    17    96K   320K   148K  
17.7M







^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 20:48  Jim C. Nasby <decibel@decibel.org>
  parent: Josh Berkus <josh@agliodbs.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Jim C. Nasby @ 2004-09-16 20:48 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: ricardo@sqlmagazine.com.br; pgsql-performance; br@postgresql.org

On Thu, Sep 16, 2004 at 10:50:33AM -0700, Josh Berkus wrote:
> The second step is to have lots of disks; 5 drives is a minimum for really 
> good performance.   3-drive RAID5, in particular, is a poor performer for 
> PostgreSQL, often resulting in I/O that is 40% or less as efficient as a 
> single disk due to extremely slow random seeks and little parallelization.
> 
> Once you have 6 drives or more, opinions are divided on whether RAID 10 or 
> RAID 5 is better.   I think it partly depends on your access pattern.

What about benefits from putting WAL and pg_temp on seperate drives?
Specifically, we have a box with 8 drives, 2 in a mirror with the OS and
WAL and pg_temp; the rest in a raid10 with the database on it. Do you
think it would have been better to make one big raid10? What if it was
raid5? And what if it was only 6 drives total?
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 21:07  Josh Berkus <josh@agliodbs.com>
  parent: Jim C. Nasby <decibel@decibel.org>
  0 siblings, 1 reply; 10+ messages in thread

From: Josh Berkus @ 2004-09-16 21:07 UTC (permalink / raw)
  To: Jim C. Nasby <decibel@decibel.org>; +Cc: ricardo@sqlmagazine.com.br; pgsql-performance; br@postgresql.org

Jim,

> What about benefits from putting WAL and pg_temp on seperate drives?
> Specifically, we have a box with 8 drives, 2 in a mirror with the OS and
> WAL and pg_temp; the rest in a raid10 with the database on it. Do you
> think it would have been better to make one big raid10? What if it was
> raid5? And what if it was only 6 drives total?

OSDL's finding was that even with a large RAID array, it still benefits you to 
have WAL on a seperate disk resource ... substantially, like 10% total 
performance.    However, your setup doesn't get the full possible benefit, 
since WAL is sharing the array with other resources.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 22:19  Jim C. Nasby <decibel@decibel.org>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Jim C. Nasby @ 2004-09-16 22:19 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: ricardo@sqlmagazine.com.br; pgsql-performance; br@postgresql.org

On Thu, Sep 16, 2004 at 02:07:37PM -0700, Josh Berkus wrote:
> Jim,
> 
> > What about benefits from putting WAL and pg_temp on seperate drives?
> > Specifically, we have a box with 8 drives, 2 in a mirror with the OS and
> > WAL and pg_temp; the rest in a raid10 with the database on it. Do you
> > think it would have been better to make one big raid10? What if it was
> > raid5? And what if it was only 6 drives total?
> 
> OSDL's finding was that even with a large RAID array, it still benefits you to 
> have WAL on a seperate disk resource ... substantially, like 10% total 
> performance.    However, your setup doesn't get the full possible benefit, 
> since WAL is sharing the array with other resources.
 
Yes, but if a 3 drive raid array is 40% slower than a single disk it
seems like the 10% benefit for having WAL on a seperate drive would
still be a losing proposition.

BTW, my experience with our setup is that the raid10 is almost always
the IO bottleneck, and not the mirror with everything else on it.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Article about PostgreSQL and RAID in Brazil
@ 2004-09-16 23:00  mudfoot@rawbw.com
  parent: Josh Berkus <josh@agliodbs.com>
  2 siblings, 0 replies; 10+ messages in thread

From: mudfoot@rawbw.com @ 2004-09-16 23:00 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Quoting Josh Berkus <josh@agliodbs.com>:

> The first and most important step for RAID performance with PostgreSQL is to
> 
> get a card with onboard battery back-up and enable the write cache for the 
> card.   You do not want to enable the write cache *without* battery back-up
> 

I'm curious about this -- how do you avoid losing data if a cache stick dies? 
Without redundancy, whatever hasn't been destaged to the physical media vanishes
 Dual-controller external arrays (HDS, EMC, LSI, etc.) tend to mirror (though
algorithms vary) the cache in addition to battery backup.  But do onboard arrays
tend to do this as well?

Mark



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2004-09-16 23:00 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-16 17:50 Article about PostgreSQL and RAID in Brazil Josh Berkus <josh@agliodbs.com>
2004-09-16 18:10 ` Daniel Ceregatti <daniel@omnis.com>
2004-09-16 18:57   ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-09-16 19:00   ` Josh Berkus <josh@agliodbs.com>
2004-09-16 19:01     ` Daniel Ceregatti <daniel@omnis.com>
2004-09-16 20:43     ` Qing Zhao <qzhao@supplyfx.com>
2004-09-16 20:48 ` Jim C. Nasby <decibel@decibel.org>
2004-09-16 21:07   ` Josh Berkus <josh@agliodbs.com>
2004-09-16 22:19     ` Jim C. Nasby <decibel@decibel.org>
2004-09-16 23:00 ` mudfoot@rawbw.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox