Re: Distant mirroring - Mailing list pgsql-performance

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