MySQL gradually slows down until server reboot

MySQLperformance

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?

Best Answer

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

Related Solutions

Mysql – the.cnf parameters to enable binary logging theSQL 4.1.20

In general on Linux systems, an Errno 13 means Permissions Denied or Access Denied.

What's the output of

# ls -ld /var/log/mysql
# ls -l /var/log/mysql

Does this match with your MySQL user?

If not, suggested solution (providing mysql is your mysql user and mysql is your group):

chown -R mysql:mysql /var/log/mysql

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

Related Topic