Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

The performance of pages on my site that access my mysql database slows down over time until I reboot my server. (Static pages are not affected.) Restarting mysqld itself doesn't help, but rebooting the entire server helps. I've tried various MySQL performance tweaks and enabled caching, none of which helped.

It's initially very fast, but after a few days becomes much slower, and it eventually becomes unbearably slow. Any mysql operations that I run from the command line are perfectly fine, including connecting to mysql, connecting to a database, and running queries. The slow queries log doesn't show anything abnormal. But any page on my site that connects with mysql runs very slowly.

If I reboot my server, everything runs fast again, until it slows down again.

my.cnf:

max_connections = 500
query_cache_size = 10M
tmp_table_size = 16M
max_heap_table_size = 16M
thread_cache_size = 4
innodb_buffer_pool_size = 7G
symbolic-links=0

top, server up 3 days:

top-3days

top, server up 5 mins after reboot: top-5min

What are some steps I should take to isolate and fix this problem?

share|improve this question
1  
how much ram do you have on the system? –  Mike Jun 6 '13 at 21:40
    
15360mb - I added top to the original post. –  Eric Yang Jun 6 '13 at 21:45
1  
A load of 154 with 76% system time and 0% iowait time means something is really wrong, but at least your drives aren't failing. –  DerfK Jun 6 '13 at 23:06
    
Are your tables mostly MyISAM or InnoDB? I assume InnoDB due to setting the buffer_pool size.. There is nearly 13GB being used by the cache (not MySQL). What distribution of MySQL are you using? Newer releases of Percona Server allow you to allocate all of the space in the buffer pool upon start up which would eliminate the memory being taken by the cache. –  yoonix Jun 6 '13 at 23:16
    
Large tables are mostly InnoDB, although there are some MyISAM mixed in there. The server was getting too slow again so I had to do a reboot. I edited the post to add top after the server's been up 5 mins (it's very fast now). Looks like there's a big difference, esp. in load average and CPU. What's the best way to find the problem and prevent it from slowing down again? –  Eric Yang Jun 9 '13 at 3:46

4 Answers 4

You are in 76% system waits, most probably due to heavy swapping.

  1. use mysqltuner.pl to find what are your variables that affect memory usage
  2. press "m" when in top to see what ate the memory
  3. use free -m to see how much is cached
  4. use iotop to see who is eating the IO
share|improve this answer
    
I did use mysqltuner. I'll try iotop when it starts to slow again. –  Eric Yang Jun 10 '13 at 19:35

Sounds like you're swapping badly.

Your problem could be double buffering. Try adding this to your my.cnf (and restarting your mysql server, obviously):

innodb_flush_method = O_DIRECT

And reduce your swapiness at the OS level, like so:

echo 0 > /proc/sys/vm/swappiness
share|improve this answer
    
Tried this, will let you know how it works out in a few days. –  Eric Yang Jun 10 '13 at 19:34
up vote -1 down vote accepted

Looks like an OSSEC firewall process was the culprit - it took up too much CPU and bogged everything down. When that was reconfigured everything ran smoothly again.

share|improve this answer
1  
What did you do to reconfigure it? –  Ladadadada Jun 12 '13 at 16:40

In your web pages are you closing your database connection and freeing the memory you used? If not, you should be.

Take a look at your system specs at 3-days - your system and memory usage are up, with limited free. Since your command line queries are working fine with no lag, but your web pages are slow you've got a memory leak that's staying open, probably in your web server memory space (since the command line works fine it's not in mySQL).

Mark H

share|improve this answer
2  
With the information provided you cannot conclusively determine that the memory utilization is the result of a memory leak (or that the web application is the culprit) - there are an infinite number of possible causes (to name a few: More web server workers started up to handle load, more MySQL backends started for the same reason, a large query running which is using that RAM). –  voretaq7 Jun 12 '13 at 15:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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