【案例】常驻查询引发的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秒
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 脚本内容
线上某业务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秒
测试结果如下：符合预期
[root@rac1:/u01/test]$sh test_rt.sh
Sun Oct 12 15:13:42 CST 2014
Sun Oct 12 15:13:42 CST 2014
Sun Oct 12 15:13:42 CST 2014
Sun Oct 12 15:13:42 CST 2014
Sun Oct 12 15:13:42 CST 2014
Sun Oct 12 15:13:42 CST 2014
Sun Oct 12 15:13:42 CST 2014
Sun Oct 12 15:13:44 CST 2014
Sun Oct 12 15:13:44 CST 2014
Sun Oct 12 15:13:44 CST 2014
Sun Oct 12 15:13:44 CST 2014
Sun Oct 12 15:13:44 CST 2014
Sun Oct 12 15:13:44 CST 2014
Sun Oct 12 15:13:44 CST 2014
Sun Oct 12 15:13:46 CST 2014
Sun Oct 12 15:13:48 CST 2014
Sun Oct 12 15:13:50 CST 2014
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 脚本内容
#!/bin/bash
function test()
{
mysql.local -e \'select sleep(2)\' > /dev/null
date
}
function r1()
{
for i in {1..18};
do
test &
done
}
function r2()
{
for i in {1..3};
do
test &
done
}
function r3()
{
for i in {1..9000};
do
r2
sleep 2
done
}
r1