Re: Distant mirroring
От | dforums |
---|---|
Тема | Re: Distant mirroring |
Дата | |
Msg-id | 48A1A6FE.9050706@vieonet.com обсуждение исходный текст |
Ответ на | Re: Distant mirroring ("Scott Marlowe" <scott.marlowe@gmail.com>) |
Список | pgsql-performance |
Tx to all. I reach the same reflection on partitionning the data to those tables. And postgresql is giving very good tools for that with the rules features. I got the SAS server for immediate fix. But I'm looking for buying a machine that will handle my needs for more long time. Regarding partitionning it seems that I could just use a daily tables for daily treatment and keeping a another one for mass reading. I even things to partition per years or half years. One question is on table that have FK constraint, I don't know how to maintain it ? Could I use rules for it too ? Tx for helps Regards David Scott Marlowe a écrit : > On Mon, Aug 11, 2008 at 8:26 AM, dforums <dforums@vieonet.com> wrote: >> Houlala >> >> I got headache !!! >> >> So please help...........;; >> >> "Assuming they all happen from 9 to 5 and during business days only, >> that's about 86 transactions per second. Well within the realm of a >> single mirror set to keep up if you don't make your db work real fat." >> >> OK i like, But my reality is that to make an insert of a table that have 27 >> millions of entrance it took 200 ms. >> so it took between 2 minutes and 10 minutes to treat 3000 records and >> dispatch/agregate in other tables. And I have for now 20000 records every 3 >> minutes. > > Can you partition your data on some logical barrier like a timestamp > or something? that would probably help a lot. also, are you doing > all 3000 records in one transaction or individual transactions? If > one at a time, can you batch them together for better performance or > are you stuck doing them one at a time? > >> At the moment I have a >> >> I have a Linux 2.6.24.2-xxxx-std-ipv4-64 #3 SMP Tue Feb 12 12:27:47 CET 2008 >> x86_64 Intel(R) Xeon(R) CPU X5355 @ 2.66GHz GenuineIntel GNU/Linux >> with 8Gb of memory. Using sata II disk in RAID 1 (I known that is bad, but >> it would change has quickly I can). > > Yeah, you're gonna be I/O bound as long as you've only got a single > mirror set. A machine with 8 or 12 SAS 15K drives should make it much > more likely you can handle the load. > >> I got 1-2 GO per week > > Definitely let's look at partitioning then if we can do it. > >> I can change to 2 kinds of server, using 8.3.3 postgresql server, and even >> taking more sever if need. But it is the biggest computer that I can rent >> for now. >> >> Intel 2x Xeon X5355 >> 2x 4x 2.66 GHz >> L2: 8Mo, FSB: 1333MHz >> Double Quadruple Coeur >> 64 bits >> 12 Go FBDIMM DDR2 >> 2x 147 Go >> SAS 15 000 tr/min >> RAID 1 HARD > > All that memory and CPU power will be wasted on a db with just two > drives. Do you at least have a decent RAID controller in that setup? > >> I can add 500 Go under sataII >> >> OR >> >> Intel 2x Xeon X5355 >> 2x 4x 2.66 GHz >> L2: 8Mo, FSB: 1333MHz >> Double Quadruple Coeur >> 64 bits >> 12 Go FBDIMM DDR2 >> 5x 750 Go (2.8 To **) >> SATA2 RAID HARD 5 >> >> I can add 500 Go under sataII > > RAID5 is generally a poor choice for a write limited DB. I'd guess > that the dual SAS drives above would work better than the 5 SATA > drives in RAID 5 here. > >> After several tunings, reading, ect... >> >> The low speed seems to be definetly linked to the SATA II in RAID 1. > > Going to 15k SAS RAID 1 will just about double your write rate > (assuming it's a commits/second issue and it likely is). going to a 4 > disk SAS RAID10 will double that, and so on. > >> So I need a solution to be able to 1st supporting more transaction, secondly >> I need to secure the data, and being able to load balancing the charge. > > Look at slony for read only slaves and the master db as write only. > If you can handle the slight delay in updates from master to slave. > Otherwise you'll need sync replication, and that is generally not as > fast. > > Take a look at something like this server: > > http://www.aberdeeninc.com/abcatg/Stirling-229.htm > > With 8 15k SAS 146G drives it runs around $5k or so. Right now all > the servers your hosting provider is likely to provide you with are > gonna be big on CPU and memory and light on I/O, and that's the > opposite of what you need for databases. > -- <http://www.1st-affiliation.fr> *David Bigand *Président Directeur Générale* *51 chemin des moulins 73000 CHAMBERY - FRANCE Web : htttp://www.1st-affiliation.fr Email : david@1st-affiliation.com Tel. : +33 479 696 685 Mob. : +33 666 583 836 Skype : firstaffiliation_support
В списке pgsql-performance по дате отправления: