1
# 连接建立时间计算示例
2
dns_time = 10  # ms, DNS 解析
3
tcp_rtt = 1    # ms, 本地网络 RTT
4
tcp_handshake = 3 * tcp_rtt  # 3 次握手
5
tls_handshake = 4 * tcp_rtt  # TLS 1.3 2 RTT
6
auth_time = 3  # ms, 认证
7
init_time = 2  # ms, 会话初始化
8

9
total_time = dns_time + tcp_handshake + tls_handshake + auth_time + init_time
10
print(f"连接建立总耗时: {total_time} ms")
11
# 输出: 连接建立总耗时: 26 ms

1
-- 查看每个连接的内存使用
2
SHOW STATUS LIKE 'Threads_connected';
3
SHOW STATUS LIKE 'Threads_running';
4

5
-- 连接内存占用
6
SELECT
7
    CONNECTION_ID() as conn_id,
8
    THREAD_ID,
9
    PROCESSLIST_USER,
10
    PROCESSLIST_HOST,
11
    PROCESSLIST_DB,
12
    PROCESSLIST_COMMAND,
13
    PROCESSLIST_TIME,
14
    PROCESSLIST_STATE
15
FROM performance_schema.threads
16
WHERE TYPE = 'FOREGROUND';

1
# 性能计算
2
requests_per_second = 1000
3
query_time = 1  # ms
4
connection_time = 26  # ms
5
close_time = 1  # ms
6

7
# 无连接池
8
time_without_pool = connection_time + query_time + close_time
9
throughput_without = 1000 / time_without_pool * 1000
10
print(f"无连接池每请求耗时: {time_without_pool} ms")
11
print(f"无连接池吞吐量: {throughput_without:.0f} req/s")
12

13
# 有连接池（连接复用）
14
time_with_pool = query_time  # 仅查询时间
15
throughput_with = 1000 / time_with_pool * 1000
16
print(f"有连接池每请求耗时: {time_with_pool} ms")
17
print(f"有连接池吞吐量: {throughput_with:.0f} req/s")
18

19
print(f"性能提升: {throughput_with/throughput_without:.0f}x")
20

21
# 输出:
22
# 无连接池每请求耗时: 28 ms
23
# 无连接池吞吐量: 35 req/s
24
# 有连接池每请求耗时: 1 ms
25
# 有连接池吞吐量: 1000 req/s
26
# 性能提升: 28x

1
# 查看连接状态统计
2
netstat -n | awk '/^tcp/ {++state[$NF]} END {for(key in state) print key,"\t",state[key]}'
3

4
# 可能的输出：
5
# TIME_WAIT      12000
6
# ESTABLISHED    200
7
# CLOSE_WAIT     5

1
# TIME_WAIT 对端口的影响
2
ephemeral_ports = 65535 - 32768  # 临时端口范围
3
connections_per_second = 1000
4
time_wait_timeout = 60  # 秒
5

6
time_wait_count = connections_per_second * time_wait_timeout
7
print(f"临时端口总数: {ephemeral_ports}")
8
print(f"TIME_WAIT 连接数: {time_wait_count}")
9
print(f"端口利用率: {time_wait_count/ephemeral_ports*100:.1f}%")
10

11
# 输出:
12
# 临时端口总数: 32767
13
# TIME_WAIT 连接数: 60000
14
# 端口利用率: 183.1%
15
# 结论: 端口耗尽，新连接无法建立

1
连接数 = CPU 核心数 × (1 + 等待时间/计算时间)

1
# 连接池大小计算示例
2
import math
3

4
# 假设数据库服务器配置
5
cpu_cores = 8  # CPU 核心数
6
disk_count = 2  # SSD 数量
7

8
# PostgreSQL 公式
9
# connections = ((core_count * 2) + effective_spindle_count)
10
pg_connections = cpu_cores * 2 + disk_count
11
print(f"PostgreSQL 建议连接数: {pg_connections}")
12

13
# MySQL 公式考虑 I/O 等待
14
# 假设 I/O 等待时间 / CPU 计算时间 = 5
15
io_wait_ratio = 5
16
mysql_connections = cpu_cores * (1 + io_wait_ratio)
17
print(f"MySQL 建议连接数: {mysql_connections}")
18

19
# 通用公式（更保守）
20
general_connections = cpu_cores * 2 + disk_count
21
print(f"通用建议连接数: {general_connections}")
22

23
# 输出:
24
# PostgreSQL 建议连接数: 18
25
# MySQL 建议连接数: 48
26
# 通用建议连接数: 18

1
// HikariPool 监控
2
HikariPoolMXBean poolProxy = dataSource.getHikariPoolMXBean();
3

4
System.out.println("活跃连接: " + poolProxy.getActiveConnections());
5
System.out.println("空闲连接: " + poolProxy.getIdleConnections());
6
System.out.println("等待线程: " + poolProxy.getThreadsAwaitingConnection());
7
System.out.println("总连接数: " + poolProxy.getTotalConnections());

1
HikariConfig config = new HikariConfig();
2
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
3
config.setUsername("user");
4
config.setPassword("password");
5

6
// 核心配置
7
config.setMinimumIdle(5);
8
config.setMaximumPoolSize(20);
9
config.setConnectionTimeout(30000);
10
config.setIdleTimeout(600000);
11
config.setMaxLifetime(1800000);
12

13
// 性能优化
14
config.addDataSourceProperty("cachePrepStmts", "true");
15
config.addDataSourceProperty("prepStmtCacheSize", "250");
16
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
17

18
HikariDataSource dataSource = new HikariDataSource(config);

1
db, err := sql.Open("mysql", "user:password@/dbname")
2
if err != nil {
3
    log.Fatal(err)
4
}
5

6
// 配置连接池
7
db.SetMaxOpenConns(25)      // 最大连接数
8
db.SetMaxIdleConns(5)       // 最大空闲连接数
9
db.SetConnMaxLifetime(5 * time.Minute)  // 连接最大生命周期
10
db.SetConnMaxIdleTime(time.Minute)      // 空闲连接超时

1
from sqlalchemy import create_engine
2

3
engine = create_engine(
4
    "postgresql://user:password@localhost/mydb",
5
    pool_size=10,          # 连接池大小
6
    max_overflow=5,        # 允许的最大溢出连接
7
    pool_timeout=30,       # 获取连接超时（秒）
8
    pool_recycle=1800,     # 连接回收时间（秒）
9
    pool_pre_ping=True,    # 使用前检查连接
10
)

1
const mysql = require("mysql2");
2

3
const pool = mysql.createPool({
4
  host: "localhost",
5
  user: "user",
6
  password: "password",
7
  database: "mydb",
8
  waitForConnections: true,
9
  connectionLimit: 10, // 最大连接数
10
  queueLimit: 0, // 等待队列无限制
11
  idleTimeout: 60000, // 空闲超时（毫秒）
12
});

1
// 正确使用方式：try-with-resources
2
try (Connection conn = dataSource.getConnection();
3
     PreparedStatement stmt = conn.prepareStatement(sql);
4
     ResultSet rs = stmt.executeQuery()) {
5
    // 处理结果
6
} // 自动关闭资源，归还连接
7

8
// 错误方式：忘记关闭
9
Connection conn = dataSource.getConnection();
10
// ... 操作后忘记关闭
11
// 连接泄漏！

1
// 启用泄漏检测
2
config.setLeakDetectionThreshold(60000); // 60秒未归还视为泄漏
3

4
// 日志输出示例
5
// WARNING - Connection leak detection triggered,
6
// connection has been out of pool for 65000ms

1
// 应用启动时预热连接池
2
@PostConstruct
3
public void warmUpPool() {
4
    int minIdle = dataSource.getHikariPoolMXBean().getIdleConnections();
5
    List<Connection> connections = new ArrayList<>();
6

7
    // 借出所有最小空闲连接
8
    for (int i = 0; i < minIdle; i++) {
9
        connections.add(dataSource.getConnection());
10
    }
11

12
    // 立即归还
13
    for (Connection conn : connections) {
14
        conn.close();
15
    }
16

17
    log.info("连接池预热完成，空闲连接: {}",
18
        dataSource.getHikariPoolMXBean().getIdleConnections());
19
}

1
@PreDestroy
2
public void shutdown() {
3
    if (dataSource instanceof HikariDataSource) {
4
        HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
5

6
        // 等待活跃连接完成
7
        while (hikariDataSource.getHikariPoolMXBean().getActiveConnections() > 0) {
8
            try {
9
                Thread.sleep(100);
10
            } catch (InterruptedException e) {
11
                Thread.currentThread().interrupt();
12
                break;
13
            }
14
        }
15

16
        // 关闭连接池
17
        hikariDataSource.close();
18
    }
19
}

1
// 连接池状态诊断
2
public void diagnosePool(HikariDataSource ds) {
3
    HikariPoolMXBean pool = ds.getHikariPoolMXBean();
4

5
    int active = pool.getActiveConnections();
6
    int idle = pool.getIdleConnections();
7
    int total = pool.getTotalConnections();
8
    int waiting = pool.getThreadsAwaitingConnection();
9

10
    log.info("连接池状态: 活跃={}, 空闲={}, 总数={}, 等待={}",
11
        active, idle, total, waiting);
12

13
    if (waiting > 0) {
14
        log.warn("有 {} 个线程等待获取连接", waiting);
15
    }
16

17
    double usage = (double) active / ds.getMaximumPoolSize() * 100;
18
    if (usage > 80) {
19
        log.warn("连接池使用率 {:.1f}%，考虑扩容", usage);
20
    }
21
}

1
// 泄漏检测与处理
2
public class ConnectionLeakDetector {
3

4
    private static final Map<Connection, StackTraceElement[]> borrowedConnections
5
        = new ConcurrentHashMap<>();
6

7
    public static void trackConnection(Connection conn) {
8
        borrowedConnections.put(conn, Thread.currentThread().getStackTrace());
9
    }
10

11
    public static void reportLeaks() {
12
        for (Map.Entry<Connection, StackTraceElement[]> entry : borrowedConnections.entrySet()) {
13
            System.out.println("疑似泄漏连接:");
14
            for (StackTraceElement element : entry.getValue()) {
15
                System.out.println("\t" + element);
16
            }
17
        }
18
    }
19
}

1
// 1. 预热连接池
2
config.setMinimumIdle(10);  // 预创建连接
3

4
// 2. 限制连接创建速率
5
// HikariCP 通过 connectionTimeout 控制等待
6

7
// 3. 使用信号量限制并发
8
Semaphore semaphore = new Semaphore(maxConcurrentCreates);
9

10
public Connection getConnection() throws SQLException {
11
    if (!semaphore.tryAcquire(5, TimeUnit.SECONDS)) {
12
        throw new SQLException("获取连接超时");
13
    }
14
    try {
15
        return dataSource.getConnection();
16
    } finally {
17
        semaphore.release();
18
    }
19
}

1
-- 检查 MySQL 连接数配置
2
SHOW VARIABLES LIKE 'max_connections';
3

4
-- 检查当前连接数
5
SHOW STATUS LIKE 'Threads_connected';
6

7
-- 检查最大已用连接数
8
SHOW STATUS LIKE 'Max_used_connections';

1
数据库 max_connections >= 应用实例数 × 每实例最大连接数 + 预留连接数

1
# 连接数规划
2
app_instances = 5  # 应用实例数
3
pool_max_size = 20  # 每实例最大连接数
4
reserved = 10       # 预留连接（管理员、监控等）
5

6
db_max_connections = app_instances * pool_max_size + reserved
7
print(f"数据库建议 max_connections: {db_max_connections}")
8
# 输出: 数据库建议 max_connections: 110