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=iD3yTCmvmSKR7fsvcCOwX/ZJZD1HW4TEzECqnlqDsXqllEhOSB2K9B22i3jTRclaiW
	t5ro9K0X1X7nDZbmcytHkCZXHhzZysrLu98E72mbYiepnQKspodQ66yyRHthtIv7+mA1
	ZhVEUq9hN7PX2xnkm1nZkpReI7sNSpntUQ6Xk=
Message-ID: <dcc563d10806170723g6327ff8ap9fa6f4fe65c84d7a@mail.gmail.com>
Date: Tue, 17 Jun 2008 08:23:22 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: Lionel <lionel@art-informatique.com>
Subject: Re: Which hardware ?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <g38epm$2pl4$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <g38epm$2pl4$1@news.hub.org>

On Tue, Jun 17, 2008 at 7:38 AM, Lionel <lionel@art-informatique.com> wrote=
:
> Hi,
>
> I need to install a 8.3 database and was wondering which hardware would b=
e
> sufficient to have good performances (less than 30s for=B2 slowest select=
).
>
> Database size: 25 Go /year, 5 years of history
> One main table containing 40 million lines per year.
> Batch inserts of 100000 lines. Very very few deletes, few updates.
>
> 30 other tables, 4 levels of hierarchy, containing from 10 lines up to 20=
000
> lines.
> 5 of them have forein keys on the main table.
>
> I will use table partitionning on the year column.
>
> Statements will mainly do sums on the main table, grouped by whatever col=
umn
> of the database (3-5 joined tables, or join on join), with some criterion=
s
> that may vary, lots of "joined varchar in ('a','b',...,'z')".
> It's almost impossible to predict what users will do via the webapplicati=
on
> that queries this database: almost all select, join, group by, where...
> possibilities are available.
>
> Up to 4 simultaneous users.
>
> I'm planning to host it on a quad xeon 2.66Ghz with 8Go of DDR2, and a du=
al
> (RAID1) SATA2 750Go HD.
> Perharps with another HD for indexes.
>
> Do you think it will be enough ?
> Is another RAID for better performances a minimum requirement ?
> Will a secondary HD for indexes help ?

More drives, all in the same RAID-10 setup.  For reporting like this
writing speed often isn't that critical, so you are often better off
with software RAID-10 than using a mediocre hardware RAID controller
(most adapatecs, low end LSI, etc...)

You'd be surprised what going from a 2 disk RAID1 to a 4 disk RAID10
can do in these circumstances.  Going up to 6, 8, 10 or more disks
really makes a difference.

> Which OS would you use ? (knowing that there will be a JDK 1.6 installed
> too)

I'd use RHEL5 because it's what I'm familiar with.  Any stable flavor
of linux or FreeBSD7 are good performance choices if you know how to
drive them.

> With 5 millions of lines, the same application runs quite fast on windows
> 2000 on a single P4 2.8 GHz (very few statements last more than 10s, most=
ly
> when concurrent statements are made). Each statement consumes 100% of the
> CPU.

That statement about concurrent statements REALLY sells me on the idea
of a many disk RAID10 here.  I'd take that over quad cores for what
you're doing any day.  Not that I'd turn down quad cores here either.
:)