Thread: Article about PostgreSQL and RAID in Brazil

Article about PostgreSQL and RAID in Brazil

From
Josh Berkus
Date:
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

Re: Article about PostgreSQL and RAID in Brazil

From
Daniel Ceregatti
Date:
>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


Re: Article about PostgreSQL and RAID in Brazil

From
"Steinar H. Gunderson"
Date:
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/

Re: Article about PostgreSQL and RAID in Brazil

From
Josh Berkus
Date:
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

Re: Article about PostgreSQL and RAID in Brazil

From
Daniel Ceregatti
Date:
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

Re: Article about PostgreSQL and RAID in Brazil

From
Qing Zhao
Date:
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





Re: Article about PostgreSQL and RAID in Brazil

From
"Jim C. Nasby"
Date:
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?"

Re: Question about PG on OSX

From
Josh Berkus
Date:
Qing,

Please don't start a new question by replying to someone else's e-mail.   It
confuses people and makes it unlikely for you to get help.

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

That seems rather unlikely to me.    Unless you've *really* complex queries
and some unusual settings, you can't swamp the CPU through query planning.

On the other hand, your mention of REINDEX indicates that the table is being
updated very frequently.   If that's the case, then the solution is probably
for you to cut back on the number of indexes.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Article about PostgreSQL and RAID in Brazil

From
Josh Berkus
Date:
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

indexes make other queries slow!

From
Qing Zhao
Date:
Josh:

Sorry for the reply to the existing subject!

The newly added indexes have made all other queries much slower except
the uploading ops.
As a result, all the CPU's are running crazy but not much is getting
finished and our Application
Server waits for certain time and then times out. Customers thought the
system hung.

My guess is that all the queries that involves the columns  that are
being indexed need to
be rewritten to use the newly created indexes to avoid the performance
issues. The reason
is that REINDEX does not help either. Does it make sense?

Thanks!

Qing

On Sep 16, 2004, at 2:05 PM, Josh Berkus wrote:

> Qing,
>
> Please don't start a new question by replying to someone else's
> e-mail.   It
> confuses people and makes it unlikely for you to get help.
>
>> 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.
>
> That seems rather unlikely to me.    Unless you've *really* complex
> queries
> and some unusual settings, you can't swamp the CPU through query
> planning.
>
> On the other hand, your mention of REINDEX indicates that the table is
> being
> updated very frequently.   If that's the case, then the solution is
> probably
> for you to cut back on the number of indexes.
>
> --
> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>


Re: indexes make other queries slow!

From
Josh Berkus
Date:
Qing,

> My guess is that all the queries that involves the columns  that are
> being indexed need to
> be rewritten to use the newly created indexes to avoid the performance
> issues. The reason
> is that REINDEX does not help either. Does it make sense?

What's the rate of updates on the newly indexed tables?    If you have a lot
of updates, the work that the database does to keep the indexes current would
put a big load on your server.   This is far more likely to be the cause of
your issues.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: indexes make other queries slow!

From
"Jason Coene"
Date:
> My guess is that all the queries that involves the columns  that are
> being indexed need to
> be rewritten to use the newly created indexes to avoid the performance
> issues. The reason
> is that REINDEX does not help either. Does it make sense?
>

Qing,

Generally, adding new indexes blindly will hurt performance, not help it.

More indexes mean more work during INSERT/UPDATE.  That could easily be
hampering your performance if you have a high INSERT/UPDATE volume.

Run your queries through EXPLAIN ANALYZE to make sure they're using the
right indexes.  Take a look at the pg_stat_user_indexes table to see what
indexes are simply not being used.

Jason


Re: Article about PostgreSQL and RAID in Brazil

From
"Jim C. Nasby"
Date:
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?"

Re: Article about PostgreSQL and RAID in Brazil

From
mudfoot@rawbw.com
Date:
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

CPU maximized out!

From
Qing Zhao
Date:
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