How to optimise MySQL Settings

  • Answered
Our websites have been down for almost 3 days now.
Server load spikes up to 15-20 (4 CPUs).
(we use a dedicated server)

Developers have been trying to locate the query that is causing chaos but so far were not able to do it. I need to optimise MySQL Settings.

I though that perhaps a good place to start would be our mysql settings.
Our current settings are listed below.
Any feedback or suggestions would be much appreciated as we are just running out of options.

long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log
key_buffer = 256M
table_cache = 4096
sort_buffer_size = 128M
read_buffer_size = 48M
read_rnd_buffer_size = 96M
join_buffer_size = 48M
tmp_table_size = 128M
max_heap_table_size = 128M
myisam_sort_buffer_size = 64M
net_buffer_length = 8K
thread_stack = 256K
query_cache_size = 128M


Your required settings can greatly vary depending on your specific usage and environment, but what you have here appears to be fine. In locating the issues, I recommend review your low queries as I do see that you are currently logging them. Take a look over the longer queries to determine which are becoming problematic.