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=UOY98FGebjhMHvjZyf2r3XL5OI0m3ESOvMS/IwUwDyI3Em5i9Hx3PErRzFbhtrexvY
	E9ZWc/HqKTxosd7WQuQ1GlnTbVpcohBVVq5awrsMdyK/rhFmBNLFTgu0GtfKVtDv7pb9
	QY9ZVrs9Zt0d5bVSu5CCFGEd+qp+D9FBQ05iI=
Message-ID: <dcc563d10810230938n4672582bo2ec8b989f41c5430@mail.gmail.com>
Date: Thu, 23 Oct 2008 10:38:34 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: Lionel <lionel@art-informatique.com>
Subject: Re: Hardware HD choice...
Cc: pgsql-performance@postgresql.org
In-Reply-To: <gdq44g$f4o$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <gdq44g$f4o$1@news.hub.org>

On Thu, Oct 23, 2008 at 9:10 AM, Lionel <lionel@art-informatique.com> wrote:
> Hello,
>
> I have to choose a dedicated server to host a big 8.3 database.
> The global size of the database (indexes included) will grow by 40 Go every
> year (40 millions of lines/year)
> Real data (indexes excluded) will be around 5-7 Go/year.
> I need to store 4 years of activity.
> Very few simultaneous users (~4).
> 100000 rows added every day via csv imports.
> The application will be a reporting application.
> Main statements: aggregation of 10000 to 10millions of line.
> Vast majority will hit 300000 lines (90% of the connected users), few will
> hit more than 10 millions (10% of the connected users, there may never be 2
> simultaneous users of this category).
> 20sec - 30sec for such a statement is acceptable.

In that case, throw memory at the problem first, then lots of hard
drives on a good RAID controller.

> It's quite easy to choose CPU (xeon quad core 2.66, maybe dual xeon), RAM
> (8-12Go) but I still hesitate for hard disks.

I've had better luck with opterons than Xeons, but they're both pretty
good nowadays.  I'd look at at least 16 Gigs ram, if you can afford it
get 32.

> these are options possible with the hosters I usually work with:
>
> Option 0)
> RAID1 750Go SATA2
>
> Option 1)
> RAID1 750Go SATA2 + 500Go USB disk
>
> Option 2)
> RAID1 SAS 15000rpm 147 Go hard disk + 500Go USB
>
> Option 2+)
> RAID1 SATA2 SSD intel X25-M 80Go + 500Go USB
>
> Option 3)
> RAID5 SATA2 5x750Go
>
> Option 4)
> RAID10 SAS 15000rpm 4x146 Go
>
> Option 5)
> RAID10 SATA2 4x250 Go
>
> Any other better option that I could ask for ?

Yes, more drives.  4 drives in a RAID10 is a good start.  If you could
get 8 or 12 in one that's even better.

>
> What would be the best choice in case of an external USB drive : using it
> for indexes or x_log ?

Not to use one.  Generally USB transfer speeds and external USB drives
aren't reliable or fast enough for serious server use.  I say this
with two very nice external USB drives sitting next to me.  They store
my videos, not customer data.

> And what is the best option to backup such a database ?

PITR

-- When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis