> 文档中心 > 快来给你的MySQL做个检查吧-数据库优化之全局分析

快来给你的MySQL做个检查吧-数据库优化之全局分析


前言

平台后端数据库是自己搭建和自己运维的Mysql ,版本是5.5.64,历史原因没有上云,有机会还是上云,把db维护交给专业的人去运维,没有专门的dba运维出现了不少问题,如mysql 主节点硬盘故障,备机切主导致系统中断半小时;后端接口调用不合理导致循环调用数据库致使数据库cpu持续维持在高位以及前端接口数据返回缓慢等问题。这块Mysql优化 就需要开发自己去多了解mysql系统架构、性能调优相关问题,监控Mysql 机器运行状态,本文就简单介绍下Mysql系统分析思路和采用的工具。

概述

数据库是个比较大的话题,有各种各样数据库常见的关系型数据库如Mysql 、oracle、非关系型数据库,还有图数据库等。数据库性能会跟许多部分有关联,从硬件底层存储设备、操作系统、数据库配置参数、数据库架构、数据库表结构、应用层面的连接池设置、以及SQL索引等。

数据库架构

对Mysql数据库进行分析,首先需要了解MySql的系统架构,如下图所示:
在这里插入图片描述
从这个架构图,来看Mysql系统架构分为应用层、MySql服务层、存储引擎层。

  • 应用层,应用层是MySQL体系架构的最上层,它和其他client-server架构一样,主要包含:连接处理、用户鉴权、安全管理
  • MySQL服务层:该层是Mysql Server的核心层,提供了Mysql Server 数据库所有逻辑功能
  • 存储引擎层
    存储引擎是MySQL中具体与文件打交道的子系统,也是MySQL最有特色的地方。MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。他根据MySQL AB公司提供的文件访问层抽象接口来定制一种文件访问的机制(该机制叫存储引擎)。
    物理文件包括:redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等

SQL运行过程

知道数据库架构后,在性能分析时候需要知道这些模块的功能及运行逻辑,明白一个具体的sql所需要经历的过程:一个sql首先经过Connection Pool到达系统后,需要先进入Sql interface模块判断这个语句,是什么类型。然后通过Parser 模块进行语法与语义检查,并生成相应的执行计划;接着到Optimizer模块进行优化,判断走什么索引,执行顺序等,然后就到Cache中找数据,如果Caches中找不到数据的话,就得通过文件系统到磁盘中进行寻找。

性能分析基本监控指标

了解了mysql系统架构和mysql执行过程还不够,在进行性能分析时,需要找出mysql的问题所得先了解一些基础知识和相应的监控工具。
首先需要了解的两个Schema 分别是information_schema和performance_schema,information_schema,它们保存了数据库中的所有表、列、索引、权限、配置参数、状态参数 等信息。像我们常执行的show processlist;就来自于这个 schema 中的 processlist 表。 performance_schema提供了数据库运行时的资源消耗情况,它以较低的代价收集信息, 可以提供不少性能数据。
还有在分析mysql是需要知道的两个命令:show global variables ;和show global status ;前一个用来查看配置的参数值,后一个用来查询状态值。不过这些命令只是简单的罗列信息,并没有统计分析,接下来我们来看下一个比较好的监控工具。

全局分析:mysqlreport

show status 输出的报告是用来计算性能瓶颈的参考数据,但是数据只是简单的罗列,不好一下子看出性能问题,而mysqlreport 不像show status简单的罗列数据,而是对这些参考数据加以融合计算,整理成一个个优化参考点,然后就可以根据这个优化参考点的值以及该点的衡量标准,进行对应的调整。

linux 环境下mysqlreport安装

  • 步骤一:yum -y install perl-DBD-MySQL 依賴包
  • 步骤二:yum -y install perl-DBI #依賴包
  • 步骤三 :yum -y install mysqlreport
    在linux系统上经过这三步就安装好了这个工具。接下来就可以对数据库运行状况进行分析了。

mysqlreport使用

使用比较简单,直接执行: mysqlreport --user tesla --password xxx@2015 --host 127.0.0.1 --no-mycnf --flush-status --outfile ./result.txt 就可以把数据库整体情况保存到当前目录中。
具体命令参数查看 mysqlreport —help

mysqlreport结果分析:

  • 数据库操作报表和查询排序报表

在这里插入图片描述

这个表反映数据库使用情况,608每秒操作量有点大,slow 这个参数挺重要,只是因为这里设置的慢查询10s太长了,正常情况下尽量设置在1s左右,这块需要对db 进行配置,把慢查询统计设置的短些。
DMS部分告诉我们这个数据库中各种 SQL 所占的比例,这个例子中,SELECT多,要做 SQL 优化的话,肯定优先考虑SELECT语句,才会起到立竿见影的效果。

  • select and sort 查询和排序报表
    这块的报表数据具有极大的参考性,一下就能看出问题的所在,这里的Scan(代表全表扫描)每秒48次执行全表扫描,实在是太多了,需要对语句进行修改,也是我们后面优化的重点内容。

  • InnoDB 缓存池报表

在这里插入图片描述
InnoDB 缓存池报表,Innodb Buffer Pool size 定义了Innodb 存储引擎的表数据和索引数据的最大内存缓存大小。这部分对MySQL来说很重要,这里使用已经达到100% 这种情况下就必须要增加Innodb缓存池了。这里的Read hit达到 92.57%,这个值越大越好,尽量达到100% 这里的值与Innodb buffer太小有关。

  • 连接报表

在这里插入图片描述
从这里可以看出数据连接还完全够用

  • 表锁报表

  • 在这里插入图片描述
    Waited表示有多少次查询需要等待表锁定;Immediate表示有多少次查询可以立即获得表锁定,同时后面还有一个比例
    对数据库来说『等待』几乎可以肯定是一件很不好的事情,因此 Waited 的值应该要越小越好。最具有代表性的是第三个字段 (Waited 占所有 table lock 的百分比)这里是0.00%,非常好,没有发送过表锁。

  • 临时表报表

在这里插入图片描述

执行explain 在sql分析时出现Using temporary的状态,这意味着查询过程中需要创建临时表来存储中间数据,我们需要通过合理的索引来避免它。另一方面,当临时表在所难免时,也要尽量减少临时表本身的开销,MySQL可以将临时表创建在磁盘(Disk table)、内存(Table)以及临时文件(File)中,显然,在磁盘上创建临时表的开销最大,所以我们希望MySQL尽量不要在磁盘上创建临时表,上面分析结果来看从临时表创建在磁盘(Disk table)和临时文件(File) 上的 量级来说,还是有点偏大了,所以,可以增大tmp_table_size。
其它全局信息可以查下资料

总结:

通过mysqlreport这个工具反应的结果,有以下问题需要去解决下:

  • 总体数据库操作达到600多每秒,对于内网系统用户不太多,操作有点太频繁,看下能够减少不必要的数据库操作。
  • 慢查询未开启,而且设置的时间太长长达10s,通常一个语句大于100ms 可任务需要进行优化,这里需要设置较短分析下慢查询
  • 全表扫描48.5/s 这块要分析下具体的sql写法
  • Innodb 缓存占用使用100% ,而且设置大小太小,需要增加缓存大小。