Re: High-Speed/Volume Database

Bob Smith <gatesucks_at_hotmail.com> 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.

Are you *sure* you've designed this table, or did it just congeal?

>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.

These rates aren't very scary, a small to mid-size machine should handle them without much effort.

Partitions could really help, e.g. if it's time dependent data, then you could delete an entire days worth almost effortlessly by just dropping that day's partition.

If at all possible, _do_not_ commit every row, have a front-end app bundle up the inserts into an array insert and commit in batches (e.g. 100). You can increase your performance on the same hardware by an order of magnitude by doing this.

>My questions are, what database software should we use,
>what kind of hardware platform will be needed to
>support the specifications enumerated above, and what
>kind of average query performance can we expect?

Are you really coming at this from a totally clean slate? The answer to "what hardware" is usually more of what you're used to using.

You're posting to an Oracle newsgroup, what answer do you expect for "what software" ?

As for "average query performance", how should we know? Your queries might be doing hideous joins across three dozen tables, or just a straight PK lookup on a single table. Benchmarking your own app is the only way to tell. Benchmark on a test system and extrapolate to your production, just like everybody else.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/