Category: UNIX Commands

Good MySQL related application requires right performance tuning. Without right tuning of MySQL configuration, application connections started failing and application became slowing down.

If you are using persistent connection, there may be so many connections which are running but in ‘Sleep’ mode currently. If you have MySQL >= 5.1, where there is a processlist in the INFORMATION_SCHEMA. 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

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.

To run add this query in crontab, create a shell file with following content:

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

Shell file with above content will kill all queries running more than 1200 seconds (20 mins).

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

Shell file with above content will kill all queries running more than 1200 seconds (20 mins) with adding all queries in log file and running same log file afterwards.