If you are going to be storing only 2 months of data at a time, I would suggest using
merge tables. Use one table per month. This
makes it very easy to "delete" data, simple redeclare what the union is. Then you can also
retain previous months without affecting
One of the databases I manage adds up to 2.5 million records per day. Like you, I only
need about 3 months worth (currently 165
million records). Deleting millions of records at a time would take quite a while and slow
everything down. Redeclaring a merge
table is instantaneous.
I don't need to plot output, but I do need to check for duplicate informatio within a 24
hour period when adding the batch of daily
records. Joining 2.5 million records with 165 million and checking for duplicates within a
range I discovery MySQL just can't do, at
least not in a single query. It would just never finish. By processing in small batches,
the joining takes about 30 minutes, but
that is essentially hitting the entire database. This is on a dual Xeon (not dual core),
So if you have performance issues, try processing in increments.
Or, the free versions of DB2, Oracle, SQL Server may suite your needs.
I have seen three legged dogs run pretty fast.
----- Original Message -----
From: "Andy Ford" <Andy.Ford@stripped>
Sent: Tuesday, August 22, 2006 7:56 AM
Subject: limitations of mySQL i.e. number of rows
I have a requirement to store in the region of 3 million entries per month (30 ish days)
of data in a mySQL database totalling a
maximum of 2 months (6 Million entries).
The database will be 'probably' reside on a Sun V240. I have a similar size database
running on an old Sun E450 and it runs like a
dog (with three legs).
I'll need to access the data and plot the output almost real time, or as close to it as
I may have to go down the route of buying another database like DB2, Informix or Oracle
but I have no idea of the costs involved or
whether I will get improved performance form these compared to mySQL.
Anyone have experience of large datasets within mySQL.
This e-mail is private and may be confidential and is for the intended recipient only. If
misdirected, please notify us by
telephone and confirm that it has been deleted from your system and any copies destroyed.
If you are not the intended recipient you
are strictly prohibited from using, printing, copying, distributing or disseminating this
e-mail or any information contained in it.
We use reasonable endeavours to virus scan all e-mails leaving the Company but no warranty
is given that this e-mail and any
attachments are virus free. You should undertake your own virus checking. The right to
monitor e-mail communications through our
network is reserved by us.