I have multiple java hibernate apps connecting to 1 single mysql DB. Sometimes some of the apps hangs on DB select query. I think it is because of many connections the apps are to DB and remains open. So, I changed variables 'wait_timeout=3' & 'interactive_timeout=3600' & 'max_connections=5000'. After that, I saw many errors/exceptions of not being able to connect to DB OR not being able to insert into DB. Therefore I reset all 3 above variables to their default values: 'wait_timeout=28800' & 'interactive_timeout=28800' & 'max_connections=151'. Now there is no error/exception but hangs of the apps. What should I do?
When the app hangs on a query, it is because of one of these things:
- You are using MyISAM; switch to InnoDB.
- Another query is blocking because it is locking something your "hung" query needs. Find it. Suggest
SHOW FULL PROCESSLIST;as a quick way that sometimes finds the problem.
- Your query is somehow slow. Let's see the query that is "hanging", plus
SHOW CREATE TABLE.
max_connections = 5000 is generally unwise -- it can lead to problems of its own.
wait_timeout = 3 is masking the problem without solving it.