likes
comments
collection
share

数据连接已满，导致新连接无法成功

作者站长头像
站长
· 阅读数 67

个人项目中经常会把每个项目的平台部署成开发、测试环境，而数据库就有可能是多个平台共用一个了，现在基本上都是用的微服务架构，那么数据库连接就不够用了。

我们用的是MySQL数据库，最近遇到了这个尴尬的问题，本地修改了代码启动的时候经常会连不上数据库既然连接太多，要么减少连接，要么扩大最大可连接数，

经过查询，MySQL 数据库 的默认最大连接数是经常设置在151的默认值，而最大连接数可以达到16384个

对应报错信息一般为：

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

too many connections

  1. 查看数据库当前连接信息，可以看到连接数据库的进程id，ip,用户名，连接的数据库，连接状态，连接时长等如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。

    1. SHOW FULL processlist;

    2. 在MySQL中，使用 SHOW FULL PROCESSLIST; 命令可以显示系统中所有当前运行的线程，包括每个线程的状态、是否锁定等信息。这个命令输出的表格中包含的字段具有以下含义：

      1. Id: 线程的唯一标识符。这个ID可以用来引用特定的线程，例如，在需要终止一个特定的线程时可以使用 KILL 命令。
      2. User: 启动线程的MySQL用户。
      3. Host: 用户连接到MySQL服务器的主机名和端口号，显示格式通常是 host_name:port。
      4. db: 当前线程所在的数据库。如果线程没有使用任何数据库，这一列可能显示为NULL。
      5. Command: 线程正在执行的命令类型，例如 Query、Sleep、Connect 等。
      6. Time: 命令执行的时间，以秒为单位。对于 Sleep 状态，这表示线程处于空闲状态的时长。
      7. State: 线程的当前状态，提供了正在执行的命令的额外信息。这可以是 Sending data、sorting result、Locked 等。
      8. Info: 如果线程正在执行查询，则这一列显示具体的SQL语句。对于其他类型的命令，这一列可能为空或显示为NULL。

      当 Command 列显示为 Sleep 时，这意味着该线程当前没有执行任何查询，只是在连接池中等待下一个查询命令。通常，应用程序执行完一个查询后，连接可能会保持打开状态而不是立即关闭，以便可以重用该连接执行后续的查询。在这种状态下，线程不会使用服务器资源来处理任何数据，但仍占用一个连接槽。如果看到很多线程处于 Sleep 状态且持续时间较长，这可能是一个优化点，例如，通过调整应用逻辑或连接池设置来减少空闲连接的数量。

  2. 查询当前最大连接数和超时时间

    1. # 查看最大连接数
show variables like '%max_connections%';
# 查看非交互式超时时间 单位秒
show variables like 'wait_timeout';
# 查看交互式叫号时间 单位秒
show variables like 'interactive_timeout';

      1. max_connections:

        1. max_connections 参数定义了数据库服务器能够同时接受的最大客户端连接数。当达到这个限制时，任何新的尝试连接的客户端将会收到一个错误，通常是“Too many connections”。
        2. 默认值通常基于系统的能力和配置，但经常设置在151的默认值。这个值可以根据服务器的硬件资源（如CPU和内存）和负载要求进行调整。

      2. mysqlx_max_connections:

        1. mysqlx_max_connections 参数是专门为MySQL的X协议（一种扩展的协议，支持更复杂的操作，如CRUD操作和实时通知）设定的最大连接数。X协议使得开发者能够使用NoSQL风格的接口与数据库交互。
        2. 默认值通常较小，因为X协议的使用还不如传统SQL协议普遍。这个参数允许你独立于max_connections控制通过X协议可能的连接数。

      3. wait_timeout：

        1. wait_timeout 设置的是非交互式（非控制台）客户端连接在变成非活动状态后，在被自动关闭之前等待的秒数。非交互式连接通常指的是通过网络或API等进行的数据库连接，如应用程序服务器到数据库的连接。
        2. 默认值通常较长，如8小时（28800秒），但这可以根据需要进行调整，特别是在连接数资源受限的环境中。

      4. interactive_timeout：

        1. interactive_timeout 适用于MySQL服务器与客户端进行交互式会话时的连接超时设置。交互式会话通常是指用户通过MySQL命令行客户端或类似工具直接连接并操作数据库。
        2. 这个超时值只会在MySQL服务器识别连接为交互式时应用。它的默认值也通常是8小时。

  3. 修改最大连接数和超时时间

    1. SQL直接改

      1. # 重启后失效 这里直接设置1000
SET GLOBAL max_connections = 1000;
# 设置全局 非交互连接 超时时间 单位秒
SET GLOBAL wait_timeout = 300;

    2. 配置文件改

      1.     MySQL的配置文件通常是 my.cnf（在Linux系统中）或 my.ini（在Windows系统中）。你应该在 [mysqld] 部分中设置这些参数
      2.     在Linux系统上，MySQL的配置文件一般位于以下几个路径之一：
      3. /etc/my.cnf
      4. /etc/mysql/my.cnf
      5. /var/lib/mysql/my.cnf
      6.     具体位置可能会根据不同的Linux发行版和MySQL安装方式有所不同。你可以使用 find 命令来搜索这个文件，例如：
      7. sudo find / -name my.cnf

    3.   找到到文件后,将这些值修改为下列的值 这里直接设置1000

    4. [mysqld]
max_connections = 1000

wait_timeout = 300

    5. docker情况

      1. 使用Docker命令行参数
      2.     你可以在运行MySQL容器时通过Docker命令行直接设置配置参数，例如：
      3. docker run -d \
  -p 3306:3306 \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -e MYSQL_DATABASE=mydatabase \
  mysql:tag --max-connections=1000 --wait-timeout=300
      4.     在这个例子中，--max-connections=1000 是作为命令行参数传递给MySQL服务器的。
      5. 修改配置文件并挂载
      6.     如果你需要修改多个配置项或者希望使用配置文件来管理设置，可以创建一个自定义的 my.cnf 文件，然后在启动容器时将它挂载到容器中适当的位置。例如：
      7. docker run -d \
  -p 3306:3306 \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -v /path/to/your/custom/my.cnf:/etc/mysql/my.cnf \
  mysql:tag

  4. 修改完后再查一遍看看有没有改成功

    1. # 查看最大连接数
show variables like '%max_connections%';
# 查看非交互式超时时间
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

  5. 拓展

    1. wait_timeout 变量分为全局级别和会话级别

      •     执行 SET GLOBAL wait_timeout = 300;后,
      •     使用执行 show variables like 'wait_timeout'; 发现并没有改变
      •     是因为在MySQL中，当你执行 SET GLOBAL wait_timeout = 300; 这条命令时，理论上应该是会设置全局的 wait_timeout 值为300秒。在查询 wait_timeout 时，没有指定是查询全局变量，可能会返回会话级的值。会话级的 wait_timeout 并没有被改变。尝试使用以下命令来查看全局设置：
      • # 查看全局变量
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
# 与之对应的，查看会话级别的变量可以使用：
SHOW SESSION VARIABLES LIKE 'wait_timeout';

    2. 全局变量和会话变量的区别

      • 全局变量：

        1. 全局变量对服务器上所有当前会话和未来会话都有效。
        2. 当你设置一个全局变量时，它的值会影响所有新建的连接。然而，对于已经存在的连接，全局变量的更改通常不会影响它们，除非这些连接被重新启动或者明确地重新读取全局设置。
        3. 通过 SET GLOBAL 命令修改全局变量，或者在服务器的配置文件中设置并重新启动服务器。

      • 会话变量：

        1. 会话变量只对当前连接的会话有效，并且当会话结束时，会话变量的设置就会失效。
        2. 修改会话变量的命令是 SET SESSION 或者简单的 SET，它不会影响其他会话或连接。
        3. 每个新的会话都会从当前的全局设置中继承变量的值，但在会话中对这些变量的修改不会影响到其他会话。

      • 关于是否改变全局变量，这取决于你试图解决的具体问题：

        • 如果你需要修改的设置应该对所有新的连接生效，例如，修改 wait_timeout 来减少空闲连接超时，那么修改全局变量是合适的。这样，所有新建立的连接都会采用新的超时设置。

        • 然而，如果你需要立即影响当前活动的会话，你必须在每个会话中单独设置会话变量。这在某些操作中可能是必需的，比如调整当前事务的隔离级别或者调试中动态改变某些性能调优参数。

        • 因此，如果改变是为了长期或持久的配置调整，修改全局变量通常是正确的做法。但如果需要对当前会话立即生效的改变，应该使用会话变量。

转载自:https://juejin.cn/post/7361056871673446437
评论
请登录