When MySQL queries run longer than expected, they can monopolize server resources and exhaust available connections. This creates a cascading effect where new connections are refused despite available capacity, leading to application downtime.

The simplest approach uses MySQL's built-in event scheduler:

SET GLOBAL event_scheduler = ON;
CREATE EVENT kill_slow_queries 
ON SCHEDULE EVERY 1 MINUTE
DO
  SELECT CONCAT('KILL ',id,';') 
  FROM information_schema.processlist 
  WHERE TIME > 60 AND COMMAND = 'Query' 
  INTO OUTFILE '/tmp/kill_queries.sql';
SOURCE /tmp/kill_queries.sql;

For more control, here's a production-tested bash script:

#!/bin/bash

# Configurable variables
MYSQL_USER="admin"
MYSQL_PASS="secure_password"
MAX_EXECUTION_TIME=60
LOG_FILE="/var/log/mysql_query_killer.log"

# Get current timestamp
TIMESTAMP=$(date +"%Y-%m-%d %H:%M:%S")

# Query to find and kill slow running queries
QUERY="SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 
       FROM information_schema.processlist 
       WHERE COMMAND = 'Query' 
       AND TIME > $MAX_EXECUTION_TIME
       AND USER NOT IN ('system user','repl','event_scheduler')"

# Execute the kill commands
KILL_COMMANDS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "$QUERY" -ss)

if [ ! -z "$KILL_COMMANDS" ]; then
    mysql -u$MYSQL_USER -p$MYSQL_PASS -e "$KILL_COMMANDS"
    echo "$TIMESTAMP - Killed queries: $KILL_COMMANDS" >> $LOG_FILE
fi

For enterprise environments, Percona's toolkit offers robust solutions:

pt-kill --host=localhost --user=monitor --password=secret \
        --busy-time=60 --kill --print --log=/var/log/pt-kill.log \
        --ignore-users='system user,repl,event_scheduler'

When deploying any kill script, consider:

• Whitelist critical admin users
• Set appropriate time thresholds per environment
• Implement proper logging for audit trails
• Consider connection pool behaviors in your application

Track effectiveness with this query:

SELECT COUNT(*) AS killed_queries, DATE_FORMAT(kill_time, '%Y-%m-%d %H:00') AS hour
FROM mysql_query_kill_log
GROUP BY hour
ORDER BY hour DESC LIMIT 24;

When MySQL queries run longer than expected, they can monopolize connections and lead to max_connections exhaustion. While optimizing queries is the long-term solution, we need immediate protection against connection pool depletion.

The SHOW PROCESSLIST command reveals active queries with their execution time. We'll use this to identify and terminate slow-running queries:

#!/bin/bash
# Set your threshold in seconds
QUERY_TIMEOUT=30

# Get slow queries and kill them
mysql -e "SELECT ID FROM information_schema.processlist 
          WHERE COMMAND NOT IN ('Sleep') 
          AND TIME > ${QUERY_TIMEOUT} 
          AND USER NOT IN ('system user','repl')" | \
while read ID; do
  if [[ ! -z "${ID}" ]]; then
    mysql -e "KILL ${ID}"
    echo "$(date): Killed query ${ID}" >> /var/log/mysql_query_killer.log
  fi
done

For enterprise use, consider these enhancements:

#!/bin/bash
# Configurable parameters
QUERY_TIMEOUT=${1:-30}  # Default 30 seconds
DB_USER="monitor_user"
DB_PASS="secure_password"
EXCLUDE_USERS=("'repl'" "'system user'" "'monitor_user'")
LOG_FILE="/var/log/mysql_query_killer.log"

# Generate excluded users string
EXCLUDED=$(IFS=,; echo "${EXCLUDE_USERS[*]}")

# Main execution
slow_queries=$(mysql -u"${DB_USER}" -p"${DB_PASS}" -sNe "
  SELECT CONCAT_WS('|', ID, USER, HOST, DB, TIME, LEFT(REPLACE(INFO, '\n', ' '), 100)) 
  FROM information_schema.processlist 
  WHERE COMMAND NOT IN ('Sleep') 
  AND TIME > ${QUERY_TIMEOUT} 
  AND USER NOT IN (${EXCLUDED})")

while IFS='|' read -r ID USER HOST DB TIME INFO; do
  if [[ -n "${ID}" ]]; then
    mysql -u"${DB_USER}" -p"${DB_PASS}" -e "KILL ${ID}"
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Killed query: ID=${ID} USER=${USER} HOST=${HOST} DB=${DB} TIME=${TIME}s QUERY='${INFO}'" >> "${LOG_FILE}"
  fi
done <<< "${slow_queries}"

For those preferring MySQL-native solutions:

-- Set global wait_timeout (affects all connections)
SET GLOBAL wait_timeout = 30;

-- Or use the Event Scheduler
CREATE EVENT kill_slow_queries
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE query_id BIGINT;
  DECLARE cur CURSOR FOR 
    SELECT ID FROM information_schema.processlist 
    WHERE TIME > 30 AND USER NOT IN ('system user','repl');
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO query_id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @kill_query = CONCAT('KILL ', query_id);
    PREPARE stmt FROM @kill_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END;

Always include these protective measures:

• Whitelist critical admin connections
• Set reasonable timeouts (start with 30s and adjust)
• Log all killed queries for auditing
• Monitor the kill ratio to identify systemic issues