Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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
share|improve this answer
add comment

Download mysql tuner and run it after you tried the import. It will tell you which settings to adjust in /etc/my.cnf 32GB of RAM should be enough if you have set your indexes right.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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