一:检查mysql所在的服务器的性能,linux环境使用top和iostat
[root@S243 etc]# top
top - 11:40:41 up 23 days, 17:06, 2 users,
load average: 0.11, 0.31, 0.32
Tasks: 617 total, 1 running, 616 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 1.0%us, 0.7%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu3 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
关于top的结果主要关注下
load average: 0.11, 0.31, 0.32
系统负载(任务队列的平均长度)三个值分别为1分钟、5分钟、15分钟前到现在的平均值,小于1正常【这三个一般会小于1,如果持续高于5,请仔细查看那个程序影响系统的运行】”
[root@S243 etc]# iostat -d -x
Linux 2.6.32-131.0.15.el6.x86_64 (S243) 2016年10月20日 _x86_64_ (24 CPU)
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 1.02 14.59 6.77 27.88 297.25 519.70 23.58 0.07 1.92 0.34 1.17
输出信息的含义
rrqm/s:每秒这个设备相关的读取请求有多少被Merge了(当系统调用需要读取数据的时候,VFS将请求发到各个FS,如果FS发现不同的读取请求读取的是相同Block的数据,FS会将这个请求合并Merge);wrqm/s:每秒这个设备相关的写入请求有多少被Merge了。
rsec/s:每秒读取的扇区数;
wsec/:每秒写入的扇区数。
rKB/s:The number of read requests that were issued to the device per second;
wKB/s:The number of write requests that were issued to the device per second;
avgrq-sz 平均请求扇区的大小
avgqu-sz 是平均请求队列的长度。毫无疑问,队列长度越短越好。
await: 每一个IO请求的处理的平均时间(单位是微秒毫秒)。这里可以理解为IO的响应时间,一般地系统IO响应时间应该低于5ms,如果大于10ms就比较大了。
这个时间包括了队列时间和服务时间,也就是说,一般情况下,await大于svctm,它们的差值越小,则说明队列时间越短,反之差值越大,队列时间越长,说明系统出了问题。
svctm 表示平均每次设备I/O操作的服务时间(以毫秒为单位)。如果svctm的值与await很接近,表示几乎没有I/O等待,磁盘性能很好, 如果await的值远高于svctm的值,则表示I/O队列等待太长, 系统上运行的应用程序将变慢,上例中1.92高于0.34说明存在一定的等待。
rsec/s:每秒读取的扇区数;
wsec/:每秒写入的扇区数。
rKB/s:The number of read requests that were issued to the device per second;
wKB/s:The number of write requests that were issued to the device per second;
avgrq-sz 平均请求扇区的大小
avgqu-sz 是平均请求队列的长度。毫无疑问,队列长度越短越好。
await: 每一个IO请求的处理的平均时间(单位是微秒毫秒)。这里可以理解为IO的响应时间,一般地系统IO响应时间应该低于5ms,如果大于10ms就比较大了。
这个时间包括了队列时间和服务时间,也就是说,一般情况下,await大于svctm,它们的差值越小,则说明队列时间越短,反之差值越大,队列时间越长,说明系统出了问题。
svctm 表示平均每次设备I/O操作的服务时间(以毫秒为单位)。如果svctm的值与await很接近,表示几乎没有I/O等待,磁盘性能很好, 如果await的值远高于svctm的值,则表示I/O队列等待太长, 系统上运行的应用程序将变慢,上例中1.92高于0.34说明存在一定的等待。
%util: 在统计时间内所有处理IO时间,除以总共统计时间。例如,如果统计间隔1秒,该设备有0.8秒在处理IO,而0.2秒闲置,那么该设备的%util =
0.8
/
1
=
80
%,所以该参数暗示了设备的繁忙程度
。一般地,如果该参数是100%表示设备已经接近满负荷运行了(当然如果是多磁盘,即使%util是100%,因为磁盘的并发能力,所以磁盘使用未必就到了瓶颈)。
二:查看有没有阻塞:
mysql> show processlist; 关注state 有没有lock的状态。有的话kill解决。
+---------+-------------+---------------------+---------------+-------------+---------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
| 3 | mailer | 192.168.0.225:45135 | mailer | Sleep | 26 | | NULL |
| 8 | info_reader | 192.168.0.225:45194 | info | Sleep | 56 | | NULL |
| 23 | info_reader | 192.168.0.225:45451 | info | Sleep | 13 | | NULL |
| 78 | info_reader | 192.168.0.225:54249 | info | Sleep | 68 | | NULL |
| 180 | web_editer | 192.168.0.225:46200 | info | Sleep | 26 | | NULL |
| 226 | web_editer | 192.168.0.225:46584 | info | Sleep | 13 | | NULL |
| 2035 | info_reader | 192.168.0.225:53314 | info | Sleep | 36 | | NULL |
| 2052 | info_reader | 192.168.0.225:53447 | info | Sleep | 36 | | NULL |
| 2384 | ruby_syncer | 192.168.0.218:41868 | info | Sleep | 0 | | NULL |
| 2387 | ruby_syncer | 192.168.0.218:41870 | info | Sleep | 0 | | NULL |
三:查看mysql慢sql
1)首先检查是否开启了慢sql. 如下红色显示为开启了mysql慢查询。
1)首先检查是否开启了慢sql. 如下红色显示为开启了mysql慢查询。
mysql> show variables like '%query%';
+------------------------------+------------------------------+
| Variable_name | Value |
+------------------------------+------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 4.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 104857600 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /mysql/datadir/S243-slow.log |
+------------------------------+------------------------------+
13 rows in set (0.00 sec)
拓展:mysql慢查询相关的主要的三个参数为
(1)
slow_query_log #指定是否开启慢查询日志(0/1或者off/on)
(2)slow_query_log_file # 指定慢日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log
(3)long_query_time #设定慢查询的阀值,超出次设定值的SQL即被记录到慢查询日志,缺省值为10s
(4)log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引 (on/off)
(5)min_examined_row_limit:查询检查返回少于该参数指定行的SQL不被记录到慢查询日志 。
2)mysql> show status like '%Slow_queries%'; #显示了当前慢查询的数量,也就是正在执行的。如果显示0,说明当前没有正在执行的时间超过long_query_time 值的sql.你需要去慢查询日志中查看。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2 |
+---------------+-------+
1 row in set (0.00 sec)
3)直接查看慢查询日志的内用就可以,如下显示了两条慢sql.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2 |
+---------------+-------+
1 row in set (0.00 sec)
3)直接查看慢查询日志的内用就可以,如下显示了两条慢sql.
[root@S243 datadir]# tail -n 10 S243-slow.log
# Time: 161020 15:19:39
# User@Host: mailer[mailer] @ [192.168.0.226] Id: 1372832
# Query_time: 12.617656 Lock_time: 0.000072 Rows_sent: 1 Rows_examined: 19796776
SET timestamp=1476947979;
select count(*) as col_0_0_ from mailer.kehuguanzhus_logs_meta kehuguanzh0_ where kehuguanzh0_.state<>100 and kehuguanzh0_.guanzhu_id='4f41910e-0ec6-4042-8c4a-b2f0f9c8' and kehuguanzh0_.last_modify>='2016-09-20' and kehuguanzh0_.last_modify<'2016-10-21' order by kehuguanzh0_.id desc limit 2;
# Time: 161020 15:21:19
# User@Host: mailer[mailer] @ [192.168.0.226] Id: 1372832
# Query_time: 12.489680 Lock_time: 0.000155 Rows_sent: 1 Rows_examined: 19796842
SET timestamp=1476948079;
select count(*) as col_0_0_ from mailer.kehuguanzhus_logs_meta kehuguanzh0_ where kehuguanzh0_.state<>100 and kehuguanzh0_.guanzhu_id='53b344cf-6239-4882-afbb-772b90a4' and kehuguanzh0_.last_modify>='2016-09-20' and kehuguanzh0_.last_modify<'2016-10-21' order by kehuguanzh0_.id desc limit 2;
我们也可以利用mysqldumpslow来格式化慢查询日志的格式,便于我们查阅。
#获取mysqldumpslow的帮助信息
[root@S243 datadir]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
例如:
以下是按照最大耗用时间排最后,只显示2条的方式格式化日志文件
[root@S243 datadir]# mysqldumpslow -r -t 2 /mysql/datadir/S243-slow.log
Reading mysql slow query log from /var/lib/mysql/suse11b-slow.log
Count: 1
Time=1.57s (1s) Lock=0.00s (0s) Rows=83.0 (83), root[root]@localhost
select table_schema,table_name,count(*) from tb_slow
group by table_schema,table_name order by N,N
Count: 4
Time=16.87s (67s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into tb_slow select * from tb_slow
[root@S243 datadir]# mysqldumpslow -s /mysql/datadir/S243-slow.log ###最耗时间的依次排序。
由于这个慢查询日志是无限增大的,他是好长时间的一个累计,而数据库性能慢的时候,往往你只需要查询当时的那段时间的慢sql语句,然后针对性的去优化,所以没必要使用mysqldumpslow 去做什么排序之类的,因为最耗时的sql可能仅仅是在很久以前执行过一次,直接用tail -n 去看某个时间段的就可以了。
确定了影响性能的慢sql之后,然后针对性的去优化,加索引,改写sql,。。。。。
体会:mysql性能诊断过程和oracle类似,也是首先查看mysql的服务器的性能,然后看mysql数据的性能(有没有锁之类的)最后确定性能慢的sql.然后针对性的去优化。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-2126818/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29654823/viewspace-2126818/