Linux – How much memory do I need to load a 50GB+ MySQL table

linuxmemoryMySQL

I've got a MySQL table with a .MYD file of 44GB, and a .MYI file of 34GB. I'm running scripts from the command line that analyze the data in this table. MySQL is not maxing out the CPU, my memory IS maxed, and VMSTAT is reporting that data is being swapped to disk … but I'm not terribly familiar with this utility and it's reports.

I am running a High Memory Double XL Amazon EC2 Instance. It has 34GB of RAM. I am not very familiar with MySQL tuning and the system is running with the default install on Linux CentOS.

Does the current machine have enough memory to deal with this table efficiently? What other configurations do I need to worry about? The largest system available to me has 68GB of memory. What exactly is MySQL loading into memory when it loads a table? I'd like to get a better sense for what is going on.

Best Answer

Keep in mind that MyISAM does not cache MyISAM data. It only caches MyISAM indexes.

The MyISAM Key Cache (as sized by key_buffer_size) has a max size of 4GB in 32-bit systems. You can make MyISAM Key Caches bigger in 64-bit systems.

Here is something that may help you if you must cache the entire .MYI file:

Try using a dedicated MyISAM Key Cache.

For example, let's say you have a MyISAM table called mydb.mytable whose .MYI file is 34 GB.

Here is how you set up a dedicated key cache just for that table:

SET GLOBAL my_dedicate_keycache.key_buffer_size = 1024 * 1024 * 1024 * 34; 
CACHE INDEX mydb.mytable INTO my_dedicate_keycache; 
LOAD INDEX INTO CACHE mydb.mytable;

This is such a large file. How would you like to load it when mysql starts?

Create a file called /var/lib/mysql/startup_stuff.sql

cd /var/lib/mysql
echo "SET GLOBAL my_dedicate_keycache.key_buffer_size = 1024 * 1024 * 1024 * 34;" > startup_stuff.sql
echo "CACHE INDEX mydb.mytable INTO my_dedicate_keycache;" >> startup_stuff.sql
echo "LOAD INDEX INTO CACHE mydb.mytable;" >> startup_stuff.sql

Next, add this to /etc/my.cnf

[mysqld]
init_file=/var/lib/mysql/startup_stuff.sql

Finally, restart mysql

service mysql restart
Related Topic