MySQL问题定位-性能优化之我见

时间:2022-08-27 02:31:22

前言

首先任何一个数据库不是独立存在的,也不是凭空想象决定出来的。

数据库的架构离不开应用的场景。所以,为了解决某些深入的问题,首先你得掌握数据库的原理与架构。原理掌握得越深入,越能帮助你定位复杂与隐藏的问题。

其次,DBA不能只局限于数据库本身。因为问题的来源,很多时候都来自用户表象(比如说用户反映查询某个东西很慢)。这个表象 问题,可能来自从应用到数据库,到OS,存储等方面。或者是网络链路的任一环节等。

最后,DBA常需要关注的层面,除了应用,更重要的还有OS硬件相关的层面。如内存,CPU,磁盘等。

背景知识:数据库经典三层架构图

MySQL问题定位-性能优化之我见

背景知识:MySQL体系架构

图1即为MySQL的逻辑架构图,可以简单地归结为四层结构:

第一层为客户端连接层,主要是做一些连接处理、权限认证、安全连接等处理。

第二层为服务管理层,实现了诸如SQL接口、解析、优化、缓存以及备份恢复、复制等核心功能。

第三层为插件存储引擎层,这是MySQL区别于其它数据库系统如ORACLE、MSSQL SERVER最重要的一点,MySQL中数据的存储和提取最终是由存储引擎来实现的,不同的存储引擎存取数据的方式不一样,它们通过统一的API与服务层进行通信。

第四层为数据存储层,确切地说它不属于MySQL系统,只是MySQL生成的数据、日志等文件最终是要保存在磁盘文件系统中的。

MySQL问题定位-性能优化之我见

背景知识:MySQL体系架构

MySQL问题定位-性能优化之我见

背景知识:MySQL内核架构

MySQL问题定位-性能优化之我见

MySQL定位问题关注方面

数据库层面

  1. MySQL Slow log (80%的问题,都来自SQL应用的问题)
  2. Mysql error log
  3. MySQL统计状态信息
  4. Show global status;
  5. Show engine innodb status\G
  6. Show full processlist;
  7. Show master status
  8. Show slave status

OS层面

  1. 内存,磁盘,IO,网络等
  2. free, vmstat, iostat, top, sar -n DEV, demesg, perf等

有图表的情况下,尽量多通过图表方式来查看指标变化趋势。所谓一图顶千言。

MySQL定位问题思路与方法

排除法

  1. 排除应用问题(查看slow log)
  2. 排除OS问题(查看OS各类输出,图表等)
  3. 排除数据库本身问题(查看数据库,状态,延时等)

搜索路径(知识爆炸的时代,谁也不可能记住所有的问题)

  1. 百度
  2. 谷歌
  3. Oracle Support知识库  https://support.oracle.com/portal/

慢SQL问题定位方法

  1. Explain
  2. Show Profile
  3. mysqldumpslow

MySQL性能优化关注点

SQL及索引优化

  • 根据需求写出良好的SQL,并创建有效的索引,实现某一种需求可以多种写法,这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化
  • 数据库表结构优化
  • 根据数据库的范式,设计表结构,表结构设计的好直接关系到写SQL语句。

系统配置优化

  • 大多数运行在Linux机器上,如tcp连接数的限制、打开文件数的限制、安全性的限制,因此我们要对这些配置进行相应的优化。

硬件配置优化

  • 选择适合数据库服务的cpu,更快的IO,更高的内存。
  • 但不意味着越强越好。因为我们总是在成本与收益之间权衡。配置过低,性能无法满足要求。配置太高,造成浪费。
  • 我们应该在选择合理的配置,并预留部分资源以应对突发流量。

注:通过下图可以看出,该金字塔中,优化的成本从下而上逐渐增高,而优化的效果会逐渐降低。

MySQL问题定位-性能优化之我见

性能优化的原则

  • 优化永远不会结束(也即不需要做无畏的优化)
  • 2/8理论,短板理论(改善20%的短板,提升80%的性能)

所谓性能优化,在大部分情况而言,就是找到导致性能的瓶颈所在,并加以解决。

案例分享

某部门系统从ORACLE数据库迁移到MySQL,碰到了严重的性能问题。实测TPS不足ORACLE DB的30%。

我的性能优化过程步骤:

  • 前期现场查看数据表定义,配置文件等。
  • 发现问题表没有使用主键,insert values一条一条插入导致速度很慢,配置文件参数不合适(innodb buffer pool, redo log, io_capacity等)等问题。
  • 现场测试,性能还是有问题(表象为Mysql服务器IO,CPU负载都很小)

    采用排除法

    • 用sysbench压测没有问题,排除硬件OS问题
    • 数据库查询状态正常,没有问题
    • 剩下,只能是应用问题。

      查看网络流量,抓取数据包,发现应用流量很小。

      代码排查,oracle以前用sequence。MySQL不支持,应用代码实现生成ID。

      这段代码有问题,出现锁争用,导致应用流量一直很小。