最近在做sql性能优化,下面是DBA给的指引
我们怎么决定,是采用读写分离的架构,还是采用sharding的架构?
总体来讲,DBA团队prefer sharding机制,而不是严重依赖于replication based read/write split;
对于现有的读写分离应用,要进行梳理;
新的读写分离的方案,要么经过架构评审委员会评审,要么经过开发总监和DBA总监确认;
读写分离的好处:
1. 在相对简单的付出下(只需要做读写分离,相对于sharding而言,开发简单很多),可以解决系统的scalability的问题;
2. 对于写的高可用相对要求低,对读的高可用要求/读的qps要求非常高(比如用户登陆,移动的配置类型信息)可以很容易的实现系统扩展新问题
3. 很容易实现读库的99.999%的高可用
4. 可以某种程度上,降低大查询/报表类应用,对在线系统的压力;(比如wms/tms之类,不过不鼓励这种做法)
读写分离的坏处:
1. 应用开发需要清楚知道,什么时候读主库,什么时候读从库;如果主从延迟,导致异常单,应该作为bug处理;
2. 对于应用开发,某种程度上,对开发的要求更高:不能写大job(从库一定delay),不能写大SQL(从库也很容易delay);
3. 需要处理从库delay的exception;何时回源主库;大部分是不能/不应该回源的;集中回源很容易压死主库;
4. 容易让dev abuse系统;不做过多优化;
5. 降低主库的写容量,当TPS超过6k,从库就会延迟逐步增加,而主库的TPS其实可以去到1.5w甚至2w
总体判断的guideline:
1. 读写比率,必须在10:1以上(建议20:1以上),才有可能考虑读写分离;不然原则上,不同意读写分离,不管如何还是要求避免延迟,比如:防止延迟的时候突发failover,那要么抛弃延迟的数据(如果硬件故障,大事务产生的大量binlog来不及传送,必然丢数据),要么等延迟过去拉长了故障恢复时间;
2. 读写分离的数据库,必须在读库前面,有LVS,来虚拟化读库的IP;原则上,10:1的读写分离,读库的压力会大大于写库;也需要多个数据库服务器来支撑;也需要LVS来掩盖和解决后端数据库服务器的维护问题(基本上读库可以逻辑上always online)
3. 读写分离的应用,程序要能够handle延迟的情况,必须能够容忍读库delay 3~5s; 如果不能容忍,请不要连接从库;
4. 读写分离的主库,在系统设计支撑的容量,3年内,写库应该不需要sharding拆分;不然,就是白白引入先读写分离,然后在主库sharding的复杂度;
5. 读写分离的应用,不能因为读写分离了,就可以滥用系统;在主库的大事务,和在从库的大SQL,都会导致slave的较大规模的delay;
6. 读写分离的应用,对于一个transaction里面的或者有强一致性依赖的,不能一下子去从库,一下子去主库;一个transaction里面的信息,必须都是consistent的;属于ms级别的;主从复制必然有这个delay的;
7. 原则上,读写分离的数据库,尽量不要让主库过于大(2个TB);
8. 读写分离,不应该作为报表的标准解决方案;报表类型的应用(后端系统,比如vis, tps,ods, tms, wms等较多)原则上,应该考虑通过hadoop来解决;MySQL天然不适合报表类型的应用;在线系统和报表系统,天然应该分离;
9. 读写分离应用:对于cache侧有可能回源的应用,可以考虑选择读写分离, 所有的读回源必须到 LVS
过去的例子,读写分离之后,由于应用设计的问题,带来的问题:
1. 订单/Coupon的问题;一个交易里面,一会儿读从库,一会儿读主库;在部分有几秒或者十几秒delay的场景下,发生异常;
a. 订单例子
业务场景:用户订单支付成功第三方支付平台回调流程中,pay域调用订单接口更新订单支付状态,主库更新成功从库同步存在延迟,pay域进一步调用审单接口进行审核(其中审核数据包含支付状态的判断),读取从库订单数据支付状态仍处于未支付状态,此时订单审单流程异常。
i. 原始订单数据<order_sn,pay_status>= <15032483118418,0> (pay_status 0:未支付,1:支付成功)
ii. 订单支付成功后,订单主库数据<15032483118418,1>从库数据<15032483118418,0>
iii. 审单时读取从库订单数据<15032483118418,0>,判断订单支付状态时出现异常
b. coupon例子
业务场景:coupon.api在大促前为了降低主库压力,应开发人员要求,在变更余额时判断余额金额的查询由主库迁到从库,并做了数据库主从一致性判断,当数据库主从不一致时,不能变更会员账户余额。但是订单退款其他操作是没有判断数据库主从一致性的,所以导致当退款操作时,数据库主从不同步时其他金额退款成功,礼品卡账户退款是失败的。这段时间数据库主从一致性不稳定,导致出现异常单。
解决方案:
在订单退款的时候,礼品卡账户从主库来获取。
常见的slave delay的原因:
1. 大job,一个update几万条几十万条记录的;
2. 一个update好多条记录(100+),而且是全表扫描类型的,没有合适的where条件可以走索引的;因为我们是基于row的复制,每一条都要跑一边这个全表扫描/低效的索引扫描;这个问题在slave会放大;
3. 表没有主键,导致从库复制每一条记录的DML都会做全表扫描
4. slave大SQL,比如频繁的大量的复杂join,耗尽磁盘的io能力或者耗光CPU资源;slave delay;
5. DBA维护操作,比如大表的online DDL,或者dba晚上的归档的job; --应该禁止;我们online ddl要求控制速度;归档也要控制速度和并发;麻烦的是两个同时kick off的时候;
6. 网络或者系统问题;比如跨机房网络,同机房网络不稳定,系统不稳定(机器故障);
7. MySQL bug;
8. 频繁对Text字段的表的读写,会把IO资源耗光,哪怕是flash卡
9. 一些框架,查询也会发起事务,如set autocommit=0,又不关闭连接,到发布需要做ddl变更时,就会导致表锁延迟
有读写分离的系统(判断标准:QPS最高是否超过10)
什么样的读写比率,和读写绝对值,我们决定采用读写分离架构?读写分离,我们在业务开发层面让他们aware,还是我们开发proxy中间件,隐藏读写分离对于业务的复杂度?
我们规划主库多少的tps,在接下来可以预见的将来(3年,或者100x的流量增幅)的成长下,可以采用读写分离架构?
读写分离的好处是什么:
在读写比率很悬殊的情况下,做读写分离,读通过LVS-MySQL的模式,可以做到几乎always online;而且后端系统维护,我们可以对业务透明;业务不会下线;
后端系统可以平滑扩容;对前端无感知;
坏处是什么:
必须写的量足够小;不然容易一个batch操作导致slave跟不上,或者大促情况下,写的绝对值增加导致从库可能的跟不上的情况;
数据库相对比较小;如果读写分离的数据库很大,那么会有每个都是几个TB的读库;
代码逻辑需要容错性,因为master-slave复制存在延迟的可能性
1、核心的读在mysql master上,如需要读取数据后判断是否更新状态,避免因为slave延迟导致判断失误
2、非核心的读,都在mysql slave上,如用户自发的查询,1)查询自己拿到多少奖励;2)查询自己的钱包余额;3)查询自己的会员信息等
所以,一个transaction,必须不能拆分;
多个 transaction ,或者业务上,不会再 10 , 100ms 内发生的两个 query ,就可以读写分离查阅从库;