-1

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?

2

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.

  • @Rick_James How to analyze the result of 'SHOW FULL PROCESSLIST;', to see the locker query? – Mohsen Abasi Dec 16 '17 at 5:17
  • @MohsenAbasi - order them by time. The ones that have been running the longest (other than a couple of system tasks) may be the villain. Would you care to show us the PROCESSLIST. – Rick James Dec 16 '17 at 6:14
  • @Rick_James I think that the issue is not from another query lock. The connections count is high. Max_Connections is 151 and when I count threads of MySQL process (ps -T PIDofMySQL), it is 131. Do u agree with me that the connections limit is the issue? – Mohsen Abasi Dec 16 '17 at 7:26
  • @MohsenAbasi - PROCESSLIST is more useful than ps. – Rick James Dec 16 '17 at 19:13
  • @Rick_James $ mysql --login-path=dbpass -e"show processlist" | wc -l resulted in: '75' and '$ ps -T 31345 | wc -l' resulted in: '104' My max_connection var is '151'. However I can't run any new query!!!! – Mohsen Abasi Dec 17 '17 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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