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!
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
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.
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.
# 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