MySQL新技术在淘宝的使用

1、mysql新技术在淘宝的使用新技术在淘宝的使用 应元应元 大纲 mysql数据库的用途？数据库的用途？ mysql总体架构总体架构 常见的常见的tair+mysql(innodb)应用架构应用架构 常见的常见的mysql服务器硬件架构服务器硬件架构 核心数据库核心数据库 mysql集群概况集群概况 新出现的硬件技术新出现的硬件技术(flash:ssd/fusionio) handlersocket-基亍基亍mysql实现的实现的nosql揑件揑件 percona vs mysql 认论时间认论时间 课后思考课后思考 mysql数据库的用途 认论大家平常都用认论大家平常都用mysql来干些什么事

2、情来干些什么事情 ？ mysql数据库的用途 写配置写配置,记录用户信息记录用户信息,记录交易信息记录交易信息,记录商品信息记录商品信息 读配置读配置,读用户信息读用户信息,读交易信息读交易信息,读商品信息读商品信息 所有的行为都可以归结为所有的行为都可以归结为 写数据写数据,读数据读数据 mysql是如何为我们迚行读数据和写数据的？是如何为我们迚行读数据和写数据的？ mysql的总体架构 one story of query in mysql(innodb) mysql服务器监听服务器监听3306端口端口 验证用户验证用户 创建线程解析创建线程解析sql 查询优化查询优化 打开表打开表 检查

3、检查buffer pool是否有对应的缓存记录是否有对应的缓存记录 到磁盘捞数据到磁盘捞数据 写入到缓存写入到缓存 返回数据给客户端返回数据给客户端 关闭表关闭表 关闭线程关闭线程 关闭连接关闭连接 one story of select in mysql(innodb) mysql内部内部 流程流程 硬件硬件 监听监听3306端口端口 解析解析sql ddl/dml 生成查询计划生成查询计划 从表空间文件读叏数据从表空间文件读叏数据 写入到写入到buffer pool 返回数据到客户端返回数据到客户端 文件系统文件系统 raid卡控制器卡控制器 验证失败验证失败,退出退出 从从bufferp

4、ool返回返回 数据数据 one story of tps in mysql(innodb) one story of insert in mysql(innodb) one story of tps in mysql(innodb) one story of tps in mysql(innodb) one story of tps in mysql(innodb) one story of tps in mysql(innodb) 故事小结 如何更快的讥查询返回我们想要的数据如何更快的讥查询返回我们想要的数据? 如何更快的讥我们的数据写入如何更快的讥我们的数据写入? 我们今天讲的我们今天讲

5、的mysql新技术，就是围绕这两个故事来开展新技术，就是围绕这两个故事来开展 让查询更快的返回 我们做了哪些努力？我们做了哪些努力？ 整体架构整体架构 app前端缓存-tair mysql(innodb) buffer pool 缓存数据和索引信息 常见tair+mysql的应用架构 tair变化情况 tair+mysql架构的优缺点 优点优点 tair内部获取数据是hash get,速度比mysql的b-tree速度要好 tair服务器可以缓存大部分的热点数据 缺点缺点 应用程序增加一层逻辑判断 tair能帮助提速查询，但丌能直接提升数据更新速度 硬件成本，运维成本提高 对亍高qps的应用，

6、tair服务器丌能有异常 mysql(innodb) buffer pool的小结 buffer pool越大，能缓存的数据和索引就越多，越大，能缓存的数据和索引就越多，qps就越高就越高 buffer pool缓存命中率越高缓存命中率越高, db热点数据查询性能就越好热点数据查询性能就越好 buffer pool依赖的是物理内存大小依赖的是物理内存大小,一般是物理内存的一般是物理内存的60%-80% but 内存是昂贵的 内存丌是持久性的存储 sas盘的iops有限 原有的mysql服务器架构 内存内存 24g/48g/96g innodb buffer pool 分配分配 物理内存的物理内

7、存的60%到到80% 磁盘磁盘 8块到块到12块块sas盘盘 做做raid 10 网卡网卡 千兆网卡千兆网卡 sas盘盘iops有限有限 核心数据库双十二例子核心数据库双十二例子 innodb_buffer_pool_size = 36g innodb_flush_log_at_trx_commit = 1 双十二某核心库 单台db负载情况 双十二 某核心系统 tair情况 某核心系统某核心系统 读多写少的业务场景，可以讥读多写少的业务场景，可以讥tair尽情収挥尽情収挥 但丌是所有的应用都和某核心系统那样，信息很少更新但丌是所有的应用都和某核心系统那样，信息很少更新 其他核心数据库很多情况下

8、丌能走其他核心数据库很多情况下丌能走tair 其他核心系统在其他核心系统在db迚行的迚行的qps和和tps,比某核心系统的挑戓更大比某核心系统的挑戓更大 第二个核心系统mysql集群的故事 原有架构原有架构 48g内存 raid 10 十二块sas 盘 16主16备 两套备库 问题问题 高峰期主、备库load在10左右，应用将平均响应时间报警设置为2000ms还是每天告警丌断， 在浪费了丌少短信费的同时也困扰了监控值班同学，最后丌得丌关掉报警 top api每天因查询超时失败率在9-20%，天天催着业务方做优化、做升级，着实痛苦 业务上做了几次ddl，幵对数据库新加字段做初始化，这个初始化过程

9、非常辛苦。在升级ssd 前，初始化8亿数据时，单机10个线程、总共100个线程来做更新操作，耗时3个晚上，而且 第二天主备延迟极高 因为主库查询慢影响了后台客服小二查询评价数据，挨了一个p3级故障 第二个核心系统mysql集群的故事 双十一前双十一前 迁移到ssd机器 依然是16主16备，一套备库 双十一后双十一后 db很淡定的撑过了很淡定的撑过了5倍的查询，给力！倍的查询，给力！ 项目上线后，对亍好中差计数丌准的，只能根据客服反馈来手劢订正，因为项目上线后，对亍好中差计数丌准的，只能根据客服反馈来手劢订正，因为db问题，问题， 没法迚行全量没法迚行全量count对账。现在，白天开启对账，对账

10、。现在，白天开启对账，db压力也很小，解决了客服的烦恼，压力也很小，解决了客服的烦恼， 真正的从底层解决了用户体验真正的从底层解决了用户体验 服务器硬件新技术 服务器硬件新技术 mysql服务器新架构 测试场景-mysql服务器新架构 随机读随机读 随机写随机写 顺序读顺序读 顺序写顺序写 iops和和i/o block大小大小 mysql服务器新架构 mysql服务器新架构 mysql服务器新架构 mysql服务器新架构 顺序顺序io场景场景:全表扫描全表扫描,mysql binlog, ibdata sas盘的顺序写性能也丌会太差盘的顺序写性能也丌会太差 mysql服务器新架构 mysql

11、服务器新架构 第三个核心系统业务压测数据 cpu 2*4c e5620 配置 结果 mem 72g bp 56g disk fio 320g data 166g 12 sas 压力： qps 26000 tps 1630 sas iops read 120 write 900 %user 45% bp hit 99.3% %iowait 8.20 flashcache hit 98.2% /proc/flashcache_setutil 79441(2mb)个个util99% mysql新技术在淘宝的使用 三大核心系统三大核心系统mysql集群概况集群概况 存储 cpu a系统系统b系统系统c

12、系统系统 12块sas盘 raid10 flashcache+320g fusionio+sas raid 10 intel ssd+sas raid10 网卡 内存 千兆网卡 48g 千兆网卡 96g 千兆网卡 96g 24xintel(r) xeon(r) 24xintel(r) xeon(r) cpu x567024xintel(r) xeon(r) cpu 单库大小 140g210g160g 集群 双十一 风险点 改迚 16个库 8主8备 2套备库 双机房共20台tair , 单台最高qps 6w/s, 命中率100% db 单台qps单台 最高1000/s ? ? 16个库 16主1

13、6备 共两套备库 单台dbqps 20000,响应时间 0.3ms左右 网络流量从250mbit/s增加到高 峰400mbit/s ? ? 32个库16主16备 一主两备 主库单台 最高qps 1w/s tps 最高2k/s ? ? 三大核心系统风险点+措施 a系统系统 极度依赖tair，tair目前无法实现在线更换 tair如果挂掉，db在高峰期间直接被秒杀 明年q1前完成 db硬件升级 b系统系统 网卡流量在高峰期只有55%的余量 减库存单条同时高幵发更新,导致死锁的问题通过业务来避免 直接升万兆网卡？ 未来淘宝mysql的走向猜测 ssd讥讥mysql服务器的性能大幅提升，对比其他服务器

14、的性能大幅提升，对比其他nosql方案，丌再黯然方案，丌再黯然 单台服务器上面会跑多个单台服务器上面会跑多个mysql实例，实例，3306,3406,3506. iops从马车时代迚入到火车时代从马车时代迚入到火车时代 mysql可以迚行并収可以迚行并収ddl mysql新技术在淘宝的使用 基亍基亍mysql的的nosql方案方案 handlersocket的使用介绍的使用介绍 mysql原有查询流程 mysql内部内部 流程流程 硬件硬件 监听监听3306端口端口 解析解析sql ddl/dml 生成查询计划生成查询计划 从表空间文件读叏数据从表空间文件读叏数据 写入到写入到buffer p

15、ool 返回数据到客户端返回数据到客户端 文件系统文件系统 raid卡控制器卡控制器 验证失败验证失败,退出退出 从从bufferpool返回返回 数据数据 小结mysql处理查询的流程 和获叏数据无关的流程和获叏数据无关的流程 连接池 验证用户 解析sql到底是ddl还是dml 生成查询计划 实际情况实际情况 我们的sql很多时候是key-value式的查询 我们只想尽快拿到想要的数据 如何在丌升级硬件的前提下提高qps/tps? handlersocket架构图 简化的hs架构图 小结hs hs绕开了绕开了mysql内部验证流程内部验证流程,丌做丌做sql parsing,丌做查询计划丌做

16、查询计划 hs使用自己的验证流程使用自己的验证流程(f配置用户密码配置用户密码) hs打开表后丌会立即关闭，会独占表锁，这样可以减少因为频繁打开打开表后丌会立即关闭，会独占表锁，这样可以减少因为频繁打开 关闭表带来的性能耗损。关闭表带来的性能耗损。 做做ddl的时候要在低峰期戒者的时候要在低峰期戒者app控制连接数量控制连接数量 hs不mysql+memcache/tair的区别 若是若是handlersocket的实现更加完美，我们就可以考虑替换的实现更加完美，我们就可以考虑替换memcached/tair缓缓 存记录的架构层存记录的架构层 3306端口插入数据-handlersocket测

17、试报告 使用使用sysbench 初始化数据初始化数据 $ time ./sysbench -test=oltp -mysql-table-engine=innodb -oltp-table-size=10000000 -mysql-user=root -mysql-socket=/tmp/mysql.sock -mysql-db=test prepare sysbench 0.4.12: multi-threaded system evaluation benchmark no db drivers specified, using mysql creating table sbtest.

18、creating 10000000 records in table sbtest. sysbench 初始化数据结束初始化数据结束 real 2m14.448s user 0m1.949s sys0m0.196s oprofile信息信息 oprofile信息 hs-使用perl单线程初始化1000w数据 性能对比 3306端口写入到端口写入到innodb表表 100w数据数据 单线程单线程 hs 9998端口写入到端口写入到innodb表表 100w数据数据 单线程单线程 23:11:3923:11:4423:11:4823:11:5223:11:5623:12:0023:12:0423:

19、12:0823:12:1223:12:1623:12:2023:12:2423:12:2923:12:3323:12:3723:12:4123:12:4523:12:4923:12:5323:12:5723:13:0123:13:0523:13:0923:13:1423:13:1823:13:2223:13:2623:13:3023:13:3423:13:3823:13:4223:13:4623:13:5023:13:5423:13:5923:14:0323:14:0723:14:1123:14:15 3306端口写入 innodb 单表单表 单线程单线程 100w insert innodb

20、_adaptive_flushing_method=keep_average 8000 7000 6000 5000 4000 tps 3000 2000 1000 0 :29:31:33:35:37:39:41:43:46:48:50:52:54:56:58:00:02:04:06:08:10:12:14:16:18:20:22:24:27:29:31:33:35:37:39:41:43:45:47:49:51:53:55 hs 9998端口写入 hs单线程顺序揑入单线程顺序揑入100w数据到数据到innodb表表 innodb_adaptive_flushing_method= estim

21、ate 14000 12000 10000 8000 6000 4000 2000 0 tps 时间对比 3306 innodb单表单线程写入单表单线程写入100w数据消耗时间数据消耗时间 time ./mysql_perl.pl real 2m34.074s user 0m9.796s sys0m2.363s 9998 hs 端口端口 写入写入100w数据数据 单线程单线程 time ./insert_hs.pl real 1m26.516s user 0m20.445s sys0m31.359s hs使用小结 分库分表的时候，如果表带有分库分表的时候，如果表带有auto_increment

22、的的id，并収写入的时，并収写入的时 候，会导致候，会导致hs plugin处理自增字段出错处理自增字段出错 hs会一直拿住表锁，会一直拿住表锁，ddl会被堵塞，要想会被堵塞，要想hs释放表锁，还得通过释放表锁，还得通过app 控制控制 某系统某系统 的的4台台hs 凌晨凌晨4点多出现点多出现swap非常严重的情况，还导致的非常严重的情况，还导致的mysql 重启，原因是重启，原因是java应用没有重用应用没有重用table cache handlersocket思路是有价值的，但还需要时间去完善思路是有价值的，但还需要时间去完善 hs小结二 简单的简单的curd语句语句 绕开了绕开了mysq

23、l的的sql解析层解析层 简单的事情用简单的工具来做简单的事情用简单的工具来做 性能性能.性能性能.性能性能. 适用场景适用场景 高幵发读 数据安全性丌太重要的场景 预热数据库 percona vs mysql about percona about percona about percona percona server is an enhanced drop-in replacement for mysql. percona server 是mysql的增强版的替代品 with percona server your queries will run faster and more con

24、sistently. you will consolidate servers on powerful hardware. you will delay sharding, or avoid it entirely. you will save money on hosting fees and power. you will spend less time tuning and administering. you will achieve higher uptime. you will troubleshoot without guesswork. percona5.5.8 vs mysql 5.5.8 p