I have to work on a project which would have a extra large table with billions of records but only two fields one is id and another is text.

And id would be set to BIGINT and PK then if 8+2 (extra 2 bytes for overhead) bytes per row is taken to index then in my calculation 64GB RAM should be enough to hold at least 5 billion records index.

But its only one factor I am considering.

Do you think there are other factors also which I should consider?

And RAM size can also be increased if needed. And that table would be most busy table with few writes and lot of reads.

  • 1
    May I ask for a bit more information about this project? What is this table meant to be used for? What problem are you trying to solve? – Jonathan Sep 15 '11 at 7:14

You need to consider the storage engine you will be using for your project because MySQL caches differently for each storage engine.

MyISAM caches index pages only. InnoDB caches data and index pages.

Given that you have a table with large ids, I hope you are using MyISAM.

Here is a query to help you determine the best key_buffer_size:

SUBSTR(' KMG',IF(PowerOf1024<0,0,
recommended_key_buffer_size FROM
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.

UPDATE 2011-09-16 14:49 EDT

If you are using InnoDB, sizing the key_buffer_size will not help. InnoDB and MyISAM cache differently.

Here is the same kind of query to size your InnoDB Buffer Pool:

SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;
  • Interestingly I am thinking of using InnoDB because it works better under load. So, what I learned from your answer is that as long as there is enough key cache. DB size doesn't matter? – Gary Lindahl Sep 16 '11 at 16:56
  • I updated my answer to address using InnoDB – RolandoMySQLDBA Sep 16 '11 at 18:49
  • But as you mentioned that max cap is 8GB for MyISAM then it means beyond that RAM resources will not be utilized efficiently? {Sorry for asking this question too late..} – Gary Lindahl Oct 1 '11 at 22:14
  • 1
    @Gary : You are right. Memory allocated above 8GB for key_buffer_size is wasted. This is critical since only index pages are cached for MyISAM. You could create key caches dedicated to specific MyISAM tables : dev.mysql.com/doc/refman/5.5/en/cache-index.html – RolandoMySQLDBA Oct 1 '11 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.