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
performance.
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), Debian Linux.
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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, August 22, 2006 7:56 AM
Subject: limitations of mySQL i.e. number of rows
Hi everyone
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 possible.
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.
Thanks
Regards
Andy
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
