又是美创科技对客户一个季度一次的现场巡检，期待数据库能跑的又快又稳，毕竟这是对DBA最大的馈赠了。
结果不遂人意发现在错误日志内存在大量的如下报错：
查看当前数据库的状态值：
查看数据库关于数据库会话的关键参数：
| 数据库环境及相关参数 | |
|---|---|
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
| max_connections | 151 |
| net_write_timeout | 60 |
| net_read_timeout | 30 |
可见，自数据库启动，440万尝试连接中，近140万会话异常退出，近200万会话未能正常连接到数据库环境。而排查错误日志中该报错无时间规律，同时客户反馈在业务层面，经常有长连接断开的现象。
TIP：
首先我们通过官方文档来了解Aborted_clients和Aborted_connects两个状态变量的代表意义，以及哪些情况或因素会导致这些状态变量变化呢？
造成Aborted_connects状态变量增加的可能原因：
1.客户机试图访问数据库，但没有数据库的特权。
2.客户端使用了错误的密码。
3.连接包不包含正确的信息。
4.获取一个连接包需要的时间超过connect_timeout秒。
造成Aborted_clients 状态变量增加的可能原因：
1.程序退出前，客户机程序没有调用mysql_close()。
2.客户端睡眠时间超过了wait_timeout或interactive_timeout秒。
3.客户端程序在数据传输过程中突然终止。
简单来说即：数据库会话未能正常连接到数据库，会造成Aborted_connects变量增加。数据库会话已正常连接到数据库但未能正常退出，会造成Aborted_clients变量增加。
根据错误日志中报错：
Got timeout reading communication packets
出现如上错误，基本上可判断为数据库认证超时导致，或者业务线程异常退出。
客户反馈并无相关业务客户端异常退出等操作或现象。
可简单判断会话超过interactive_timeout/ wait_timeout限制时间(28800)导致会话被数据库杀掉，跟应用沟通之后，应用确认其业务逻辑会话均为长连接，不会主动进行断开操作。如上可初步解释为何Aborted_clients状态变量会如此之高。
那又该如何解释Aborted_connects这个状态变量如何之高？
能使该状态变量增加的几种可能性，我们依次来确认排查。
1.客户机试图访问数据库，但没有数据库的特权。
2.客户端使用了错误的密码。
3.连接包不包含正确的信息。
4.获取一个连接包需要的时间超过connect_timeout秒。
关于1、2、3这三点，可统一解释为 用户/密码/权限错误导致无法正常连接到数据库。这几个错误不会在错误日志中报该错误(Got timeout reading communication packets),错误日志中也不存在(Access denied for user)该类错误，且业务能正常运行。这样就能排除这三点的可能性。
那唯一可能就是由于连接认证超时时间超过connect_timeout秒，数据库层面connect_timeout参数设置为默认的10s。根据官方文档解释：
10s基本上能够支持业务使用。
那还有什么可能呢？
跟客户确认之后，了解到应用是通过MySQL Router连接到数据库服务器。检查Router 参数文件配置，发现如下参数设置
发现在Router的配置中connect_timeout 配置为3s，那是否可能由于客户端连接数据库的认证超过该限制导致。
因此建议修改Router配置文件中该参数，然后运行一段时间后是否情况得到一定的改善。
后续排查往网络方向排查，简单可通过客户端长ping数据库服务端，查看网络是否存在波动现象。
TIP:
根据官方文档中介绍，还可能是由于网络或者硬件层面的问题造成这个问题。
1. max_allowed_packet变量值太小，或者查询需要的内存比分配给mysqld的内存多。
2. 在Linux中使用以太网协议，包括半双工和全双工。一些Linux以太网驱动程序有这个bug。您应该通过在客户机和服务器机器之间使用FTP传输一个大文件来测试这个bug。如果传输以突发-暂停-突发-暂停模式进行，那么您正在经历一种Linux双工综合征。将网卡和集线器的双工模式切换到全双工或半双工模式，并测试结果以确定最佳设置。
3. 线程库中导致读取中断的问题。
4. 错误的TCP / IP配置。
5. 有故障的以太网、集线器、交换机、电缆等等。只有通过更换硬件才能正确诊断。
下面对各类Aborted connection的可能性进行一定的测试与分析：
测试环境说明：MySQL5.7
| 测试环境及相关参数 | |
|---|---|
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
| max_connections | 151 |
| net_write_timeout | 60 |
| net_read_timeout | 30 |
注：每次测试前均重启数据库重置状态值，方便后续比较
测试一：错误密码、错误用户
错误用户：数据库不存在该用户。
查看数据库内状态值：
查看错误日志：
测试二：超时参数
当前数据库wait_timeout 及interactive_timeout均为默认的28800，下面调整这两个参数，测试对数据库连接的行为影响。
该实验同时修改两个参数为10：
查看数据库内状态值：
查看错误日志：
测试三：最大连接数
当前数据库max_connections参数默认为151，下面调整改参数，测试对数据库连接的行为影响。
当开启第四个连接会话，报如下错误：
查看数据库内状态值
此时错误日志无变化。
测试四
第三方工具SQLyog select结果没有出来的时候选择停止则出现：
查看数据库内状态值：
此时错误日志无变化。
结论：
1.建议业务操作结束后使应用程序逻辑以正确关闭连接，以短连接替代长连接。
2.确保max_allowed_packet的值足够高，并且客户端没有收到“数据包太大”消息。
3.确保客户端应用程序不中止连接。
4.检查是否启用了skip-name-resolve，检查主机根据其IP地址而不是其主机名进行身份验证。
5.尝试增加MySQL的net_read_timeout和net_write_timeout值，看看是否减少了错误的数量。
参考文献
https://dev.mysql.com/doc/refman/5.6/en/communication-errors.html
美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。
来源：freebuf.com 2020-06-04 12:10:25 by: database
请登录后发表评论
注册