如何有效利用MYSQL的连接数

连接数配置2500~3000 依然发现连接不够用？

-- 查看当前最大连接数
SHOW VARIABLES LIKE 'MAX_CONNECTIONS';
-- 查看当前总链接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看当前进程明细
SHOW PROCESSLIST;

合理设置以下参数：

1. MySQL 的参数设置

MySQL 有两个关键参数控制连接的空闲超时时间：

wait_timeout

• 作用：控制非交互式连接（如通过 JDBC、ODBC 等程序连接的客户端）的空闲超时时间。

• 默认值：28800 秒（8 小时）。

• 建议值：根据业务需求设置，通常可以设置为 600 秒（10 分钟）。

interactive_timeout

• 作用：控制交互式连接（如通过 MySQL 命令行客户端或 PHPMyAdmin 等工具连接的客户端）的空闲超时时间。

• 默认值：28800 秒（8 小时）。

• 建议值：与 wait_timeout 保持一致，通常设置为 600 秒（10 分钟）。

设置方法

• 临时设置（重启后失效）：

  sql

  复制

  SET GLOBAL wait_timeout = 600;
  SET GLOBAL interactive_timeout = 600;

• 永久设置（修改配置文件）：
  在 MySQL 配置文件（my.cnf 或 my.ini）中添加或修改以下内容：

  ini

  复制

  [mysqld]
  wait_timeout = 600
  interactive_timeout = 600

  然后重启 MySQL 服务。

---

2. HikariCP 连接池的参数设置

HikariCP 是一个高性能的连接池，以下是关键参数及其与 MySQL 超时设置的关联：

max-lifetime

• 作用：控制连接在连接池中的最大存活时间。超过该时间的连接会被销毁并重新创建。

• 建议值：设置为小于 MySQL 的 wait_timeout 和 interactive_timeout 的值。例如，如果 MySQL 的超时时间是 600 秒，可以将 max-lifetime 设置为 540000 毫秒（9 分钟）。

• 单位：毫秒。

idle-timeout

• 作用：控制连接在连接池中的最大空闲时间。超过该时间的空闲连接会被销毁。

• 建议值：根据业务需求设置，通常可以设置为 60000 毫秒（1 分钟）。

• 单位：毫秒。

connection-timeout

• 作用：控制从连接池获取连接的最大等待时间。如果超时，会抛出异常。

• 建议值：通常设置为 30000 毫秒（30 秒）。

• 单位：毫秒。

minimum-idle

• 作用：控制连接池中保持的最小空闲连接数。

• 建议值：根据业务需求设置，通常可以设置为 10~50。

• 注意：设置过高会导致资源浪费，设置过低可能影响性能。

maximum-pool-size

• 作用：控制连接池的最大连接数。

• 建议值：根据业务需求和数据库性能设置，通常可以设置为 100~200。

---

3. 配置示例

以下是一个完整的 HikariCP 配置示例（以 Spring Boot 的 application.yml 为例）：

yaml

复制

spring:datasource:hikari:connection-test-query: SELECT 1  # 连接测试查询connection-timeout: 30000       # 连接获取超时时间（30 秒）maximum-pool-size: 150          # 最大连接数max-lifetime: 540000           # 连接最大存活时间（9 分钟）minimum-idle: 10               # 最小空闲连接数idle-timeout: 60000            # 空闲连接超时时间（1 分钟）

---

4. 参数之间的关系

• MySQL 的 wait_timeout 和 interactive_timeout：控制 MySQL 服务器关闭空闲连接的时间。

• HikariCP 的 max-lifetime：控制连接在连接池中的最大存活时间。

• HikariCP 的 idle-timeout：控制连接在连接池中的最大空闲时间。

关键点：

• max-lifetime 必须小于 MySQL 的 wait_timeout 和 interactive_timeout，否则连接池中的连接可能会被 MySQL 关闭，导致应用使用无效连接。

• idle-timeout 可以适当设置，以定期清理空闲连接，避免资源浪费。

---

5. 验证配置

• 使用 SHOW VARIABLES LIKE 'wait_timeout'; 和 SHOW VARIABLES LIKE 'interactive_timeout'; 确认 MySQL 的超时设置。

• 使用 SHOW PROCESSLIST; 查看 MySQL 的连接状态，确认是否有大量 Sleep 状态的连接。

• 监控 HikariCP 的连接池状态，确保连接池中的连接数在合理范围内。

---

6. 总结

• MySQL 设置：

  ini

  复制

  wait_timeout = 600
  interactive_timeout = 600

• HikariCP 设置：

  yaml

  复制

  hikari:max-lifetime: 540000idle-timeout: 60000connection-timeout: 30000minimum-idle: 10maximum-pool-size: 150

通过以上配置，可以确保 MySQL 和 HikariCP 的参数设置是合理的，避免连接被关闭或连接池使用无效连接的问题。

wait_timeout 和 interactive_timeout 通常需要同时设置，因为它们分别控制不同类型的连接空闲超时时间。以下是具体原因和建议：

---

1. wait_timeout 和 interactive_timeout 的区别

• wait_timeout:

  • 适用于非交互式连接（如通过 JDBC、ODBC 等程序连接的客户端）。

  • 控制连接在空闲状态下的最大存活时间。

• interactive_timeout:

  • 适用于交互式连接（如通过 MySQL 命令行客户端或 PHPMyAdmin 等工具连接的客户端）。

  • 控制连接在空闲状态下的最大存活时间。

---

2. 为什么要同时设置？

• 如果只设置 wait_timeout，交互式连接的空闲超时时间仍然会使用默认的 interactive_timeout 值。

• 如果只设置 interactive_timeout，非交互式连接的空闲超时时间仍然会使用默认的 wait_timeout 值。

• 为了确保所有类型的连接都遵循相同的空闲超时规则，建议同时设置这两个参数。