诡异的”慢查询“

       早上接到研发的通知，说数据库有大量的慢查询，并把调用的时间发过来，查询时间基本在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以内，没有出现忽高忽低的情况了。         第二天研发反馈，以前半小时一次的慢查询告警消失了。诡异的慢查询终于结束了。