Java JDBC 壓測 MySQL 最大連接數：完整代碼與參數調優指南

測試目的

• 驗證 MySQL max_connections 設置是否生效。
• 發現應用層連接池洩漏、未正確關閉導致的連接耗盡。
• 評估不同連接池（HikariCP、Druid、DBCP2）在高併發下的表現。

改進版測試代碼（支援連接池、統計、優雅關閉）

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.LongAdder;

public class MySqlMaxConnectionsStressTest {

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "password";
    private static final int TARGET_CONNECTIONS = 500; // 目標併發連接數
    private static final int THREADS = 50;             // 併發線程數

    private static final AtomicInteger active = new AtomicInteger(0);
    private static final LongAdder success = new LongAdder();
    private static final LongAdder failed = new LongAdder();
    private static final LongAdder totalLatencyMs = new LongAdder();

    public static void main(String[] args) throws InterruptedException {
        // 1. 配置 HikariCP（生產級連接池）
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(JDBC_URL);
        cfg.setUsername(USER);
        cfg.setPassword(PASS);
        cfg.setMaximumPoolSize(TARGET_CONNECTIONS);
        cfg.setMinimumIdle(TARGET_CONNECTIONS);
        cfg.setConnectionTimeout(30_000);
        cfg.setIdleTimeout(600_000);
        cfg.setMaxLifetime(1_800_000);
        cfg.setPoolName("StressTestPool");
        DataSource ds = new HikariDataSource(cfg);

        ExecutorService executor = Executors.newFixedThreadPool(THREADS);
        CountDownLatch startLatch = new CountDownLatch(1);
        CountDownLatch endLatch = new CountDownLatch(THREADS);

        // 2. 提交任務
        for (int i = 0; i < THREADS; i++) {
            executor.submit(() -> {
                try { startLatch.await(); } catch (InterruptedException ignored) {}
                while (!Thread.currentThread().isInterrupted()) {
                    long start = System.nanoTime();
                    try (Connection conn = ds.getConnection()) {
                        active.incrementAndGet();
                        // 模擬簡單查詢
                        try (var stmt = conn.createStatement();
                             var rs = stmt.executeQuery("SELECT 1")) {
                            rs.next();
                        }
                        success.increment();
                        totalLatencyMs.add((System.nanoTime() - start) / 1_000_000);
                    } catch (SQLException e) {
                        failed.increment();
                        if (e.getSQLState().equals("08004")) { // Server rejected connection
                            System.err.println("MySQL 拒絕連接: " + e.getMessage());
                            Thread.currentThread().interrupt(); // 停止該線程
                        }
                    } finally {
                        active.decrementAndGet();
                    }
                    // 微小間隔避免 CPU 100%
                    LockSupport.parkNanos(1_000_000); // 1ms
                }
                endLatch.countDown();
            });
        }

        // 3. 統計報告線程
        Thread reporter = new Thread(() -> {
            while (endLatch.getCount() > 0) {
                LockSupport.parkNanos(1_000_000_000L); // 1s
                System.out.printf("活躍:%d 成功:%d 失敗:%d 平均延遲:%dms QPS:%d%n",
                        active.get(), success.sum(), failed.sum(),
                        success.sum() > 0 ? totalLatencyMs.sum() / success.sum() : 0,
                        success.sum());
            }
        });
        reporter.start();

        System.out.println("開始壓測...");
        startLatch.countDown();
        endLatch.await(); // 等待所有線程結束（被中斷或達標）
        executor.shutdownNow();
        ds.close();
        System.out.println("壓測結束。最終統計: " + success.sum() + " 成功, " + failed.sum() + " 失敗");
    }
}

MySQL 服務端關鍵參數

常見錯誤碼與對策

連接池監控指標（生產必備）

• HikariCP：HikariPoolMXBean → getActiveConnections(), getIdleConnections(), getTotalConnections(), getThreadsAwaitingConnection()。
• Prometheus + Grafana：導出 hikaricp_connections_active, hikaricp_connections_pending, hikaricp_connections_max。
• 告警閾值：active / max > 0.8 持續 5 分鐘 → 擴容或排查洩漏。

總結

使用生產級連接池 (HikariCP) + 併發壓測 + 服務端參數調優 + 監控告警閉環，才能真正掌握 MySQL 最大連接承載能力。切勿在生產環境直接運行無限制壓測，建議在預發/測試環境模擬生產配置驗證。