我在MySQL5.7和« 连接太多» 导致服务崩溃。max\u connections系统变量设置为1000,平均每个线程有+/-250个会话,因此达到max connections是很奇怪的。这个问题主要出现在晚上10点到11点的某些工作日。
这台机器是一台Windows2008R2企业服务器,带有32GB ram和双xeon 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)
我对发生的事有点不知所措。任何建议都是非常有用的!
1条答案
按热度按时间qcbq4gxm1#
为my.ini[mysqld]部分考虑的建议
前4个是每个连接的ram请求,您当前的配置对大多数请求都是100x默认值,这会导致服务器超过可用ram。您可以在mysql的错误日志中检测到这一点。
如需其他建议,请查看我的个人资料、联系信息的网络资料并通过skype联系。