MySQL问题定位-性能优化之我见
MySQL问题定位-性能优化之我见
前言
首先任何一个数据库不是独立存在的,也不是凭空想象决定出来的。
数据库的架构离不开应用的场景。所以,为了解决某些深入的问题,首先你得掌握数据库的原理与架构。原理掌握得越深入,越能帮助你定位复杂与隐藏的问题。
其次,DBA不能只局限于数据库本身。因为问题的来源,很多时候都来自用户表象(比如说用户反映查询某个东西很慢)。这个表象 问题,可能来自从应用到数据库,到OS,存储等方面。或者是网络链路的任一环节等。
最后,DBA常需要关注的层面,除了应用,更重要的还有OS硬件相关的层面。如内存,CPU,磁盘等。
背景知识:数据库经典三层架构图
背景知识:MySQL体系架构
图1即为MySQL的逻辑架构图,可以简单地归结为四层结构:
第一层为客户端连接层,主要是做一些连接处理、权限认证、安全连接等处理。
第二层为服务管理层,实现了诸如SQL接口、解析、优化、缓存以及备份恢复、复制等核心功能。
第三层为插件存储引擎层,这是MySQL区别于其它数据库系统如ORACLE、MSSQL SERVER最重要的一点,MySQL中数据的存储和提取最终是由存储引擎来实现的,不同的存储引擎存取数据的方式不一样,它们通过统一的API与服务层进行通信。
第四层为数据存储层,确切地说它不属于MySQL系统,只是MySQL生成的数据、日志等文件最终是要保存在磁盘文件系统中的。
背景知识:MySQL体系架构
背景知识:MySQL内核架构
MySQL定位问题关注方面
数据库层面
MySQL Slow log (80%的问题,都来自SQL应用的问题)
Mysql error log
MySQL统计状态信息
Show global status;
Show engine innodb status\G
Show full processlist;
Show master status
Show slave status
OS层面
内存,磁盘,IO,网络等
free, vmstat, iostat, top, sar -n DEV, demesg, perf等
有图表的情况下,尽量多通过图表方式来查看指标变化趋势。所谓一图顶千言。
MySQL定位问题思路与方法
排除法
排除应用问题(查看slow log)
排除OS问题(查看OS各类输出,图表等)
排除数据库本身问题(查看数据库,状态,延时等)
搜索路径(知识爆炸的时代,谁也不可能记住所有的问题)
百度
谷歌
Oracle Support知识库 https://support.oracle.com/portal/
慢SQL问题定位方法
Explain
Show Profile
mysqldumpslow
MySQL性能优化关注点
SQL及索引优化
根据需求写出良好的SQL,并创建有效的索引,实现某一种需求可以多种写法,这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化
数据库表结构优化
根据数据库的范式,设计表结构,表结构设计的好直接关系到写SQL语句。
系统配置优化
大多数运行在Linux机器上,如tcp连接数的限制、打开文件数的限制、安全性的限制,因此我们要对这些配置进行相应的优化。
硬件配置优化
选择适合数据库服务的cpu,更快的IO,更高的内存。
但不意味着越强越好。因为我们总是在成本与收益之间权衡。配置过低,性能无法满足要求。配置太高,造成浪费。
我们应该在选择合理的配置,并预留部分资源以应对突发流量。
注:通过下图可以看出,该金字塔中,优化的成本从下而上逐渐增高,而优化的效果会逐渐降低。
性能优化的原则
优化永远不会结束(也即不需要做无畏的优化)
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。
这段代码有问题,出现锁争用,导致应用流量一直很小。
来源https://www.cnblogs.com/chasetimeyang/p/15069478.html