【架构性能分析(1)】MySQL性能瓶颈排查定位
来源:互联网 发布:盐城java软件培训 编辑:程序博客网 时间:2024/04/29 01:52
导读:
作为一个系统管理员(运维/架构师),我们时刻关注服务的变化情况,今天分享一篇关于MySQL性能瓶颈排查定位相关性的文章。
OS层面的检查确认
首先要确认当前是否存在哪些进程引起的负载高,以及这些进程卡在什么地方,瓶颈是什么。通常来说,服务器上最容易成为瓶颈的是磁盘I/O子系统,因为它的读写速度通常是最慢的。即便是现在的PCIe SSD,其随机I/O读写速度也是不如内存来得快。当然了,引起磁盘I/O慢得原因也有多种,需要确认哪种引起的。
第一步,我们一般先看整体负载如何,负载高的话,肯定所有的进程跑起来都慢。
执行指令 w 或者 sar -q 1 来查看负载数据:
[ly@imysql.com:~ ]# w 11:52:58 up 702 days, 56 min, 1 user, load average: 7.20, 6.70, 6.47USER TTY FROM LOGIN@ IDLE JCPU PCPU WHATroot pts/0 1.xx.xx.xx 11:51 0.00s 0.03s 0.00s w
sar -q 的观察结果:
[ly@imysql.com:~ ]# sar -q 1Linux 2.6.32-431.el6.x86_64 (ly.imysql.com) 01/13/2016 _x86_64_ (24 CPU)02:51:18 PM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 blocked02:51:19 PM 4 2305 6.41 6.98 7.12 302:51:20 PM 2 2301 6.41 6.98 7.12 402:51:21 PM 0 2300 6.41 6.98 7.12 502:51:22 PM 6 2301 6.41 6.98 7.12 802:51:23 PM 2 2290 6.41 6.98 7.12 8
load average大意表示当前CPU中有多少任务在排队等待,等待越多说明负载越高,跑数据库的服务器上,一般load值超过5的话,已经算是比较高的了。
引起load高的原因也可能有多种:
某些进程/服务消耗更多CPU资源(服务响应更多请求或存在某些应用瓶颈);
发生比较严重的swap(可用物理内存不足);
发生比较严重的中断(因为SSD或网络的原因发生中断);
磁盘I/O比较慢(会导致CPU一直等待磁盘I/O请求);
这时我们可以执行下面的命令来判断到底瓶颈在哪个子系统:
[ly@imysql.com:~ ]# toptop - 11:53:04 up 702 days, 56 min, 1 user, load average: 7.18, 6.70, 6.47Tasks: 576 total, 1 running, 575 sleeping, 0 stopped, 0 zombieCpu(s): 7.7%us, 3.4%sy, 0.0%ni, 77.6%id, 11.0%wa, 0.0%hi, 0.3%si, 0.0%stMem: 49374024k total, 32018844k used, 17355180k free, 115416k buffersSwap: 16777208k total, 117612k used, 16659596k free, 5689020k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND14165 mysql 20 0 8822m 3.1g 4672 S 162.3 6.6 89839:59 mysqld40610 mysql 20 0 25.6g 14g 8336 S 121.7 31.5 282809:08 mysqld49023 mysql 20 0 16.9g 5.1g 4772 S 4.6 10.8 34940:09 mysqld
很明显是前面两个mysqld进程导致整体负载较高。而且,从 Cpu(s) 这行的统计结果也能看的出来,%us 和 %wa 的值较高,表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。我们先分析下磁盘I/O的情况。
执行 sar -d 确认磁盘I/O是否真的较大:
[ly@imysql.com:~ ]# sar -d 1Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com) 01/13/2016 _x86_64_ (24 CPU)11:54:32 AM dev8-0 5338.00 162784.00 1394.00 30.76 5.24 0.98 0.19 100.0011:54:33 AM dev8-0 5134.00 148032.00 32365.00 35.14 6.93 1.34 0.19 100.1011:54:34 AM dev8-0 5233.00 161376.00 996.00 31.03 9.77 1.88 0.19 100.0011:54:35 AM dev8-0 4566.00 139232.00 1166.00 30.75 5.37 1.18 0.22 100.0011:54:36 AM dev8-0 4665.00 145920.00 630.00 31.41 5.94 1.27 0.21 100.0011:54:37 AM dev8-0 4994.00 156544.00 546.00 31.46 7.07 1.42 0.20 100.00
再利用 iotop (注意版本)确认到底哪些进程消耗的磁盘I/O资源最多:
[ly@imysql.com:~ ]# iotopTotal DISK READ: 60.38 M/s | Total DISK WRITE: 640.34 K/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND16397 be/4 mysql 8.92 M/s 0.00 B/s 0.00 % 94.77 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320 7295 be/4 mysql 10.98 M/s 0.00 B/s 0.00 % 93.59 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=332014295 be/4 mysql 10.50 M/s 0.00 B/s 0.00 % 93.57 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=332014288 be/4 mysql 14.30 M/s 0.00 B/s 0.00 % 91.86 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=332014292 be/4 mysql 14.37 M/s 0.00 B/s 0.00 % 91.23 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
可以看到,端口号是3320的实例消耗的磁盘I/O资源比较多,那就看看这个实例里都有什么查询在跑吧。
MySQL层面检查确认
首先看下当前都有哪些查询在运行:
[ly@imysql.com(db)]> mysqladmin pr|grep -v Sleep+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+| Id |User| Host | db |Command|Time | State | Info |+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+| 25 | x | 10.x:8519 | db | Query | 68 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 || 26 | x | 10.x:8520 | db | Query | 65 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 || 28 | x | 10.x:8522 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 || 27 | x | 10.x:8521 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 || 36 | x | 10.x:8727 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 |
可以看到有不少慢查询还未完成,从slow query log中也能发现,这类SQL发生的频率很高。
这是一个非常低效的SQL写法,导致需要对整个主键进行扫描,但实际上只需要取得一个最大值而已,从slow query log中可看到:
Rows_sent: 1 Rows_examined: 5502460
每次都要扫描500多万行数据,却只为读取一个最大值,效率非常低。
经过分析,这个SQL稍做简单改造即可在个位数毫秒级内完成,原先则是需要150-180秒才能完成,提升了N次方。
改造的方法是:对查询结果做一次倒序排序,取得第一条记录即可。而原先的做法是对结果正序排序,取最后一条记录。
我们也可以通过MySQL自带的命令排查
慢SQL和mysql当前数据库连接数的查看方法如下:
- command:query 是执行状态
- command:sleep 是空闲状态
查询当前执行时间超过50ms的慢SQL
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 50 AND command = 'query' ORDER BY state DESC LIMIT 0,10000
显示所有连接
SHOW FULL PROCESSLIST
- KILL到一个连接
- KILL 256115
查询连接了这个数据库的服务器的请求数
SELECT hostid,COUNT(hostid) FROM(SELECT SUBSTRING_INDEX(HOST,':',1) AS hostid FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 5 AND command = 'sleep' LIMIT 0,10000) AS hosttable GROUP BY hostid ORDER BY COUNT(hostid) DESC
查看阻塞的进程,并kill掉
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command !='Sleep' LIMIT 100000
- KILL 811478
- 【架构性能分析(1)】MySQL性能瓶颈排查定位
- 实例解析MySQL性能瓶颈排查定位
- 实例解析MySQL性能瓶颈排查定位
- 优化系列 | 实例解析MySQL性能瓶颈排查定位
- 优化 | 实例解析MySQL性能瓶颈排查定位
- linux_CPU性能瓶颈分析定位
- 存储性能瓶颈的成因、定位与排查
- 存储性能瓶颈的成因、定位与排查
- 存储性能瓶颈的成因、定位与排查
- MySql sql性能瓶颈分析
- MYSQL——性能瓶颈定位
- 排查tomcat服务性能瓶颈
- 怎么定位性能瓶颈
- 如何定位性能瓶颈
- 性能测试如何定位瓶颈(一)
- 性能测试如何定位瓶颈(二)
- MySQL数据库性能优化之(六)硬件瓶颈分析
- 性能瓶颈分析方法
- jsp/servlet第四章第一节Jsp九大内置对象及四大作用域概述
- 软件工程(C编码实践篇)学习总结
- Vue 增删示例
- 网站关键词布局的最佳位置,知道5个轻松做排名
- Codeforces 888B Buggy Robot (模拟)
- 【架构性能分析(1)】MySQL性能瓶颈排查定位
- LeetCode 之
- 【Java】ArrayList的源码分析
- L1-004. 计算摄氏温度
- win10下基于python(anaconda)安装gpu版本的TensorFlow以及kears深度学习框架
- JavaCompiler:java.lang.NullPointerException报空指针异常——JDK与JRE开发时区别
- 查看数据库表详情进行展示
- 第一篇博客
- Python 中的 if __name__ == '__main__' 该如何理解