Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am looking for a well tested bash script (or alternative solution) to do so, in order to avoid max_connection to be exhausted. I know that it is fighting the symptoms, but really need such script as a short term solution.

share|improve this question
    
What version of MySQL are you running ??? –  RolandoMySQLDBA Jun 25 '12 at 15:46
    
the mysql version is 5.5 –  alfish Jun 25 '12 at 19:06

3 Answers 3

check out pt-kill command from the percona toolkit.

and.. do start monitoring your system - munin, cacti with better cacti templates for mysql, anything so you get some idea what's going on. logging mysql slow queries will be a good idea too.

share|improve this answer
    
Thanks about the suggestions, but really look for a "one-off" script. –  alfish Jun 24 '12 at 20:25
4  
pt-kill is very well tested and solves your exact problem. It takes like 10 minutes to figure out the command line parameters and get it running. What more do you want? –  Aaron Brown Jun 25 '12 at 22:10

If you have MySQL 5.1 where the processlist is in the INFORMATION_SCHEMA, you can do this to generate the KILL QUERY commands in bulk from within the mysql client for query running longer than 20 minutes (1200 seconds):

SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery
FROM information_schema.processlist WHERE user<>'system user'
AND time >= 1200\G

You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.

If you are root@localhost, you should have full privileges to run this as follows

SECONDS_TOO_LONG=1200
KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword

You can crontab this as follows:

SECONDS_TOO_LONG=1200
QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword
fi

Here is another variation:

SECONDS_TOO_LONG=1200
QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT CONCAT('KILL QUERY ',id,';') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" > /tmp/kill_log_queries.sql
    mysql -uroot -ppassword < /tmp/kill_log_queries.sql
fi

BTW You do not have specify a myDB since I explicit read from information_schema.processlist as a fully qualified tablename.

Here is a demonstration of what you should see. For this example, I will echo the KILL command of all processes whose time > 20000 seconds:

[root@***** ~]# mysql `lwdba_connect` -ANe"SELECT GROUP_CONCAT('KILL ',id,'; ' SEPARATOR ' ') FROM information_schema.processlist WHERE time > 25000 AND user<>'system user';"
+----------------------------------------------------+
| KILL 180186;  KILL 180141;  KILL 176419;  KILL 3;  |
+----------------------------------------------------+
[root@***** ~]#

I have been doing this technique for the last 5 years. In fact, I submitted this answer to the DBA StackExchange last year and it got accepted.

share|improve this answer
    
Roland, could you please clarify these: Is this command persistent, or need to be run frequently? What should be substituted in the command assuming that the mysql user is 'root' and the database name is myDB? Thanks –  alfish Jun 25 '12 at 19:05
    
I updated my answer. –  RolandoMySQLDBA Jun 25 '12 at 19:14
    
Thanks, but after turning your last recipe into a bash script, I get: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 –  alfish Jun 25 '12 at 20:47
    
Which SQL command generated that error ??? –  RolandoMySQLDBA Jun 25 '12 at 20:48
    
Roland SF is not a sandbox. Better to test before you suggest something as solution. In addition, when all the connections are saturated, how mysql is going to run your procedure, assuming that it is not faulty? –  alfish Jun 25 '12 at 21:14

I found the following code-snipped here:

Update 2013-01-14: There was an anonymous hint that this is potentially dangerous and can kill replication processes as well. So use at your own risk:

mysql -e 'show processlist\G' |\
egrep -b5 'Time: [0-9]{2,}' |\
grep 'Id:' |\
cut -d':' -f2 |\
sed 's/^ //' |\
while read id
do
  mysql -e "kill $id;"
done
share|improve this answer
    
What is the time constant in the snippet above? –  alfish Jun 25 '12 at 19:08
    
@alfish I am not the author - but I would say this is a regular expression that matches all time values that have at least two digits. So the assumtion here is that 10 is too long. –  Nils Jun 25 '12 at 21:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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