DBA很忙:MySQL性能优化及自动化运维实践 - 上篇

发布时间:2018-08-15 编辑:小张个人博客 查看次数:3178


本文作者将站在更加全面的角度分享他在这一年多 DBA 工作中的经验,希望可以给大家带来启发和帮助。

MySQL性能优化及自动化运维实践

DBA 的日常工作

DBA 的日常工作

我觉得 DBA 真的很忙,我们来看看 DBA 的具体工作:备份和恢复、监控状态、集群搭建与扩容、数据迁移和高可用。

上面这些是我们 DBA 的功能,了解这些功能以后要对体系结构有更加深入的了解,你不知道怎么处理这些故障和投诉的事情。

所以我们要去了解缓存/线程、SQL 优化、存储引擎、SQL 审计以及锁与实务;体系结构更深一点,就去研究内核原理和源码定制。

DBA 有这么多工作,它们就像一个小怪兽一样等着我们去解决。

MySQL 的性能优化

MySQL 的性能优化

性能优化让我们的 MySQL 跑的更快、更顺畅。在我们开始 MySQL 性能优化之前,我想提出 MySQL 性能优化的三个关键点:Why?What?How?

为什么我们要做性能优化?我们的运维来反映我们的数据库,正常情况下是 1 秒,后来变成 10 秒,我们就要启动优化的动作。原本他的访问时间是 1 秒,我们想优化成 0.01 秒就要开启优化。

第二就是 What?哪里是导致我们数据库性能变差的原因,需要找到这个关键点。

当我们找到这个问题以后,我们就需要有的放矢地进行优化。MySQL 优化之前我们要明确 3W 关键点。

MySQL 优化基本流程

MySQL 优化基本流程

对于开展 MySQL 优化一个基本的流程如下:我们首先要登陆到操作系统,通过操作系统的命令进行优化。

比如说通过操作系统的基本命令,去看我们操作系统有什么资源的占用率比较高。

就是哪里出现了资源短板,短板的意思就是这个资源的占用率或者是使用率特别高,我们要密切关注。

比如说像 CPU 的负载特别高,已经超过了我们的核数,或者是使用率特别高,已经达到了 80% 以上,这就引起我们的关注了。

确定这个短板之后,我们就要确认哪个进程使用我们这个资源,使得它的使用率或者是占用率特别高。

一般情况下跟我们相关的就是 MySQL 这一层,比方说使用 CPU 的 70% 以上,我们就要去检查一下这个 MySQL 出现了什么问题。

再进一步往里推进,如果我们发现 MySQL 里面是执行某一条大 MySQL 的时候,发现整个服务器或者是整个数据库就在那里,可能就是语句问题。

我们就要进一步通过 MySQL 的监控或者是日志信息去排查 MySQL 的问题,很重要的是发现哪个资源出现问题进行排查。

我们登陆系统发现 CPU、IO、网络等等都很正常,这种情况下怎么办?

这种情况下我们可以分三种情况判断:

1、操作系统的问题,可能我们登陆 MySQL 的时候整个系统就在那里了,我们需要通过操作系统去查是哪个资源的问题。

2、数据库实例问题,数据库实例问题跟数据库配置参数相关,也就是说我们配置参数可能存在一些不合理的设置需要我们去优化。

3、会话问题,我们登陆到 MySQL 里面,一开始很正常,后来我们发现这个实例慢下来了,可能就是 MySQL 语句有问题,我们需要看 MySQL 的执行计划到具体哪一步比较慢,拖慢了整个流程。

我们发现数据库性能出现问题,都可以沿着这个流程走下去,从而定位出问题。

MySQL 优化的几个关键点

MySQL 优化的几个关键点

我们通过刚才的基本流程,可以确定出 MySQL 需要优化的几个关键点如下:

1、应用访问的优化,因为有应用需要访问我们的数据库,有请求的发送、数据的存储和网络的交互等等,会导致数据库性能发生比较慢的地方。

2、服务器硬件选型,不知道大家 DBA 对服务器有没有自主权,如果有自主权的情况下,我觉得我们应该按照 MySQL 的特性来选择服务器的硬件。

比方说我们可能要考虑到数据和日志的存储机理不同,要选择不同的类型去优化它。

3、操作系统的优化,就是我们部署配置数据库之前,要对操作系统有什么优化?能够让我们的数据库有优化。

4、数据库优化,数据库优化过程是一个全局角度优化的过程,不仅仅是是针对数据库本身优化的流程。

应用访问优化

应用访问优化

我们根据每个关键点稍微开展一下,比方说应用访问的优化。

首先第一步就是减少数据的访问。因为减少数据的访问其实就是减少磁盘的访问。

我们知道数据访问磁盘获得数据的速度很慢,如果我们是器械磁盘,因为器械磁盘是通过器械旋转来获得数据。

我们应该把活跃数据和内存数据放在内存里面,这样可以使我们的数据库性能提升 1-1000 倍,它的优化成本很低。

第二步是减少返回更多的数据,减少返回更多的数据终结就是减少了网络的传输,有很多大的系统,网络传输是一个很重要的瓶颈。

假设我们的数据库服务器跟我们应用服务器的距离是 20 公里的话,因为光线数据库是 20 公里,一个光的请求是 0.2 毫秒。如果我们减少更少的数据请求的话,那这个时间就会变短很多。

所以说如果我们发现数据库的性能有问题,我们可以去看是否网络上存在问题或者是通过 P 命令看时间是否会变得长。

第三是减少交互次数,每个交互假设还是按照 20 公里来说,一个交互的时间就是 0.2 毫秒,2 个交互就是 0.4 毫秒。如果有 1 万个操作的话,就是 1 万乘 0.4 毫秒,那就变得整个交互时间变短了很多。

但是也有它的复杂性或者是不宜扩展的局面。从应用层就降低了优化。这个成本也是很低的。

应用访问的优化

我们公司的 DBA 对于服务器的应用选型没有太多的话语权,移动公司都是集团公司通过集采来选择的。

在采集的时候我们不可能规定这几台服务器是用在哪个数据库,这几个数据库用在什么服务系统。所以我们在服务器选型时候 DBA 是没有办法参与进去的。

这是我们移动云的一些服务器的选型:采用的服务器是惠普的 DL360G9,CPU是 2 核×e5-2650V4,内存是 8×32G,硬盘是 6×1.2TSAS,网卡是 4×10GE+4×1GE+1IPMI。

这里特别说一下,如果我们 DBA 对于服务器有自主权的话,我们可以把数据放到 SSD 盘,把日志放到 SAS 上,这就是服务器硬件选型需要主要的地方。

操作系统层面的优化

我们推荐使用 Linux 操作系统,一些开源主流的是我们做的。像一些商业版 Linux 这些就是我们在用的。

如果要使用这个 SWAP 值,我们尽量不去使用虚拟内存,而使用物理内存。因为物理内存的访问速度肯定比去访问磁盘要快得多。

所以我们把这个值设成了 10。有的同学可能就会说为什么不把这个值设成 0,就直接全部访问物理内存就好了。

如果把它设为 0 的话,可能就会出现内存溢出的现象,就是 OOM。这不是我们 DBA 想看到的情况,所以我们一般把这个值设成 10。

关闭 NUMA 特性:我们公司一般是单实例的情况,所以这个时候 NUMA 的特性要关注。

NUMA 特性就是假设我们一个服务器上有两个 CPU,分布在服务器左右两边,同时有四块内存,把同一侧 CPU 作为一个 NUMA 节点,就是在物理位置分布同一侧 CPU 访问同一侧内存,距离比较近,速度更快。

我们尽量同一侧 CPU 访问同一侧内存,这跟我们数据库的特性是相违背的。因为我们数据库希望它一般部署了数据库的服务器就不会布其他的应用系统资源了。

所以我们希望数据库是独占数据库资源,在这种情况下我们要尽量关闭这个 NUMA 特性。

网卡优化:我们采用多个物理网卡通过做 Bond 绑定成虚拟网卡,就是一些双网卡做成 Bond 或者调整网络参数。

磁盘调度设置:一般会有几个算法,如 NOOP 算法、CFQ 或者是 Deadline 算法。

比如说这 NOOP 算法用在我们数据库上有什么问题?就会有饿死读操作的方式存在,如果两个写操作,第一个写操作进来不需要等这个结束以后第二个写操作就可以开展了。

如果是读操作的话,第二个读操作就一定要在在前一个完成。如果有几毫秒的时间里面,进来一堆写操作,后面的读操作就会饿死的,这个不符合我们数据库算法调动的方式。

另外就是 CFQ 算法,CFQ 算法不适合我们的数据库服务器,MySQL 是单操作服务器。所以我们这个算法也不适合我们使用。

一般情况下数据服务器会使用 Deadline 的算法,程序会调用这个时候的 IO 请求去解决这个请求。

这种 Deadline 算法更加适合数据库,因为这个 Deadline 的算法更加适合。

数据库实例的优化

数据库实例的优化

我列了几个我们在标准化的时候需要规范和配置的参数,这里不一一揭示了。

这些参数很重要,因为它决定了我们实例的性能。某一些参数配置不合理,我们实例的性能就会受到很大的影响。

SQL 语句的优化

SQL 语句的优化

这里有编写高效 SQL 语句的原则,这个原则我们 DBA 要知道,同时要通知业务方的研发,让他们也知道。

有很多业务侧进来都是业务写的,他没有经验的话,就会写出一些有问题的语句,所以最后就变成我们 DBA 要去严查。

所以最开始要把这些思想贯彻给业务研发,让他们按照这个流程去编写 SQL 的设计。

索引的设计

索引的设计

这里说的是覆盖索引,比如说有了这个覆盖索引我们的查询,查询的字段都是在这个索引内。

还有我们查询的后面的字段也是索引,然后还有我们一些排序位置也是覆盖索引,就是这一系列全部都是中了索引的情况所以就叫覆盖索引。

这里我也列举了一些不能使用索引的情况:比如说不要给选择率低的字段选择索引,如果通过索引扫描记录数超过 30% 就变成全表扫描了。

还有 Like 额查询条件列最左以通配符 % 开始,两个独立索引,其中一个用于索引一个用于排序。以上就是对于 MySQL 性能优化的步骤。




出处:小张个人博客

网址:http://blog.023xs.cn/

您的支持是对博主最大的鼓励,感谢您的认真阅读。欢迎转载,但请保留该声明。

顶部

Copyright © 小张个人博客 All Rights Reserved 渝ICP备15006773号-1

联系方式:[email protected] | 本站文章仅供学习和参考

渝公网安备 50024102500267号