MySQL数据库服务器逐渐变慢分析
第一步 检查系统的状态
1.1 使用sar来检查操作系统是否存在IO问题
#sar -u 2 10 — 即每隔2秒检察一次，共执行20次。
[root@CacheMemCache tester]# sar -u 2 1010:51:22 AM CPU %user %nice %system %iowait %steal %idle 10:51:24 AM all 13.74 0.00 5.60 42.49 0.00 38.17 10:51:26 AM all 20.15 0.00 11.99 43.37 0.00 24.49 10:51:28 AM all 14.07 0.00 9.72 56.78 0.00 19.44 10:51:30 AM all 37.24 0.00 11.99 30.61 0.00 20.15 10:51:32 AM all 33.25 0.00 10.23 44.50 0.00 12.02 10:51:34 AM all 22.76 0.00 12.28 49.10 0.00 15.86 10:51:36 AM all 17.97 0.00 8.10 43.29 0.00 30.63 10:51:38 AM all 19.44 0.00 9.21 53.20 0.00 18.16 10:51:40 AM all 26.85 0.00 12.53 38.36 0.00 22.25 10:51:42 AM all 22.25 0.00 9.21 42.46 0.00 26.09 Average: all 22.77 0.00 10.08 44.41 0.00 22.74
其中：
%usr指的是用户进程使用的cpu资源的百分比；
%sys指的是系统资源使用cpu资源的百分比；
%iowait指的是等待io完成的百分比，这是值得观注的一项；
%idle即空闲的百分比。
如果wio列的值很大，如在35%以上，说明系统的IO存在瓶颈，CPU花费了很大的时间去等待I/O的完成。Idle很小说明系统CPU很忙。
1.2 使用vmstat监控内存 cpu资源
# vmstat
[root@CacheMemCache tester]# 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 5 0 2654300 181588 2070476 0 0 0 31 4 0 0 0 99 0 0
vmstat 的输出那些信息值得关注?
io bo: 磁盘写的数据量稍大，如果是大文件的写，10M以内基本不用担心，如果是小文件写2M以内基本正常
1.3 使用iostat查看io读写情况
#iostat -x -k -d 1 10
-d 采样时间为1秒，采样2次
-x 显示更详细的io设备统计信息
-k 以kB为单位显示读写信息
linux # iostat -x -k -d 1 10 Linux 2.6.16.60-0.21-smp (linux) 06/13/12 …… Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 9915.00 1.00 90.00 4.00 34360.00 755.25 11.79 120.57 6.33 57.60
以上各列的含义如下：
- rrqm/s: 每秒对该设备的读请求被合并次数，文件系统会对读取同块(block)的请求进行合并
- wrqm/s: 每秒对该设备的写请求被合并次数
- r/s: 每秒完成的读次数
- w/s: 每秒完成的写次数
- rkB/s: 每秒读数据量(kB为单位)
- wkB/s: 每秒写数据量(kB为单位)
- avgrq-sz:平均每次IO操作的数据量(扇区数为单位)
- avgqu-sz: 平均等待处理的IO请求队列长度
- await: 平均每次IO请求等待时间(包括等待时间和处理时间，毫秒为单位)
- svctm: 平均每次IO请求的处理时间(毫秒为单位)
- %util: 采用周期内用于IO操作的时间比率，即IO队列非空的时间比率
对于以上示例输出，我们可以获取到以下信息：
- 每秒向磁盘上写30M左右数据(wkB/s值)
- 每秒有91次IO操作(r/s+w/s)，其中以写操作为主体
- 平均每次IO请求等待处理的时间为120.57毫秒，处理耗时为6.33毫秒
- 等待处理的IO请求队列中，平均有11.79个请求驻留
使用iotop 可以查出使用io读写最高的程序
需要装一下 yum install iotop
1.4网络问题
telnet一下MySQL对外开放的端口，如果不通的话，看看防火墙是否正确设置了。
看看MySQL是不是开启了skip-networking的选项，如果开启请关闭。
第二步 检查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)。
mysql> show variables like 'max_connections';
请根据硬件情况调整到合适的大小，一般经验值可设为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
mysql> status; -------------- Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.39-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 min 17 sec Threads: 1 Questions: 9 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.065
3.1.2 show full processlist (show processlist state 只显示100条，显示全部用 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.2 关注慢查询（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.2.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禁止排序。
