Hello Everyone,
I am back again with another query 
We have been receiving complaints from the client saying that he sees this error in the error log files of his database server :-
[Warning] Too many connections
At times, they get resolved on their own, But a couple of times, The server’s CPU would spike up to unexpected levels
and would start causing issues to the application server and we had to reboot the server to resolve the issue.
I am not sure how to trouble shoot this issue. ? Would some one, care to explain me the reason for this issue ?
I have set max connections values to 500 in the configuration file I am not sure what needs to be done when this event occurs !!!
Thank you for your time.
Generally when you see connections increase and the CPU spike, it means that there is contention going on that is backing things up. The best thing you can do is get a “show engine innodb status \G” ran while the issue is going on to see what is causing the blockage. This could be as simple as a bad query that is locking things up, or it could be something worse like an optimizer bug that you are hitting.
If you are able to identify the blocking query (if that is the case), then you may be able to kill it (if the client wants that) in order to get things moving again. Short of that all you can do is wait, and if possible reduce the load going to the system (i.e. if there are non-critical batch jobs you can stop that would help to prevent additional backlog). If locking is the issue, one thing you could look into is lowering the isolation level to something like READ-COMMITTED (if it’s not already there). Just make sure to talk to the client about the pros and cons of that if you go that route.
Increasing the max connections is a temporary fix, however if the problem persists then the system will likely just use up all the connections you give it and end up bogging down / crashing the system anyway.
You’d be mainly looking in the “TRANSACTIONS” section, which lists all the threads currently in InnoDB. You’ll want to look for threads that are “RUNNING” and then look for references to locks and lock waits. Hard to say ahead of time what you’ll see, but you’ll likely know it when you see it.
You can set the transaction isolation level dynamically (set global tx_isolation = “READ-COMMITTED”), and then set it in your config (transaction-isolation = “READ-COMMITTED”) for when the server restarts. As for pros and cons, the MySQL manual has a pretty good write-up:
[URL=“MySQL :: MySQL 8.0 Reference Manual :: 13.3.7 SET TRANSACTION Statement”]http://dev.mysql.com/doc/refman/5.5/...ansaction.html[/URL]
The main point is that any transaction isolation level looser than REPEATABLE-READ is no longer fully ACID compliant, so if the client needs to be fully ACID compliant then changing it to READ-COMMITTED is not an option. Aside from that, the application that is using MySQL on the backend needs to be smart enough to handle the change in isolation level. READ-COMMITTED is generally pretty safe because it only takes into account committed data, but it could still confuse some applications, so they would need to test their app for compatibility.
The overall idea of the transaction isolation level is to determine how a transaction handles changes in data that occur while the transaction is still open. In REPEATABLE-READ, any changes to data that occur from another transaction are not seen while the initial transaction is still running. With READ-COMMITTED, however, a running transaction can see changes to data from other transactions as long as those changes have been committed.