Message-ID: <48A04C17.3030005@vieonet.com>
Date: Mon, 11 Aug 2008 16:26:31 +0200
From: dforums <dforums@vieonet.com>
User-Agent: Thunderbird 2.0.0.14 (X11/20080501)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Distant mirroring
References: <489DD403.6010101@vieonet.com>
	<dcc563d10808091229n78d88637tb9b26fd204f77c9d@mail.gmail.com>
In-Reply-To: <dcc563d10808091229n78d88637tb9b26fd204f77c9d@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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.

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

I got 1-2 GO per week

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

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


After several tunings, reading, ect...

The low speed seems to be definetly linked to the SATA II in RAID 1.

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.

Please, give me any advise or suggestion that can help me.

regards to all

David


Scott Marlowe a écrit :
> On Sat, Aug 9, 2008 at 11:29 AM, dforum <dforums@vieonet.com> wrote:
>> Hello,
>>
>> I'm trying to install a solution to permit me to :
>> - Secure the datas, without RAID
> 
> Nothing beats a simple mirror set for simplicity while protecting the
> data, and for a pretty cheap cost.  How much is your data worth?
> 
>> - Giving ability to increase the potentiality of the database towards the
>> needs.
>>
>> I have read about slony, DRBD, pgpool....
>>
>> I don't find the good system to do what I want.
>>
>> I manage for now 50 millions of request per month.
> 
> 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.
> 
>> I will reach 100 millions in the end of the year I suppose.
> 
> That takes us to 172 transactions per second.
> 
>> There is 2 difficulties :
>> 1 - is the storage : to get faster access,it is recommend to use SAS 15 000
>> tps. But the disk I can get are 149 GO of space. As the database is growing
>> par 1,7 Go per week at the moment, it will reach is maximum in 3 month. I
>> can add 3 disk at least so It can go to 9 month. What to do after, and
>> especially what to do today to prevent it?
> 
> No, don't piecemeal just enough to outrun the disk space boogieman
> each month.  Buy enough to last you at least 1 year in the future.
> More if you can afford it.
> 
>> 2 - The machine will treat more and more simultaneous entrance, so I need to
>> loadbalance those inserts/updates on several machine and to replicate the
>> datas between them. It's not a real problem if the data are asynchrony.
> 
> Then PostgreSQL might not be your best choice.    But I think you're
> wrong.  You can easily handle the load you're talking about on a
> mid-sized box for about $5000 to $10000.
> 
> You can use 7200 rpm SATA drives, probably 8 to 12 or so, in a RAID-10
> with a battery backed cache and hit 172 transactions per second.
> 
> Given the 1+ G a week storage requirement, you should definitely look
> at using inheritance  to do partitioning.  Then use slony or something
> to replicate the data into the back office for other things.  There's
> always other things most the time that are read only.
> 

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