Обсуждение: 3 x PostgreSQL in cluster/redunant
Hello *, I have three Sun Server where I have reserved on each Server a Raid-5 of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running with a database of 150 GByte. Now I like to make the three Sun Servers redunant but I do not find any usefull HOWTO's or manuals how to setup PostgreSQL to work redunant. Can anyone point me to the right documentation please? How can I redirect requests to one of the other PostgreSQL servers, if one has to much load ? My servers have only 32 CPU's of 650 MHz and 64 GByte of memory running Debian GNU/Linux 3.1 (sparc), and they must support around 17.000 $USER currently. The Internet connection is curently for Testing an E1 (with Backup) but I am looking for FiberOptic Provider E1/STM1. Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)
On Mon, 2005-11-14 at 12:36, Michelle Konzack wrote: > Hello *, > > I have three Sun Server where I have reserved on each Server a Raid-5 > of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running > with a database of 150 GByte. > > Now I like to make the three Sun Servers redunant but I do not find > any usefull HOWTO's or manuals how to setup PostgreSQL to work redunant. > > Can anyone point me to the right documentation please? > > How can I redirect requests to one of the other PostgreSQL servers, if > one has to much load ? > > My servers have only 32 CPU's of 650 MHz and 64 GByte of memory running > Debian GNU/Linux 3.1 (sparc), and they must support around 17.000 $USER > currently. > > The Internet connection is curently for Testing an E1 (with Backup) but > I am looking for FiberOptic Provider E1/STM1. You want to look at a couple of different options. slony http://gborg.postgresql.org/project/slony1/projdisplay.php pgpool http://pgpool.projects.postgresql.org/ mammoth replicator http://www.commandprompt.com/products/mammothreplicator pgcluster http://pgfoundry.org/projects/pgcluster/
On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: > Hello *, > > I have three Sun Server where I have reserved on each Server a Raid-5 > of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running > with a database of 150 GByte. Keep in mind that databases and RAID5 generally don't mix very well. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: >>I have three Sun Server where I have reserved on each Server a Raid-5 > Keep in mind that databases and RAID5 generally don't mix very well. oh, how come? What is the ideal setup of a database server when it comes to storage? regards -- Johnny Ljunggren, Vestlia 6, 3080 HOLMESTRAND, 918 50 411
On Tue, Nov 15, 2005 at 12:09:40AM +0100, Johnny Ljunggren wrote: > Jim C. Nasby wrote: > >On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: > >>I have three Sun Server where I have reserved on each Server a Raid-5 > > >Keep in mind that databases and RAID5 generally don't mix very well. > > oh, how come? > What is the ideal setup of a database server when it comes to storage? RAID5 has horrible write performance, especialy for random writes (which is what databases tend to do). If you're running essentially a read-only database then raid5 is OK. Otherwise you'll be much better off with RAID10. You also want to put the WAL on a seperate set of drives from the main database (though again that doesn't buy much if your database is read-only). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: >>>Keep in mind that databases and RAID5 generally don't mix very well. >> >>What is the ideal setup of a database server when it comes to storage? > > You also want to put the WAL on a seperate set of drives from > the main database (though again that doesn't buy much if your database > is read-only). Thanks for the information. Sorry for my ignorance but what is WAL? Neither Google nor Wikipedia could come up with an explanation. -- Johnny Ljunggren, Vestlia 6, 3080 HOLMESTRAND, 918 50 411
On Nov 15, 2005, at 19:03 , Johnny Ljunggren wrote: > Sorry for my ignorance but what is WAL? Neither Google nor > Wikipedia could come up with an explanation. http://www.postgresql.org/docs/current/static/wal.html http://www.google.com/search?hl=en&q=postgresql+wal&btnG=Google+Search Hopefully that'll get you started. ;) Michael Glaesemann grzm myrealbox com
Am 2005-11-14 16:54:41, schrieb Jim C. Nasby: > On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: > > Hello *, > > > > I have three Sun Server where I have reserved on each Server a Raid-5 > > of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running > > with a database of 150 GByte. > > Keep in mind that databases and RAID5 generally don't mix very well. Can you explain me why? Unfortunatly the Controllers in the three SUN-Servers do not support 300 GByte SCSI-Drives, so I have to continue with the Raid-5 of 16x 76 GByte. Oh yes, I have started the PostgreSQL in 03/2000 with 7x 18 GByte and never had problems with Raid-5. Suggestions? Please note, that in the coming year, my PostgreSQL will increase to at least 900 GByte (text only) and the corresponding FileServer with original Documents and additional Infos from 1,6 TByte to 28 TByte. OK, I was thinking of a Server-Farm of small AMD64FX or Opteron 140 with two 300 GByte SCSI-Drives in Hardware Raid-1, and then installing 6-8 Systems running in Cluster, but I have no experience in such systems... And the price will explode... (Hardware recommandations?) Are there any acceptable Tyan Opteron 140 Mainboards with SCSI Raid-5 onboard? - It should work with Debian GNU/Linux Sarge(amd64). Oh yes, I have seeen that 300 GByte Drives are around 1200 Euro/drive. So a "simpel" server will eat up at least 4200 Euro. Any suggestions are welcome. Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)
On Tue, Nov 15, 2005 at 07:38:06PM +0100, Michelle Konzack wrote: > Am 2005-11-14 16:54:41, schrieb Jim C. Nasby: > > On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: > > > Hello *, > > > > > > I have three Sun Server where I have reserved on each Server a Raid-5 > > > of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running > > > with a database of 150 GByte. > > > > Keep in mind that databases and RAID5 generally don't mix very well. > > Can you explain me why? You'll be better off googling for "raid5 performance", but in a nutshell write performance on raid5 is horrible. If you database is read-only then that shouldn't be an issue, but as soon as you start writing much at all you'll probably be unhappy with the performance. As for hardware, what is your application? If your choice is between SCSI raid5 and SATA raid10, you're probably better off with the SATA raid10. PostgreSQL doesn't have any kind of native clustering (depending on your definition of clustering), but you might be able to roll your own depending on your application. I didn't know they were even making 300GB SCSI drives... > Unfortunatly the Controllers in the three SUN-Servers do not support > 300 GByte SCSI-Drives, so I have to continue with the Raid-5 of 16x > 76 GByte. > > Oh yes, I have started the PostgreSQL in 03/2000 with 7x 18 GByte and > never had problems with Raid-5. > > Suggestions? > > Please note, that in the coming year, my PostgreSQL will increase to > at least 900 GByte (text only) and the corresponding FileServer with > original Documents and additional Infos from 1,6 TByte to 28 TByte. > > OK, I was thinking of a Server-Farm of small AMD64FX or Opteron 140 > with two 300 GByte SCSI-Drives in Hardware Raid-1, and then installing > 6-8 Systems running in Cluster, but I have no experience in such > systems... > > And the price will explode... (Hardware recommandations?) > > Are there any acceptable Tyan Opteron 140 Mainboards with SCSI Raid-5 > onboard? - It should work with Debian GNU/Linux Sarge(amd64). > > Oh yes, I have seeen that 300 GByte Drives are around 1200 Euro/drive. > So a "simpel" server will eat up at least 4200 Euro. > > Any suggestions are welcome. > > > Greetings > Michelle > > -- > Linux-User #280138 with the Linux Counter, http://counter.li.org/ > Michelle Konzack Apt. 917 ICQ #328449886 > 50, rue de Soultz MSM LinuxMichi > 0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com) > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Michelle Konzack wrote: > Am 2005-11-14 16:54:41, schrieb Jim C. Nasby: > >> On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: >> >>> Hello *, >>> >>> I have three Sun Server where I have reserved on each Server a Raid-5 >>> of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running >>> with a database of 150 GByte. >>> >> Keep in mind that databases and RAID5 generally don't mix very well. >> > > Can you explain me why? > RAID 5 is very expensive for writes. > Unfortunatly the Controllers in the three SUN-Servers do not support > 300 GByte SCSI-Drives, so I have to continue with the Raid-5 of 16x > 76 GByte. > Could you do RAID 10? Sincerely, Joshua D. Drake
On Tuesday 2005-11-15 13:06, Joshua D. Drake wrote: > Michelle Konzack wrote: > > Am 2005-11-14 16:54:41, schrieb Jim C. Nasby: > >> On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: > >>> Hello *, > >>> > >>> I have three Sun Server where I have reserved on each Server a Raid-5 > >>> of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running > >>> with a database of 150 GByte. > >> > >> Keep in mind that databases and RAID5 generally don't mix very well. > > > > Can you explain me why? > > RAID 5 is very expensive for writes. > > > Unfortunatly the Controllers in the three SUN-Servers do not support > > 300 GByte SCSI-Drives, so I have to continue with the Raid-5 of 16x > > 76 GByte. > > Could you do RAID 10? > > Sincerely, > > Joshua D. Drake I've seen books on tuning recommend RAID-5 into the low terrabyte range for read-dominated databases (notably small data warehouse applications). For very large multi-terrabye applications the suggestion is that RAID-50 along with streaming to and from stochastically accessed distributed storage can partially hide the expense of writing to storage while bringing the money cost of storage down considerably.
Am 2005-11-15 12:06:20, schrieb Joshua D. Drake: > >Unfortunatly the Controllers in the three SUN-Servers do not support > >300 GByte SCSI-Drives, so I have to continue with the Raid-5 of 16x > >76 GByte. > > > Could you do RAID 10? With 14 Drives ? Do not know, whether the controller support it, because they are 16 drives on each box and NOT 15. It is connected to the normal SCSI controller but suppoert 16 Drives because Hot-Spare. Raid-10 mean, that I will lost the half of my Diskspace which is realy inacceptable. I have gotten those 3 Servers from an Enterprise which has changed the System entirely and I have currently not the money to buy such Server new... Even if I want to buy a couple of 1U Raid-1 Servers with two 300 GByte SCSI drives each, it will up all my resources. ~4500 Euro/Server > Sincerely, > > Joshua D. Drake Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)
Am 2005-11-15 13:45:24, schrieb Jim C. Nasby: > You'll be better off googling for "raid5 performance", but in a nutshell > write performance on raid5 is horrible. If you database is read-only > then that shouldn't be an issue, but as soon as you start writing much > at all you'll probably be unhappy with the performance. OK, I have per day only around 50-120 MByte new data which must be replicated between the 3 Servers. But on the other hand I have enormous READ requests, which mean, on my old backbone I had around 5-12 GByte per day in the middle. But now, the database is growing and... > As for hardware, what is your application? If your choice is between > SCSI raid5 and SATA raid10, you're probably better off with the SATA > raid10. It is a 7 years old SUN Server with 6 Raid-5 of 1 TByte (each 16x 76GB SCSI) Forget it with SATA, because they arte no 300 GByte drives with 15.000 RPM availlable and SATA with PATA Hardware is the hell. If you need ONLY storage for STATIC documents (I have several TBytes in addition to my PostgreSQL) then SATA-Drives with 400 or 500 GByte are fine with 3Ware 3w95xxS-12 Controllers. I have one running, which has curently 6x 400 GByte SATA. In the next time I will install the missing 6x 500 GByte Drives. > PostgreSQL doesn't have any kind of native clustering (depending on your > definition of clustering), but you might be able to roll your own > depending on your application. > > I didn't know they were even making 300GB SCSI drives... They are relativ new... Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)