快乐学习
前程无忧、中华英才非你莫属!

闲聊篇-我们如何正确使用MySQL

一、互联网有很多关于数据库的故事!

       但大多数是事故,如何避免事故的发生呢!

数据库集群中只有一个主服务器并且没有任何一种主从复制高可用的组件。

如果主服务出现了故障并且没有自动切换的程序,需要DBA手动的在从服务器中选择数据最新的从服务器,提升为主服务器,并且在   与其他从服务器建立新的主从关系

如果从服务器相当多,手动操做的话这会消耗大量的时间,并产生客户的流失,导致双十一活动直接失败!

要在活动之前,提前做优化、安全工作,并模拟OLTP等测试。监测数据库的压力、TPS、QPS、并发量、CPU使用率、磁盘IO等、

发现哪块不行要及时的进行的调优方面的work!

  



   

二、数据库的事故是如何发生的呢?

双十一电商的某些活动策略,导致服务器压力成倍的增长,平时也就几万的访问,到了双十一就有几百万,上千万的并发访问量,很容易出现数据库的事故的发生。

三、如何减轻web服务的压力?如何减轻数据库的压力?

减轻web服务的压力 :部署、克隆HTTP服务器并实现集集群,通过session共享,保持会话一致,并通过url路由策略,很容易就实现web服务器的集群。

减轻数据库的压力:而我们数据库集群要考虑的东西太多了,要保持数据的完整性,一致性!

                             如果配置不得当,很容易发生多处写入数据,就会破坏数据的完整性和一致性!这种对业务会产生很可怕的影响!

                             数据库扩展如果没有良好的架构支持,是非常头疼的事情!

                             很可能双11搞得活动,就会因为数据库的gameover而随之gameover了,所以现在的大型电商公司,是非常重视DBA、以及研发人员数据库操作能力!




四、有哪些因素会对数据库的性能造成影响?

  

 4.1、超高的QPS和TPS 

 

           带来的风险是效率低下的SQL请求。

      

   TPS( 事务数/秒) = (Com_commit + Com_rollback) / Seconds

   QPS( 查询量/秒) = (Q2 - Q1)/10   间隔10秒查询两次并记录Q1,Q2,以此计算出QPS值

比较精准的测试公式:

          Com_diff = (int(mystat2['Com_commit'])   - int(mystat1['Com_commit']) ) / diff
          del_diff = (int(mystat2['Com_delete'])   - int(mystat1['Com_delete']) ) / diff
          ins_diff = (int(mystat2['Com_insert'])   - int(mystat1['Com_insert']) ) / diff
          rol_diff = (int(mystat2['Com_rollback']) - int(mystat1['Com_rollback']))/ diff
          sel_diff = (int(mystat2['Com_select'])   - int(mystat1['Com_select']) ) / diff
          upd_diff = (int(mystat2['Com_update'])   - int(mystat1['Com_update']) ) / diff
          que_diff = (int(mystat2['Questions'])    - int(mystat1['Questions']) )  / diff
          qps_s = sel_diff
          tps_iud = del_diff+ins_diff+upd_diff
          qps_ques=que_diff
          tps_Com_rol= Com_diff + rol_diff

          print 'qps_s = %s , qps_ques = %s , tps_iud = %s ,tps_Com_rol = %s'  %(qps_s ,qps_ques, tps_iud,tps_Com_rol)

4.2 大量的并发超高的CPU使用率

 大量的并发:会导致数据库连接数被占满(max_connections 默认100)

  默认的就太小了,因为生产环境中有很多nginx,tomcat服务器去跟数据库要链接,很快就会到达峰值,并且再次请求链接的时候,数据库就会拒绝,并在前端页面产生500页面 等,影响用户的访问。损失用户,根据自己的服务器现状调大连接数,如果调整完毕没有生效,那就是被自身操作系统文件的连接数限制了。

 建议MySQL升级到中坚版本,通过InnoDB的努力,能更加好的利用CPU等,来提高TPS和QPS.和并发。

 不要升级到最新版本,因为最新版本有可能之前用的中间件、和一些使用成熟的工具,不兼容!得不偿失!

 避免超高的CPU使用率:因CPU资源耗尽而出现宕机。

 

4.3、磁盘IO

 风险:磁盘IO性能突然下降(粗暴的解决方案:使用更快的磁盘设备)

          在有大活动之前,一定要做精细化的磁盘维护,保证IO的性能。

          也不要忽略磁盘的日常维护,日积月累所带来的隐患,也是很可怕的!

     

  

   举个例子:没有及时取消远程对主服务器做的备份计划,导致活动当天主服务磁盘IO超高,性能下降,所以监控的实时性很有必要,并能直接反馈出,哪个IO、线程所导致的,监控帮我们直接定位出问题点,直接干掉他,是避免数据库的事故发生的主要手段、

    所以活动之前,一定要取消各种影响数据库主服务器的IO计划   以及一些不利于的性能的因素!

4.4、网卡流量

 1、过多的从服务复制流量会增加网卡流量的负担,大活动之前应尽量减少不必要的从库的数量。

 2、 进行分级缓存,避免前端大量的缓存失效,直接对数据库流量的一种冲击!

 3、禁止select * 进行查询。为了避免前端研发没有严格遵守sql开发规范,直接在服务器端设置屏蔽掉select*的查询请求。

 4、分离业务网络和服务器网络

4.5、大表和大事务

大表:单表超过5千万行、表数据单个文件超过20G

大表你就意味着慢查询的产生,慢查询(平常只需要2秒就能查询出来数据,今天却用了20分钟!这里可能夸张了些,就是让用户再也无法忍受的时间,那么用户就很有可能弃你而去!)

大表建立索引需要很长时间,所产生的风险:小于5.5的版本建立索引时会锁表,大于等于5.5不会锁表,但会引起主从延迟。

大表对DDL操作的影响:修改表结构会需要长时间的锁表 ,所带来的风险,会造成长时间的主从延迟。因为小于等于5.5的版本的主从复制机制是单线程的都是在主库完成DDL操作之后,再通过日志传输到从库上进行相同的操作,这样来完成表结构变更和复制。这样其他从库就会等待,如果一个服务器需要480秒,多个服务器就会成倍的时间增长。所以更新mysql的版本是有必须要的!mysql5.6~5.7 已经实现多线程主从复制功能!

在主库上进行大表的表结构修改,有可能这个表的所有操作被阻塞。然后产生大量的数据库连接数,是因为所有的操作链接都被阻塞掉了。

说明:数据库主从复制干嘛的?相信很多开发的小伙伴都会很陌生?
      它解决的问题是:复杂均衡,实现对读密集型的应用的优化,故障切换、备份、并且只需要一个简单的change语句。
      并且运维的小伙伴简单的通过DNS轮询、lvs等就能很轻松的实现读负载均衡。这个小编没有尝试过,具体问问运维的小伙伴DNS轮询、LVS的原理吧~ 
      这里我推荐的是跟高级的玩法,就是通过中间件的功能:sql路由,来实现带有权重的读写分离,分库分表、故障自动化切换,sql防火墙与一体的!
      只需一个成熟的中间件即可!

如何处理数据库中的大表?

进行分库、分表

很少用公司自己实现分库分表的中间层代码,或者直接在源代码里,用代码策略来达到分库分表的效果,因为维护成本太大,一般公司还是承担不起。

所以小编直接建议使用成熟的商业数据库中间件服务即可。既不用破坏源程序的代码以及各个应用的之间的关联。并很好的保证了数据的一致性和完整性。

如果实在一点预算都没有,也没有能力分库,分表,那就只能对大表进行历史数据归档了、

实现一般是按照日期把以前经常不访问的数据,抽出来一个历史表。并在前端代码中实现一个历史查询接口。

难点:

1、分表主键的选择。

例如:订单表,可以用订单号来分表、或者以地区来分表,目标是利用分表策略来减轻数据库的压力。

2、归档时间点的选择,和如何进行归档操作

要评估哪些日期前的数据是根本不怎么访问的了,并且对上亿行的数据,DDL操作大量历史数据会直接造成更大的主从延迟,应当避免。     

大事务

运行时间比较长、操作数据比较多的事务。如果中间出现问题,也会发生回滚会消耗大量的时间,并且会造成锁定太多的数据,造成大量的阻塞和锁超时,执行时间长以及造成主从延迟。

处理大事务

1、避免一次处理太多的数据,要分批次获得。

2、移出不必要在事务中的SELECT操作。

4.6、服务器硬件

小编也是玩机爱好者,如果有钱我会追求极致的硬件性能。

有点钱就尽量选择多核高频的CPU,和高频大容量的内存,以及SSD固态硬盘,闪存盘等等。

如果要扩展内存,也要尽量买同品牌、同型号、同版本、相同颗粒数的一致的内存条,来达到最佳的双通道,提升内存的整体性能。

如果没有多少钱有成本的考量:根据是否为密集型的业务选择高频的CPU,而不是多核的CPU,

如果业务偏向于高并发、大吞吐那就多核一些。

       

    

4.7、服务器系统选择、和系统内核优化

在众多系统中,因为CentOS 是RedHat的克隆版,如果需要可以随时平滑切换到 RedHat,从而享受RedHat的服务支持

众多互联网公司也选择Centos作为服务器的首选,这里跟其他系统就不做比较了。用的人多,自然就好!

总体优化策略就是关闭不需要的服务,Cpu性能优化、 内核优化:/etc/sysctl.conf ,文件打开数等。让centos服务器系统保持最优状态!

没有什么资深不资深的玩家,玩的多了,就知道了!就像windows上,我们用现成的优化工具360等关闭不需要的服务,清理不需要的垃圾,以及windows优化大师调整cpu内核数,二级缓存大小xxx的,只不过在linux需要已命令行的方式进行手动调整而已!都很简单,多百度,多玩,多看调整后的效果,以及调整坏了,我们怎么来修复。这都是在不断的积累经验!

还有linux的文件系统,江湖传闻,XFS是性能最高的。那我们就使用它!并且Centos 7 默认的文件系统就是XFS,那这就肯定没错了!XFS跟Windows上的NTFS,FAT 是一样的东东,不要过分的想象他,如果理解不了,就拿windows上做对比和理解,其实Widows上可视化做的这么好,底层API也是蛮复杂,要不然也不能风靡全球!

下面的链接就是基本对centos的调优:

http://www.cnblogs.com/dejun/p/5726407.html

http://www.laozuo.org/1917.html

https://yq.aliyun.com/ziliao/56840

  


五、如何对数据库优化并获得良好的性能?

1、数据库的全局配置文件中核心参数的配置要精细化调整、并进行测试。这些可以看小编的前些天的文章找到更多参数的调优细节!

2、直接透明指定存储引擎为InnoDB, 因为InnoDB是事务性的成熟方案,并且mysql5.7上已经支持全文,空间的搜索技术,万不在要服务器上混合使用存储引擎,造成的bug不可预估!,风险很大!

3、 在设计表时,尽量避免创建太多的列,在可拓展的情况下,避免太多列可提高查询速,以及减少CPU的消耗。

4、不要过分的范式化、标准化、你越标准会造成太多的表关联。

     太多的表关联就会造成性能的降低,mysql本身限制是你最多可以关联61个表。

     所以有的时候要反范式化,就是不标准化一些,把一些小表合并成一张表,来提高数据库的性能!

5、在OLTP环境中使用不恰当的分区,会降低数据库的性能!

     一定要注意,如果您分区玩的好可以使用,玩的不好,就杜绝在小于等于千万级级别的表中使用分区功能,玩的不好的下场就是性能的急剧下降!

     不要迷恋分区,分区只适合用在OLAP或者非常大的日志表当中。

   

解释:https://www.ztloo.com/2017/05/08/day14-mysql高级开发之分区 中的二、分区和性能

6、很多开发人员喜欢使用外键保证数据的完整性!

     这种做法会让数据库的效率和性能降低的!MySQL对使用外键的表进行修改时都要对外键约束进行检查,这样会带来额外锁的开销,严重影响数据库的修改效率,等等~

    并且做主从复制清空表的操作时候,复杂度会变高,只能用delete。是无法使用Truncate命令的,Truncate是重构表,效率非常快的。

  

                        

打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » 闲聊篇-我们如何正确使用MySQL

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!