How to optimise MySQL Settings

Category: Server Usage

LevRoss
Asked:
2014-03-08 6:15 pm EST

Hits: 81
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 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.

[mysqld]
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
max_connections=2000

skip-bdb
skip-innodb

ft_min_word_len=3

You must login before you can ask a follow up question.

You must login before you can submit an answer.

OTHER ANSWERS

0

JeffMa
Staff
8,314 Points
2014-03-10 7:44 am EST
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.

You must login before you can post a comment about this answer.

Like this Question?

Related Articles

It looks like there are no related articles.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!