I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data). Since the databases size is several times smaller than the available memory, how can I make full usage of my server's resources?
What settings should I attempt configure in my.cnf?
Can I force data + indexes to stay in memory?
MySQL only caches the indexes in MySQL's memory, not the data. The data is only cached by the underlying operating system's filesystem caches. The most important value to tune in my.cnf is key_buffer_size which will allocate memory for MyISAM to cache indexes.
However, unless you have some specific reason for using MyISAM tables you should convert those tables to InnoDB. In this day and age, there are less than a handful of reasons to use use MyISAM and MyISAM is not good for concurrency, data integrity, or crash recovery.
If you switch to InnoDB, the most important setting is innodb_buffer_pool_size which is the internal memory cache that will cache data pages & indexes. Other things to consider are the innodb_log_file_size and make sure to set innodb_file_per_table=1. With 4GB of data+indexes, setting innodb_buffer_pool_size=4G is reasonable. If you are running in a highly concurrent environment, you could experiment with values for innodb_thread_concurrency. My recommendation is to start with innodb_thread_concurrency=0, but you might find it necessary to explicitly set it to be somewhere between 4-8 depending on your workload and version of MySQL.
I agree with Aaron (+1) in that you should use InnoDB because you cannot cache data for MyISAM.
Here are the settings you should be concerned with
innodb_buffer_pool_size : While you have 4GB of data and setting innodb_buffer_pool_size to 4G would be adequate, I recommend setting innodb_buffer_pool_size to 8G. Why ? In the event of any bulk operations, you basically wipe out some or all of the content of the innodb buffer pool.
innodb_log_file_size : As for innodb_log_file_size, you should set it to the maximum value, which is 2047M. If you already have log files at the default size of 5M, you will have to resize them.
innodb_file_per_table : By all means, set this. If you do not, the InnoDB system file, known as ibdata1, will grow uncontrollably. If you already have tables loaded inside ibdata1, you will need to cleanup the InnoDB infrastructure in order to keep ibdata1 as small as possible.
innodb_thread_concurrency : The default value is zero(0) and should be left that way. I wrote an earlier post describing how and why.
You should upgrade to MySQL 5.5 because there are new settings to engage multiple cores by means of increased hyperthreading. The options exist in MySQL 5.1 starting with 5.1.38 but only in the InnoDB Plugin. It is simpler and more practical to upgrade to 5.5 rather than attempting to wedge in the InnoDB Plugin.
