To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: Hardware HD choice...
Date: Thu, 23 Oct 2008 22:47:45 +0200
Lines: 76
Message-ID: <gdqnto$a8i$1@ger.gmane.org>
References: <gdq44g$f4o$1@news.hub.org>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig5FD3ECB7C53A2FC2626CC0DF"
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
In-Reply-To: <gdq44g$f4o$1@news.hub.org>

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig5FD3ECB7C53A2FC2626CC0DF
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Lionel wrote:
> Hello,
>=20
> 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 e=
very=20
> 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 w=
ill=20
> hit more than 10 millions (10% of the connected users, there may never =
be 2=20
> simultaneous users of this category).
> 20sec - 30sec for such a statement is acceptable.
>=20
> It's quite easy to choose CPU (xeon quad core 2.66, maybe dual xeon), R=
AM=20
> (8-12Go) but I still hesitate for hard disks.

If the number of users is low or your queries are complex, a faster CPU
with fewer cores will serve you better because PostgreSQL cannot split a
single query across multiple CPUs/cores. It will also speed up your CSV
imports (be sure to do them as a single transaction or with COPY). If
you can bear the heat (and the electricity bill) you can get 3.4 GHz
Xeons. Be sure to use a 64-bit OS and lots of memory (16 GB+).

> Option 5)
> RAID10 SATA2 4x250 Go

Good enough.

> Any other better option that I could ask for ?

Yes, 8x250 :) You need as much drives as possible - not for capacity or
reliability but for speed. Use RAID 5 or RAID 6 only if the database
isn't going to be updated often (for example, if you add records to the
database only several times a day, it's ok).

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

Skip any USB-connected drives for production environments. It's not
worth it. If new data isn't going to be added to the database
continuously, you don't need a separate x_log. Better use the drive as a
(hot, if possible) spare for RAID in case one of the RAID drives
malfunctions.


--------------enig5FD3ECB7C53A2FC2626CC0DF
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkkA4vEACgkQldnAQVacBciEkQCghzWI27Q9AHSrlHfYyIo3UBeK
RJ4AoK3A4o7Bqe2XTWiAhwtKg9TQlSqe
=YDno
-----END PGP SIGNATURE-----

--------------enig5FD3ECB7C53A2FC2626CC0DF--