MySQL分布式集群连接池低潮抖动问…

最近在测试mysql集群自增插入性能问题时发现，对于短小事务（一个事务中只包含了一个单行插入），增加集群的分片不会导致集群整体插入TPS的提升。

测试工具是sysbench，测试脚本是自行指定的lua脚本。

function event(thread_id)
  db_query("begin;")
  db_query("INSERT INTO test.tt(TRAN_DATE,TRAN_TYPE,PLT_BATCHNO,PLT_INST_ID,PLT_MERCH_ID,PLT_TERM_ID,ROUTE_DATE_TIME,ROUTE_BATCH_NO,ROUTE_SERIAL,ROUTE_ID,ROUTE_MERCH_ID,ROUTE_MERCH_NAME,ROUTE_TERM_ID,ROUTE_TRAN_CODE,MSG_TYPE,PRI_NO,PRO_CODE,MCC,TRAN_AMT,FEE,REFERENCE_NUM,AUTH_RESP_NO,TRAN_STATUS,RESP_CODE,CARD_VALIDITY_DATE,SETTLE_DATE,ACC_INST_ID,ISS_INST_ID,CURRENCY_TYPE,SERIVE_MODE,ORIG_TRAN_DATE,ORIG_TRAN_TYPE,ORIG_BATCHNO,ORIG_SERIAL)VALUES(NULL,'000011','000001','48870000','104110049006453','11261491','20141016','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1');")
  db_query("commit;")
end


mysql集群是通过中间件进行管理的，中间件本身会导致一定的瓶颈，但应该不能2个分片以上就没有提升了。

为了找到问题的原因，我调整了一下测试用例，让每个测试线程只会固定插入到后端的一个分片中:

function event(thread_id)
local id1
   local id

   id1 = sb_rand(1,10000)
   id = id1 * 4 +thread_id

  db_query("begin;")
  db_query("INSERT INTO test.t4(TRAN_DATE,TRAN_TYPE,PLT_BATCHNO,PLT_INST_ID,PLT_MERCH_ID,PLT_TERM_ID,ROUTE_DATE_TIME,ROUTE_BATCH_NO,ROUTE_SERIAL,ROUTE_ID,ROUTE_MERCH_ID,ROUTE_MERCH_NAME,ROUTE_TERM_ID,ROUTE_TRAN_CODE,MSG_TYPE,PRI_NO,PRO_CODE,MCC,TRAN_AMT,FEE,REFERENCE_NUM,AUTH_RESP_NO,TRAN_STATUS,RESP_CODE,CARD_VALIDITY_DATE,SETTLE_DATE,ACC_INST_ID,ISS_INST_ID,CURRENCY_TYPE,SERIVE_MODE,ORIG_TRAN_DATE,ORIG_TRAN_TYPE,ORIG_BATCHNO,ORIG_SERIAL)VALUES('" .. id .."','000011','000001','48870000','104110049006453','11261491','20141016','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1');")
  db_query("commit;")
end

因为测试使用的分区算法是 mod 取模算法， 并且分片数为4, 所以每个线程生成出来的 id值最终进行分发时是固定分发到一个分片上的。（这个测试中把自增给取消了）

测试结果发现，集群的TPS有了显著的提升（约1.5倍）。

从本质上来说前后2个测试对于中间件层来说走的代码应该是一样的，为什么会有如此大的性能差异？

我们在集群在测试运行过程中对后端每个分片的工作连接使用情况进行了统计，每次统计记录当前每个分片上的工作连接数，每2s钟统计一次。
统计结果大致如下：

time 1
par1_Sum =  4
par2_Sum =  1
par3_Sum =  0
par4_Sum =  294

time 2
par1_Sum =  295
par2_Sum =  0
par3_Sum =  0
par4_Sum =  5

time 3
par1_Sum =  5
par2_Sum =  1
par3_Sum =  11
par4_Sum =  279

time 4
par1_Sum =  30
par2_Sum =  39
par3_Sum =  31
par4_Sum =  161

time 5
par1_Sum =  25
par2_Sum =  9
par3_Sum =  224
par4_Sum =  25

time 6
par1_Sum =  59
par2_Sum =  170
par3_Sum =  22
par4_Sum =  16
...


根据统计结果可以看出，后端每个分配的连接池使用是极其不均衡！
不均衡的原因我认为主要是后端mysql的性能波动。
因为本身单行插入操作的耗时是很小的，当4个分片中的某个mysql出现性能低潮时，就会导致该分片上的连接持续速度变慢。
而每个测试线程访问每个后端分片的概率是一样的，当其他3个分片性能较好时，就会出现测试现场对其他3个分片的插入请求都完成后，被“阻塞”在性能较慢的分片上。

出现性能低潮的时间本身不长，但由于单行插入语句本身执行的非常的快，所以会导致大量的连接堆积，从而让该低潮变的更久。
当一个分片低潮结束时，可能其他的分片出现低潮了，然后连接就会向新的低潮分片进行堆积。

mysql出现低潮的可能的原因有很多，例如正常在刷盘。

问题的解决：

对于该问题的解决，目前还没想到太通用的方案，但对于自增插入的场景是可以尽量规避这个问题的。
因为自增的id可以中间件进行分配，所以可行的算法为：

1.在给每个插入语句分配自增id前获取当前每个分片的连接使用情况。
2.选取连接使用数最多的一半的分片，组成一个集合 conn_vec1
3. 为该插入语句获取自增id，如果该id在集合 conn_vec1 中，那么跳过该id，重新获取一个

这样做的核心思想就是避开所有可能处于低潮期的分片。

对于非自增插入的其他场景，如修改，目前还没想到特别好的解决方案。


转自请注明转自高孝鑫的博客