MySQL自我保护与并发优化

问题背景

线上MySQL在大促时遇到并发压力大导致threads_running(并发执行query的线程数目)飙升,MySQL出现性能抖动问题甚至hang住,虽然在InnoDB层有innodb_thread_concurrency限制同时进入InnoDB存储层读写数据的线程数,但是这个参数不是一个硬限制(线程在锁等待结束后强制进入InnoDB层),而且代码路径比较靠后,作用有限,于尝试在server层限制并发数来解决这个问题,通过不断地优化和测试,开发完成了一个高水位限流/低水位优化的patch

patch功能介绍

这个patch是在之前thread running control的基础上优化的,将之前位于同一个函数中两个功能独立出来并将之前低水位限流实现从基于sleep-retry的机制优化成基于FIFO的cond-wait/signal机制

1. 高水位限流:严格控制threads_running上限,当并发运行query数目超过阈值threads_running_high_watermark时,直接拒绝执行并返回错误,保证MySQL服务稳定性

2. 低水位优化:控制活跃并发线程数,当使用one-thread-per-connection调度模式时(线上目前使用),每个连接在MySQL中都会创建一个线程来服务,当并发突增时,大量线程被创建,导致资源使用,锁竞争,context-switch大大增加,往往会导致性能的下降,为了解决这些问题,社区中mariadb开发了threadpool方案,percona在其上实现了优先队列(后续会撰文介绍),解决问题的思路都是限制活跃并发线程数,让少量的worker线程服务大量的连接,我们实现的低水位优化的思路与threadpool一致,但是实现方案更加简洁(patch不超过1000行),而且增加了针对特定query过滤的特性
高水位限流实现细节

监控系统status变量threads_running,当满足拒绝条件是,拒绝执行sql,返回用户:MySQL Server is too busy,判断逻辑在dispatch_command中,sql解析之前

增加的系统variables:

threads_running_ctl_mode: 限流的sql类型,有两个取值:[ALL | SELECTS],默认SELECTS,设置为ALL需谨慎
threads_running_high_watermark: 限流水位值,只有threads_running超过此值才会触发,默认值为max_connections,当set global threads_running_high_watermark=0时自动设置为max_connections

拒绝必要条件:
1.threads_running超过threads_running_high_watermark
2.threads_running_ctl_mode与sql类型相符

以下情况不拒绝:
1.用户具有super权限
2.sql所在事务已经开启
3.sql为commit/rollback

低水位优化实现细节

1. 增加了系统变量threads_active来记录并发线程数目,判断逻辑在mysql_execute_command中,sql解析之后,threads_active只统计SELECT/INSERT/UPDATE/DELETE,COMMIT/ROLLBACK/DDL等不统计在内,因此不受限流影响

2. 实现采用FIFO排队思路,当系统当前threads_active超过阈值threads_running_low_watermark时,线程进入FIFO中排队等待,其它线程在执行完sql后依次唤醒FIFO中线程,保证并发线程控制在threads_running_low_watermark之内,同时引入变量threads_running_wait_timeout控制线程在FIFO等待最大时间,等待超时的sql被reject,返回用户:MySQL Server is too busy

3. 为了降低高并发下大量线程进入/退出FIFO的锁竞争,实现过程中采用multi-fifo的策略(8个fifo队列),每个队列限制并发运行线程数为(threads_running_low_watermark/8),线程按照round robin被分配到不同的fifo中

增加的系统variables:

threads_running_ctl_mode: 作用同高水位
threads_running_low_watermark: threads_active阈值,当前threads_active超过此值时,触发限流排队,默认为0(不开启限流)
threads_running_wait_timeout:进入FIFO排队最长时间,等待超时后sql被拒,默认100,单位为毫秒ms

拒绝必要条件:
1.threads_running_low_watermark设置不为0,且threads_active超过threads_running_low_watermark
2.threads_running_ctl_mode与sql类型相符

以下情况不拒绝:
1.用户具有super权限
2.sql所在事务已经开启

增加的status:

threads_active: 当前并发SELECT/INSERT/UPDATE/DELETE执行的线程数目
threads_wait:当前进入到FIFO中等待的线程数目
threads_rejected: 拒绝的sql数目,包含高水位和低水位限流

性能优化结果

测试脚本:

./sysbench --test=tests/db/select.lua --max-requests=0 --mysql-host=myxxxx.cm3 --mysql-user=test 
--mysql-table-engine=innodb --oltp-table-size=5000000 --oltp-tables-count=32  --num-threads=$threads run

tr-ctl
normal mysql-0 : 未打补丁版本,设置innodb_thread_concurrency=0
normal mysql-1 : 未打补丁版本,innodb_thread_concurrency=32
patched mysql : 低水位限流补丁版本(活跃线程数不超过64)

在超过10000个连接下,低水位限流版本通过限制活跃并发线程数目可以保证MySQL性能维持在一个较高值

patch下载

若对限流优化patch的实现细节感兴趣,可以下载patch,欢迎提出问题!

《MySQL自我保护与并发优化》上有4条评论

  1. 你好
    麻烦问一下
    这个patch 与 thread_running_ctl.patch是不是需要都打上(还是说两个patch只要打一个就可以)?
    mysql 5.6.35 是否可以打呢?

回复 王海龙 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注