Strictly only RAM matters?

Question

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.

Answer 1

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:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
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:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
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;