MySql performance optimization is an interesting task, I like to focus on it on my free time.

Please note that these are house rules. There is no guarantee that your performance will improve. Use with precautions!

key_buffer_size

An important variable setting is key_buffer_size; a good value is the 20% of total RAM.

Here it is a sample:

key_buffer_size = 820M

max_connections

Also, the max_connections variable could be safely lowered from its default value. Take a look at your Max used connections status variable; you can safely set the max_connections to Max used connections x 5, with a minimum of 40.

Sample:

max_connections = 85

tmp_table_size and max_heap_table_size

Finally, it’s important to take a look to tmp_table_size and max_heap_table_size. You can get a good value for them by dividing the available RAM by the max_connections variable.

Sample:

# Total RAM: 4GB; Mysql supposed available RAM: 3GB; Max_connections: 85;
# Good Value = 3GB / 80 = 35MB
tmp_table_size = 35M
max_heap_table_size = 35M