Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm a bit confused about which settings to change in my my.conf file to optimise for my server (the mysql server keeps crashing due to our high traffic).

Here's the my.conf file:

local-infile = 0
max_connections = 300
key_buffer = 32M
myisam_sort_buffer_size = 1G
join_buffer_size = 10M
read_buffer_size = 10M
sort_buffer_size = 20M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 12M
query_cache_type = 1
tmp_table_size = 16M
open_files_limit = 8192
max_allowed_packet = 16M
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

And the server specs are:

CPU Cores   2 cores
RAM (Memory)    1GB
SSD (Disk Space)    20GB

Running Ubuntu 12.04 LTS

Here are my partitions:

vda              20G            
├─vda1 ext4     476M /boot      
├─vda2 swap     477M [SWAP]     
└─vda3 ext4    19.1G / 

Any help would be much appreciated.

share|improve this question

closed as off-topic by Iain, Oliver, Katherine Villyard, Ward, Falcon Momot Feb 19 '14 at 6:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must demonstrate a minimal understanding of the problem being solved. Try including attempted solutions, why they didn't work, and the expected results. See How can I ask better questions on Server Fault? for further guidance." – Iain, Oliver, Ward, Falcon Momot
If this question can be reworded to fit the rules in the help center, please edit the question.

I'd start by investigating the cause of the crash and defining what actually happens or crashes in a bit more detail. – Anigel Feb 17 '14 at 10:55
I think you need to talk to you manager and get them to get you some MySQL education - you desperately need it. How can we help you optimize your environment when you haven't presented any metrics indicating what the problem is ? You should read… and it's linked documents. You need to gather evidence to support your investigation. This is relevant to you too. – Iain Feb 17 '14 at 10:57
I'm having some trouble locating the error logs. Both var/log/mysql.err and /var/log/mysql.log are empty. – babbaggeii Feb 17 '14 at 10:58
In that case you need to start by enabling logging – Anigel Feb 17 '14 at 11:03

Not really much to say here with a generalised question like this. It would be helpful to actually know what was happening when it crashed, so the next time it happens, connecting and having a look at the process list could be a good way to go. I'd then start by enabling the slow query log and analysing long running queries (or even more verbose logging in general), taking note of diskspace/ram utilisation at the time. You can start playing around with settings and tweaking, but this would be moreso to improve performance rather than solve a problem with crashing. You also haven't elaborated on "crash"; does mysql die or does it just get stuck on a query etc?

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.