MySQL Forums
Forum List  »  Newbie

Re: Which storage engine?
Posted by: Rick James
Date: September 23, 2009 10:07PM

The 950-50GB of space is 'free' and will be eventually used.
50GB / 2 months -> 3 years = ~900GB, so it should fit. MyISAM would be about 1/3 the disk space.

Note: after about 2 years it will be impossible to do an ALTER TABLE (due to lack of spare disk space), so get the schema perfected now.

But...
Ad hoc queries against such a big table will probably bring it to its knees. Consider seriously having exactly one index (PRIMARY KEY) on that table, and build summary tables. The latter can have more indexes, will be smaller, and will be used for the queries.

A table scan of 900GB will take hours. Many hours.

If you have two indexes, then probably the second index will be 'random'. By now (or soon), updating the secondary index will almost always be a disk hit. This will limit your insert rate to about 100/sec -- only 8M per day, well under your 60M/day. For this reason, I insist on nothing more than an AUTO_INCREMENT PRIMARY KEY. InnoDB or MyISAM -- either one would have all activity clustered at the end of the table/index.

With that kind of a key, you can easily keep track of which rows are yet to be summarized into the summary tables.

In other words, you are asking the wrong question. That is, picking the Engine is minor compared to the other problems you will encounter later. Show me the CREATE TABLE, and some of the SELECTs, and I can advise further, maybe even taking a stand on storage engine.



Edited 1 time(s). Last edit at 09/23/2009 10:08PM by Rick James.

Options: ReplyQuote


Subject
Written By
Posted
September 23, 2009 07:53AM
September 23, 2009 09:23AM
Re: Which storage engine?
September 23, 2009 10:07PM
September 24, 2009 03:45AM
September 24, 2009 10:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.