MySQL Forums
Forum List  »  General

Re: 13 million record table, growing to 25 million+
Posted by: James Day
Date: April 10, 2005 11:22AM

With 1.3GB of data you've probably already encountered any performance issues, unless you have more RAM for caching than you have data. You'll gradually accumulate more index tree levels as the total number of records grows but that's unlikely to have a visible effect.

1.7GB per year means trouble in 12-14 months from the current max_data_length value. If keeping the records together you might want to take care of preparing for more records now by changing the MAX_ROWS and AVG_ROW_LENGTH options in create/alter table so you'll be able to go over 4GB/about 40 million records. Described at http://dev.mysql.com/doc/mysql/en/create-table.html . Note that it mentions some speed penalty from bigger pointers. It won't take much of an increase in volume - only a bit more than twice as much each year - before you'll need to do this anyway to handle one year of data per table.

When you're describing performance as exceptional, it seems like a fine opportunity to do whatever is most convenient and best for making your life easy. Eliminating annual chores, which people tend to forget or have to do on holiday days, is worth considering unless you see a problem in the future. I'd let the database be my servant in this case, and tell it to make my life as easy and automatic as possible. Adding another disk drive or some RAM might well be cheaper than your time as a way to buy speed. You can revisit the decision if it ever gets to be a possible solution to some problem you see.

This isn't the best performance advice, though. If you do want best possible performance, splitting is the way to go. Splitting will also make for faster repairs after any crashes.

Assuming here that you've already done things like making sure that your queries are using good indexes and creating covering indexes for any problematic cases. And that you've tweaked the my.cnf settings as far as they can be tweaked. If not, either of those might provide a much greater payback for your time investment.

Options: ReplyQuote


Subject
Written By
Posted
Re: 13 million record table, growing to 25 million+
April 10, 2005 11:22AM


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.