Message-ID: <48A1A6FE.9050706@vieonet.com>
Date: Tue, 12 Aug 2008 17:06:38 +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>
CC: pgsql-performance@postgresql.org
Subject: Re: Distant mirroring
References: <489DD403.6010101@vieonet.com>	
	<dcc563d10808091229n78d88637tb9b26fd204f77c9d@mail.gmail.com>	
	<48A04C17.3030005@vieonet.com>
	<dcc563d10808110900o541a9e0eldeb510a8a2632161@mail.gmail.com>
In-Reply-To: <dcc563d10808110900o541a9e0eldeb510a8a2632161@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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