List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:August 22 2006 9:11pm
Subject:Re: limitations of mySQL i.e. number of rows
View as plain text  
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" <Andy.Ford@stripped>
To: <mysql@stripped>
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.



Thread
limitations of mySQL i.e. number of rowsAndy Ford22 Aug
  • Re: limitations of mySQL i.e. number of rowsDan Buettner22 Aug
    • Re: limitations of mySQL i.e. number of rowsDan Buettner22 Aug
  • RE: limitations of mySQL i.e. number of rowsAndy Ford22 Aug
  • Re: limitations of mySQL i.e. number of rowsBrent Baisley22 Aug