When monitoring MySQL's processlist (SHOW PROCESSLIST;), you'll often see connections with the Command column set to SLEEP. These are idle connections waiting for new queries from applications. While generally harmless, excessive SLEEP processes may indicate connection management issues.

SLEEP processes become problematic when:

• Your max_connections limit is being approached
• Connection pooling isn't properly configured
• Applications aren't closing connections after use
• You notice performance degradation

Run this query to analyze SLEEP connections:

SELECT * FROM information_schema.processlist 
WHERE COMMAND = 'Sleep' 
AND TIME > 60 
ORDER BY TIME DESC;

1. Configure Connection Timeout:

SET GLOBAL wait_timeout = 60;  -- seconds
SET GLOBAL interactive_timeout = 60;

2. Fix Application Connection Handling:

For PHP (PDO example):

try {
    $db = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
    // Execute queries...
} finally {
    $db = null; // Explicitly close connection
}

3. Implement Connection Pooling:

For Java applications using HikariCP:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost/test");
config.setUsername("user");
config.setPassword("pass");
config.setMaximumPoolSize(10);
config.setIdleTimeout(30000); // 30 seconds
HikariDataSource ds = new HikariDataSource(config);

Create a stored procedure to kill long-running idle connections:

DELIMITER //
CREATE PROCEDURE clean_idle_connections()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE process_id BIGINT;
    DECLARE cur CURSOR FOR 
        SELECT ID FROM information_schema.processlist 
        WHERE COMMAND = 'Sleep' AND TIME > 300;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO process_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @kill_query = CONCAT('KILL ', process_id);
        PREPARE stmt FROM @kill_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

• Set reasonable timeout values based on your application needs
• Always close database connections in finally blocks
• Monitor connection counts and SLEEP processes regularly
• Consider using a connection pool for web applications
• Review ORM framework configurations (they often manage connections)

---

When examining your MySQL process list (SHOW PROCESSLIST;), you'll frequently encounter connections with the "Command" column showing "SLEEP". These represent idle connections where:

• The client established a connection but isn't currently executing queries
• The connection remains open due to connection pooling or persistent connections
• The application hasn't explicitly closed the connection

Example output showing sleep processes:

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 5  | app  | 10.0.0.1  | shop | Sleep   | 85   |       | NULL             |
| 7  | app  | 10.0.0.1  | shop | Query   | 0    | NULL  | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+

SLEEP processes become problematic when:

• They accumulate beyond your max_connections limit
• They remain for unusually long durations (hours/days)
• Your server shows signs of connection starvation

To check your current connection metrics:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

1. Configure Connection Timeout

Set wait_timeout (default 28800 sec/8 hours) to automatically close idle connections:

-- Set global timeout to 300 seconds (5 minutes)
SET GLOBAL wait_timeout = 300;

2. Application-Level Solutions

For PHP applications using persistent connections:

// Instead of mysql_pconnect() use regular connection
$conn = new mysqli($host, $user, $password, $database);
// Explicitly close when done
$conn->close();

3. Monitor and Alert

Create a monitoring script:

#!/bin/bash
MAX_IDLE=300 # 5 minutes
ALERT_EMAIL="admin@example.com"

idle_count=$(mysql -e "SHOW PROCESSLIST" | grep -c "Sleep")
if [ $idle_count -gt 50 ]; then
  echo "High idle connections: $idle_count" | mail -s "MySQL Alert" $ALERT_EMAIL
fi

To identify and terminate long-running idle connections:

-- Find processes sleeping longer than 1 hour
SELECT id, user, host, time 
FROM information_schema.processlist 
WHERE command = 'Sleep' AND time > 3600;

-- Kill specific process (replace 123 with actual ID)
KILL 123;

Modern connection pools (HikariCP, c3p0) handle this better. Example HikariCP configuration:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setIdleTimeout(300000); // 5 minutes
config.setMaxLifetime(1800000); // 30 minutes