High load average on MySQL server

Question

Recently we're experiencing serious performance problem on our MySQL server. Application server and database server are separate. At the database server end, load average getting higher in no time. CPU usage remain high too (around 200%).

load average: 16.91, 21.48, 30.91

At the application end we've manually closed the database connections wherever they have manually opened. my.cnf is also a bit configured using following parameters:

innodb_buffer_pool_size = 4G
query_cache_type        = 1
wait_timeout            = 1800

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 32

query_cache_limit       = 5M
query_cache_size        = 640M
query_cache_type        = 1

But still there's no visible improvement. Server usage is still very high. What could possibly go wrong with configuration? How to keep server load average normal (or at least close to normal)?

Answer 1

To solve this problem you (or someone on your behalf) will need to gather some data about your system and analyze it using Scientific Method (or a process you prefer) .

You can gather the data using system tools like sar, free, iostat, vmstat etc.

Install monitoring to gather and track data 1,2

Reading your logs is also frequently helpful.

Now that you have a view of how your system is performing you can start t ask questions, perform trials and analyse the results.

---

1. What is the actual problem you're trying to solve ?

My load average is unusually high.¹

2. So, now we know what the actual problem is we're solving we have some direction. Let's gather some information to help us figure out a solution.

   • Is the problem time related? Does it happen regularly or randomly.
   • Check your logs, all of them, not just the particular services's logs as something else may be causing the problem. Log entries generally have timestamps, this is to help you correlate events across multiple applications and services - use them. If necessary increase the log verbosity too.
   • Watch what your system is doing. Use tools like top, vmstat, iostat, sar, ps,tcpdump or even full blown monitoring.
     
     

3. Analyse the information you have gathered. What is actually happening on the system when the service stops responding? What is the state of the system's resources ?

4. Take appropriate action to remediate. Hopefully it's pretty obvious what's going on, you're running out of memory and OOM killer comes out to play, your swap activity is too high, your run queue is too long, you're iobound etc. If it's not obvious then you're probably not gathering the correct data - you know what to do, go back to 2.

5. Monitor what the changes introduced at 4. do.

6. Did the changes fix the problem ? Is it better? Is it worse ? Is there no difference ? Where you go from here depends on what you find. You may need to go back to 2. and gather more pertinent data or 3. to reanalyse what data you have or 4. because you identified a number of potential solutions.

7. Document your findings and the changes you made.