Re: Setup for large database

On Thu, Apr 20, 2006 at 06:02:17PM -0700, mlartz@gmail.com wrote:
> 1) How anal should I be about my hardware setup?  I have about 15 300GB
> 10K RPM SCSI drives, 4 of which I can directly attach to the server and
> the rest one the FC array.  Should I just put the OS and transaction
> logs on the direct attached storage and and then RAID10 the rest of
> them and be done, or would I significantly benefit from separating out
> the indexes and partitioning across tablespaces across drives?  Would
> RAID5 across 10+ drives yield acceptable performance numbers?

My experience is more OLTP than OLAP, but for a warehouse envirenment
RAID5 can be a good solution since there's typically not a lot of
updating going on.

I've yet to see much gain from moving pg_xlog onto it's own seperate set
of drives; there's usually not enough traffic from the OS to justify it.
But it is possible that you could end up generating enough WAL traffic
that pg_xlog would become a performance limiter on only 2 drives, though
I suspect you'd have to have over 20-30 drives for data before that
happened.

> 3) I've currently installed RHEL4 AS for my OS, which I am very
> comfortable with.  I was going to go with EXT3 on everything (noatime)
> ... sound good?

There's a data=writeback option for ext3 that can make a big performance
difference.

> 2) Assuming that my data is roughly evenly distributed among IP
> addresses, I figured that a naive partitioning based on the first octet

See my other reply...

> 3) I guess I don't quite understand Bizgres.  At the moment, it seems
> to be just a development beta of Postgres ... is this true?  I realize
> that the focus is on BI/ETL stuff, but the current improvements seem to
> benefit Postgres as a whole.  Is there currently or can you imagine a
> case where a feature in Bizgres won't get integrated into Postgres?
> How significant is the fork between Bizgres and Postgres?  I've also
> considered taking a look at Bizgres MPP.  I know that its the wrong
> forum, but any comments?

You'd probably be better off asking on a bizgres list...

> 4) Not to start any sort of flame war, but my company has an Oracle
> license and there are a bunch of people wanting me to go that way.
> I've been doing just fine with Postgres at the moment and am quite
> comfortable with it, but am being pressured to go with our Oracle
> license.  Cost (and prejudices) aside, do you think it would be wise to
> go with Oracle to begin with, considering the size of the database that
> I'm planning?

There's certainly people out there running multi-terrabyte databases on
PostgreSQL. Unless there's a sound technical reason to switch, I'd stick
with PostgreSQL, especially because migrating to Oracle from PostgreSQL
is fairly easy.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461