Re: High-Speed/Volume Database

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 Received on Sat May 04 2002 - 01:36:25 CEST