MySQL性能分析
上一篇 / 下一篇 2013-03-17 20:50:15 / 个人分类:mysql
文章来源
- 文章来源:【转载】
第一步 检查系统的状态
通过操作系统的一些工具检查系统的状态,比如CPU、内存、交换、磁盘的利用率,根据经验或与系统正常时的状态相比对,有时系统表面上看起来看空闲,这也可能不是一个正常的状态,因为cpu可能正等待IO的完成。除此之外,还应观注那些占用系统资源(cpu、内存)的进程。
dcB3a9xT01.1 使用sar来检查操作系统是否存在IO问题
#sar -u 2 10 — 即每隔2秒检察一次,共执行20次。
结果示例:
注:在redhat下,%system就是所谓的%wio。
Linux 2.4.21-20.ELsmp (YY075) 05/19/2005
10:36:07 AM CPU %user %nice %system %idle
10:36:09 AM all 0.00 0.00 0.13 99.87
10:36:11 AM all 0.00 0.00 0.00 100.00
10:36:13 AM all 0.25 0.00 0.25 99.49
10:36:15 AM all 0.13 0.00 0.13 99.75
10:36:17 AM all 0.00 0.00 0.00 100.00
其中:
Ø %usr指的是用户进程使用的cpu资源的百分比;
Ø %sys指的是系统资源使用cpu资源的百分比;
Ø %wio指的是等待io完成的百分比,这是值得观注的一项;
Ø %idle即空闲的百分比。
如果wio列的值很大,如在35%以上,说明系统的IO存在瓶颈,CPU花费了很大的时间去等待I/O的完成。Idle很小说明系统CPU很忙。像以上的示例,可以看到wio平均值为11,说明I/O没什么特别的问题,而idle值为零,说明cpu已经满负荷运行了。
1.2 使用vmstat监控内存 cpu资源
[root@mysql1 ~]# vmstat
g,Y6|x3MEc0procs ———–memory———- —swap– —–io—- –system– —–cpu——51Testing软件测试网Co[ r7{6B"Z9`M
r b swpd free buff cache si so bi bo in cs us sy id wa st
@Im@(bc!PUH4S00 0 72 25428 54712 672264 0 0 14 43 53 59 1 1 98 0 0
nIu(] tQ#_B02a9\OE,I6o;k0
vmstat 的输出那些信息值得关注?
]3IZ;T.QQ/e {0io bo: 磁盘写的数据量稍大,如果是大文件的写,10M以内基本不用担心,如果是小文件写2M以内基本正常51Testing软件测试网Gs4HcQ
51Testing软件测试网LAJdC9~ LW]%NF
1.2.1 CPU问题
下面几列需要被察看,以确定cpu是否有问题
Processes in the run queue (procs r)
User time (cpu us)
System time (cpu sy)
Idle time (cpu id)51Testing软件测试网'S(utA'z#\
P"p@
问题情况:
1.) 如果processes in run queue (procs r)的数量远大于系统中cpu的数量,将会使系统便慢。
2.) 如果这个数量是cpu的4倍的话,说明系统正面临cpu能力短缺,这将使系统运行速度大幅度降低
3.) 如果cpu的idle时间经常为0的话,或者系统占用时间(cpu sy)是用户占用时间(cpu us)两辈的话,系统面临缺少cpu资源
解决方案 :
解决这些情况,涉及到调整应用程序,使其能更有效的使用cpu,同时增加cpu的能力或数量
1.2.2内存问题
主要查看页导入的数值(swap中的si),如果该值比较大就要考虑内存,大概方法如下:
1).最简单的,加大RAM
2).减少RAM的需求
1.3磁盘IO问题
处理方式:做raid10提高性能51Testing软件测试网.\Z#n#{ [4y0|
1.4网络问题
telnet一下MySQL对外开放的端口,如果不通的话,看看防火墙是否正确设置了。另外,看看MySQL是不是开启了skip-networking的选项,如果开启请关闭。
b'j2d yQHYK%j&R0第二步 检查mysql参数
2.1 几个不被注意的mysql参数
2.1.1 max_connect_errors
max_connect_errors默认值为10,如果受信帐号错误连接次数达到10则自动堵塞,需要flush hosts来解除。如果你得到象这样的一个错误:
:{8C^ y d;Bw&{0Host ’hostname’ is blocked because of many connection errors.51Testing软件测试网!w i.Cw9g4[ N
Unblock with ’mysqladmin flush-hosts’51Testing软件测试网$S5oMm]4c"cvf/V
这意味着,mysqld已经得到了大量(max_connect_errors)的主机’hostname’的在中途被中断了的连接请求。在 max_connect_errors次失败请求后,mysqld认定出错了(象来字一个黑客的攻击),并且阻止该站点进一步的连接,直到某人执行命令 mysqladmin flush-hosts。
\GwW+L1b ~x:c0内网连接的话,建议设置在10000以上,已避免堵塞,并定期flush hosts。
i B9c6i? Cp#c?02.1.2 connect_timeout
指定MySQL服务等待应答一个连接报文的最大秒数,超出该时间,MySQL向客户端返回 bad handshake。默认值是5秒,在内网高并发环境中建议设置到10-15秒,以便避免bad hand shake。建议同时关注thread_cache_size并设置thread_cache_size为非0值,大小具体调整。51Testing软件测试网4D:KtQK5GOK8q
2.1.3 skip-name-resolve
skip-name-resolve能大大加快用户获得连接的速度,特别是在网络情况较差的情况下。MySQL在收到连接请求的时候,会根据请求包中获得的ip来反向追查请求者的主机名。然后再根据返回的主机名又一次去获取ip。如果两次获得的ip相同,那么连接就成功建立了。在DNS不稳定或者局域网内主机过多的情况下,一次成功的连接将会耗费很多不必要的时间。假如MySQL服务器的ip地址是广域网的,最好不要设置skip-name- resolve。51Testing软件测试网%i0rx9Y7|JV
2.1.4 slave-net-timeout=seconds
参数含义:当slave从主数据库读取log数据失败后,等待多久重新建立连接并获取数据。默认值是3600秒,如果需要保证同步性,如此NC的参数请极力控制在10秒以下。51Testing软件测试网 ?~!_:Z$H u#J
2.1.5 master-connect-retry
参数含义:当重新建立主从连接时,如果连接建立失败,间隔多久后重试。默认是60秒,请按照合理的情况去设置参数。51Testing软件测试网#jX Xx k/Uxv
U @ J:R-Dd'PJ/}0
第三步 检查mysql 相关状态值
3.1关注连接数
如果连接数达到了最大连接数,那不管 有多少资源,用户都会阻塞在外面。
Y}N S-D0修改mysql最大连接数:
w{UOBz0打开my.ini,修改max_connections=100(默认为100)。51Testing软件测试网J*`N/i-~5b
请根据硬件情况调整到合适的大小,一般经验值可设为3000。Windows服务器大概支持量为1500-1800个连接,linux服务器可以支持到8000个左右。
'eJ \-?&GlRP&s+I0请将max_user_connections设0——–这个0代表不限制单用户的最大连接数,其最大连接值可以等于max_connections值。
*l&BF!oG$j8fQg Q1`%R0mysql> show global status like ‘Max_used_connections’;51Testing软件测试网.X8xi-W4p7n
检查下最大的过往使用连接数,这个值在max_connections的85%左右是比较合适的,如果过高则是max_connections过少或者系统负荷过高了。
,|(]AM^)_1`%I051Testing软件测试网Kut3wv)nA-O
3.1.1 mysqladmin -uroot status
[root@mysql1 ~]# mysqladmin -uroot status51Testing软件测试网D!Z?\Lw.w
Uptime: 1742276 Threads: 2 Questions: 2538 Slow queries: 0 Opens: 145 Flush tables: 1 Open tables: 23 Queries per second avg: 0.1
u@9\L*D+Vk03.1.2 show full processlist
1.显示所有进程
0g(m3vQ.Hb0mysql> show full processlist;
YA H1a3ISZ9K0+—–+——+———–+——+———+——+——-+———————–+51Testing软件测试网9V smi]:KzZ
| Id | User | Host | db | Command | Time | State | Info |
/RB'v1UJ0+—–+——+———–+——+———+——+——-+———————–+
g.e%z!f8o)T7R V~k0| 629 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
"p7gi+O\D^5P&p^{(m0| 633 | root | localhost | NULL | Sleep | 11 | | NULL |
*w2S.`c e0+—–+——+———–+——+———+——+——-+———————–+
Nh)m8S7C7~/B.mr02 rows in set (0.00 sec)
eS9Kl!dQYI051Testing软件测试网3?y ]0?fvB5V|;H
2.如果正在运行的语句太多,运行时间太长,表示MySQL效率有问题。必要的时候可以将对应的进程kill掉。51Testing软件测试网9n,j K7j"_'|'s([k
杀死休眠的进程kill ID号
4a$U+] _dy%ACp0mysql> kill 633;51Testing软件测试网W4tv3Zg6I)Db\
Query OK, 0 rows affected (0.00 sec)
9xD jO?8s051Testing软件测试网?6y0w3s{ k:O4y
3.关注TIME参数,看看正在运行的用户进程有多少是长时间占用的,具体分析下。
]l+I,Aed-N4n%S6N03.1.3使用mysqlreport关注Connections,Threads
__ Connections _________________________________________________________
5P4V!{8E1y w W0Max used 3 of 200 %Max: 1.50
i;Ka WXg @0Total 30.16k 0.7/s
(OF%XF ^-H/K S0。。。。。。51Testing软件测试网J SXDr:[z%E
__ Threads _____________________________________________________________51Testing软件测试网2w!k~RX;[/f9s$r
Running 1 of 251Testing软件测试网-q+x/X.d&A-? p*K1WwQ
Cached 1 of 300 %Hit: 99.9951Testing软件测试网n9~/g~4kw0U*@
Created 3 0.0/s51Testing软件测试网 T'u0I Dd!TZ
Slow 0 0/s
FO] PL03.2关注下系统锁情况
3.2.1 mysql> show status like ‘%lock%’;
+——————————-+———+51Testing软件测试网7@:Rc)jk N'_sM
| Variable_name | Value |51Testing软件测试网;hd'`~.Uh@S!i
+——————————-+———+51Testing软件测试网Hg-i Y.cT
| Com_lock_tables | 0 |51Testing软件测试网.y1p3P:t`sR|.uS
| Com_unlock_tables | 0 |
(Enj7e\2ix(X0| Innodb_row_lock_current_waits | 0 |51Testing软件测试网4D5ms9YK0]9BK(a)O
| Innodb_row_lock_time | 0 |
m&M8{j$^DM {Q0| Innodb_row_lock_time_avg | 0 |51Testing软件测试网6xr!S `Sf0k*k1b0rJW
| Innodb_row_lock_time_max | 0 |51Testing软件测试网&z\lMzaqu8CA
| Innodb_row_lock_waits | 0 |51Testing软件测试网$}2Z.wd&mX oN+}'o:O
| Table_locks_immediate | 2667760 |51Testing软件测试网j7f6mK)Yf
| Table_locks_waited | 0 |51Testing软件测试网m(W(D}E.bW
3.2.2使用mysqlreport关注Table Locks,InnoDB Lock
__ Questions ___________________________________________________________51Testing软件测试网 GY EC4~yj
Total 3.38M 81.4/s
sQJ'c[ch{&zO0DMS 2.88M 69.3/s %Total: 85.1151Testing软件测试网\ \a:aDYL
QC Hits 382.70k 9.2/s 11.32
YH&H3P8~*ZJ^ T0Com_ 90.50k 2.2/s 2.6851Testing软件测试网4\%|^_3s5EA
COM_QUIT 30.15k 0.7/s 0.8951Testing软件测试网*xv7p"K-c}%di2w1B"`
+Unknown 18 0.0/s 0.00
{!D+_c`xEpS0Slow 1 s 92 0.0/s 0.00 %DMS: 0.00 Log: OFF51Testing软件测试网NN _x-G3w
。。。。。。
}&X9a^;G|,s0__ Table Locks _________________________________________________________
k5j8}Gv X1P8a x0Waited 0 0/s %Total: 0.00
L(iaZRx1i0Immediate 2.67M 64.2/s
U5L.s4['MXw.[f0。。。。。。
ve @!y3Q9F,MOq0__ InnoDB Lock _________________________________________________________51Testing软件测试网EeVrA.pY
Waits 0 0/s
bF/E:D1^Dz0Current 0
4a s/C)` dU%U A'c0Time acquiring51Testing软件测试网!Q/CEA-ZX2H"\
Total 0 ms51Testing软件测试网 KZ:Cy*l9I`
Average 0 ms51Testing软件测试网l}[0n2Y,c+K W:q
Max 0 ms51Testing软件测试网p6Zb?!Hy6Vii
。。。。。。51Testing软件测试网O&v;[0Qj
如果wait过多,平均时间过长,那就是查询设计的有问题,仔细关注下超长时间的查询,并打开slow_query_log。
)K;}bB/apnB\03.3 关注慢查询(slow query)日志
日志必然会拖慢系统速度,特别是CPU资源,所以如果CPU资源充分,可以一直打开,如果不充足,那就在需要调整的时候,或者在replication从服务器上打开(针对select)51Testing软件测试网/W#Y#h)^gF,Qz
mysql> show variables like ‘%slow%’;51Testing软件测试网*E.M?T6Y$q&Lm
+———————+—————————————-+51Testing软件测试网)v!^.R[%e UQ!_gpsq
| Variable_name | Value |
)q e!v&}njV0+———————+—————————————-+
/K@5S _7n;w0| log_slow_queries | OFF |
ba^_1Q#X.R0| slow_launch_time | 2 |
lNs(k0U(S4a4sq)z0| slow_query_log | OFF |51Testing软件测试网 P#KR(X,K
| slow_query_log_file | /data0/mysql/3306/data/mysql1-slow.log |
4}o!_)Y!y+H0+———————+—————————————-+
m n3sB4nc1c9R1d04 rows in set (0.00 sec)51Testing软件测试网i.Rp$y~1f H
Bb z"y?Q \]0
mysql> set GLOBAL slow_query_log=on;
*mZ E `.mInO Q0Query OK, 0 rows affected (0.00 sec)
[F @⪙P l03.3.1关注慢查询涉及的表的相关状态
1. 表内记录数。尽量控制在500万行以内(有索引),建议控制在200万行
}vX$W&h02. 表内索引的使用。
8k9Zs HR].is/Ky03. 表如果update,delete,insert频繁,可以考虑optimize table优化下文件存放,索引,存储空间。51Testing软件测试网y&|&X*b3S,d:[ h5e)e
4. 表内update,insert,delete查询的锁定时间。
Ak d;j nF R{s05. select for update如果条件字段无索引的话,会引起的是锁全表而不是行锁,请关注。51Testing软件测试网FRy&k!A3Iz*z K
6. 如果查询包括GROUP BY但你想要避免排序结果的消耗,你可以指定ORDER BY NULL禁止排序。51Testing软件测试网C\A^A L
3.3.2定期分析表
ANALYZE TABLE
a6^-C;coO8`:V0语法:51Testing软件测试网 x F"O$zr7Lk
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …51Testing软件测试网V8\+e*]j1?|
本语句用于分析和存储表的关键字分布。在分析期间,使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表,本语句与使用myisamchk -a相当。
h3U:bS.T q0CHECK TABLE
/e`|v*F0语法:
UB%A;Gv1SS0CHECK TABLE tbl_name [, tbl_name] … [option] …
S+mO5oNt$[0option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
"J~!]-D(nFs6]6B6s~*F0检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表,关键字统计数据被更新。51Testing软件测试网!k&a.Rb'H J
CHECK TABLE也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在。
sk3Z}l[(OT5qK0CHECKSUM TABLE51Testing软件测试网+b XsgYZ
语法:
G B2o+lKHV0CHECKSUM TABLE tbl_name [, tbl_name] … [ QUICK | EXTENDED ]
+dEW^#U[KTJR5O0报告一个表校验和。51Testing软件测试网f+P]'IU*[q
3.3.3使用optimize table
OPTIMIZE TABLE
oI7g|,B,c['M3x8|0语法:51Testing软件测试网r(i!a1US u,mrw&DQ6{
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …51Testing软件测试网9|"wlN^6H
如果已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。
s[d `A9E0OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
ScI Q(eT,b*{"Z bIW0相关阅读:
- 快速高效的破解MySQL本地和远程密码 (xin_晴, 2013-1-15)
- MySQL Merge表的使用注意事项和优点介绍 (xin_晴, 2013-1-25)
- 非常实用的mysql 替换语句 替换字段内容(全部或部分) (wanghuiwan, 2013-2-17)
- 基于SSL实现MySQL的加密主从复制 (xin_晴, 2013-3-07)
- 优化MySQL嵌套查询和联表查询 (xin_晴, 2013-2-28)
- MySQL忘记密码的解决办法 (89798520, 2013-3-01)
- Linux下禅道项目管理软件安装 (hunterno4, 2013-3-15)
生活在互联网领域,从事WEB测试工作.
我的栏目
标题搜索
日历
|
|||||||||
日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
1 | 2 | ||||||||
3 | 4 | 5 | 6 | 7 | 8 | 9 | |||
10 | 11 | 12 | 13 | 14 | 15 | 16 | |||
17 | 18 | 19 | 20 | 21 | 22 | 23 | |||
24 | 25 | 26 | 27 | 28 | 29 | 30 | |||
31 |
我的存档
数据统计
- 访问量: 1690237
- 日志数: 556
- 图片数: 1
- 文件数: 3
- 书签数: 8
- 建立时间: 2007-06-08
- 更新时间: 2023-01-07