宝宝树的网站架构运维总监刘秋岐《大型网站的数据库架构演变和优

大型网站的数据库架构演变和优化之道提纲 数据库架构设计的重要性 网站的数据库架构演进过程中遇到的问题主从延迟、主从不一致读写分离的高可用设计分库分表分布式Mysql DB集群

数据库集群的优化之道软件层面的优化硬件层面的优化指标先行软硬监“施”

刘秋岐

自我介绍 之前在搜狗和兰亭集势负责数据库的运维和架构设计 目前在宝宝树(母婴社区网站)负责网站的运维和架构 一枚热爱数据库技术的少年 邮箱：liuqiuqi@http://www.77cn.com.cn 个人微信号 acmore007扫码见右边，欢迎技术交流，共同进步

先讲两个故事某电商网站图书促销，我在购物车里面塞了些书，点击“购买”按钮后，浏览器迟迟没有响应…当晚该电商公司大老板发微博：“我已经紧急购买采购了10台服务器，增强网站后台，明天继续促销一天。”第二天一上班，我再次点击“购买”按钮后，悲剧的发现页面还是“Service is too busy”.当晚贵司老板发微博要请信息部同事喝茶(咖啡) 2012年年初，http://www.77cn.com.cn上线之后，在春运期间因为大量用户访问而崩溃，无法有效访问，网站崩溃时间非常长。该网站的技术引其业界各种讨论的声音，不过后来该网站逐渐稳定成熟起来。

数据库架构设计的重要性电商网站——图书促销： 能访问购物车，不能成功购买，问题出在订单系统 订单系统——数据库事务操作，缓存解决不了 事前的数据库伸缩性架构设计很重要 12306网站： 高并发的数据库访问，全是数据库事务操作 逻辑运算的复杂性，例如有人购买了区间票 单台DB无法承载访问量的压力，根据压力横向扩展很重要

网站的数据库架构演进随着网站壮大，Mysql数据库架构一般会经历如下演进：主从(两台多组)一主多从(多台读写分离)

分库分表

分库式DB

架构演进中会遇到的问题： 主从架构：主从延时、主从数据不一致了 一主多从：一主多从(读写分离)的高可用实现

分库分表：怎样合理分库分表 分布式DB:怎样设计分布式的Mysql DB集群，高性能高可用的实现DB路由

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架构演进——主从主从数据不一致：主从机制是主将它的变更作为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架构演进——一主多从,读写分离一个主库，多个从库，主库写，从库负责查询，主库的ha通过keepalived实现，从库读的高可用通过lvs或者haproxy实现 (haproxy修改权重生效慢)

Mysql架构演进——分库分表

为什么要分库分表？ 单个库数据容量太大，单个DBServer存储空间不够

单个库表太多，查询的时候，打开表操作也消耗系统资源 单个表容量太大，查询的时候，扫描行数过多，磁盘IO大，查询缓慢 单个库能承载的访问量有限，再高的访问量只能通过分库分表实现

Mysql架构演进——分库分表怎样合理分库分表：

一般按照业务来分库，避免跨库查询 分表有多种分法，大多按照主键id分表或者有区分度的主键字段分表 例如一些用户表的用户注册id是邮箱号(qweras345@http://www.77cn.com.cn),这种可以 按照id做MD5值，然后根据MD5串的第一位分16张表，按照第二位的话可以分 16*16=256张表等等

Mysql架构演进——分布式DB分布式DB主要依靠Web前端到DBServer中间的DBProxy proxy功能： mysql存活检测 主备库自动切换 读写分离，读请求负载均衡 分表读写问题： MySQL协议解析、事务支持产品：官方：MysqlProxy淘宝：Cobar 360:atlas

数据库优化——软软——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

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被污染)

数据库优化——软软——操作系统层面： 文件系统推荐使用 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

数据库优化——硬硬——硬件使用层面：数据库是三高系统(高CPU、高MEM、高IO) 选用大内存可以大大提升数据库，128/256G (国外有篇知名论文讲了数据库性能提升选择大内存的方案是优于选择提升IO的方案的) SAS磁盘、SSD、PCIE-Flash对比：

一般数据放在SSD盘上，日志放在普通磁盘上。

数据库优化——监监——合理的监控报警和预警机制：指标先行(制定指标，以此为基准进行优化比较) Mysql监控： Mysql服务可用：监控test库的一张表可以写入之后立刻查询 Mysql当前连接数、IOPS、CPU使用率、网络流量、QPS、TPS InnoDB缓存池：脏块的百分率、读命中率、缓存池利用率 InnoDB每秒读写量、InnoDB每秒从文件中读取/写入的次数 InnoDB日志：每秒日志物理写次数、每秒日志写请求数、每秒向日志文件完成的 fsync()写数量 临时表：创建到disk上的临时表的数量 (团购搜索项目)

MyISAM Key Buffer:平均每秒读/写命中率 MyISAM读写次数：每秒从缓冲池读/写次数、每秒从硬盘读/写次数 COMDML:每秒Delete、Insert、Replace、Select、Update语句执行次数