At 12:56 PM 5/6/2002, you 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.
>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,
>what kind of hardware platform will be needed to
>support the specifications enumerated above, and what
>kind of average query performance can we expect?
>
Bob,
You left out some critical information.
1a) How many concurrent users will there be reading from the busiest table,
and
1b) how many will be deleting records from the busiest table?
2) Also how do you determine which records will be deleted? Do you delete
an entire days worth of records at a time? If so, you might consider
creating a table for each day, like table_mon, table_tue, ... table_sun,
table_lastmon, ... table_lastsun so you have 14 tables. When you get a new
day, you do a "Delete * from table_mon" (if it is Monday) which in effect
drops the table and recreates it which is extremely fast. To query the
entire set of tables, use Merge tables. This only works with MyISAM tables.
If on the other hand you need to add and delete records concurrently, try
InnoDb table type. It stands up well to deletes and inserts.
One suggest I have is to use CHAR() field types rather than VarChar(). If
you delete fixed length records (CHAR) then MySQL can re-use the deleted
space. VarChar will have internal fragmentation especially when doing all
those deletes, so eventually you will need to reload the data. So stick
with CHAR if you can.
You will need at least dual processors and stuff it with as much RAM as it
can hold (2g or more).
Mike
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
