近期，由于上层组件异常，DB雪崩事件屡屡发生。作者为部分监控DB开启了线程池功能。在使用线程池不断深入学习的同时，也遇到了很多问题。本文将详细介绍MySQL线程池相关知识，帮助DBA们快速了解MySQL线程池机制，快速配置MySQL线程池以及其中的一些陷阱。其实我想说，看完这篇文章就足以理解和使用MySQL线程池了。一、为什么要使用MySQL线程池在介绍为什么要使用线程池之前，我们都知道随着DB访问次数的增加，DB的响应时间也会增加，如下图所示：而DB访问到了一定程度，DB的吞吐量也会下降，而且会越来越差，如下图：有什么办法可以实现随着DB的访问量增加，DB总是shows最好的表现呢？类似于下图的表现：答案就是我们今天要重点说的线程池功能。总结一下，使用线程池有两个原因：1.减少重复创建和销毁线程的开销，提高性能。线程池技术预先创建一定数量的线程。分配现有线程之一以提供服务。服务结束后，这个线程不会直接销毁，而是去处理其他的请求。这样避免了线程和内存对象的频繁创建和销毁，减少了上下文切换，提高了资源利用率，在一定程度上提高了系统性能和稳定性。2.保护系统。线程池技术限制了并发线程数，相当于限制了MySQL的运行线程数。无论系统当前有多少连接或请求，超过最大线程数的线程都需要排队，使系统保持高性能水平，从而防止DB雪崩，保护底层DB。可能有人会问，用连接池能达到类似的效果吗？有些DBA可能会混淆线程池和连接池，但其实两者有很大的区别：连接池一般设置在客户端，而线程池则配置在DB服务器；另外，连接池可以避免Connection被频繁创建和销毁，但无法达到控制MySQL活跃线程数的目的。在高并发场景下，它不能保护DB。更好的方法是结合连接池和线程池。2.MySQL线程池介绍MySQL线程池介绍为了解决one-thread-per-connection（one-threadperconnection）和DBavalanche在高并发情况下频繁创建和销毁大量线程的问题，实现DB在高并发环境下依然可以保持高性能。Oracle和MariaDB都推出了ThreadPool方案。目前，Oracle的Threadpool是作为Plugin实现的，并且只在Enterprise版本中加入。Percona移植了MariaDB的线程池功能，并做了进一步的优化。本文环境基于PerconaMySQL5.7版本。MySQL线程池架构MySQL的线程池（threadpool）分为多个组（group），每个组都有对应的工作线程。整体的工作逻辑还是比较复杂的。下面我尝试简单介绍一下MySQL线程池是如何工作的。1.架构图首先我们来看一下ThreadPool的架构图。2.ThreadPool的组成从架构图中我们可以看出ThreadPool由一个Timer线程和多个ThreadGroup组成，每个ThreadGroup由两个队列组成，一个监听线程和多个工作线程。下面分别介绍各部分的作用：队列（高优先级队列和低优先级队列）用于存放待处理的IO任务。它们分为高优先级队列和低优先级队列。高优先级队列中的任务将被优先处理。哪些任务会被放入高优先级队列？事务中的语句会被放入高优先级队列。比如一个事务中有两条updateSQL，其中一条已经执行，那么另一个update任务会被放到高优先级队列中。这里需要注意的是，如果是非事务引擎，或者启用了Autocommit的事务引擎，会被放到低优先级的队列中。在另一种情况下，任务将被放入高优先级队列。如果语句在低优先级队列中停留时间过长，语句也会被移至高优先级队列以防止饥饿。listenerthread监听线程监听线程组的语句，并在转化为工作线程时决定是立即执行相应的语句还是放入队列中。判断的标准是队列中是否有要执行的语句。如果队列中要执行的语句数为0，则监听线程转为工作线程，立即执行相应的语句。如果队列中要执行的语句数不为0，则认为任务较多，将语句放入队列中，等待其他线程处理。这里的机制是为了减少线程的创建，因为一般SQL的执行速度都非常快。工作线程工作线程是实际执行工作的线程。Timer线程Timer线程用于周期性检查组是否处于阻塞状态。当发生阻塞时，会通过唤醒线程或创建新线程来解决。具体检测方法是：通过queue_event_count的值判断线程组是否阻塞，IO任务队列是否为空。工作线程每次检查队列中的任务时，queue_event_count都会+1，每次Timer检查组是否阻塞时，queue_event_count都会清0。如果检查时任务队列不为空，且queue_event_count为0，说明任务队列没有正常处理。这时候群里就被封了。Timer线程会唤醒worker线程或者创建一个新的wokrer线程来处理队列中的任务，防止group长时间阻塞。3、线程池是如何工作的？下面描述极简ThreadPool操作，只是简单的描述，省略了很多复杂的逻辑，请不要批评~Step1：请求连接MySQL，根据threadid%thread_pool_size判断属于哪个组；Step2：group中的监听线程监听到group有新的请求后，检查队列中是否还有未处理的请求。如果没有，立即切换到工作线程来处理请求。如果队列中有未处理的请求，则将相应的请求放入队列中，让其他线程处理；Step3：组内thread线程检查队列中的请求，如果队列中有请求，则进行处理。如果没有请求，它会休眠，一直没有被唤醒。thread_pool_idle_timeout后会自动退出。线程结束。当然在拿到请求之前，它会检查组内运行的线程数是否超过了thread_pool_oversubscribe+1，超过了就休眠；Step4：timer线程周期性的检查各组是否阻塞，如果阻塞则唤醒wokrer线程或者创建新的worker线程。4.线程池的分配机制线程池会根据参数thread_pool_size的大小分成若干组，每组维护客户端发起的连接。当client向MySQL发起连接时，MySQL会跟进连接的线程id(thread_id)modulothread_pool_size落入对应组。thread_pool_oversubscribe参数控制每组最大并发线程数，每组最大并发线程数为thread_pool_oversubscribe+1。如果对应组达到最大并发线程数，则对应连接需要等待。这种分配机制会导致在一组有多个慢SQL的场景下，普通SQL的运行时间很长。稍后将详细描述该问题。MySQL线程池参数说明线程池参数不多。使用showvariableslike'thread%'查看下图中的参数。下面一一分析：thread_handling这个参数是用来配置线程模型的。默认是one-thread-per-connection，即不开启线程池；将此参数设置为pool-of-threads可启用线程池。thread_pool_size这个参数是设置线程池中Groups的数量。默认为系统中CPU的个数，以充分利用CPU资源。thread_pool_oversubscribe该参数设置组中的线程数。每组线程数为thread_pool_oversubscribe+1。请注意，不包括侦听器线程。thread_pool_high_prio_mode高优先级队列的控制参数有三个值（transactions/statements/none），默认为transactions，三个值的含义如下：transactions：已经开始事务的statements都放在高优先级队列中，但是这取决于后面的thread_pool_high_prio_tickets参数。statements：该模式下的所有语句都会放入高优先级队列，不会使用低优先级队列。none：该模式不使用高优先级队列。thread_pool_high_prio_tickets这个参数控制每个连接被放入高优先级队列的最大次数。默认为4294967295，注意这个参数只有在thread_pool_high_prio_mode为transactions时才有效。thread_pool_idle_timeoutworker线程空闲时间，默认60秒，超过限制会退出。thread_pool_max_threads这个参数用来限制线程池的最大线程数。超过这个限制后，就不能再创建线程了。默认为100000。thread_pool_stall_limit该参数设置定时器线程检测组是否异常的时间间隔，默认为500ms。3.MySQL线程池的使用线程池的使用比较简单，只需要添加配置和重启实例即可。具体配置如下：#threadpoolthread_handling=pool-of-threadsthread_pool_oversubscribe=3thread_pool_size=24performance_schema=off#extraconnectionextra_max_connections=8extra_port=33333备注：其他参数可以默认。要点：1.之所以加performance_schema=off是因为在测试的时候，发现同时开启Threadpool和PS时，会出现内存泄露的问题（后面会详细介绍）；2、增加额外的连接是为了防止线程池满了在某些情况下无法登录MySQL，所以管理端口专门用于应急；重启实例后，您可以使用showvariableslike'%thread%';查看配置的参数是否生效。四、使用中遇到的问题在使用线程池的过程中，我遇到了几个问题。这里顺便总结一下：内存泄漏问题DB中开启线程池后，内存飙升了8G左右，如下图：不仅开启线程池后，内存飙升了约8G8G，内存还在继续增长。很明显开启线程池后有内存泄漏。网上很多人也遇到了这个问题，经确认是percona（https://jira.percona.com/browse/PS-3734）的bug导致的。它只会在Performance_Schema和ThreadPool打开时出现。解决方法是关闭Performance_Schema，具体操作方法是在配置文件中添加performance_schema=off，然后重启MySQL就OK了。以下是关闭PS后的内存占用对比：备注：目前Perconaserver5.7.21-20版本已经修复了线程池和PS同时开启内存泄漏的问题。从我的测试情况来看，问题也已经解决了。大家可以直接使用Perconaserver5.7.21-20版本，如下图。拨号测试异常问题开启线程池后，相当于限制了MySQL的并发线程数。当达到最大线程数时，其他线程需要等待，新连接也会卡在连接验证这一步。此时会导致拨测程序连接MySQL超时，拨测返回错误如下：拨测程序连接实例超时后，会认为master有一个问题。极端情况下，反复重试异常后，启动自动切换操作，将业务切换到从机上。这种情况有两种解决方法：1、开启MySQL的旁路管理端口，直接使用MySQL的旁路管理端口进行监控和高可用。具体方法是：在my.cnf中添加如下配置并重启，即可通过bypass端口登录MySQL，不受线程池最大线程数影响：extra_max_connections=8extra_port=33333备注：建议开启线程池，也加上这个，以备不时之需。2、修改高可用检测脚本，将达到线程池最大活跃线程数返回的错误作为异常处理，作为超过最大连接数的场景。（备注：如果超过最大连接数，只会报警，不会自动切换。）慢SQL引入的问题随着拨号测试超时问题的深入分析，全线程池拨号测试超时只是其中一种情况。有一种情况是线程池未满，线上的两个配置：thread_pool_oversubscribe=3thread_pool_size=24如果按照上面两个配置计算，一共可以并发运行24x(3+1)=96个，但是根据tomultipleproblems追上去后，发现线程池很多次都没有达到96，也就是说线程池整体没有满。什么会导致拨号测试失败？鉴于线程池的结构和分配机制，通过前面对线程池的描述，大家知道线程池内部是一个一个分组的。我们在线配置了24个组，线程池的分配机制是对Threadid取模，然后判断线程属于哪个组。当发生超时时，有很多加载线程导入数据。也就是说，当时有些线程是比较慢的。那么会不会是某个组的线程满了，导致新分配的线程等待呢？有了这个猜想，接下来就是验证这个问题了。验证分为两步：1、抓取在线运行的processlist，然后对threadid取模，看是否有多个load线程落在同一个group；2.在测试环境中模拟这个场景，看是否符合预期。线上场景分析我们先来看线上场景。通过抓取拨号超时时间点的processlist，我们可以找出当时正在加载的线程，根据threadid去除model，进行汇总统计。可以得到如下结果：发现第4组和第7组的请求数都超过了4，说明拨号测试异常是单组爆满导致的。当然，它也会导致一些快速运行的SQL变慢。测试环境模拟场景分析为了搭建快速复现环境，我调整了参数如下：thread_pool_oversubscribe=1thread_pool_size=2通过以上参数的调整，可以计算出最大并发线程数为2x(1+1)=4，如下图所示，当活跃线程数超过4时，其他线程必须等待：我模拟线上环境的方法是启动1线程的慢SQL。此时测试环境的线程池如下：根据之前的推测，此时Group1的处理能力相当于Group2处理能力的50%。如果前面的推断是正确的，那么分配给Group1的线程将被阻塞。比如此时来了20个线程请求。根据线程池的分配原则，此时会为Group1和Group2分配10个线程请求。如果所有线程请求花费相同的时间，那么分配给Group1的线程请求的总处理时间应该是分配给Group2的总处理时间的两倍。我用一个脚本，发起12个线程请求，每个线程请求运行selectsleep(2)，然后当Group1和Group2都空闲时，运行如下：2018-03-18-20:23:532018-03-18-20:23:532018-03-18-20:23:532018-03-18-20:23:532018-03-18-20:23:552018-03-18-20:23:552018-03-18-20:23:552018-03-18-20:23:552018-03-18-20:23:572018-03-18-20:23:572018-03-18-20:23:572018-03-18-20:23:57每次4个线程，共运行6秒。接下来，Group1被一个长时间运行的线程覆盖后，我们看看测试结果是怎样的：2018-03-18-20:24:352018-03-18-20:24:352018-03-18-20:24:352018-03-18-20:24:372018-03-18-20:24:372018-03-18-20:24:372018-03-18-20:24:392018-03-18-20:24:392018-03-18-20:24:392018-03-18-20:24:412018-03-18-20:24:432018-03-18-20:24:45从上面的结果可以看出图中没有阻塞时，每次有4个线程，当有一个线程运行时间长时，长线程对应的组就会排队。***虽然有3个空闲线程，但只有1个线程在处理（结果标记为红色）。解决办法有两种：1、适当增加thread_pool_oversubscribe。这种方法只能缓解类似问题，不能解决；2、找到慢SQL，解决慢问题。参考资料：https://www.percona.com/doc/percona-server/LATEST/performance/threadpool.htmlhttps://www.percona.com/blog/2013/03/16/simcity-outages-traffic-control-and-thread-pool-for-mysql/http://www.cnblogs.com/cchust/p/4510039.htmlhttp://blog.jobbole.com/109695/http://blog.csdn.net/u012662731/文章/详情/54375137
