Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2809473
  • 博文数量: 587
  • 博客积分: 6356
  • 博客等级: 准将
  • 技术积分: 6410
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-23 10:54
个人简介

器量大者,福泽必厚

文章分类

全部博文(587)

文章存档

2019年(3)

2018年(1)

2017年(29)

2016年(39)

2015年(66)

2014年(117)

2013年(136)

2012年(58)

2011年(34)

2010年(50)

2009年(38)

2008年(16)

分类: LINUX

2012-10-16 16:57:19

 (2012-08-04 18:18)
标签:  转载 

原文地址找出mysql慢的瓶颈 是什么限制了mysql的性能[转载] 作者《一剑》飘香


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失败次数  如果指过高 那就该检查一下网络 错误链接失败会在此记录
mysql> show status like'%aborted%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 46    |
| Aborted_connects | 1     |
+------------------+-------+
2 rows in set (0.00 sec)
 
慢查询:
show variables like '%slow%' 
show status like '%slow%';
默认是关闭的 开启 set global log_slow_queries=ON ;
Slow_launch_threads 值较大 说明有些东西正在延迟链接的新线程
mysql> show variables like '%slow%' ;
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| log_slow_queries    | ON                                   |
| slow_launch_time    | 1                                    |
| slow_query_log      | ON                                   |
| slow_query_log_file | /data0/mysql/3306/data/ks01-slow.log |
+---------------------+--------------------------------------+
4 rows in set (0.00 sec)
mysql> show status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
2 rows in set (0.00 sec)
 
连接数
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设置过低或者超过服务器负载上限了
mysql> show variables like 'max_connections' ;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.00 sec)

缓存簇
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值
mysql> show status like 'key_blocks_u%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Key_blocks_unused | 213839 |
| Key_blocks_used   | 503    |
+-------------------+--------+
2 rows in set (0.00 sec)
mysql> show  variables like '%Key_cache%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| key_cache_age_threshold  | 300   |
| key_cache_block_size     | 1024  |
| key_cache_division_limit | 100   |
+--------------------------+-------+
3 rows in set (0.00 sec)
mysql> show  variables like '%Key_buffer_size%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 268435456 |
+-----------------+-----------+
1 row in set (0.00 sec)
 
线程使用情况
show status like 'Thread%';如果发现Threads_created值过大的话,可以适当增加配置文件中thread_cache_size值
Threads_cached用来缓存线程
mysql> show status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 4     |
| Threads_connected | 1     |
| Threads_created   | 5     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

打开的文件数
show  status like '%open_file%';
show  variables like '%open_file%';
如果Open_files和open_files_limit接近 就应该增加open_files_limit的大小
不过mysql打开的文件描述符限制都是OS的文件描述符限制,和配置文件中open_files_limit的设置没有关系
mysql> show  status like '%open_file%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 178   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show  variables like '%open_file%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 300   |
| open_files_limit  | 25000 |
+-------------------+-------+
2 rows in set (0.00 sec)
全联接
show  status like '%select_full__%';
全链接是无索引链接 最好避免
如果Select_full_range_join过高 说明系统运行了很多范围查询联接表
mysql> show  status like '%select_full__%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Select_full_join       | 0     |
| Select_full_range_join | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

打开表情况
show tatus like 'open%tables%'; 
如 果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,说明table_cache不够用 表缓存没有完全用上 那就要考虑增加table_cache的大小了。还有就是Table_locks_waited比较高的时候,也需要增加table_cache

mysql> show status like 'open%tables%'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 95    |
| Opened_tables | 0     |
+---------------+-------+
2 rows in set (0.00 sec)

查询缓存
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 并不一定能提高速度
mysql> show  status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

高速缓存
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 variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 268435456 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show  status like 'key_read%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Key_read_requests | 115144 |
| Key_reads         | 1311   |
+-------------------+--------+
2 rows in set (0.00 sec)
mysql>

表锁情况
show  status like 'table_locks%';
Table_locks_waited显示了多少表呗锁住并导致了mysql的锁等待 可以开启慢查询看一下
mysql> show  status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 20370 |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

表扫描情况
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)
mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 0     |
+---------------+-------+
1 row 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 过高的话 那么就有话查询吧
mysql>  show  status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> show  variables like 'tmp_table%';
+----------------+-----------+
| Variable_name  | Value     |
+----------------+-----------+
| tmp_table_size | 257949696 |
+----------------+-----------+
1 row in set (0.00 sec)
mysql> show  variables like 'max_heap%';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 257949696 |
+---------------------+-----------+
1 row in set (0.00 sec)

二进制日志缓存

show status like'%binlog%';
 show variables like'%binlog%';
如果Binlog_cache_disk_use 和 Binlog_cache_use 比例很大 那么就应该增加binlog_cache_size的值
mysql> show status like'%binlog%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Binlog_cache_disk_use  | 0     |
| Binlog_cache_use       | 69166 |
| Com_binlog             | 0     |
| Com_show_binlog_events | 0     |
| Com_show_binlogs       | 0     |
+------------------------+-------+
5 rows in set (0.00 sec)
mysql> show variables like'%binlog%';
+-----------------------------------------+------------+
| Variable_name                           | Value      |
+-----------------------------------------+------------+
| binlog_cache_size                       | 4194304    |
| binlog_direct_non_transactional_updates | OFF        |
| binlog_format                           | MIXED      |
| innodb_locks_unsafe_for_binlog          | OFF        |
| max_binlog_cache_size                   | 8388608    |
| max_binlog_size                         | 1073741824 |
| sync_binlog                             | 0          |
+-----------------------------------------+------------+
7 rows in set (0.00 sec)

原文 http://blog.phpbean.com/a.cn/18/

阅读(1143) | 评论(0) | 转发(1) |
0

上一篇:centos6禁用ipv6(转载)

下一篇:hadoop问题

给主人留下些什么吧!~~