DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=XDDkX3jxnRv5lzpJmZIeDHxQB5zr4BK9QcWYtHgFhI1S4X92lIS1LLl6wIFLDKSjgL
	p3RZ1g8tKdVG+TXU6pBx+jl49wmBMbgffkyPEFJhsEIAr/J8SKbHmP2nAvbmVHaJSQ61
	FRDF+p6YSekGsvujveVdZvT1OeIpGwq+IpYOE=
Message-ID: <dcc563d10808110900o541a9e0eldeb510a8a2632161@mail.gmail.com>
Date: Mon, 11 Aug 2008 10:00:26 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: dforums <dforums@vieonet.com>
Subject: Re: Distant mirroring
Cc: pgsql-performance@postgresql.org
In-Reply-To: <48A04C17.3030005@vieonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <489DD403.6010101@vieonet.com>
	<dcc563d10808091229n78d88637tb9b26fd204f77c9d@mail.gmail.com>
	<48A04C17.3030005@vieonet.com>

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.