MySQL Performance Tuning: Optimal wait_timeout and interactive_timeout Settings for High-Traffic Servers


5 views

In MySQL server configuration, wait_timeout and interactive_timeout control how long inactive connections remain open before being terminated. While they serve similar purposes, their application differs:

# Default values in typical MySQL installations
wait_timeout = 28800      # 8 hours
interactive_timeout = 28800  # 8 hours

For high-traffic servers, maintaining optimal connection management becomes critical. Consider these factors:

  • Connection pool utilization: Applications with connection pooling (like Java's HikariCP or PHP's persistent connections) need shorter timeouts
  • Web server behavior: PHP-FPM processes typically maintain connections for script execution duration
  • Monitoring metrics: Check SHOW STATUS LIKE 'Threads_connected' during peak loads

Based on empirical data from production systems:

# Web application servers (typical LAMP/LEMP stack)
wait_timeout = 300        # 5 minutes
interactive_timeout = 600 # 10 minutes

# Batch processing servers
wait_timeout = 1800       # 30 minutes
interactive_timeout = 3600 # 1 hour

To apply these settings, you have multiple options:

1. Permanent configuration in my.cnf:

[mysqld]
wait_timeout = 300
interactive_timeout = 600

2. Runtime modification (requires SUPER privilege):

SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 600;

Use these queries to monitor connection behavior:

-- Check current timeout settings
SHOW VARIABLES LIKE '%timeout';

-- Monitor abandoned connections
SHOW PROCESSLIST;

-- Track connection statistics
SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Aborted_connects';

For specialized environments:

  • Adjust timeout values based on max_connections and available RAM
  • Consider setting wait_timeout lower than interactive_timeout for CLI vs web traffic
  • Monitor SHOW ENGINE INNODB STATUS for connection-related bottlenecks

Remember that changes to these parameters should always be tested in staging environments before production deployment.

When dealing with MySQL performance tuning under heavy load, two crucial parameters demand attention:

wait_timeout = 600  # seconds (10 minutes)
interactive_timeout = 1800  # seconds (30 minutes)

In our production environment with 500+ concurrent connections, we observed these effects:

  • Set too high (28800): Connection pool exhaustion from idle phantom connections
  • Set too low (30): Constant reconnection overhead affecting transaction-heavy applications

Here's our battle-tested approach for determining optimal values:

# First analyze current connection patterns
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

# Then implement gradual adjustments
SET GLOBAL wait_timeout = 900;
SET GLOBAL interactive_timeout = 3600;

Key factors influencing your decision:

Application Type Recommended Range
Web App (Stateless) 60-300 seconds
Reporting System 1800-7200 seconds
Persistent Connection App Custom connection pooling

For a PHP/MySQL web application handling e-commerce traffic:

[mysqld]
# Set in my.cnf/my.ini
wait_timeout = 300
interactive_timeout = 600

# Connection pool configuration for PDO
$options = [
    PDO::ATTR_PERSISTENT => false,
    PDO::ATTR_TIMEOUT => 30,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION wait_timeout=300"
];

Essential metrics to track after changes:

# Monitor abandoned connections
SHOW STATUS LIKE 'Aborted_connects';

# Track connection duration statistics
SELECT AVG(TIME_TO_SEC(TIMEDIFF(now(), time))) 
FROM information_schema.processlist 
WHERE command = 'Sleep';