Re: High-Speed/Volume Database

From: Anton Versteeg <av_at_nospam.for.me>
Date: Sat, 04 May 2002 11:51:10 +0200
Message-ID: <3CD3AF0E.789EF440_at_nospam.for.me>


I agree with almost everything Bob is saying except of course his choice for Oracle. I would consider using DB2 UDB EEE (parallel database) where you can spread the data across different machines (nodes). This is a very scalable solution.
As for the platform: Win2K, Unix, or Linux.

Bob Hairgrove wrote:

> On 3 May 2002 11:32:44 -0700, gatesucks_at_hotmail.com (Bob Smith) wrote:
>
> >We are designing an application that needs to use
> >a relational database to hold quite a large amount
> >of data.
> >
> >In particular there is one table that has about 33
> >fields, 18 indexes, and 120 bytes per record.
>
> How do you know how many "bytes per record" there will be? What
> difference does it make? This will vary a great deal and is a rather
> meaningless quantity compared to other things such as block size.
> Besides, those indexes will take up quite a bit of space on their own
> ... sometimes almost as much as the data itself.
>
> >Additionally, we are going to need to add about 2
> >million records per day to the table, delete about 2
> >million records per day from the table, hold 2 weeks
> >worth of data within the database (approx. 30 million
> >records), and sustain an average add rate of about 23
> >records per second while, at the same time, sustaining
> >an average delete rate of 23 records per second.
> >
> >My questions are, what database software should we use,
>
> Depends on how much your data integrity is worth to you as well as how
> much (or little) downtime you can afford. You look at the licensing
> and do your own arithmetic. ;-)
>
> >what kind of hardware platform will be needed to
> >support the specifications enumerated above,
>
> Same as above.
>
> >and what kind of average query performance can we expect?
>
> Depends on whether you're talking about reads or writes. You're well
> advised to leave index creation alone until you're actually up and
> running because they can really get in the way of your updates,
> inserts and deletes. Only use an index if query performance demands
> one (I'm not talking about constraints here). With only 33 fields, 18
> indexes sounds like a few too many to me, but it really depends on
> your data design. Each foreign key or unique constraint as well as
> primary keys will usually be implemented through one or more indexes,
> so it's always a trade-off as to how many are necessary. In general,
> the fewer the better if you can get away with it performance-wise and
> relational integrity-wise.
>
> You have a lot of data to manage and a lot of activity on the
> database, so I would look at Oracle if I were you. And you'll need one
> or more DBA's that know what they are doing. As to OS, some flavor of
> Unix (better Sun than Linux because of the better support ... you pays
> for what you gets ... and I would stay away from Windows for something
> this big) is probably the way to go if you choose Oracle.
>
> Seriously, though, you should get some real professional consulting
> instead of posting to newsgroups ... do you really want to take the
> risk of having some college student with no experience telling you how
> to handle this?
>
> Bob Hairgrove
> rhairgroveNoSpam_at_Pleasebigfoot.com

--
Anton Versteeg
DB2 Specialist
IBM Netherlands
Received on Sat May 04 2002 - 11:51:10 CEST

Original text of this message