The reference below was an excellent source for writing this and there are a few more suggestions in it that you may want to look at as well. Make sure to back up your configuration file and databases before you start playing around with it.
Calculating Free Memory
We need to find out how much memory is available for MySQL to use. This example is not going to include swap space in the total because what would be the point in tweaking MySQL to run faster if it is going to be swapping off of the hard drive? When calculating the amount of free memory, don’t forget to include the amount of memory MySQL is currently using. The “ps -o vsz,cmd -C mysqld”* command can be used to find out how much memory MySQL is using.
localhost mysql # ps -o vsz,cmd -C mysqld VSZ CMD 123456 /usr/sbin/mysqld
So, we take 123456 divided by 1024 and we get about 120 of memory being used by MySQL in its current configuration. After running the “free -m” command and finding out there is 415 Megabytes of free memory, we add that to the previous 120 and get 535 megabytes. We will round this down to a nice number of 512.
localhost mysql # free -m total used free shared buffers cached Mem: 7991 7575 415 0 722 5778 -/+ buffers/cache: 1074 6917 Swap: 0 0 0
The following is a template to add into the [mysqld] section in your my.cnf file. It will usually be in /etc/mysql or /etc. Remember to use the free memory calculated from above when figuring the percentages.
Query_cache_limit = 2M Query_cache_size = 25% Query_cache_type = 1 Thread_cache_size = 128 Key_buffer = 25% Join_buffer = 4M Table_cache = 25% Sort_buffer = 4M Read_rnd_buffer_size = 1% Tmp_table_size = 10% (or 32M, whichever is greater)
This example is for a computer with 512MB of memory (including what is being used by mysqld).
[mysqld] query_cache_limit = 2M query_cache_size = 128M query_cache_type = 1 thread_cache_size = 128 key_buffer = 128M join_buffer = 4M table_cache = 128M sort_buffer = 4M read_rnd_buffer_size = 5M tmp_table_size = 51M
This example is for a computer with 4GB of memory (including what is being used by mysqld).
[mysqld] query_cache_limit = 2M query_cache_size = 1024M query_cache_type = 1 thread_cache_size = 128 key_buffer = 1024M join_buffer = 4M table_cache = 1024M sort_buffer = 4M read_rnd_buffer_size = 41M tmp_table_size = 410M