MySQL server hangs without reason

Question

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?

Solution

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

Comments

[Ralfeus]

I have slow query logging. slow-query log is exactly the result of that :-) max_connections = 10 max_used _connections = 11

[Ralfeus]

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]

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?