1

I have a server running web site with back-end database MySQL (more specific - 5.5.37-MariaDB, for debian-linux-gnu on x86_64).

Sometimes it stops accepting connections and web site scripts report "unable to connect to the server". After I restart MySQL server it works ok for some time (up to several days).

syslog, mysql.error and dmesg show no related errors. slow-query log however shows that at some moment queries are executed slower and slower (starting from about 1 second and ending at 70). After I restart MySQL same queries are executed immediately.

How can I fix it or at least define issue cause?

2 Answers 2

1

When the server is hanging run strace -fp <pid of mysql>

That will show which command is waiting. Its likely to be a read with a number

lsof -p <mysql pid> will display what that number actually is.

Its hard to say without being on the server when its breaking if there is nothing else to go off in the logs

0

Enable slow query logging in my.cnf to find where your bottleneck queries are and assume taking a look if max_used_connections is greater than max_connections.

SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'max_used_connections';
4
  • I have slow query logging. slow-query log is exactly the result of that :-) max_connections = 10 max_used _connections = 11
    – Ralfeus
    Oct 5, 2014 at 19:14
  • increase max_connections to 15 and see if that helps
    – txm
    Oct 5, 2014 at 20:55
  • I'm increasing number of connections slowly. But 10 connections weren't initially set. Initially there was no setting, so I assume it was some large default value. This raises a question - shouldn't amount of connections be reasonable unlimited? If I have one connection per HTTP request it shouldn't be a problem, should it?
    – Ralfeus
    Oct 8, 2014 at 6:14
  • I have been raising max connections from 10 to 40. Reason of raising was that while having little amount of connections web clients often got "database server unavailable" error. On 40 connections quantity of such errors isn't that large and CPU load reaches 100% reasonable often. What is the proper approach? Keep amount of max connections to MySQL server small and queue HTTP requests on Apache side until connection is freed up? Or allow unlimited amount of MySQL connections and investigate MySQL load?
    – Ralfeus
    Oct 17, 2014 at 18:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .