【案例】常驻查询引发的thread pool 性能问题之一

一 问题现象
    线上某业务A的数据库版本MySQL 从5.5 升级到5.6 版本之后多次出现RT抖动的问题,其现象主要为：
    1 rt严重不稳定,部分query rt非常高。
    2 orzdba间歇性阻塞。

二 原理知识
    在MySQL Thread Pool根据参数thread_pool_size被分为若干个group,每个group维护client 发起的 connections,当MySQL建立 connection 时, MySQL 根据connection的thread id 对thread_pool_size取模,将connection 发起的sql 语句分配到对应的group。每个group的最大worker数量为thread_pool_oversubscribe+1。若worker达到最大数量后还是不足以处理回话请求,则连接在本group上等待,导致sql 语句的rt 增大。

三 问题分析
     目前线上的配置参数thread_pool_size为32,thread_pool_oversubscribe 为3 ,也即是ThreadPool共有32个group,每个group最多可容许4个worker。 但是业务A的场景中存在6个binlog dump任务和2个从库和1个日志抓取任务,因为COM_BINLOG_DUMP命令是个执行时间非常长的命令(可以理解为slow query),因此其长期持有worker。 当一个group被2个及以上COM_BINLOG_DUMP命令长期持有的时候,相对于其他group其处理能力会下降到其他group的1/2甚至1/4,最严重的会导致完全阻塞一个group。
Thread Pool本身没有存在group之间的均衡策略,因此新的连接还是会均匀的分配到所有的group上,当负载较大的时候,被COM_BINLOG_DUMP占有的group出现了严重的排队现象。
    在业务A场景中排队时间大约在10-30秒之间,因此有部分query的rt达到10-30秒；而大部分query还是被分配到正常的group上,其rt还是正常的。 根据观察,一般拥堵的group在1-2个之间,因此影响1/32 ~ 1/16的query .

四 验证
      根据以上的原理，为了更好的复现，将thread_pool_oversubscribe调整为2，即每个group有2个worker；同时将thread_pool_size调整为3.
通过锁表阻塞的方法模拟落在group 1的2个COM_BINLOG_DUMP，此时group 1只剩下1个worker，而group 0和group 2还剩在3个worker。
并发向mysql发送select sleep(2)，来模拟业务 。
case 1：
  同一时间并发发起18个query。这时每个group分到6个query。因为g0和g2都有3个worker，因此这些query在4秒钟时处理完毕，但是g1只有1个worker，需要12秒
测试结果如下：符合预期

1. [root@rac1:/u01/test]$sh test_rt.sh 
2. Sun Oct 12 15:13:42 CST 2014
   
3. Sun Oct 12 15:13:42 CST 2014
   
4. Sun Oct 12 15:13:42 CST 2014
   
5. Sun Oct 12 15:13:42 CST 2014
   
6. Sun Oct 12 15:13:42 CST 2014
   
7. Sun Oct 12 15:13:42 CST 2014
   
8. Sun Oct 12 15:13:42 CST 2014
   
9. Sun Oct 12 15:13:44 CST 2014
   
10. Sun Oct 12 15:13:44 CST 2014
    
11. Sun Oct 12 15:13:44 CST 2014
    
12. Sun Oct 12 15:13:44 CST 2014
    
13. Sun Oct 12 15:13:44 CST 2014
    
14. Sun Oct 12 15:13:44 CST 2014
    
15. Sun Oct 12 15:13:44 CST 2014
    
16. Sun Oct 12 15:13:46 CST 2014
    
17. Sun Oct 12 15:13:48 CST 2014
    
18. Sun Oct 12 15:13:50 CST 2014
    
19. Sun Oct 12 15:13:52 CST 2014

case 2：
     开始时发起6个query，以后每隔2秒并发发起3个query。 因为每个query本身耗时2秒，因此对于g0和g2完全能够处理这种并发，但是g1只有1个worker，因此永远都有一个query需要等待，所以被分配到g1上的query都需要等待2秒后才能被执行。
这个时候开启orzdba就会发现有1/3的概率被hang住2秒。线上出现的就是这种现象。

五 解决
1 系统层面 通过改大thread_pool_oversubscribe和thread_pool_size可以极大的减小这种现象的发生，但是不能完全的避免；
同时大量的常驻长连接是否合理（业务A中有9个拉binlog的连接常驻），也值得商榷（减少常驻连接也可以极大的减小这种现象的发生）。
2 源码设计层面:针对类似于binlogdump 的常驻链接存在很多优化点，针对binlogdump 这种长连接优化worker持有策略或者计数方式。

感谢 江疑 同学的详细分析。
test_rt.sh 脚本内容  

1. #!/bin/bash
2. function test()
   
3. {
   
4.     mysql.local -e \'select sleep(2)\' > /dev/null
   
5.     date
   
6. }
   
7. 
   
8. function r1()
   
9. {
   
10.     for i in {1..18};
    
11.     do
    
12.         test &
    
13.     done
    
14. }
    
15. 
    
16. function r2()
    
17. {
    
18.     for i in {1..3};
    
19.     do
    
20.         test &
    
21.     done
    
22. }
    
23. 
    
24. function r3()
    
25. {
    
26.     for i in {1..9000};
    
27.     do
    
28.         r2
    
29.         sleep 2
    
30.     done
    
31. }
    
32. 
    
33. r1

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

转载于:http://blog.itpub.net/22664653/viewspace-1447127/