数据库优化——监
监——合理的监控报警和预警机制： 指标先行(制定指标，以此为基准进行优化比较） Mysql监控：
Mysql服务可用：监控test库的一张表可以写入之后立刻查询
Mysql 当前连接数、IOPS、CPU使用率、网络流量、QPS、TPS InnoDB缓存池：脏块的百分率、读命中率、缓存池利用率 InnoDB每秒读写量、InnoDB每秒从文件中读取/写入的次数 InnoDB日志：每秒日志物理写次数、每秒日志写请求数、每秒向日志文件完成的 fsync()写数量 临时表：创建到disk上的临时表的数量 (团购搜索项目)
监控工具：
Nagios (基本数值型监控，监控Mysql服务可用性、当前连接数等) Zabbix 可以根据监控数值绘图，可以保留历史数据，可以根据收集的数值做逻 辑运算，符合报警的条件即报警.
数据库优化——施
施——运维中的一些经验: 备份是最重要的，任何对表的操作都提前备份表 Sql审核，上线前对sql做审核，查看sql的执行计划，检查索引
Mysql server上线之前文件系统最好做好lvm，便于日后扩展空间，或者做
snapshot快速备份或者搭建从 定期做主从一致性校验，防止从库切换为主库发现问题 Slowlog 每日利用工具做reporting，工具： mysqlsla、 pt-query-digest 定时验证备份可用性
• innodb_adaptive_flushing_method 设置为 keep_average
• innodb_stats_on_metadata=0 关掉一些访问information_schema库下表而产生的 索引统计
• innodb_flush_neighbor_pages=0
• innodb_change_buffering=inserts • innodb_old_blocks_time=1000 (使Block在old sublist中停留时间长为1s，不会被 转移到new sublist中,避免了Buffer Pool被污染)
• 目前在宝宝树(母婴社区网站)负责网站的运维和架构 • 一枚热爱数据库技术的少年 • 邮箱：liuqiuqi@ • 个人微信号 acmore007 扫码见右边，欢迎技术交流，共同进步
先讲两个故事
某电商网站图书促销，我在购物车里 面塞了些书，点击“购买”按钮后， 浏览器迟迟没有响应… 当晚该电商公 司大老板发微博：“我已经紧急购买 采购了10台服务器，增强网站后台， 明天继续促销一天。” 第二天一上班，我再次点击“购买” 按钮后，悲剧的发现页面还是 “Service is too busy”.当晚贵司老 板发微博要请信息部同事喝茶(咖啡) 2012年年初， 上线之后，在春运期间因为大量 用户访问而崩溃，无法有效访 问，网站崩溃时间非常长。 该网站的技术引其业界各种讨论 的声音，不过后来该网站逐渐稳 定成熟起来。
MyISAM Key Buffer：平均每秒读/写命中率
MyISAM读写次数：每秒从缓冲池读/写次数、每秒从硬盘读/写次数 COMDML：每秒Delete、Insert、Replace、Select、Update语句执行次数
数据库优化——监
监——合理的监控报警和预警机制： 辅助监控：
Slowlog的每分钟/每5分钟的数目的统计曲线 对Binlog的一些定制化统计曲线(比如对订单表的更新次数曲线等)
数据库优化——软
软——操作系统层面：
文件系统推荐使用 XFS，挂载时候设置挂载属性noatime,nodiratime,nobarrier (nobarrier 是避免操作系统的cache) 关闭 numa=off 增加本地端口，以应对大量连接 echo ‘5120 65000’> /proc/sys/net/ipv4/ip_local_port_range 增加队列的连接数 echo ‘8192’> /proc/sys/net/ipv4/tcp_max_syn_backlog 设置连接超时时间 echo ’60’> /proc/sys/net/ipv4/tcp_fin_timeout
数据库优化——软
软——mysql优化调整层面：
• innodb_flush_log_at_trx_commit (根据安全性考虑可以设置为2日志先写到log_file 然后每隔1秒刷新到disk) • innodb_write_io_threads=16 (脏页写的线程数，加大该参数可以提升写入性能) • innodb_flush_method=O_DIRECT • innodb_adaptive_flushing 设置为 ON
数据库架构设计的重要性
电商网站——图书促销： • 能访问购物车，不能成功购买，问题出在订单系统 • 订单系统——数据库事务操作，缓存解决不了 • 事前的数据库伸缩性架构设计很重要 12306网站： • 高并发的数据库访问，全是数据库事务操作 • 逻辑运算的复杂性，例如有人购买了区间票 • 单台DB无法承载访问量的压力，根据压力横向扩展很重要
数据库优化——硬
硬——硬件使用层面：
数据库是三高系统(高CPU、高MEM、高IO) 选用大内存可以大大提升数据库，128/256G (国外有篇知名论文讲了数据库 性能提升选择大内存的方案是优于选择提升IO的方案的) SAS磁盘、SSD、PCIE-Flash 对比：
一般数据放在SSD盘上，日志放在普通磁盘上。
• 单个库表太多，查询的时候，打开表操作也消耗系统资源
• 单个表容量太大，查询的时候，扫描行数过多，磁盘IO大，查询缓慢 • 单个库能承载的访问量有限，再高的访问量只能通过分库分表实现
Mysql架构演进——分库分表
怎样合理分库分表：
• 一般按照业务来分库，避免跨库查询
• 分表有多种分法，大多按照主键id分表或者有区分度的主键字段分表 • 例如一些用户表的用户注册id是邮箱号(qweras345@)，这种可以 •按照id做MD5值，然后根据MD5串的第一位分16张表，按照第二位的话可以分 16*16=256张表等等
Mysql架构演进——一主多从,读写分离
一个主库，多个从库，主库写，从库负责查询，主库的ha通过keepalived实现， 从库读的高可用通过lvs或者haproxy实现 (haproxy修改权重生效慢)
Mysql架构演进——分库分表
为什么要分库分表？ • 单个库数据容量太大，单个DBServer存储空间不够
Mysql架构演进——主从
主从数据不一致：
主从机制是主将它的变更作为event发送给从，从将更改记录存成relay log放在本地，从 的sql_thread 执行relay log，这个过程中event发送和从执行relay_log中出现问题导致主 从数据不一致
解决办法：
使用percona-toolkit中pt-table-checksum和pt-table-sync做同步 重新从主库使用xtrabackup对innodb做在线热备然后做新的从库 使用了lvm的DBServer可以使用lvm的snapshot做数据快照进行数据拷贝而搭建新从 库(保证数据完全一致)
Mysql架构演进——主从
主从延时： 主库写多，从库单slave_sql_thread跟不上主库并发写，主从同步就会产生延时 解决办法： • 升级mysql至mysql-5.6.3，支持多线程的主从复制
• 使用MariaDB-10，可以实现并行复制
• 主库使用机械硬盘，从库可以使用SSD盘或者PCIe Flash，尽量使主从库一个机房 • sync_binlog=0 , innodb_flush_log_at_trx_commit = 0/2 • 拆主库，拆一个主库为两个主库
Mysql架构演进——分布式DB
分布式DB主要依靠Web前端到DBServer中间的DBProxy proxy功能： • mysql存活检测 • 主备库自动切换 • 读写分离，读请求负载均衡 • 分表读写 问题： MySQL协议解析、事务支持 产品： 官方：MysqlProxy 淘宝：Cobar 360 ：atlas
大型网站的数据库架构演变和优化之道
提纲
• 数据库架构设计的重要性 • 网站的数据库架构演进过程中遇到的问题
主从延迟、主从不一致 读写分离的高可用设计 分库分表 分布式Mysql DB集群

• 数据库集群的优化之道
软件层面的优化 硬件层面的优化 指标先行 软硬监“施”
刘秋岐
自我介绍
• 之前在搜狗和兰亭集势负责数据库的运维和架构设计
网站的数据库架构演进
随着网站壮大，Mysql数据库架构一般会经历如下演进：
主从(两台多 组) 一主多从(多台 读写分离)
分库分表
分库式DB
架构演进中会遇到的问题： 主从架构：主从延时、主从数据不一致了 一主多从：一主多从(读写分离)的高可用实现
分库分表：怎样合理分库分表
分布式DB：怎样设计分布式的Mysql DB集群，高性能高可用的实现DB路由