Thread: Article about PostgreSQL and RAID in Brazil
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
>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
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/
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
Josh Berkus wrote:
Yes, it came up finally. Fortunately I'm Brazilian. :)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.
-- Daniel Ceregatti - Programmer Omnis Network, LLC Too clever is dumb. -- Ogden Nash
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
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?"
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
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
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 >
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
> 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
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?"
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
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