免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2159 | 回复: 0

如果mysql系统突然慢了怎么办? [复制链接]

论坛徽章:
0
发表于 2010-02-21 16:24 |显示全部楼层

如果mysql系统突然慢了怎么办?
Write by Saver.Li
欢迎爱好mysql的朋友完善该文档,欢迎转载,完善后mail一份给我就好,嘿嘿。
第一步 检查系统的状态... 2
1.1 使用sar来检查操作系统是否存在IO问题... 2
1.2 使用vmstat监控内存 cpu资源... 2
1.2.1 CPU问题... 3
1.2.2内存问题... 3
1.3磁盘IO问题... 3
1.4网络问题... 3
第二步 检查mysql参数... 3
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
第三步 检查mysql 相关状态值... 4
3.1关注连接数... 4
3.1.1 mysqladmin -uroot status. 5
3.1.2 show full processlist 5
3.1.3使用mysqlreport关注Connections,Threads. 5
3.2关注下系统锁情况... 6
3.2.1 mysql> show status like '%lock%'; 6
3.2.2使用mysqlreport关注Table Locks,InnoDB Lock. 6
3.3 关注慢查询(slow query)日志... 7
3.3.1关注慢查询涉及的表的相关状态... 7
3.3.2定期分析表... 7
3.3.3使用optimize table. 8
附录:... 8
一.Sar命令获得... 8
在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。建议同时关注
thread_cache_size
并设置
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/u3/111930/showart_2183398.html
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP