• 1. MySQL并发控制&限流设计楼方鑫 微博:平民架构 微信:anysql
  • 2. 个人介绍楼方鑫 Oracle ACE 16年工作经验 主要经历 eBay Staff DBA 支付宝数据架构师 支付宝DBA Manager 天猫资深架构师 AUL/MyDUL作者 SQLULDR作者 MySQL Contributor
  • 3. 主要内容MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 4. 性能1.1 褒贬不一致 1.2 测试很重要 1.3 读比写要强MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 5. 性能 – 各有说辞业务所需的功能不同 对MySQL的撑握力度 Flash设备与传统磁盘 对开发人员的支持力度 公说公有理,婆说婆有理1.1褒贬不一致MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 6. 性能 – 业务匹配对业务中的SQL进行测试。 改写或简化业务的SQL。 测试工具:mydbtest程序。1.2 测试很重要MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 7. 性能 – 读&写?查询操作比较强 简单的SQL很强(PK或有效的索引查找) 要求:内存里完成排序、分组统计、无子查询 更新操作很弱 写存贮引挚日志 写MySQL Binlog Server&Storage层的分布式事务协议1.3 读比写要强MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 8. 问题2.1 全局锁太多 2.2 并发是关键 2.3 读写没分开 2.4 优化器太弱MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 9. 问题 – 全局锁SYS CPU过高 Trx_list Read View List Lock List Spinlock & RW Lock & Mutex 监界区太大 大锁(事务表、Buffer、Index、Commit)2.1 全局锁太多MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 10. 问题 – 关键点降底并发减少SYS CPU 合理的并发时程减少锁争用 减少事务表长度,缩短事务表的临界区 控制表的大小,缩短索引维护的临界区2.2 并发是关键MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 11. 问题 – 读写控制调整优化应用的读写比例 MySQL无法分别控制读写,以配合应用的读写比例2.3 读写要分开MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 12. 问题 – 优化器太弱对子查询的优化很差 对Join的优化很差 对索引的选择不可靠2.4 SQL写法很重要MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 13. 改进3.1 多Buffer Pool(5.5) 3.2 读写事务双队列(5.6) 3.3 读事务优化&索引全局锁(5.7) 3.4 下一个版本?MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 14. 改进 – MySQL 5.5代码重构 & Plugin API重构。 MySQL 5.5的主要改进。 不能按表指定Buffer Pool,运气很重要。 代码中到处可见的kernel_mutex。 读写相互影响很明显。3.1 多Buffer PoolMySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 15. 改进 – MySQL 5.6MySQL 5.6在不见了kernel_mutex。 最重要的是读写事务分了不同的队列。 ro_trx_list rw_trx_list 读写操作并发的情况明显改善。3.2 读写事务双队列MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 16. 改进 – MySQL 5.7查询不需要申请事务ID 自动识别只读操作 不再申请事务ID,减轻死锁检测的代价。 去掉索引全局锁 按索引Range查询得到明显改善 索引增大时DML的速度明显改善3.3 读事务优化 & 索引全局锁MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 17. 改进 – MySQL 5.8?事务提交?3.4 下一个版本?MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 18. 并发控制4.1 操作分类 4.2 读写分开 4.3 事务分类 4.4 查询分类 4.5 控制机制 4.6 效果测试MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 19. 并发控制 – 操作分类查询 复杂查询 简单查询 更新 简单事务 复杂事务4.1 操作分类MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 20. 并发控制 – 读写分开限定总的并发数 限定查询的并发数 限定事务的并发数4.2 读写分开MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 21. 并发控制 – 事务分类简单事务 Auto Commit模式 每个DML语句后自动Commit 复杂事务 非Auto Commit模式 由多个DML构成一个语句4.3 事务分类MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 22. 并发控制 – 查询分类简单查询 Auto Commit模式 根据PK或索引查找少量记录 复杂查询 Auto Commit模式 包含子查询、分组统计、没有Where条件等 事务查询 人为事务或非Auto Commit下的查询4.4 查询分类MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 23. 并发控制 – 控制机制
  • 24. 并发控制 – 效果测试
  • 25. 并发控制-读写平衡
  • 26. MySQL的测试5.1 最简的查询 5.2 正常的查询 5.3 多行的查询 5.4 自动提交测试 5.5 人为事务测试MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 27. 测试 – 最简单的查询用例 Create table t_item (col1 int not null primary key, col2 int); Select col1, col2 from t_item where col1 = :id; 并行度 8,16,32,64,128,256,512,1024,2048,4096,8192,16384 测试结果 都能达到20万以上,5.1.71也不例外。5.1 最简单的查询MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 28. 测试 – 正常的查询用例 建一个有24个字段的表。 根据主键选择中间及后面的两个字段。 并行度 8,16,32,64,128,256,512,1024,2048,4096,8192,16384 测试结果 都能达到15万以上,5.1.71也不例外。5.2 正常的查询MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 29. 测试 – 多行的查询用例 建一个有24个字段的表。 按第二索引选择中间的两个字段,平均返回10条记录。 并行度 8,16,32,64,128,256,512,1024,2048,4096,8192,16384 测试结果 都能达到10万的样子,5.1.71也不例外。5.3 多行的查询MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 30. 测试 – 自动提交的DML用例 建一个有24个字段的表。 分别测试插入、和更新操作。 并行度 8,16,32,64,128,256,512,1024,2048,4096,8192,16384 测试结果 5.1.71 1.2万 5.5.32 2.0万 5.6.12 2.8万 5.7.02 4.0万5.4 非事务DML测试MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 31. 测试 – 事务内的DML用例 建一个有24个字段的多个表。 一个事务内分别对不同的表进行插入和更新操作。 并行度 8,16,32,64,128,256,512,1024,2048,4096,8192,16384 测试结果 5.1.71 1.4万 5.5.32 2.3万 5.6.12 3.0万 5.7.02 4.8万5.4 多DML的事务测试MySQL的性能 MySQL的问题 MySQL的改进 并发控制 MySQL的测试
  • 32. QUESTION & answerThanks!