- 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!