From: Johan De Meersman Date: November 5 2009 10:44am Subject: Re: Fw: 50M records each year.. how to handle List-Archive: http://lists.mysql.com/mysql/219258 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 I did some quick math, and it comes down to 1.5 records per second on average. Even with peak loads, this is doable without the annoying manual partitioning, I think. Just make sure your storage is optimal, think Raid10. If possible, use INSERT DELAYED. If you do updates and deletes, too, use InnoDB, if just select and insert, use MyISAM - or if you can do with really slow selects, use the Archive engine. It depends a lot on your planned use, really, but the above should help. On 11/5/09, || Sudhir Nimavat || wrote: > Ok, > > Now the problem is.. currently we are using mysql 4.1 and it is not possible > to upgrade mysql at this time.. as far as I know mysql 4.1 does not support > partitioning.. So the only solution would be to use manual partitioning and > handle it through code.. > > Can any one tell me.. how this requirement may be satisfied with my sql > 4.1.. > > Any ideas, inputs ? > > Thanks > SN > > > > > > > > Personally I'm always ready to learn, although I do not always like being > taught > > > > > ----- Forwarded Message ---- > From: Rob > To: || Sudhir Nimavat || > Sent: Mon, 2 November, 2009 3:18:05 PM > Subject: Re: 50M records each year.. how to handle > > Hi , > > Yes pretty much, depending on the type of access to the records of > course. But 50 million sounds like historical semi static data. It will > function pretty much like a sort of raid. Having more storage devices to > seek through all the data. > > (see your other mail), partitioning support is fine. See the example there, > > CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) > ENGINE=INNODB > PARTITION BY HASH( MONTH(tr_date) ) > PARTITIONS 6; > > > The partitioning take place based on the tr_date colum, based on month, > and chops it up in 6 partitions. > > There are some quirks with partitioning, like it does not automatically > partition the data yet, which i think is strange, and something that you > want (specially based on time). But if performance is a big issue at the > moment then the manual partitioning is something worth it. > > Kind Regards > > > On 11/02/2009 10:35 AM, || Sudhir Nimavat || wrote: >> Thanks Rob for quick response.. >> >> Will that give decent performance ? >> >> Note: There would be more read operations and slow write.. The table >> is very simple with few columns only.. >> >> >> >> >> >> >> *Sudhir Nimavat* >> *Senior software engineer. ** >> Quick start global PVT LTD. >> Baroda - 390007* >> *Gujarat, India >> >> Personally I'm always ready to learn, although I do not always like >> being taught >> * >> >> >> >> ------------------------------------------------------------------------ >> *From:* Rob >> *To:* || Sudhir Nimavat || >> *Sent:* Mon, 2 November, 2009 3:01:39 PM >> *Subject:* Re: 50M records each year.. how to handle >> >> On 11/02/2009 10:27 AM, || Sudhir Nimavat || wrote: >> > I have come across a requirement where I need to store a very large >> amount of data in a table. >> > In one of our app.. we can have around 50 Million records each >> year.. Can any one guide me in choosing a strategy than can handle >> this load. >> > >> > >> > >> > >> > Thanks >> > SN >> > >> > >> > >> > >> > Sudhir NimavatSenior software engineer. >> > Quick start global PVT LTD. >> > Baroda - 390007 >> > Gujarat, India >> > >> > Personally I'm always ready to learn, although I do not always like >> being taught >> > >> > >> > Try the new Yahoo! India Homepage. Click here. >> http://in.yahoo.com/trynew >> > >> Consider vertical partitioning with seperate storage devices >> >> >> >> ------------------------------------------------------------------------ >> Add whatever you love to the Yahoo! India homepage. Try now! >> >> -- >> This message has been scanned for viruses and >> dangerous content by *MailScanner* , and is >> believed to be clean. > > > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. > http://in.yahoo.com/