• 1. MySQL开发规范和平台简介卓汝林
  • 2. About Me 2011 ~ 2012 就职于惠普, Java工程师 2012 ~ 2013 就职于58,MySQL和MongoDB DBA 2013 ~ 至今 就职于小米,MIUI、小米互娱等MySQL和Redis的运营管理;关注自动化运维设计和开发 @RogerZhuo
  • 3. AgendeMySQL开发规范简介MySQL部分常用平台简介Redis Cluster的简介
  • 4. MySQL开发规范简介基础规范库表设计规范索引设计规范SQL规范
  • 5. 基础规范统一使用utf8字符集和utf8_general_ci字符排序规则,按需对表级设置utf8mb4 表存储引擎使用InnoDB; 默认使用REPEATABLE-READ事务隔离级别 统一命名规范:默认全小写,禁用关键字和合理使用前缀 禁止使用存储过程和函数、视图、触发器、外键约束和Event 各环境进行隔离,避免混用风险:dev->test->staging->production 生产数据安全隐私为首重,要求项目数据闭环和数据范围可控制
  • 6. 库表设计规范 单实例表个数控制在3w以内,单表行数500w~1000w内 某类分表个数控制在1000,避免过度拆分(DDL操作痛苦) 采用统一的分库分表原则,二进制或十进制 禁用MySQL自带分区表 每个表必须设置主键, 尽量使用数值类型和增长趋势 核心业务表避免使用自增列,使用外部ID生成器 字段设置原则:Simple is good, Smaller is usually better 字段和类型从产品角度按需设置,禁用预留字段,避免存储浪费 字段都设置not null和default值
  • 7. 索引设计规范 InnoDB表主键的选择策略 -每个表必须显示指定主键(RBR) -主键尽量用单字段,最好是数值类型 -主键值与插入顺序,尽量同步 禁用uuid(rebanlance和fragment) -禁止更新主键值 (逻辑定义就不合理) 单表索引数建议别超5个,每个组合索引别超过5个字段 尽量使用组合索引,避免单索引的index merge或回表操作 注意组合索引的顺序,最左原则,根据SQL条件调整索引字段顺序 避免使用冗余索引,按需创建索引
  • 8. SQL规范 Where子句右值都用引号括起,避免隐式类型转换 多表Join时注意比较字段类型一致,避免隐式类型转换 禁止对索引列进行函数和数值计算 Select/insert都枚举字段名,禁用select * 避免使用大SQL、大事务,或事务中等待用户动作行为 大事务/SQL会导致主从复制延时 MySQL的单个SQL只能在一个CPU上运行 影响数据库的并发性能,事务持有的锁等资源只在事务rollback/commit时才能释放 存在比较长的readview(快照),如果持续时间几十分钟,数据库响应时间可能聚降 (mvcc,undo) 禁用update/delete … limit N结构SQL 使用where in(),in子句中元素建议小于500
  • 9. 运营规范-举例一二 故障预案管理 故障管理- postmortem机制
  • 10. MySQL部分常用平台简介监控和告警-Open Falcon备份和还原-Xtrabackup Manager慢查询报表-Anemometer高可用 MHA中间件 MariaDB MaxScale自动审核系统 Qunar Inception自动巡检系统XiaoMi DBaaS
  • 11. 监控和告警Zabbix监控方案 Zabbix + Fromdual mysql-performance-monitor Zabbix + Percona Monitoring Plugins
  • 12. 监控和告警Zabbix监控
  • 13. 监控和告警-Open-FalconOpen-Falcon 是小米运维部开源的一款互联网企业级监控系统解决方案. 项目首页:http://open-falcon.com 使用公司:https://github.com/XiaoMi/open-falcon/issues/4
  • 14. 监控和告警-Open-Falcon
  • 15. 监控和告警-Open-FalconDashboard首页
  • 16. 监控和告警-Open-Falcon vs Zabbix 告警方面 告警模板支持继承的同时支持覆盖策略项 Tag化描述告警策略 与服务树结合—— 实例上下线自动变更监控 水平扩展
  • 17. 监控和告警-Open-FalconOpen-Falcon MySQL指标采集-mymon 项目地址: https://github.com/open-falcon/mymon 系统Falcon自带Agent单机采集400+指标:CPU, 磁盘, 内存, swap,网络相关, IO,负载机器内核参数,netstat采集等 存活MySQL_Alive和响应时间性能MySQL和InnoDB状态值采集300+指标复制MySQL复制状态监控
  • 18. 监控和告警-Open-FalconMymon监控告警项告警项目影响描述故障级别处理方式告警方式MySQL is down  MySQL挂了,不能提供服务P0立即通报&处理短信&邮件MySQL connection is full 达到连接数的上限,无法再创建新数据连接P1立即通报&处理短信&邮件MySQL Is BusyMySQL并发处理连接数比较高,整体查询响应时间变长p1立即通报&处理短信&邮件Slave lagging behind Master从库延时,影响从库只读一致性; HA架构的切换有影响P1立即通报&处理短信&邮件IO thread stopped从库不能正常同步,影响从库只读业务一致性;HA架构的切换有影响P1立即通报&处理短信&邮件SQL thread stopped从库不能正常同步,影响从库只读业务一致性;HA架构的切换有影响P1立即通报&处理短信&邮件Slave is NOT read only从库非只读,可能导致业务数据写入从库,P1立即通报&处理短信&邮件导致主从数据库脑裂,很难merge,可能引起用户数据丢失InnoDB Deadlock detected导致冲突的事务回滚,影响程序并发p2及时通报&处理短信&邮件Slow queries high 说明数据库响应程序请求过慢,造成用户检验差,过长慢查询可能导致超时被killp2及时通报&处理短信&邮件Table open cache too small对表操作,有一定的性能影响,不太明显p2及时通报&处理短信&邮件Net Interface Traffic IS High (90M)网络IO比较高,查询响应明显变慢,超时变多   CPU Idle is too low<10%说明cpu的wa/us/sys比较高,MySQL的处理能力和并发都下降严重,连接数上升;程序请求的RT升高,Throughput降低,前端可能请求堆积P1立即通报&处理短信&邮件Processor load is too high (>16)此时数据库处理能力下降P1立即通报&处理短信&邮件disk.io.util (>90%)IO响应可能开始变慢p2立即通报&处理短信&邮件 Free disk space is less than 5% on volume (/和数据目录)磁盘空间有满了的风险,如果占满,MySQL无写再入数据,基本挂死P1立即通报&处理短信&邮件Free disk space is less than 20% on volume(/和数据目录)磁盘存储空间需要扩容p2及时处理短信&邮件Lack of available memory on server (<5%)MySQL已经开始使用swap, 某些查询的数据落到swap的,形成si/so响应时间明显增大。如果swap也使用完,MySQL有被OOM Killer的危险p2及时处理短信&邮件
  • 19. 监控和告警-Open-FalconOpen-Falcon Redis指标采集-redismon 项目地址: https://github.com/ZhuoRoger/redismon 采集80+余项
  • 20. (本页无文本内容)
  • 21. 备份和还原 数据备份重要性- 案例2006年Couchsurfing 沙发旅行,因MySQL故障,且无binlog和备份,导致创业三年多公司直接关闭2009年Journal Space经历6年公司,由于Raid故障且无备份,掉了所有用户数据,停止运营2013年下厨房由于数据库升级和误操作,最终各层灾难恢复,经过1周恢复出99%的数据
  • 22. 备份和还原关键因素RPORTO容灾成本影响
  • 23. 备份和还原数据备份有效性加密压缩分布式存储数据还原延时从一致性校验基础流程
  • 24. 数据备份1 全量备份:Percona XtraBackup 2 binlog差异备份:mysqlbinlog实时远程备份
  • 25. 分布式存储单服务器单数据中心单地区
  • 26. 备份系统
  • 27. 慢查询报表项目首页:https://github.com/box/Anemometer 基于MySQL 慢查询日志 + pt-query-digest分析
  • 28. 高可用Uptime, Downtime, Nine s90% 1 个9 36.5 天/ 年 99% 2 个9 3.65 天/ 年 99.9% 3 个9 8.76 小时/ 年 99.99% 4 个9 52 分钟/ 年 99.999% 5 个9 5 分钟/ 年 99.9999% 6 个9 31 秒 / 年
  • 29. 高可用-MHAMHA for MySQL: Master High AvailabilityManager tools for MySQL 项目首页:http://code.google.com/p/mysql-master-ha[server default] manager_workdir manager_log= user=mha_user password=pwd port=3308 repl_user= repl_password= ssh_user=root master_binlog_dir remote_workdir ping_interval=15 ping_type=SELECT [server1] hostname=idc1-host1 port=3308 candidate_master=1 [server2] hostname=idc1-host2 port=3308 candidate_master=1 [server3] hostname=idc2-host2 port=3308 no_master=1 ignore_fail=1
  • 30. 中间件MySQL中间件的功能定位扩展性 自动分库分表自动读写分离安全MySQL Local AuthenticationSQL流量过滤防火墙限流高性能只读负载均衡无明显性能损耗只读权重高可用从库故障下线主库自动FailOver自身高可用
  • 31. 中间件-MaxScaleMaxScale: 是MariaDB Corporation开源的基于MySQL协议的智能中间件平台 项目首页:https://github.com/mariadb-corporation/MaxScale
  • 32. MaxScaleMaxScale: 是MariaDB Corporation开源的基于MySQL协议的智能中间件平台 项目首页:https://github.com/mariadb-corporation/MaxScale
  • 33. 审核上线系统审核上线为什么要自动化? 背景 数据库变更操作(DML和DDL) 700+次/月,曾经回58个邮件/天,多次沟通协议 一次收到40000行的SQL文件,人肉审核? 每次DML操作,需对变更结果集手动备份 每次DDL不合规范,打回后都要沟通和说明 业务发希望1小时时间窗口内,数据库变更完成,业务功能上线 DML操作where有误,导致数据订正出错,需马上回滚受影响的行 “人肉审核”的问题 DBA很累,时间和精力消耗大,我们希望DBA把80%时间投入到另外20%最有价值的工作中 审核效率低 错误率高 过度依赖于个人,有主观性(技术能力和职业素养) 需要一个自化的平台,把大家解放出来
  • 34. 审核上线系统 “技术债”越早还越好,否则”复利效应”+”时间”只会代价越来越大!!
  • 35. 审核上线系统- Qunar InceptionInception是去哪儿网开源的,集上线审核、备份和(预)执行的自动化审核上线系统 项目首页:https://github.com/mysql-inception/inception架构图服务器模式
  • 36. Qunar InceptionInception支持检查规则: 基本每个检查项都对应一个参数变量 如:inception_max_keys (1~1024, default: 16):单表最多的索引个数 DDL表操作检查检查操作的库,表必须存在表引擎必须InnoDB字符集要求utf8不支持外键表要求有注释列设置not null自增列从1开始字段不能用BLOB/TEXT表必须包含主键单表最多5个索引不能有重复索引…….DML update/delete检查操作的库,表必须存在必须指定where条件检查update字段必须存在Where指定字段必须存在被影响行数大于1w条,告警Delete不能有limit条件…….DML insert检查操作的库,表必须存在必须字段列表指定的字段必须存在…..
  • 37. Qunar InceptionInception自动支持pt-online-schema-change的表变更. 参数inception_osc_min_table_size,当计算表的大小大于此值时,Alter自动切换为osc mode. inception_osc_on开关设置为ONInception参数pt-osc参数Inception参数pt-osc参数inception_osc_check_interval--check-intervalinception_osc_max_lag --max-laginception_osc_chunk_size --chunk-sizeinception_osc_drop_new_table--[no]-drop-new-tableinception_osc_chunk_size_limit--chunk-size-limitinception_osc_drop_old_table--[no]-drop-old-tableinception_osc_chunk_time--chunk-timeinception_osc_recursion_method --recursion-methoninception_osc_max_thread_connected--max-load Threads_conneted=xxxinception_osc_print_sql--printinception_osc_max_thread_running--max-load Threads_running=xxxinception_osc_critical_thread_connected--critical-load Threads_conneted=xxxinception_osc_critical_thread_running--critical-load Threads_running=xxx
  • 38. Qunar InceptionInception支持pt-online-schema-change的常用命令: pt-osc的参数变量查看和修改:inception get variables; inception set xxx=xxx pt-osc正在执行的所有session: inception get osc processlist; 查看某个pt-osc操作进度等详细信息:inception get osc_percent “”; 中止某个pt-osc操作: inception stop alter “”;
  • 39. 自动巡检系统巡检系统 定期自动检查MySQL运行健康状态,生成报告;用于发现存在的隐患和进行容量评估。Schema无主键的表非InnoDB业务表存储过程/外键/视图检查自增ID主键溢出检查(80%)重复索引检查大表检查容量存储空间剩余时长最小 TOP 10内存使用剩余时长最小 TOP 10网络流量峰值Swap使用SSD磨损剩余QPS峰值性能慢查询数量95%/99%分位 max和avg响应时间部份AWR报告和优化建议巡检项
  • 40. 响应时间响应时间:  first outbound packet-last inbound packet 重要性:1 一个大系统的调用链都比较长,常常业务RT变慢,就会问是不是DB或cache响应慢了 2 有些系统对应用1%的延时抖动都会敏感 简单实现: Tcprstat tcprstat -f '%T\t%n\t%95a\t%95M\t%99a\t%99M\t \n' --port=3306 -t 1 -n 100 Percona的Query Response Time
  • 41. More… 数据归档 安全审计 授权系统+KeyCenter ……..
  • 42. DBaaS Database as a Service
  • 43. 传统部署——运维驱动
  • 44. 整体设计服务 发现服务器管理服务部署实例管理数据管理运行 状态
  • 45. DBaaS Architecture
  • 46. DBaaS MySQL
  • 47. DBaaS MySQL特点快速部署 1分钟内完成集群部署 单实例 & 多实例 可支持单机单实例部署,独占所有资源 也可单机多实例部署,提升硬件利用率 高可用 故障对服务透明 从库挂掉,自动重建一个从库实例,不影响访问 主库挂掉,1分钟内恢复主库功能 自动保持实例数不变
  • 48. DBaaS MySQL特点快速增加、减少只读实例 数据备份 可恢复到30天内的任意时间点 支持Proxy 统一入口 负载均衡 服务发现 状态报告 资源使用情况 数据库运行报表
  • 49. DBaaS Redis
  • 50. DBaaS Redis特点快速部署 1分钟内完成集群部署 自动分片 主从关系 高可用 故障对业务透明 从实例挂掉,自动重建一个从实例 主实例挂掉,秒级完成切换 自动保持实例数不变
  • 51. AgendeMySQL开发规范简介MySQL部分常用平台简介Redis Cluster的简介
  • 52. Redis Cluster
  • 53. FailOver
  • 54. Wants you!虚位以待: 高级运维研发工程师 高级数据库开发工程师 高级应用运维工程师 高级系统运维工程师 简历请砸向这里: zhuorulin@xiaomi.com 一起来还“技术债”,也是很有意思的
  • 55. Thanks!
  • 56. 精品课程推荐 3w教育荣誉出品《MySQL DBA脱产班》 《MySQL DBA周末加强提高班》 《HTML5 零基础全日制班》 《互联网架构师班》 《云计算周末集训营》 《Linux运维零基础脱产班》 联系方式: 咨询QQ群:167605499