ITPub博客

诡异的”慢查询“

原创 MySQL 作者:zlingyi 时间:2018-08-10 09:33:00 0 删除 编辑

       早上接到研发的通知,说数据库有大量的慢查询,并把调用的时间发过来,查询时间基本在0.5s以上。通知他们把sql发过来,打开数据库连接,执行sql,发现sql执行很快,执行时间不到0.1s,让他们排查下网络问题。

       过了段时间接到通知,网络无任何问题,怀疑是不是dns问题导致,使用本地连接和远程连接方式执行sql。


time mysql -S mysql.sock -uxxx-p'xxx' -e "select 1"

time mysql -h xx.xx.xx.xx -P xxxx-u xxx-p'xxx' -e "select 1"

       发现查询差不多,大部分的查询时间都需要0.5s左右,少部分在0.1s不到。排除dns的问题。同事确认数据库的连接确实有问题。

       通过对比发现,其他的实例均没有这种情况,在这台物理机上还有一个其他实例,访问基本在0.1s不到,只有极少部分达到0.5s,怀疑是配置参数问题,将两个实例的参数文件show global status;show global variables;做对比,没有找到明显异常。暂时陷入僵局。

        由于是数据库的连接问题,领导要求必须解决,我们再次思考,因为打开数据库的连接执行sql很快,但是像上面那样执行sql却很慢,怀疑是数据库的线程池的问题。查找相关线程池的资料。

客户端连接服务器线程,对两者进行映射,且对应关系不固定 ,服务器线程负责执行从客户端传来的sql

基本单位为线程组,每组包含一个监听线程和执行线程(有可能多个),前者负责接受客户端传来的sql,后者负责执行;

当接受到新sql时,如果此时没有其他sql则由监听线程立即执行,此期间该组暂时无监听,如果sql执行时间过长,则线程池为其分配一个新的监听线程;否则排队等待;

        mysql的线程池(thread pool)默认有三种模式:one-thread-per-connection,pool-of-threads,和no-thread

Thread_pool_size为线程组的数量,默认为cpu核数,Threads_running为正常运行的线程数量,查看当前库的thread情况:

        Threads_running明显超出Thread_pool_size值,由于thread pool最初设计的目标是保持一定数量的线程处于“ACTIVE”状态,具体的实现方式就是控制thread group的数量和thread group内部处于"ACTIVE"状态的thread的数量。控制thread group内部的ACTIVE状态的数量,方法就是最大限度地保证处于ACTIVE状态的线程个数是1。很显然当前thread group中有一个处于WAITING状态的thread了,如果再启用一个新的线程并且处于ACTIVE状态,刚才的线程由WAITING变为ACTIVE状态时,此时将会有2个“ACTIVE”状态的线程,和最初的目标似乎相背,但显然也不能让后续就绪的socket一直等待下去,那应该怎么处理?

 那么此时需要一个权衡了,提供了这样的一个方法:对正在ACTIVE或WAITING状态的线程启用一个计数器,超过计数器后将该thread标记为stalled,然后thread group创建新的thread或唤醒sleep的thread处理新的sokcet,这样将是一个很好的权衡。超时时间该参数thread_pool_stall_limit来决定,默认是500ms。

       终于找到为什么查询时间经常在0.5s的原因了!!振奋人心的消息。找到原因就好办了,mysql5.7该参数默认为6ms,生产是5.6,默认为500ms,我们可以将其改为10ms,这样就强迫被老的sql更快被标记为stalled,然后创建新的线程来处理新连接。

        网上也查看了其他的资料,据称阿里的数据库的thread_pool_stall_limit参数值也10ms,我们再次测试,连接查询都在0.1s以内,没有出现忽高忽低的情况了。

        第二天研发反馈,以前半小时一次的慢查询告警消失了。诡异的慢查询终于结束了。





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28686045/viewspace-2199616/,如需转载,请注明出处,否则将追究法律责任。

上一篇: redis在线加密
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-12-22

  • 博文量
    76
  • 访问量
    97683