如果mysql系统突然慢了怎么办？

huangxifeng607

如果mysql系统突然慢了怎么办？
Write by Saver.Li
欢
迎爱好mysql的朋友
完善该文档，欢迎转载,完善后mail一份给我就好，嘿嘿。
[color="#0000ff"]第一步 检查系统的状态... 2
[color="#0000ff"]1.1 使用sar来检查操作系统是否存在IO问题... 2
[color="#0000ff"]1.2 使用vmstat监控内存 cpu资源... 2
[color="#0000ff"]1.2.1 CPU问题... 3
[color="#0000ff"]1.2.2内存问题... 3
[color="#0000ff"]1.3磁盘IO问题... 3
[color="#0000ff"]1.4网络问题... 3
[color="#0000ff"]第二步 检查mysql参数... 3
[color="#0000ff"]2.1 几个不被注意的mysql参数... 3
2.1.1
max_connect_errors. 3
2.1.2
connect_timeout 4
2.1.3
skip-name-resolve. 4
2.1.4
slave-net-timeout=seconds. 4
2.1.5
master-connect-retry. 4
[color="#0000ff"]第三步 检查mysql 相关状态值... 4
[color="#0000ff"]3.1关注连接数... 4
3.1.1
mysqladmin -uroot status. 5
3.1.2 show
full processlist 5
[color="#0000ff"]3.1.3使用mysqlreport关注Connections，Threads. 5
[color="#0000ff"]3.2关注下系统锁情况... 6
3.2.1
mysql> show status like '%lock%'; 6
[color="#0000ff"]3.2.2使用mysqlreport关注Table Locks，InnoDB Lock. 6
[color="#0000ff"]3.3 关注慢查询（slow query）日志... 7
[color="#0000ff"]3.3.1关注慢查询涉及的表的相关状态... 7
[color="#0000ff"]3.3.2定期分析表... 7
[color="#0000ff"]3.3.3使用optimize table. 8
[color="#0000ff"]附录：... 8
一.Sar命令获得... 8
[color="#0000ff"]在linux中使用sar调优系统性能... 10
二．vmstat命令输出分成六个部分：... 14
三．根据mysql状态调整系统参数... 16


第一步 检查系统的状态
通过操作系统的一些工具检查系统的状态，比
如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来
看空闲，这也可能不是一个正常的状态，因为cpu可能正等待IO的
完成。除此之外，还应观注那些占用系统资源(cpu、内存)的
进程。
1.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
procs -----------memory----------
---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0     72  25428  54712 672264    0   
0    14    43   53   59  1  1 98  0  0　

vmstat 的输出那些信息值得关注?
　　io bo: 磁盘写的数据量稍
大，如果是大文件的写，10M以内基本不用担心，如果是小文件写2M以内基本正常

1.2.1 CPU问题
下
面几列需要被察看，以确定cpu是否有问题
Processes in the run
queue (procs r)
User time (cpu us)
System time (cpu sy)
Idle
time (cpu id)
问题情况:
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提高性能
1.4网络问题
telnet一下MySQL对外开放的端口，如果不
通的话，看看防火墙是否正确设置了。另外，看看MySQL是不是开启了skip-networking的选项，如果开启请关闭。
第二步 检查mysql参数
2.1 几个不被注意的mysql参数
2.1.1 max_connect_errors
max_connect_errors默认值为10，如
果受信帐号错误连接次数达到10则自动堵塞，需要flush hosts来解除。如果你得到象这样的一个错误：
Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
这意味着，mysqld已经得到了大量(max_connect_errors)的主机'hostname'的在中途被
中断了的连接请求。在max_connect_errors次失败请求后，mysqld认定出错了(象来字一个黑客的攻击)，并且
阻止该站点进一步的连接，直到某人执行命令mysqladmin flush-hosts。
内网连接的话，建议设置在10000以
上，已避免堵塞，并定期flush
hosts。
2.1.2 connect_timeout
指定MySQL服务等待应答一个连接报
文的最大秒数，超出该时间，MySQL向客户端返回 bad handshake。默认值是5秒，在
内网高并发环境中建议设置到10-15秒，以便避免bad hand shake。建议同时关注
[color="#0000ff"]thread_cache_size
并设置
[color="#0000ff"]thread_cache_size
为非0值，大小具体调整。
2.1.3 skip-name-resolve
skip-name-resolve能大大加快用户获得连接的速度，特别是在网络情况较差的情况下。MySQL在收到连接请求的时候，会根据请求包中获得的ip来
反向追查请求者的主机名。然后再根据返回的主机名又一次去获取ip。如果两次获得的ip相同，那么连接就成功建立了。在DNS不
稳定或者局域网内主机过多的情况下，一次成功的连接将会耗费很多不必要的时间。假如MySQL服
务器的ip地址是广域网的，最好不要设置skip-name-resolve。
2.1.4 slave-net-timeout=seconds
  参数含义：当slave从
主数据库读取log数据失败后，等待多久重新建立连接并获取数据。默认值是3600秒，如果需要保证同步性，如此NC的参
数请极力控制在10秒以下。
2.1.5 master-connect-retry
参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。默认是60秒，请按照合理的情况去设置参数。

第三步 检查mysql 相关状态值
3.1关注连接数
如果连接数达到了最大连接数，那不管 有多少资源，用户都会阻塞在外面。
修改mysql最大连接数：
打开my.ini，修改max_connections=100(默认为100)。
请根据硬件情况调整到合适的大小，一般经验值可设为3000。Windows服务器大概支持量为1500-1800个连接，linux服务器可以支持到8000个左右。
请将max_user_connections设0--------这个0代表不限制单用户的最大连接数，其最大连接值可以等于max_connections值。
mysql> show global status like 'Max_used_connections';
检查下最大的过往使用连接数，这个值在max_connections的85%左右是比较合适的，如果过高则是max_connections过少或者系统负荷过高了。

3.1.1 mysqladmin -uroot status
[root@mysql1 ~]# mysqladmin -uroot
status
Uptime: 1742276  Threads: 2  Questions:
2538  Slow queries: 0  Opens:
145  Flush tables: 1  Open
tables: 23  Queries per second avg: 0.1
3.1.2 show full processlist
1.显示所有进程
mysql> show full processlist;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User |
Host      | db   | Command |
Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
| 629 | root | localhost | NULL | Query   |    0 | NULL  |
show full processlist |
| 633 | root | localhost | NULL | Sleep   |   11 |       |
NULL                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

2.如果正在运行的语句太多，运行时间太长，表示MySQL效率有问题。必要的时候可以将对应的进程kill掉。
杀死休眠的进程kill ID号
mysql> kill 633;
Query OK, 0 rows affected (0.00 sec)

3.关注TIME参数，看看正在运行的用户
进程有多少是长时间占用的，具体分析下。
3.1.3使用mysqlreport关注Connections，Threads
__ Connections
_________________________________________________________
Max used            3
of  200      %Max:   1.50
Total          30.16k     0.7/s
。。。。。。
__ Threads
_____________________________________________________________
Running             1
of    2
Cached              1
of  300      %Hit:  99.99
Created             3     0.0/s
Slow                0       0/s
3.2关注下系统锁情况
3.2.1 mysql> show status like '%lock%';
+-------------------------------+---------+
| Variable_name               
| Value   |
+-------------------------------+---------+
| Com_lock_tables              
| 0       |
| Com_unlock_tables            
| 0       |
| Innodb_row_lock_current_waits | 0       |
| Innodb_row_lock_time         
| 0       |
| Innodb_row_lock_time_avg      | 0       |
| Innodb_row_lock_time_max      | 0       |
| Innodb_row_lock_waits        
| 0       |
| Table_locks_immediate        
| 2667760 |
| Table_locks_waited           
| 0       |


3.2.2使用mysqlreport关注Table Locks，InnoDB Lock
__ Questions
___________________________________________________________
Total           3.38M    81.4/s

DMS           2.88M    69.3/s  %Total:  85.11

QC Hits     382.70k     9.2/s           11.32

Com_         90.50k     2.2/s            2.68

COM_QUIT     30.15k     0.7/s            0.89

+Unknown         18     0.0/s            0.00
Slow 1 s           92     0.0/s            0.00  %DMS:   0.00  Log: OFF
。。。。。。
__ Table Locks
_________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate       2.67M    64.2/s
。。。。。。
__ InnoDB Lock
_________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms
。。。。。。
如果wait过多，平均时间过长，那就是查询设计的有问题，仔细关注下超长时间的查询，
并打开slow_query_log。
3.3 关注慢查询（slow query）日志
日志必然会拖慢系统速度，特别是CPU资源，所以如果CPU资
源充分，可以一直打开，如果不充足，那就在需要调整的时候，或者在replication从服务器上打开（针对select）
mysql> show
variables like '%slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                 
|
+---------------------+----------------------------------------+
|
log_slow_queries    | OFF                                    |
|
slow_launch_time    | 2                                    
|
|
slow_query_log      | OFF                                   
|
|
slow_query_log_file | /data0/mysql/3306/data/mysql1-slow.log |
+---------------------+----------------------------------------+
4 rows in set
(0.00 sec)

mysql> set  GLOBAL slow_query_log=on;
Query OK, 0
rows affected (0.00 sec)
3.3.1关注慢查询涉及的表的相
关状态
1.       表内记录数。尽量控制在500万
行以内（有索引），建议控制在200万行
2.       表内索引的使用。
3.       表如果update，delete，insert频繁，可以考虑optimize table优化下文件存
放，索引，存储空间。
4.       表内update，insert，delete查询的锁定时间。
5.       select for update如果条件字段无索引的话，会引起的是锁全表而不是行锁，请关注。
6.       如果查询包括GROUP BY但你想要避免
排序结果的消耗，你可以指定ORDER BY NULL禁止排序。
3.3.2定期分析表
ANALYZE TABLE
语法：
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_name [, tbl_name] ...
本语句用于分析和存储表的关键字分布。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM,
BDB和InnoDB表有作用。对于MyISAM表，
本语句与使用myisamchk -a相当。
CHECK TABLE
语法：
CHECK TABLE tbl_name [, tbl_name] ...
[option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED |
CHANGED}
检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。
CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
CHECKSUM TABLE
语法：
CHECKSUM TABLE tbl_name [, tbl_name] ... [
QUICK | EXTENDED ]
报告一个表校验和。
3.3.3使用optimize table
OPTIMIZE TABLE
语法：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_name [, tbl_name] ...
如果已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR,
BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操
作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整
理数据文件的碎片。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/66215/showart_2184882.html