facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
by Zafar Malik - no comments
How can track high Number of connection or threads issue OR sleep connections issue OR Threads and Abends [Threads_connected] Issue

Problem: Suddenly one day, high connection/thread alert started to come from a server.

Step1: After tracking connections/executing queries by below commands, it came to know that so many connections are going to sleep mode for a specific user (user1) and for a specific DB (mydb).
show full processlist;

Step2: As some time we were getting some queries executing by this user and some time only getting sleep connection, so not able to track the issue. Also our development team was also not getting any clue as it was very hectic task to scan whole application.

Step3: Then we took decision to enable general log, so that we can get all queries executed by these sleep connections in sequential mode.

Step4: grep pid from general log file, getting from show full processlist command, those are going in sleep mode. To know what happened before going them sleep mode.
[root@myserver ~]# cat /var/log/mysqldquery.log | grep 2398506
2398506 Connect user1@10.10.19.20 on mydb
2398506 Query SET NAMES latin1
2398506 Query SET character_set_results = NULL
2398506 Query SHOW VARIABLES
2398506 Query SHOW COLLATION
2398506 Query SET autocommit=1
2398506 Prepare [1] select (rating_sum/rating_count) res, rating_count from mytable where id= ?
2398506 Prepare [2] update mytable set rating_sum=rating_sum+0, rating_count=rating_count+1 where id=?
2398506 Execute [2] update mytable set rating_sum=rating_sum+0, rating_count=rating_count+1 where id=’98945′
2398506 Execute [1] select (rating_sum/rating_count) res, rating_count from mytable where id= ’98945′

Here we are not getting this connection close statement at the end. While if you check below normal connection then you will get its closing statement.

[root@myserver ~]# cat /var/log/mysqldquery.log | grep 2398507
2398507 Connect user2@10.10.19.20 on mydb2
2398507 Query SET NAMES latin1
2398507 Query SET character_set_results = NULL
2398507 Query SHOW VARIABLES
2398507 Query SHOW COLLATION
2398507 Query SET autocommit=1
2398507 Prepare [1] select (rating_sum/rating_count) res, rating_count from mytable2 where id= ?
2398507 Prepare [2] update mytable2 set rating_sum=rating_sum+0, rating_count=rating_count+1 where id=?
2398507 Execute [2] update mytable2 set rating_sum=rating_sum+0, rating_count=rating_count+1 where id=’98950′
2398507 Execute [1] select (rating_sum/rating_count) res, rating_count from mytable2 where id= ’98950′
2398507 Quit
Here getting proper closing statement as Quit.

Step5: So we forwarded these details to our development team, so that they can take care this issue in application.

Note: We can also provide exact file (application) details in which this issue realted coding exist, by below command (here example of a website application). So that our development team can work on filtered files for quick issue resolution.
[root@myserver ~]# grep -ril user1 /home/jboss…/server/default/deploy/myapp.war