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

Question

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

该计算机是一台Windows2008Xeon企业服务器，具有32 Gb内存和双至强CPU。以下是更多的环境信息：

    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)

我对发生了什么感到有点不知所措。任何建议都是非常有用的！

Answer 1

为您的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请求，您当前的配置是100倍的默认值，这会导致服务器超出可用RAM。您可以在MySQL的错误日志中检测到这一点。

如需更多建议，请查看我的个人资料，网络个人资料的联系信息，并通过Skype联系。