How to increase MySQL timeout in HestiaCP

Q: A. The common wait_timeout misconception?

The default value for the wait_timeout parameter in MySQL/MariaDB is 28800 seconds (8 hours). Many administrators mistakenly set this variable to low values (such as 30 seconds) under the impression that they are increasing connection tolerances.

Database management is one of the critical pillars for supporting any web hosting control panel. In HestiaCP, the MySQL/MariaDB daemon acts as the central data backplane, serving both the internal tables of the panel and the client web applications (such as WordPress, PHP frameworks, and APIs). As transaction volume and complexity grow over time, the default timeouts and limits configured in the database engine can become severe operational bottlenecks.

However, modifying database system variables requires a firm technical understanding of their default values to avoid compromising server stability.

1. Technical reasons to adjust MySQL timeouts#

The decision to tune connection timeouts and slow query thresholds in a production HestiaCP database server is based on critical SRE justifications:

A. The common `wait_timeout` misconception#

The default value for the wait_timeout parameter in MySQL/MariaDB is 28800 seconds (8 hours). Many administrators mistakenly set this variable to low values (such as 30 seconds) under the impression that they are increasing connection tolerances.

Timeout Reduction (wait_timeout = 600): Reducing the timeout to 10 minutes (600 seconds) is useful in memory-constrained environments to reclaim idle threads and conserve RAM.
Timeout Increase (wait_timeout = 86400): Increasing the timeout to 24 hours (86400 seconds) is necessary only when background scripts run extremely long tasks and need to keep inactive connections open.

Setting extremely low values (like 30 seconds) causes idle connections from PHP persistent scripts or connection pools to drop prematurely, producing the classic errors: PDOException: SQLSTATE[HY000] [2002] Connection refused or MySQL server has gone away.

B. The let's encrypt SSL renewal myth#

There is a common myth that Let's Encrypt SSL renewal challenges (HTTP-01 or DNS-01) execute long-running SQL queries. In reality, HestiaCP queries to update domain metadata execute in milliseconds. However, there is an indirect link: if the MySQL thread limit is hit due to idle connection saturation (or if the daemon crashes because of a configuration error), HestiaCP scripts will fail to connect to its internal database, causing automated renewals to fail silently.

C. Better control of slow queries#

The long_query_time variable defines the execution threshold (in seconds) above which a query is recorded in the slow-query.log file. The MySQL default is 10 seconds. Raising it to 30 seconds reduces log verbosity on high-load servers, but for profiling purposes, lower values (e.g., 1 to 2 seconds) are ideal for capturing bottlenecks early.

2. Step-by-step configuration adjustments#

This configuration requires accessing the Linux server via SSH with root privileges.

Step 0: Prerequisites and backups#

Before making global changes, create configuration backups of the active configuration file and check for active running queries (excluding idle or Sleep state connections) that might prevent a safe service restart:

# Backup the active configuration file (adjust path if needed)
sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.backup.$(date +%Y%m%d-%H%M%S)

# Check active queries in execution (ignoring Sleep states)
sudo mysql -e "SELECT * FROM information_schema.processlist WHERE command != 'Sleep';"

If heavy write queries are executing, wait for them to finish to avoid table corruption upon restarting.

Step 1: Locating the file#

HestiaCP (Debian/Ubuntu): Although my.cnf is the global entry point, in standard MariaDB installations it often only contains inclusion directives (!includedir). The actual server variables must be edited inside the 50-server.cnf file (located under the mariadb.conf.d folder) under the [mysqld] block.
Standard Linux systems: my.cnf

Step 2: Editing coordinated parameters#

Open the active configuration file in a text editor (for instance, on Debian/Ubuntu):

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Locate the [mysqld] section and configure the variables in a coordinated manner to match your workload.

Recommended configuration to INCREASE timeouts (Heavy import workloads):

[mysqld]
# Timeout for inactive non-interactive connections (default: 28800 = 8h)
wait_timeout=86400

# Timeout for interactive command-line connections (default: 28800)
interactive_timeout=86400

# Threshold to log slow queries (default: 10s)
long_query_time=10

# Timeout for obtaining InnoDB transaction locks (prevents lock contention)
innodb_lock_wait_timeout=120

# Initial connection handshake timeout limit
connect_timeout=10

Recommended configuration to REDUCE timeouts (Optimized memory usage for web environments):

[mysqld]
# 10-minute timeout (reclaims idle sockets quickly)
wait_timeout=600
interactive_timeout=600
long_query_time=5
innodb_lock_wait_timeout=50

Save and exit the file (Ctrl+O, Enter, Ctrl+X).

Step 3: Validating configuration syntax#

Never restart the database service without testing your edits first. A configuration syntax error will prevent the database service from starting:

# Validate config file syntax (MySQL 8.0+)
sudo mysqld --validate-config

# Test standard config output
sudo mysqladmin --print-defaults

Make sure no error warnings are printed in the console.

Step 4: Restarting the service#

If the validation completes successfully, restart the database daemon:

sudo systemctl restart mysql

Step 5: Verifying active variables#

Confirm that the new limits have been loaded into running memory by querying the database engine variables:

sudo mysql -e "SHOW VARIABLES LIKE 'wait_timeout';"
sudo mysql -e "SHOW VARIABLES LIKE 'interactive_timeout';"
sudo mysql -e "SHOW VARIABLES LIKE 'long_query_time';"

3. Emergency rollback procedure#

If the MySQL service fails to start, or if your applications experience dropped connections:

Restore the original configuration file from the backup (adjusting the path according to your active configuration file):

   # For Debian/Ubuntu (default HestiaCP installation)
   sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf.backup.* /etc/mysql/mariadb.conf.d/50-server.cnf

Validate the restored configuration:

   sudo mysqld --validate-config

Restart the database service:

   sudo systemctl restart mysql

Verify that the daemon is active and listening on the default socket:

   sudo systemctl status mysql
   sudo ss -tulpn | grep -E 'mysql|mysqld'

4. Automation via command line (CLI)#

To automate configuration in batches, use safe regular expressions (which tolerate variable spacing around the = character) to modify existing lines or add the parameters if they are not already in the active configuration file (such as 50-server.cnf on Debian/Ubuntu):

# Define system's active configuration file (Debian/Ubuntu uses 50-server.cnf)
CONFIG_FILE="/etc/mysql/mariadb.conf.d/50-server.cnf"

# Space-tolerant safe replacements
if grep -q "^\s*wait_timeout\s*=" "$CONFIG_FILE"; then
    sudo sed -i 's/^\(\s*wait_timeout\s*=\s*\).*/\186400/' "$CONFIG_FILE"
else
    echo "wait_timeout=86400" | sudo tee -a "$CONFIG_FILE"
fi

if grep -q "^\s*long_query_time\s*=" "$CONFIG_FILE"; then
    sudo sed -i 's/^\(\s*long_query_time\s*=\s*\).*/\110/' "$CONFIG_FILE"
else
    echo "long_query_time=10" | sudo tee -a "$CONFIG_FILE"
fi

Operational sed details:#

s/^\(\swait_timeout\s=\s\)./\186400/: Locates the line containing wait_timeout (allowing leading whitespaces and spaces around =) and replaces the rest of the line with the value 86400, preserving the original indentation and spacing captured in the first backreference group \1.

5. Safe migration script#

Below is the complete Bash script for updating and validating MySQL timeouts, including automated error handling and rollback routines:

#!/bin/bash
# increase_mysql_timeout.sh - Safe MySQL Timeout Tuning
# ⚠️ Run this script with root privileges (sudo)

set -e

NEW_WAIT_TIMEOUT=86400
NEW_LONG_QUERY_TIME=10

# Terminal colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

log() { echo -e "${GREEN}[INFO]${NC} $1"; }
warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
error() { echo -e "${RED}[ERROR]${NC} $1"; exit 1; }

# 1. Verify permissions
[[ $EUID -ne 0 ]] && error "This script must be run as root (sudo)"

# 2. Detect the active configuration file in HestiaCP / OS
if [ -f /etc/mysql/mariadb.conf.d/50-server.cnf ]; then
    CONFIG_FILE="/etc/mysql/mariadb.conf.d/50-server.cnf"
elif [ -f /etc/mysql/my.cnf ]; then
    CONFIG_FILE="/etc/mysql/my.cnf"
else
    CONFIG_FILE="/etc/my.cnf"
fi

BACKUP="${CONFIG_FILE}.backup.$(date +%Y%m%d-%H%M%S)"

# 3. Create config backup
log "Backing up active configuration file from ${CONFIG_FILE}..."
cp "$CONFIG_FILE" "$BACKUP"

# 4. Check concurrency (ignoring Sleep states)
ACTIVE_QUERIES=$(mysql -e "SELECT COUNT(*) FROM information_schema.processlist WHERE command != 'Sleep'" -N)
if [[ $ACTIVE_QUERIES -gt 2 ]]; then
    warn "Detected ${ACTIVE_QUERIES} active running queries. Consider waiting before restarting."
fi

# 5. Modify wait_timeout (space-tolerant)
log "Applying wait_timeout=${NEW_WAIT_TIMEOUT}..."
if grep -q "^\s*wait_timeout\s*=" "$CONFIG_FILE"; then
    sed -i "s/^\(\s*wait_timeout\s*=\s*\).*/\1${NEW_WAIT_TIMEOUT}/" "$CONFIG_FILE"
else
    echo "wait_timeout=${NEW_WAIT_TIMEOUT}" >> "$CONFIG_FILE"
fi

# 6. Modify long_query_time (space-tolerant)
log "Applying long_query_time=${NEW_LONG_QUERY_TIME}..."
if grep -q "^\s*long_query_time\s*=" "$CONFIG_FILE"; then
    sed -i "s/^\(\s*long_query_time\s*=\s*\).*/\1${NEW_LONG_QUERY_TIME}/" "$CONFIG_FILE"
else
    echo "long_query_time=${NEW_LONG_QUERY_TIME}" >> "$CONFIG_FILE"
fi

# 7. Validate configuration syntax
log "Validating configuration file syntax..."
if mysqld --validate-config 2>/dev/null; then
    log "Configuration validation successful."
else
    warn "Syntax error detected. Initiating automatic rollback..."
    cp "$BACKUP" "$CONFIG_FILE"
    error "Validation failed. Original configuration has been restored."
fi

# 8. Restart MySQL service
log "Restarting database daemon..."
systemctl restart mysql

# 9. Verify new values in memory
if systemctl is-active --quiet mysql; then
    log "✅ MySQL successfully restarted and active!"
    log "Active memory variables:"
    mysql -e "SHOW VARIABLES LIKE 'wait_timeout';"
    mysql -e "SHOW VARIABLES LIKE 'long_query_time';"
else
    warn "MySQL failed to start. Running emergency rollback..."
    cp "$BACKUP" "$CONFIG_FILE"
    systemctl restart mysql
    error "MySQL daemon failed to restart. Configuration backup restored."
fi

Was this article helpful?

Leave a quick reaction to help prioritize future technical guides:

510 reads

Creative Commons Attribution-NonCommercial

This post is licensed under CC BY-NC.