找出mysql慢的原因

女爷i 2023-06-25 03:21 63阅读 0赞

1 检查系统的状态

主要察看 CPU问题 内存问题 磁盘IO问题 网络问题

命令 top 、vmstat 、sar、iostat等

1) 用vmstat察看关于内核进程,虚拟内存,磁盘,cpu的的活动状态  
\[root@ks01 ~\]\# 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    208 811596 326016 2485004    0    0     0     1    0    0  0  0 100  0  0

其中:  
kthr--内核进程的状态  
\--r 运行队列中的进程数,在一个稳定的工作量下,应该少于5  
\--b 等待队列中的进程数(等待I/O),通常情况下是接近0的.  
memory--虚拟和真实内存的使用信息  
\--avm 活动虚拟页面,在进程运行中分配到工作段的页面空间数.  
\--fre 空闲列表的数量.一般不少于120,当fre少于120时,系统开始自动的kill进程去释放  
      free list  
page--页面活动的信息  
\--re 页面i/o的列表  
\--pi 从页面输入的页(一般不大于5)  
\--po 输出到页面的页  
\--fr 空闲的页面数(可替换的页面数)  
\--sr 通过页面置换算法搜索到的页面数  
\--cy 页面置换算法的时钟频率  
faults--在取样间隔中的陷阱及中断数  
\--in 设备中断  
\--sy 系统调用中断  
\--cs 内核进程前后交换中断  
cpu--cpu的使用率  
\--us 用户进程的时间  
\--sy 系统进程的时间  
\--id cpu空闲的时间  
\--wa 等待i/o的时间  
一般us+sy 在单用户系统中不大于90,在多用

2 )sar来检查操作系统是否存在IO问题

sar可以显示CPU、运行队列、磁盘I/O、分页（交换区）、内存、CPU中断、网络等性能数据

\[root@ks01 ~\]\# sar  
Linux 2.6.18-194.el5 (ks01.oss.com)     05/03/2011

12:00:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle  
12:10:01 AM       all      0.00      0.00      0.00      0.03      0.00     99.96  
12:20:01 AM       all      0.00      0.00      0.00      0.01      0.00     99.98  
...  
其中:

\-CPU  
 CPU编号  
   
\--%user  
 在用户模式中运行进程所花的时间的百分比  
   
\--%nice  
 运行正常进程所花的时间的百分比  
   
\--%system  
 在内核模式（系统）中运行进程所花的时间的百分比   
   
\--%iowait   
 没有进程在该CPU上执行时，处理器等待I/O完成的时间的百分比   --这个值过高，表示硬盘存在I/O瓶颈  
   
\--%idle  
 CPU空闲时间百分比 ---如果这个值很高 但是系统响应慢 这时候应该加大内存 如果这个值持续太低 说明系统缺少cpu资源

附：sar 命令行的常用格式：  
sar -u 是sar的缺省输出 （CPU 使用情况）

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：系统交换活动。

比如   
\[root@ks01 ~\]\# sar -u 2 5  每2秒采集一下信息 收集5次  
Linux 2.6.18-194.el5 (ks01.oss.com)     05/03/2011

03:33:47 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle  
03:33:49 PM       all      0.00      0.00      0.00      0.00      0.00    100.00  
03:33:51 PM       all      0.00      0.00      0.00      0.00      0.00    100.00  
03:33:53 PM       all      0.00      0.00      0.00      0.03      0.00     99.97  
03:33:55 PM       all      0.00      0.00      0.00      0.00      0.00    100.00  
03:33:57 PM       all      0.00      0.00      0.00      0.00      0.00    100.00  
Average:          all      0.00      0.00      0.00      0.01      0.00     99.99

**2 检查mysql状态**

几个命令：

show status        显示系统状态   
show variables     显示系统变量  
show processlist   显示进程状态  
show profiles;     收集执行查询资源信息 默认是关闭的 开启 set profiling=1;

\------------------------------------  
常用的衍生命令：

** 连接失败情况**

show status like'%aborted%'

Aborted\_clients  客户端非法中断连接次数 如果随时间而增大 看看mysql的链接是否正常 或者检查一下网络 或者检查一下max\_allowed\_packet  超过此设置的查询会被中断（ show variables like'%max%'）  
Aborted\_connects 连接mysql失败次数  如果指过高 那就该检查一下网络 错误链接失败会在此记录

**慢查询：**

show variables like '%slow%'   
show status like '%slow%';  
默认是关闭的 开启 set global log\_slow\_queries=ON ;  
Slow\_launch\_threads 值较大 说明有些东西正在延迟链接的新线程

**连接数**

show variables like 'max\_connections' 最大连接数  
show  status like 'max\_used\_connections'响应的连接数  
max\_used\_connections / max\_connections \* 100% （理想值 ≈ 85%）   
如果max\_used\_connections跟max\_connections相同 那么就是max\_connections设置过低或者超过服务器负载上限了

**缓存簇**  
show status like 'key\_blocks\_u%';使用和未使用缓存簇(blocks)数  
show  variables like '%Key\_cache%';  
show  variables like '%Key\_buffer\_size%';  
如果Key\_blocks\_used \* key\_cache\_block\_size  远小于key\_buffer\_size 那么就意味着内存呗浪费了 应该调大key\_buffer\_size值

**打开的文件数**  
show  status like '%open\_file%';  
show  variables like '%open\_file%';  
如果Open\_files和open\_files\_limit接近 就应该增加open\_files\_limit的大小  
不过mysql打开的文件描述符限制都是OS的文件描述符限制，和配置文件中open\_files\_limit的设置没有关系

**全联接**  
show  status like '%select\_full\_\_%';  
全链接是无索引链接 最好避免  
如果Select\_full\_range\_join过高 说明系统运行了很多范围查询联接表

**打开表情况**  
show tatus like 'open%tables%';   
如果open\_tables接近table\_cache的时候，并且Opened\_tables这个值在逐步增加，说明table\_cache不够用 表缓存没有完全用上 那就要考虑增加table\_cache的大小了。还有就是Table\_locks\_waited比较高的时候，也需要增加table\_cache

**查询缓存**  
show status like 'qcache%';  
show variables like 'query\_cache%';察看query\_cache的配置  
query\_cache\_limit：超过此大小的查询将不缓存  
query\_cache\_min\_res\_unit：缓存块的最小大小  
query\_cache\_size：查询缓存大小  
query\_cache\_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql\_no\_cache 查询  
query\_cache\_wlock\_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果

mysql> show status like 'qcache%';  
\+-------------------------+-----------+  
| Variable\_name           | Value     |  
\+-------------------------+-----------+  
| Qcache\_free\_blocks      | 3         |  
| Qcache\_free\_memory      | 536800368 |  
| Qcache\_hits             | 224134    |  
| Qcache\_inserts          | 382       |  
| Qcache\_lowmem\_prunes    | 0         |  
| Qcache\_not\_cached       | 404       |  
| Qcache\_queries\_in\_cache | 40        |  
| Qcache\_total\_blocks     | 95        |  
\+-------------------------+-----------+  
8 rows in set (0.00 sec)  
mysql> show variables like 'query\_cache%';

\+------------------------------+-----------+  
| Variable\_name                | Value     |  
\+------------------------------+-----------+  
| query\_cache\_limit            | 2097152   |  
| query\_cache\_min\_res\_unit     | 2048      |  
| query\_cache\_size             | 536870912 |  
| query\_cache\_type             | ON        |  
| query\_cache\_wlock\_invalidate | OFF       |  
\+------------------------------+-----------+  
5 rows in set (0.00 sec)

**排序情况**  
show  status like 'sort%';  
Sort\_merge\_passes过大 就要增加Sort\_buffer\_size  但是盲目的增加 Sort\_buffer\_size 并不一定能提高速度

**高速缓存**  
show variables like 'key\_buffer\_size';MyISAM 存储引擎键高速缓存 对MyISAM表性能影响很大大  
show  status like 'key\_read%';磁盘读取索引的请求次数  
索引未命中缓存的概率=Key\_reads / Key\_read\_requests \* 100%   
不能以Key\_read\_requests / Key\_reads原则来设置key\_buffer\_size  
Key\_reads 将这个值和系统的i/o做对比

mysql>

**表锁情况**  
show  status like 'table\_locks%';  
Table\_locks\_waited显示了多少表呗锁住并导致了mysql的锁等待 可以开启慢查询看一下

**表扫描情况**  
show status like 'handler\_read%';  
show status like 'com\_select';  
如果Handler\_read\_rnd\_next /Handler\_read\_rnd 的值过大 那么就应该优化索引、查询

mysql> show status like 'handler\_read%';  
\+-----------------------+-------+  
| Variable\_name         | Value |  
\+-----------------------+-------+  
| Handler\_read\_first    | 0     |  
| Handler\_read\_key      | 0     |  
| Handler\_read\_next     | 0     |  
| Handler\_read\_prev     | 0     |  
| Handler\_read\_rnd      | 0     |  
| Handler\_read\_rnd\_next | 20    |  
\+-----------------------+-------+  
6 rows in set (0.00 sec)

**临时表情况**

show  status like 'created\_tmp%';  
show  variables like 'tmp\_table%';  
show  variables like 'max\_heap%';  
如果Created\_tmp\_disk\_tables值较高 则有可能是因为：tmp\_table\_size或者max\_heap\_table\_size太小  
或者是选择blob、text属性的时候创建了临时表  
Created\_tmp\_tables 过高的话 那么就有话查询吧

**二进制日志缓存**

show status like'%binlog%';  
 show variables like'%binlog%';  
如果Binlog\_cache\_disk\_use 和 Binlog\_cache\_use 比例很大 那么就应该增加binlog\_cache\_size的值