Lewati ke konten
Kembali ke Blog
Database Administration MySQL Troubleshooting

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

Hendra Wijaya
· · 7 menit baca

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="[email protected]"


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):
    sql
     -- 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.

Ditulis oleh

Hendra Wijaya
Artikel Sebelumnya Cara Integrasi Netlify CMS dengan Hugo untuk Headless CMS Artikel Selanjutnya Cara Menjaga Kesehatan Pencernaan Selama Puasa Ramadhan Agar Tetap Fit

Artikel Terkait

SimPKB: Troubleshooting Masalah Umum

Februari 5, 2026

Belajar.id: Masalah Login dan Solusinya

Februari 5, 2026

Cara Install MySQL di Windows 10 dan 11: Panduan Lengkap Server Setup

Februari 3, 2026

Tinggalkan Komentar

Email tidak akan ditampilkan.