简单的SQL也慢？数据库端到端性能问题解决方案的讨论）会造成一定的可用性损失。本文将从这几年遇到的一些性能问题中选取一个疑难案例，探讨端到端数据库性能问题的解决方案，为DBA同学在解决类似问题时提供参考。问题描述一段时间内不断有开发同学反馈在线申请获取数据超时。通过CAT监控系统发现这些应用的SQL99line比较高，在一定程度上影响了相应业务的QoS，比如达不到最高99.99%的业务可用性（超时定义为不可用）).这些问题出现在很多业务场景中，是一个普遍的问题。通过CAT监控系统、SQL样本、慢查询系统等，我们发现这类SQL有以下特点：基本上是基于主键或唯一键的简单查询，查询后的结果集而且扫描的行数都比较少；查询表的数据总量也很小，最小的表也只有几千行；时间达到数百ms，甚至1s；这种类型的SQL是不会记录在数据库的慢日志中的。下图是CAT相关监控数据的示例。以服务xxx-service为例：在99line的监控数据中，很多SQL的返回时间超过100ms。2016年9月6日SQL绝对数：3788，具体到某条SQL，甚至达到了929ms。FB_Coach的表结构如下：最多可以看到641条记录和一个联合索引。profile分析为了定位原因，需要通过排除法找出SQL慢在哪个阶段，从而缩小范围。接下来我们分析一下慢SQL的耗时。从下图可以看出，时间主要由三部分组成：AppServer：发出SQL请求的时间，接收返回结果的时间Network：SQL请求包在网络上花费的时间以及查询结果MySQLServer：将SQL发送到查询结果整个过程所花费的时间我们可以使用抓包工具获取每个阶段所花费的时间，从而定位到哪个阶段比较慢。解题思路迭代思路1：确认哪个进程耗时最多方法：分别在APPServer和MySQLServer上部署TcpDump抓包工具，获取四个监控点数据包的“到达时间”。为方便起见，将以下4条Wireshark分析结果（分析TcpDump抓取的日志）分别标记为4个方向：APPServer发送SQL（左上）MySQLServer接收SQL（右上）MySQLServer发送查询（右下）APPServerAfter收到查询结果（左下），从数据可以看出时间主要花在了MySQL内部。具体时间为22.569285000-21.962634000=0.6066509999999994（秒），约606ms。抓包结果：MySQLServer端慢。思路二：一条SQL进入MySQLServer，到查询结果输出，经过了哪些阶段？方法：梳理一下MySQL内部SQL查询的过程，使用排除法定位问题。让我们拿出经典图片来谈谈事情。下面的基础知识主要来自《高性能MySQL》，“把它带到教义上”。首先可以看到MySQL主要有连接/线程处理、MySQLServer层、存储引擎层三个组成部分。最上层主要是连接处理、授权认证、安全等；第二层包括查询解析、分析、优化（这三个是解决问题最关心的）、缓存管理、所有内置函数、存储过程、触发器、视图，好像有点牵强；第三层包含主要的存储引擎层，MySQLServer层（第二层）通过“存储引擎API”从存储引擎层存储和提取数据。该层主要与数据存储有关。接下来通过客户端请求查询数据，看看MySQL主要做什么。每个client（可以理解为App负责连接数据库的组件，我们称之为DAL）在连接到MySQL服务器进程后都会有一个线程，这个连接的所有查询都会在这个线程中执行，并且服务器将同时缓存线程。减少创建或销毁线程的开销和频繁的上下文切换。当客户端连接到MySQL服务器时，服务器会分配一个线程，然后进行授权认证。认证通过后，MySQL会开始解析SQL查询，并创建一个内部数据结构（解析树），然后对其进行优化，***调用存储引擎API获取或存储需要的数据，并***返回查询结果给客户端。通过上面的“背书”，我们对一个SQL请求的执行过程有了一个大概的了解，那么慢在哪个阶段呢？根据《慢SQL特征》第4项“数据库慢日志中没有记录这条SQL”，可以排除出现慢SQL的阶段。MySQL慢日志用于记录SQL执行过程所花费的时间。记录的时间是从“SQL解析”到“存储引擎”返回数据的整个过程，所以可以排除SQL在第二层和第三层慢，所以只能在顶层花费时间？跟线程有关？结果：MySQL线程管理很可能很慢。思路三：创建线程是不是很慢？线程缓存不够，需要频繁创建线程？方法：查看当时数据库的status值，可以看到当时空闲线程很多，监控图也没有抖动，所以线程创建不频繁。慢SQL产生时，空闲线程较多，大量线程未创建。那么问题出现在线程相关的环节呢？首先列出所有与线程相关的参数。thread_cache_sizethread_concurrencythread_handlingthread_pool_high_prio_modethread_pool_high_prio_ticketsthread_pool_idle_timeoutthread_pool_max_threadsthread_pool_oversubscribethread_pool_sizethread_pool_stall_limitthread_stackthread_statistics大多相关，一目了然。还意识到这些问题是在升级到MySQL5.6时出现的，它引入了线程池功能。结果：看来MySQL5.6的Thread-Pool很可疑。思路四：关闭MySQL5.6的Thread-Pool，确认问题方法：调整MySQL参数thread_handling=pool-of-threads----→thread_handling=One-Connection-Per-Thread。结论：关闭Thread-Pool功能后，慢SQL减少了78%，证明是Thread-Pool的问题。下面是具体证据，以服务xxx-service为例：开启Thread-Pool功能（2016年9月6日数据）。99line比例：超过100ms的SQL较多。慢SQL数量：关闭Thread-Pool功能后3788条（2016年9月13日数据）。99line比例：我看不到任何超过100ms的SQL，都在10ms以内。慢SQL的数量：818，然后关闭Thread-Pool？答案显然是否定的！Thread-Pool是MySQL5.6的一个重要功能，可以保证MySQL数据库在高并发下的性能稳定。思路五：调优Thread-Pool相关参数方法：深入了解Thread-Pool的工作原理，寻找可能导致SQL慢的参数。结果：找到相关参数（thread_pool_stall_limit），效果明显。慢SQL的数量从最初的3788条减少到63条，几乎全部淘汰。以服务xxx-service为例，调整后的效果，2016年9月20日的数据：99lineratio：slowSQL的数量：63ok，效果达到了，总结一下。问题分析1.基本原理在引入Thread-Pool之前，MySQL使用一个线程一个连接。一旦连接增加到一定程度，MySQL的性能就会急剧下降，甚至不堪重负。Thread-Pool的引入将解决上述问题，同时减少MySQL内部的线程数（节省内存）和频繁创建线程的开销（节省CPU）。2.线程池是如何工作的？MySQL内部有一个专门的线程来监控数据库连接请求。当有新的请求到来时，如果使用之前的模型（one-thread-per-connection），主监听器（这是主线程中的监听器，为了避免和线程组中的监听器混淆，我们调用it"Mainlistener")会立即从线程缓存中取出一个线程或者创建一个新的thead来处理连接请求，由该线程完成连接的整个生命周期；而如果采用Thread-Pool模型，连接请求会随机放入一个线程组的队列中（线程池由多个线程组组成），然后线程组中的worker线程从中取出队列并建立连接。一旦连接建立，该连接对应的socket句柄就会与线程组中的监听器相关联，然后连接就会在线程组中完成它的生命周期。接下来说说ThreadGroup。ThreadGroup是Thread-Pool的核心组件，所有的操作都发生在线程组中。Thread-Pool由多个（数量由参数thread_pool_size决定，默认等于cpu个数）thrad组组成。一个连接请求随机绑定一个线程组，每个线程组独立工作，占用一个CPU核。所以线程组会尽量保持一个线程处于ACTIVE状态，最多只有一个，因为太多可能会压垮数据库。线程组中的线程一般有四种状态：TP_STATE_LISTENERTP_STATE_IDLETP_STATE_ACTIVETP_STATE_WAITING线程作为监听器运行时，处于“TP_STATE_LISTENER”状态。它监听所有通过epoll连接到线程组的连接。当套接字就绪时，侦听器将决定是唤醒线程还是自己处理套接字。此时，如果线程组的队列为空，则自己处理socket，并将状态变为“ACTIVE”，然后线程在MySQLServer内部处理“work”。当线程遇到锁或者异步IO（比如数据页读入缓冲池时）这些等待时，线程会通过回调函数告诉线程池将自己标记为“WAITING”状态。这时候，假设队列中有一个新的socketready，我是应该立即创建一个新的线程还是等待上一个线程执行完呢？由于Thread-Pool最初的设计目标是保持一定数量的线程处于“ACTIVE”状态，具体的实现方式是控制线程组的数量和线程内部处于“ACTIVE”状态的线程数量线程组。控制线程组内部ACTIVE状态的个数，方法是尽量保证ACTIVE状态的线程个数为1。很明显，当前线程组中有一个处于WAITING状态的线程。如果启用一个新的线程，并且它处于ACTIVE状态，当线程刚从WAITING变为ACTIVE时，此时会有两个“ACTIVE”状态。线程看似与初衷背道而驰，但显然不能让后续就绪的sockets一直等待下去，那么应该如何处理呢？那么这时候就需要权衡一下，提供了这样一种方法：为ACTIVE或者WAITING状态的线程启用一个计数器，超过计数器后，线程被标记为stalled，然后线程组创建一个新的线程或唤醒睡眠线程来处理新的sokcet，这将是一个很好的权衡。超时时间由参数thread_pool_stall_limit决定，默认为500ms。如果一个线程无事可做，它会保持空闲（TP_STATE_WAITING）一段时间（由thread_pool_idle_timeout参数决定，默认为60秒）然后“自杀”。3.与我们遇到的具体问题相关的要点假设上面提到的从“ACTIVE”状态变为“WAITING”状态的线程（标记为“ThreadA”）执行的“SQL”可能是一个标准的慢SQL（命名为SQLA，执行时间较长），则后续连接请求分配到同一个线程组，则新连接的SQL（名为SQLB）需要等待线程A结束；如果SQLA的执行时间超过500ms，线程组会创建一个新的工作线程来处理SQLB。无论哪种情况，SQLB都会花费大量时间等待线程。此时SQLB就是在CAT监控系统上看到的慢SQL。并且由于SQLA不一定都是慢SQL，SQLB也不会每次都花费更多的时间等待线程，这与我们看到的“一定比例的慢SQL”的现象是一致的。解决方案一旦发现问题，解决方案就很简单了。调整thread_pool_stall_limit=10，让SQLA更快的被强制标记为stalled，然后创建一个新的线程来处理SQLB。以xxx-service为例带来的价值，减少了98.3%的慢SQL，效果明显；该问题的解决方案已惠及数百条产品线，业务可用性超过99.99%。首先，我们分析了慢SQL的特点和SQL花费时间的构成。通过“时间都花在哪里”的通用方法，我们不断缩小问题范围，最终通过排除法将问题锁定在MySQL内部线程。对于MySQL内部线程，我们通过参数“全扫描”发现与MySQL5.6新启用的参数有关，初步判断是Thread-Pool导致了SQL慢的问题。后来进一步确认这个函数是关闭了Thread-Pool导致的。之后我们继续调整参数，查阅了很多相关资料，终于解决了问题。通过解决以上问题，我们可以学到一些端到端的性能问题解决思路：定位问题的边界和问题的划分每个问题总是有边界的。当我们无法一眼看出问题的边界在哪里时，就需要通过排除的方法不断缩小边界，利用具体的专业知识将问题定位在特定的边界内。收集关键数据以得出可靠的结论。比如生产环境会有各种数据，包括监控数据和临时部署工具获取的数据。充分利用这些数据来支持我们的结论。问题发生时间或影响范围的上下文关联许多问题都是随着一些变化而产生的，就像软件的生命周期一样，受到各种环境变化的影响。通过上下查找问题的原因来查找问题的原因，可以找到大部分问题的原因。不断尝试解决问题许多人认为，当他们知道问题的原因时，解决问题就容易多了。事实上，我认为这是相反的。因为只有清楚地知道问题已经解决了，才能证明问题的原因是正确的。在找到问题原因之前，我们通过不断的调整和测试，实际上已经解决了问题。所以解决问题很重要，好像是废话。合理的理论推断出问题的原因。问题已经解决，找到原因。最后一步是“自圆其说”。解决问题的方法有千万种，这里介绍其中一种，希望对大家有所帮助。
