MySQL数据库服务器逐渐变慢分析与解决

　一、检查系统的状态

　　通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲，这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外，还应观注那些占用系统资源(cpu、内存)的进程。

　　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已经满负荷运行了。

　　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以内基本正常

　　① CPU问题

　　下面几列需要被察看，以确定cpu是否有问题

Processes in the run queue (procs r)
User time (cpu us)
System time (cpu sy)
Idle time (cpu id)

 　　问题情况:

　　 如果processes in run queue (procs r)的数量远大于系统中cpu的数量，将会使系统便慢。

　　 如果这个数量是cpu的4倍的话，说明系统正面临cpu能力短缺,这将使系统运行速度大幅度降低

　　如果cpu的idle时间经常为0的话，或者系统占用时间(cpu sy)是用户占用时间(cpu us)两辈的话，系统面临缺少cpu资源

　　解决方案 :

　　解决这些情况，涉及到调整应用程序，使其能更有效的使用cpu，同时增加cpu的能力或数量

　　②内存问题

　　主要查看页导入的数值(swap中的si)，如果该值比较大就要考虑内存，大概方法如下：

　　最简单的，加大RAM

　　减少RAM的需求

　　3.磁盘IO问题

　　处理方式：做raid10提高性能

　　4.网络问题

　　telnet一下MySQL对外开放的端口，如果不通的话，看看防火墙是否正确设置了。另外，看看MySQL是不是开启了skip-networking的选项，如果开启请关闭。

二、检查mysql参数

　　1.几个不被注意的mysql参数

　　①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。

　　② connect_timeout

　　指定MySQL服务等待应答一个连接报文的最大秒数，超出该时间，MySQL向客户端返回 bad handshake。默认值是5秒，在内网高并发环境中建议设置到10-15秒，以便避免bad hand shake。建议同时关注thread_cache_size并设置thread_cache_size为非0值，大小具体调整。

　　③skip-name-resolve

　　skip-name-resolve能大大加快用户获得连接的速度，特别是在网络情况较差的情况下。MySQL在收到连接请求的时候，会根据请求包中获得的ip来反向追查请求者的主机名。然后再根据返回的主机名又一次去获取ip。如果两次获得的ip相同，那么连接就成功建立了。在DNS不稳定或者局域网内主机过多的情况下，一次成功的连接将会耗费很多不必要的时间。假如MySQL服务器的ip地址是广域网的，最好不要设置skip-name-resolve。

　　④ slave-net-timeout=seconds

　　参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据。默认值是3600秒，如果需要保证同步性，如此NC的参数请极力控制在10秒以下。

　　⑤ master-connect-retry

　　参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。默认是60秒，请按照合理的情况去设置参数。

　三、检查mysql 相关状态值

　　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过少或者系统负荷过高了。

　　①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

　　②show full processlist

　　显示所有进程

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)

　　如果正在运行的语句太多，运行时间太长，表示MySQL效率有问题。必要的时候可以将对应的进程kill掉。

　　杀死休眠的进程kill ID号

        mysql> kill 633;
　　Query OK, 0 rows affected (0.00 sec)

　　关注TIME参数，看看正在运行的用户进程有多少是长时间占用的，具体分析下。

　　③使用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

　　2.关注下系统锁情况

　　① 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 |

　　②使用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. 关注慢查询(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)

        ①关注慢查询涉及的表的相关状态

　　表内记录数。尽量控制在500万行以内(有索引)，建议控制在200万行

　　表内索引的使用。

　　表如果update，delete，insert频繁，可以考虑optimize table优化下文件存放，索引，存储空间。

　　表内update，insert，delete查询的锁定时间。

　　select for update如果条件字段无索引的话，会引起的是锁全表而不是行锁，请关注。

　　如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。

　　②定期分析表

　　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 ]

 　　报告一个表校验和。

　　③使用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表起作用。

以下是附录内容

       一.Sar命令获得：安装sysstat 系统状态包

root@mysql1 ~]# yum info sysstat
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Installed Packages
Name       : sysstat
Arch       : i386
Version    : 7.0.2
Release    : 3.el5
Size       : 383 k
Repo       : installed
Summary    : sar 和 iostat 系统监视命令。
URL        : http://perso.orange.fr/sebastien.godard/
License    : GPL
Description: 该软件包为 Linux 提供了 sar 和 iostat 工具。sar 和 iostat
: 使系统能够监视磁盘、网络、以及其它 IO 活动。
:
[root@mysql1 ~]# yum install sysstat
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package sysstat.i386 0:7.0.2-3.el5 set to be updated
–> Finished Dependency Resolution

Dependencies Resolved

============================================================================================
Package              Arch              Version                   Repository           Size
============================================================================================
Installing:
sysstat              i386              7.0.2-3.el5               CentOS              169 k

Transaction Summary
============================================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)

Total download size: 169 k
Is this ok [y/N]: y
Downloading Packages:
sysstat-7.0.2-3.el5.i386.rpm                                         | 169 kB     00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing     : sysstat                                                              1/1

Installed:
sysstat.i386 0:7.0.2-3.el5

Complete!

　　sar 命令行的常用格式：

　　在linux中使用sar调优系统性能

　　关键字: sar

　　sar默认在linux下没有安装,需要我们手工安装,一般建议源码方式安装,下载类似sysstat-6.1.3.tar.gz

　　然后configure make make install即可使用.

　　sar 命令行的常用格式：

sar [options] [-A] [-o file] t [n]

　　在命令行中，n 和t 两个参数组合起来定义采样间隔和次数，t为采样间隔，是必须有的参数，n为采样次数，是可选的，默认值是1，-o file表示将命令结果以二进制格式存放在文件中，file 在此处不是关键字，是文件名。options 为命令行选项，sar命令的选项很多，下面只列出常用选项：

   -A：所有报告的总和。
　　-u：CPU利用率
　　-v：进程、I节点、文件和锁表状态。
　　-d：硬盘使用报告。
　　-r：没有使用的内存页面和硬盘块。
　　-g：串口I/O的情况。
　　-b：缓冲区使用情况。
　　-a：文件读写情况。
　　-c：系统调用情况。
　　-R：进程的活动情况。
　　-y：终端设备活动情况。
　　-w：系统交换活动。

例一：使用命令行 sar -u t n

　　例如，每60秒采样一次，连续采样5次，观察CPU 的使用情况，并将采样结果以二进制形式存入当前目录下的文件zhou中，需键入如下命令：

# sar -u -o zhou 60 5

　　屏幕显示：

SCO_SV　　　scosysv　3.2v5.0.5　i80386