Salin dan Bagikan
MySQL Troubleshooting Database Administration
Cara Mengatasi Error Too Many Connections di MySQL Server: Solusi Lengkap - Panduan lengkap mengatasi error too many connections di MySQL dengan analisis root cause, solusi …

Cara Mengatasi Error Too Many Connections di MySQL Server: Solusi Lengkap

Cara Mengatasi Error Too Many Connections di MySQL Server: Solusi Lengkap

Error “too many connections” adalah salah satu masalah paling critical yang bisa membuat aplikasi down. Saya pernah mengalami scenario di mana e-commerce site dengan 10K concurrent users tiba-tiba tidak bisa akses database karena connection limit tercapai. Artikel ini akan membantu Anda mengatasi dan mencegah masalah ini.

Memahami MySQL Connection Model

1. Arsitektur Connection

MySQL menggunakan one-thread-per-connection model:

  • Setiap client connection = 1 thread
  • Thread membutuhkan memory (256KB - 1MB default)
  • Max connections dibatasi oleh: max_connections

2. Connection States

-- Lihat semua connection
SHOW PROCESSLIST;

-- Atau dengan lebih detail
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST;

3. Connection States yang Perlu Diperhatikan

  • Sleep: Connection idle, menunggu query baru
  • Query: Sedang mengeksekusi query
  • Locked: Menunggu lock
  • Copying to tmp table: Query dengan temporary table
  • Sorting result: Sedang sorting data

Immediate Fix: Mengatasi Crisis

1. Cek Current Connections

-- Cek status connections
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';

-- Cek max connections
SHOW VARIABLES LIKE 'max_connections';

2. Kill Connections yang Tidak Perlu

-- Cek connections yang sleep lama
SELECT ID, USER, HOST, TIME, STATE 
FROM information_schema.PROCESSLIST 
WHERE COMMAND = 'Sleep' 
  AND TIME > 300  -- lebih dari 5 menit idle
ORDER BY TIME DESC;

-- Kill connections (ganti ID dengan ID yang sesuai)
KILL 12345;
KILL 12346;

3. Script untuk Kill Idle Connections

sudo mysql -e "
SELECT GROUP_CONCAT(ID) INTO @killlist 
FROM information_schema.PROCESSLIST 
WHERE USER != 'root' 
  AND COMMAND = 'Sleep' 
  AND TIME > 300;

SET @killcmd = CONCAT('KILL ', @killlist);
PREPARE stmt FROM @killcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
"

4. Emergency: Tambah Max Connections

-- Sementara naikkan max connections (butuh SUPER privilege)
SET GLOBAL max_connections = 500;

-- Untuk permanent, edit my.cnf

Root Cause Analysis

1. Cek Connection Pattern

-- Connections per user
SELECT 
    USER,
    HOST,
    COUNT(*) as connection_count,
    AVG(TIME) as avg_idle_time
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep'
GROUP BY USER, HOST
ORDER BY connection_count DESC;

2. Analisis Application Behavior

-- Cek queries yang paling banyak connection
SELECT 
    USER,
    COUNT(*) as connections,
    COUNT(DISTINCT DB) as databases_used,
    AVG(TIME) as avg_time
FROM information_schema.PROCESSLIST
GROUP BY USER
ORDER BY connections DESC;

3. Connection Pool Analysis

-- Cek connections per host
SELECT 
    SUBSTRING_INDEX(HOST, ':', 1) as client_host,
    COUNT(*) as connections,
    SUM(CASE WHEN COMMAND = 'Sleep' THEN 1 ELSE 0 END) as idle_connections
FROM information_schema.PROCESSLIST
GROUP BY client_host
ORDER BY connections DESC;

Solusi Jangka Panjang

1. Optimasi Connection Pool

Konfigurasi Application Connection Pool:

// Contoh HikariCP configuration
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);  // Jangan terlalu besar
config.setMinimumIdle(5);
config.setIdleTimeout(300000);  // 5 menit
config.setMaxLifetime(600000);  // 10 menit
config.setConnectionTimeout(30000);  // 30 detik

# SQLAlchemy example
engine = create_engine(
    'mysql+pymysql://user:pass@localhost/db',
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=3600
)

2. Optimasi MySQL Configuration

Edit my.cnf:

[mysqld]
# Connection Limits
max_connections = 200
max_user_connections = 50  # Limit per user

# Connection Timeout
wait_timeout = 300        # 5 menit
interactive_timeout = 300 # 5 menit
connect_timeout = 10

# Thread Cache
thread_cache_size = 50
thread_pool_size = 8      # Jika menggunakan thread pool

# Performance untuk banyak connections
innodb_buffer_pool_size = 2G
innodb_thread_concurrency = 16

# Logging connections
log_warnings = 2

3. Implementasi Connection Queue

-- Buat stored procedure untuk queuing
DELIMITER $$

CREATE PROCEDURE GetConnection()
BEGIN
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retries INT DEFAULT 3;
    
    connection_loop: LOOP
        SET retry_count = retry_count + 1;
        
        IF retry_count > max_retries THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Max retries exceeded';
        END IF;
        
        -- Cek available connections
        IF (SELECT COUNT(*) FROM information_schema.PROCESSLIST) < 
           (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES 
            WHERE VARIABLE_NAME = 'max_connections') - 10 THEN
            LEAVE connection_loop;
        END IF;
        
        -- Tunggu sebentar
        DO SLEEP(1);
    END LOOP;
END$$

DELIMITER ;

4. Monitoring dan Alerting

Script Monitoring:

sudo nano /usr/local/bin/check_connections.sh

#!/bin/bash

THRESHOLD=80  # persen
ALERT_EMAIL="admin@example.com"

# Cek current connections
MAX_CONN=$(mysql -e "SHOW VARIABLES LIKE 'max_connections';" | tail -1)
CURRENT_CONN=$(mysql -e "SHOW STATUS LIKE 'Threads_connected';" | tail -1)
PERCENTAGE=$((CURRENT_CONN * 100 / MAX_CONN))

if [ $PERCENTAGE -gt $THRESHOLD ]; then
    echo "WARNING: MySQL connections at ${PERCENTAGE}% (${CURRENT_CONN}/${MAX_CONN})" | \
        mail -s "[WARNING] High MySQL Connections" $ALERT_EMAIL
fi

Setup Cron:

*/5 * * * * /usr/local/bin/check_connections.sh

Connection Leak Detection

1. Identifikasi Connection Leak

-- Cek connections yang terbuka lama
SELECT 
    ID,
    USER,
    HOST,
    DB,
    TIME as seconds_open,
    COMMAND,
    INFO
FROM information_schema.PROCESSLIST
WHERE TIME > 600  -- lebih dari 10 menit
ORDER BY TIME DESC;

2. Track Connection Patterns

-- Enable performance schema untuk tracking
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%waits%';

-- Query untuk analisis
SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%connection%'
ORDER BY SUM_TIMER_WAIT DESC;

Optimasi Aplikasi

1. Best Practices Application

Do:

  • Selalu tutup connections (try-finally)
  • Gunakan connection pool
  • Set timeout yang reasonable
  • Reuse connections untuk transaction

Don’t:

  • Create connection per request
  • Lupa close connection
  • Hold connection terlalu lama

Contoh Java dengan Try-With-Resources:

try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    
    stmt.setString(1, "value");
    ResultSet rs = stmt.executeQuery();
    
    while (rs.next()) {
        // process data
    }
} catch (SQLException e) {
    // auto-close connection
    logger.error("Database error", e);
}

2. Implementasi Retry Logic

import time
from sqlalchemy.exc import OperationalError

def execute_with_retry(func, max_retries=3, delay=1):
    for attempt in range(max_retries):
        try:
            return func()
        except OperationalError as e:
            if 'too many connections' in str(e) and attempt < max_retries - 1:
                time.sleep(delay * (attempt + 1))
                continue
            raise

Advanced Solutions

1. ProxySQL untuk Connection Pooling

Setup ProxySQL:

# Install ProxySQL
sudo apt install proxysql

# Konfigurasi
echo "
datadir='/var/lib/proxysql'
admin_variables=
{
    admin_credentials='admin:admin'
    mysql_ifaces='0.0.0.0:6032'
}
mysql_variables=
{
    threads=4
    max_connections=1000
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
}
mysql_servers =
(
    {
        hostgroup_id=1
        hostname='127.0.0.1'
        port=3306
        max_connections=200
    }
)
mysql_users=
(
    {
        username='app_user'
        password='password'
        default_hostgroup=1
        max_connections=50
    }
)
" > /etc/proxysql.cnf

sudo systemctl start proxysql

Aplikasi connect ke ProxySQL (port 6033):

# Ganti connection string
engine = create_engine('mysql+pymysql://app_user:password@localhost:6033/db')

2. Read/Write Split dengan Replication

-- Aplikasi connect ke master untuk write
-- Slave untuk read (bisa multiple slaves)
-- Connection pool untuk masing-masing

Emergency Response Plan

Saat Terjadi Too Many Connections

  1. Immediate (0-2 menit):

    -- Kill idle connections
KILL (SELECT GROUP_CONCAT(ID) FROM PROCESSLIST WHERE TIME > 300);

  2. Short-term (2-10 menit):

    • Naikkan max_connections temporary
    • Restart aplikasi yang leak
    • Monitor connections

  3. Long-term (setelah crisis):

    • Fix connection leak
    • Implement connection pooling
    • Setup monitoring

Prevention Checklist

  • Connection pool configured properly
  • Timeouts set appropriately
  • Connections always closed
  • Monitoring setup
  • Alerting untuk high connections
  • Max connections tuned untuk workload
  • Connection leaks detected and fixed
  • Proxy layer (opsional)

Kesimpulan

“Too many connections” adalah symptom, bukan root cause. Solusi yang tepat adalah:

  1. Fix immediate dengan kill idle connections
  2. Identify dan fix connection leaks
  3. Implement proper connection pooling
  4. Monitor dan prevent recurrence

Dengan setup yang tepat, Anda akan jarang atau tidak pernah melihat error ini lagi.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/cara-mengatasi-error-too-many-connections-mysql/

Hendra WIjaya
Tirinfo
5 minutes.
3 February 2026