时间戳

首页 > 解决方案 > MySQL 5.7 间歇性最大连接数错误

mysql - MySQL 5.7 间歇性最大连接数错误

问题描述

我在使用 MySQL 5.7 时遇到“连接过多”问题，导致服务崩溃。max_connections 系统变量设置为 1000，平均有 +/- 250 个会话/线程，因此达到最大连接数很奇怪。该问题主要出现在某些工作日晚上的晚上 10 点到 11 点之间。

该机器是具有 32 Gb RAM 和双 Xeon CPU 的 Windows 2008 R2 Enterprise Server。以下是更多环境信息：

    Variable            |   Max Connection Memory
-------------------------------------------------
join_buffer_size        |       250.00 MB
read_buffer_size        |       62.50 MB
read_rnd_buffer_size    |       250.00 MB
sort_buffer_size        |       250.00 MB
max_connections = 1000  |       812.50 MB

Timeouts                    |   VALUE
-------------------------------------------
connect_timeout             |   10
delayed_insert_timeout      |   300
have_statement_timeout      |   YES
innodb_flush_log_at_timeout |   1
innodb_lock_wait_timeout    |   50
innodb_rollback_on_timeout  |   OFF
interactive_timeout         |   28800
lock_wait_timeout           |   31536000
net_read_timeout            |   30
net_write_timeout           |   60
rpl_stop_slave_timeout      |   31536000
slave_net_timeout           |   60
wait_timeout                |   28800
-------------------------------------------
max_allowed_packet          | 33554432
slave_max_allowed_packet    | 1073741824

示例日志文件

Aborted connection 27933 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 26736 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 27200 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 27356 to db: 'wms_mysql' user: 'mysql' host: 'eifprdrds01.domain.com' (Got an error reading communication packets)
Aborted connection 29119 to db: 'wms_mysql' user: 'mysql' host: 'pc286.domain.com' (Got an error reading communication packets)
Aborted connection 16274 to db: 'wms_mysql' user: 'mysql' host: 'pc828.domain.com' (Got timeout reading communication packets)
Aborted connection 24916 to db: 'wms_mysql' user: 'mysql' host: 'pc830.domain.com' (Got an error reading communication packets)
Aborted connection 19357 to db: 'wms_mysql' user: 'mysql' host: 'pc830.domain.com' (Got an error reading communication packets)
Aborted connection 19343 to db: 'wms_mysql' user: 'mysql' host: 'pc830.domain.com' (Got an error reading communication packets)

我对正在发生的事情有些茫然。任何建议都会非常有用！

标签： mysqlconnectionmax

解决方案

为您的 my.ini [mysqld] 部分考虑的建议

join_buffer_size=256K  # from 250M to reduce RAM request
read_buffer_size=128K  # from 62.50M to reduce RAM request
read_rnd_buffer_size=256K  # from 250M to reduce RAM request
sort_buffer_size=2M  # from 250M to reduce RAM request

前 4 个是每个连接的 RAM 请求，并且您当前的配置对于它们中的大多数是 100X 默认值，并导致服务器超出可用 RAM。您可以在 MySQL 的错误日志中检测到这一点。

如需其他建议，请查看我的个人资料、网络个人资料以获取联系信息并通过 Skype 联系。

推荐阅读