01-MySQL查看连接线程

背景说明

本文使用MySQL 5.7进行分析，系统环境MacBook，讨论验证wait_timeout作用,MySQL默认配置文件/etc/my.cnf

一、MySQL默认配置下的线程连接情况

1、MySQL默认wait_timeout=8hour

show variables like '%wait_timeout%'
image.png

2、查看MySQL线程连接情况

2.1 背景：启动SpringBoot项目（访问DB即可）只配置默认DataSource参数，使用Tomcat8

  • 设置参数
    • username
    • password
    • jdbc_url

2.2 查看线程连接情况

show full processlist
image.png

2.3 线程情况说明

  • SpringBoot项目默认使用Connection Pool连接池
  • 且每个Connection Pool线程数默认10个
  • 图中的Time字段表示command字段标记的状态持续的时间,单位秒
    • 如，Sleep 410s
    • 默认情况下，只要time<8h该链接可以一直存在，即MySQL Server不会关闭/丢掉该链接

二、更改MySQL server wait_timeout默认时间

1、使用配置文件方式更新

  • 编辑/etc/my.cnf文件在mysqld下面添加wait_timeout行,设为60s(一分钟）
  • 重启MySQL
[mysqld]
wait_timeout=60
character-set-server=utf8mb4
default-time-zone = '+8:00'

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
  • 验证更改有效(注意此处要加global）
show global  gvariables like '%wait_timeout%'

2、重新启动SpringBoot应用

2.1 不断执行：show full processlist命令

  • SpringBoot应用起来后，先不去调用页面上的操作，如登录、刷新等
    • 目的：是让SpringBoot应用不做数据库请求的动作，让MySQL wait 60s之后，再进行页面操作，这样就会报wait_timeout Exception
      image.png
image.png
image.png

2.2 结果分析

  • 可以看到在Time达到60s后，SpringBoot线程池的线程连接在MySQL Server端已经被MySQL 关闭，即最后一幅图
  • 连接分两端，MySQLserver单方面因为超过wait_timeout超时就把连接关了
  • 但SpringBoot线程池中连接还保存着，这会导致，Spring应用执行SQL操作时报错
    • 前提：60s内不要进行页面操作
    • 若，60s内页面不断请求数据库，则time字段会不断刷新，刷新为页面请求DB后sleep的时长，这样也可以避免wait_timeout Exception 
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 355,390 milliseconds ago.  The last packet sent successfully to the server was 355,390 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
        at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
        at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:562)
        at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:732)
        at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:671)
        at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:986)
        at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1157)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:947)
        ... 62 more
Caused by: java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(SocketInputStream.java:210)
        at java.net.SocketInputStream.read(SocketInputStream.java:141)
        at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
        at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
        at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)
  • 此时还有一个现象，就是SpringBoot应用连接池连接长久处于close_wait状态
    • 我们知道close_wait状态是Tcp被动关闭连接方所处的状态，即A->B,A要和B断绝夫妻关系，A通知了B说我要关闭了，B想了想说同意也告诉A一声（B告诉之后处于close_wait状态），可是B还等着A回个声，但A再也没有回声，B就一直close_wait 
admindeMacBook-Pro-7:~ 07$ lsof -iTCP |grep mysql
java      17752 qiankai07  166u  IPv6 0xf3d007512bb170e9      0t0  TCP localhost:52540->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  167u  IPv6 0xf3d007512bb13d29      0t0  TCP localhost:52541->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  168u  IPv6 0xf3d007512bb159e9      0t0  TCP localhost:52542->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  169u  IPv6 0xf3d0075121c60569      0t0  TCP localhost:52543->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  170u  IPv6 0xf3d0075121c610e9      0t0  TCP localhost:52544->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  171u  IPv6 0xf3d0075121c5ee69      0t0  TCP localhost:52545->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  172u  IPv6 0xf3d0075121c60b29      0t0  TCP localhost:52546->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  173u  IPv6 0xf3d007511c1cb8a9      0t0  TCP localhost:52547->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  174u  IPv6 0xf3d007511c1cbe69      0t0  TCP localhost:52548->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  175u  IPv6 0xf3d007511c1cb2e9      0t0  TCP localhost:52549->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  176u  IPv6 0xf3d007511c1cdb29      0t0  TCP localhost:52550->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  177u  IPv6 0xf3d007512232ad29      0t0  TCP localhost:52551->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  178u  IPv6 0xf3d007512232e0e9      0t0  TCP localhost:52552->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  179u  IPv6 0xf3d00751201e5b29      0t0  TCP localhost:52553->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  180u  IPv6 0xf3d007511c391e69      0t0  TCP localhost:52554->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  181u  IPv6 0xf3d007511c3912e9      0t0  TCP localhost:52555->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  182u  IPv6 0xf3d007511c392fa9      0t0  TCP localhost:52556->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  183u  IPv6 0xf3d007511c3918a9      0t0  TCP localhost:52557->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  184u  IPv6 0xf3d00750ff44f9e9      0t0  TCP localhost:52558->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  185u  IPv6 0xf3d00750ff450569      0t0  TCP localhost:52559->localhost:mysql (CLOSE_WAIT)

三、总结

就算以MySQL默认 wait_timeout值（8hour)有时候也会报wait_timeout异常** 解决wait_timeout异常

1. 可以将wait_timeout设为更高值

  • Linux系统最高1年

2. 使用阿里德鲁伊Druid作为数据库连接池

3. 使用spring.datasource.testWhileIdle=true字段

  • 默认会在3、4s间隔刷新sleep线程，是sleep 线程Time字段最长为3、4 秒就不会超过wait_timeout的60s了
    • 页面不进行操作也会刷新sleep时长
image.png

流程总结

  • client（SpringBoot 连接池）连接处于空闲状态（（没有执行select等操作））的时长超过了MySQL设置的wait_timeout时间，MySQL单方面就把连接给关闭了，连接池再用这个关闭的连接做事情就报错
    • MySQL关闭连接后，Java连接池的连接都处于close_wait状态,而不是一开始的establish状态，本质是SpringBoot是用这些close_wait状态做事的所以就报错了
    • 从另一方面看，MySQL的wait_timeout若设为一个较大值，Java连接池的线程一直处于establish状态，随时执行操作而不报错**

testWhileIdel会保持连接新鲜程度

四、附-常用命令

#Mac MySQL重启
#启动MySQL服务
sudo /usr/local/[MySQL](http://lib.csdn.net/base/14 "undefined")/support-files/mysql.server start
#停止MySQL服务
sudo /usr/local/mysql/support-files/mysql.server stop
#重启MySQL服务
sudo /usr/local/mysql/support-files/mysql.server restart

#Mac取代netstat方式查看tcp,可以用grep做很多事
lsof -iTCP |grep mysql
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 158,233评论 4赞 360
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,013评论 1赞 291
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,030评论 0赞 241
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,827评论 0赞 204
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,221评论 3赞 286
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,542评论 1赞 216
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,814评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,513评论 0赞 198
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,225评论 1赞 241
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,497评论 2赞 244
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,998评论 1赞 258
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,342评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,986评论 3赞 235
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,055评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,812评论 0赞 194
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,560评论 2赞 271
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,461评论 2赞 266