免费注册 查看新帖 |

Chinaunix

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

sql引起的磁盘io性能下降 不知道如何进行分析 [复制链接]

论坛徽章:
0
发表于 2010-07-01 14:54 |显示全部楼层
mysql 在线表table1 (有一个text字段 a)
text和blob字段类型是不会被buffer存储的.而是存储在tmp_table中.tmp_table是存在磁盘中的.因此引起磁盘IO的问题.

请看下该mysql所在linux的 当前状态

[root@www var]# vmstat 1 10
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4 9 114368 16208 4600 855608 1 2 0 2 2 0 2 2 2 1
2 11 114852 17716 4604 854524 212 616 3476 616 317 872 1 2 0 97
0 8 115184 18804 4632 853624 140 420 3704 588 292 763 3 3 0 95
0 9 115600 17292 4632 855476 104 504 2884 504 269 749 4 4 0 92
1 11 115864 16104 4636 856968 68 264 3492 264 262 682 4 2 0 94
5 7 116368 17068 4640 856460 128 604 4212 604 337 916 5 2 0 93

[root@www var]# top -d 1

16:33:31 up 82 days, 14:46, 2 users, load average: 8.17, 4.53, 3.05
144 processes: 141 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
  total 4.3% 0.0% 2.5% 0.1% 0.0% 92.8% 0.0%
  cpu00 5.5% 0.0% 1.7% 0.0% 0.0% 92.7% 0.0%
  cpu01 3.1% 0.0% 3.4% 0.3% 0.0% 93.0% 0.0%
Mem: 1028320k av, 1011796k used, 16524k free, 0k shrd, 4800k buff

| 1672784 | root | localhost | word | Query | 380 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 44800,20 |
| 1672792 | root | localhost | word | Query | 354 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='64' AND ifcheck=1 ORDER BY postdate LIMIT 16160,20 |
| 1672804 | root | localhost | word | Query | 307 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='43' AND ifcheck=1 ORDER BY postdate LIMIT 17160,20 |
| 1672807 | root | localhost | word | Query | 298 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='44' AND ifcheck=1 ORDER BY postdate LIMIT 67780,20 |
| 1672816 | root | localhost | word | Query | 281 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='43' AND ifcheck=1 ORDER BY postdate LIMIT 17160,20 |
| 1672822 | root | localhost | word | Query | 257 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='65' AND ifcheck=1 ORDER BY postdate LIMIT 10020,20 |
| 1672825 | root | localhost | word | Query | 218 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='65' AND ifcheck=1 ORDER BY postdate LIMIT 10020,20 |
| 1672830 | root | localhost | word | Query | 206 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='66' AND ifcheck=1 ORDER BY postdate LIMIT 23120,20 |
| 1672833 | root | localhost | word | Query | 201 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='82' AND ifcheck=1 ORDER BY postdate LIMIT 40440,20 |
| 1672837 | root | localhost | word | Query | 176 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='66' AND ifcheck=1 ORDER BY postdate LIMIT 23120,20 |
| 1672845 | root | localhost | word | Query | 162 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 37260,20 |
| 1672854 | root | localhost | word | Query | 132 | Sending data | SELECT c.* FROM pwcms_comment c WHERE c.itemid='55' AND ifcheck=1 ORDER BY postdate LIMIT 37260,20 |


问题
1 这里对sql引起的磁盘io性能下降 不知道如何进行分析 , 如何针对上面的现实信息进行分析磁盘io有问题??
2 如何做data index数据分离 在mysql?

论坛徽章:
0
发表于 2010-07-01 15:07 |显示全部楼层
vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r  b   swpd free   buff   cache    si   so    bi     bo    in    cs   us  sy  id wa
0  1  24980 10792 8296  47316    5   19   205    52  1161   698  26  3  1  70
1 观察磁盘活动情况
磁盘活动情况主要从以下几个指标了解:
bi:表示从磁盘每秒读取的块数(blocks/s)。数字越大,表示读磁盘的活动越多。
bo:表示每秒写到磁盘的块数(blocks/s)。数字越大,表示写磁盘的活动越多。
wa:cpu等待磁盘I/O(未决的磁盘IO)的时间比例。数字越大,表示文件系统活动阻碍cpu的情况越严重,因为cpu在等待慢速的磁盘系统提供数据。wa为0是最理想的。如果wa经常大于10,可能文件系统就需要进行性能调整了。  
2 观察cpu活动情况
vmstat比top更能反映出cpu的使用情况:
us:用户程序使用cpu的时间比例。这个数字越大,表示用户进程越繁忙。
sy:系统调用使用cpu的时间比例。注意,NFS由于是在内核里面运行的,所以NFS活动所占用的cpu时间反映在sy里面。这个数字经常很大的话,就需要注意是否某个内核进程,比如NFS任务比较繁重。如果us和sy同时都比较大的话,就需要考虑将某些用户程序分离到另外的服务器上面,以免互相影响。
id:cpu空闲的时间比例。
wa:cpu等待未决的磁盘IO的时间比



top:
主要参数
d:指定更新的间隔,以秒计算。
q:没有任何延迟的更新。如果使用者有超级用户,则top命令将会以最高的优先序执行。
c:显示进程完整的路径与名称。
S:累积模式,会将己完成或消失的子行程的CPU时间累积起来。
s:安全模式。
i:不显示任何闲置(Idle)或无用(Zombie)的行程。
n:显示更新的次数,完成后将会退出to
显示参数:
PID(Process ID):进程标示号。
USER:进程所有者的用户名。
PR:进程的优先级别。
NI:进程的优先级别数值。
VIRT:进程占用的虚拟内存值。
RES:进程占用的物理内存值。
SHR:进程使用的共享内存值。
S:进程的状态,其中S表示休眠,R表示正在运行,Z表示僵死状态,N表示该进程优先值是负数。
%CPU:该进程占用的CPU使用率。
%MEM:该进程占用的物理内存和总内存的百分比。
TIME+:该进程启动后占用的总的CPU时间。
Command:进程启动的启动命令名称,如果这一行显示不下,进程会有一个完整的命令行。
top命令使用过程中,还可以使用一些交互的命令来完成其它参数的功能。这些命令是通过快捷键启动的。
<空格>:立刻刷新。
P:根据CPU使用大小进行排序。
T:根据时间、累计时间排序。
q:退出top命令。
m:切换显示内存信息。
t:切换显示进程和CPU状态信息。
c:切换显示命令名称和完整命令行。
M:根据使用内存大小进行排序。
W:将当前设置写入~/.toprc文件中。这是写top配置文件的推荐方法。

论坛徽章:
0
发表于 2010-07-01 15:07 |显示全部楼层
检查my.cnf配置文件,并看这两项的值
sort_buffer_size
read_buffer_size

论坛徽章:
0
发表于 2011-08-18 13:31 |显示全部楼层
inux 系统负载高如何检查?
http://szedwin.gotoip1.com/read.php?tid-1099.html
       
               
楼主  发表于: 2010-07-01
1:load Average fV "gL(7  
1.1:什么是Load?什么是Load Average? oJ:\8>)9  
   Load 就是对计算机干活多少的度量(WikiPedia:the system Load is a measure of the amount of work that a compute system is doing) 8LPvb#9=  
*wfb~&: }  
   简单的说是进程队列的长度。Load Average 就是一段时间(1分钟、5分钟、15分钟)内平均Load。 &E/0jxM1  
2HX#:y{\l  
1.2:查看指令: ~;QzV?%  
   w or uptime or procinfo or top ?=},%^  
(O(X k+L  
   ]di^H>,xU  
   load average: 0.02,   0.27,    0.17 &{R]v/{p]  
   1 per/minute 5 per/minute 15 per/minute !vY5X2?tr,  
Y CBcyE}p  
B/^1uPTZ71  
1.3:如何判断系统是否已经Over Load? Vz\?a8qQ<  
对一般的系统来说,根据cpu数量去判断。如果平均负载始终在1.2一下,而你有2颗cup的机器。那么基本不会出现cpu不够用的情况。也就是Load平均要小于Cpu的数量 gP^p7aYwn  
1.4:Load与容量规划(Capacity Planning) ^}{`bw

论坛徽章:
0
发表于 2011-08-18 16:35 |显示全部楼层
这篇文章 最好 了

http://hi.baidu.com/izouying/blo ... 4134d1a144dfca.html
1:load Average
   1.1:什么是Load?什么是Load Average?
   Load 就是对计算机干活多少的度量(WikiPedia:the system Load is a measure of the amount of work that a compute system is doing)
   简单的说是进程队列的长度。Load Average 就是一段时间(1分钟、5分钟、15分钟)内平均Load。【参考文章:unix Load Average Part1:How It Works】

   1.2:查看指令:
   w or uptime or procinfo or top

  
   load average: 0.02,   0.27,    0.17
   1 per/minute 5 per/minute 15 per/minute


1.3:如何判断系统是否已经Over Load?
对一般的系统来说,根据cpu数量去判断。如果平均负载始终在1.2一下,而你有2颗cup的机器。那么基本不会出现cpu不够用的情况。也就是Load平均要小于Cpu的数量
1.4:Load与容量规划(Capacity Planning)
       一般是会根据15分钟那个load 平均值为首先。

1.5:Load误解:
1:系统load高一定是性能有问题。
    真相:Load高也许是因为在进行cpu密集型的计算
        2:系统Load高一定是CPU能力问题或数量不够。
    真相:Load高只是代表需要运行的队列累计过多了。但队列中的任务实际可能是耗Cpu的,也可能是耗i/0奶子其他因素的。
3:系统长期Load高,首先增加CPU
    真相:Load只是表象,不是实质。增加CPU个别情况下会临时看到Load下降,但治标不治本。

2:在Load average 高的情况下如何鉴别系统瓶颈。
   是CPU不足,还是io不够快造成或是内存不足?

   2.1:查看系统负载vmstat
Vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 100152 2436 97200 289740 0 1 34 45 99 33 0 0 99 0

procs
r 列表示运行和等待cpu时间片的进程数,如果长期大于1,说明cpu不足,需要增加cpu。
b 列表示在等待资源的进程数,比如正在等待I/O、或者内存交换等。
cpu 表示cpu的使用状态
us 列显示了用户方式下所花费 CPU 时间的百分比。us的值比较高时,说明用户进程消耗的cpu时间多,但是如果长期大于50%,需要考虑优化用户的程序。
sy 列显示了内核进程所花费的cpu时间的百分比。这里us + sy的参考值为80%,如果us+sy 大于 80%说明可能存在CPU不足。
wa 列显示了IO等待所占用的CPU时间的百分比。这里wa的参考值为30%,如果wa超过30%,说明IO等待严重,这可能是磁盘大量随机访问造成的,也可能磁盘或者磁盘访问控制器的带宽瓶颈造成的(主要是块操作)。
id 列显示了cpu处在空闲状态的时间百分比
system 显示采集间隔内发生的中断数
in 列表示在某一时间间隔中观测到的每秒设备中断数。
cs列表示每秒产生的上下文切换次数,如当 cs 比磁盘 I/O 和网络信息包速率高得多,都应进行进一步调查。
memory
swpd 切换到内存交换区的内存数量(k表示)。如果swpd的值不为0,或者比较大,比如超过了100m,只要si、so的值长期为0,系统性能还是正常
free 当前的空闲页面列表中内存数量(k表示)
buff 作为buffer cache的内存数量,一般对块设备的读写才需要缓冲。
cache: 作为page cache的内存数量,一般作为文件系统的cache,如果cache较大,说明用到cache的文件较多,如果此时IO中bi比较小,说明文件系统效率比较好。
swap
si 由内存进入内存交换区数量。
so由内存交换区进入内存数量。
IO
bi 从块设备读入数据的总量(读磁盘)(每秒kb)。
bo 块设备写入数据的总量(写磁盘)(每秒kb)
这里我们设置的bi+bo参考值为1000,如果超过1000,而且wa值较大应该考虑均衡磁盘负载,可以结合iostat输出来分析。

   2.2:查看磁盘负载iostat
每隔2秒统计一次磁盘IO信息,直到按Ctrl+C终止程序,-d 选项表示统计磁盘信息, -k 表示以每秒KB的形式显示,-t 要求打印出时间信息,2 表示每隔 2 秒输出一次。第一次输出的磁盘IO负载状况提供了关于自从系统启动以来的统计信息。随后的每一次输出则是每个间隔之间的平均IO负载状况。

# iostat -x 1 10
Linux 2.6.18-92.el5xen 02/03/2009
avg-cpu:   %user %nice %system %iowait   %steal %idle
            1.10 0.00 4.82 39.54 0.07 54.46
Device:       rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await   svctm   %util
   sda             0.00     3.50   0.40   2.50     5.60 48.00 18.48     0.00 0.97 0.97 0.28
   sdb             0.00     0.00   0.00   0.00     0.00     0.00     0.00     0.00 0.00 0.00 0.00
   sdc             0.00     0.00   0.00   0.00     0.00     0.00     0.00     0.00 0.00 0.00 0.00
   sdd             0.00     0.00   0.00   0.00     0.00     0.00     0.00     0.00 0.00 0.00 0.00
   sde             0.00     0.10   0.30   0.20     2.40     2.40     9.60     0.00 1.60 1.60 0.08
   sdf              17.40     0.50 102.00   0.20 12095.20     5.60 118.40     0.70 6.81 2.09   21.36
   sdg          232.40     1.90 379.70   0.50 76451.20 19.20 201.13     4.94 13.78 2.45   93.16
   rrqm/s: 每秒进行 merge 的读操作数目。即 delta(rmerge)/s
   wrqm/s:   每秒进行 merge 的写操作数目。即 delta(wmerge)/s
   r/s:           每秒完成的读 I/O 设备次数。即 delta(rio)/s
   w/s:       每秒完成的写 I/O 设备次数。即 delta(wio)/s
   rsec/s: 每秒读扇区数。即 delta(rsect)/s
   wsec/s: 每秒写扇区数。即 delta(wsect)/s
   rkB/s:   每秒读K字节数。是 rsect/s 的一半,因为每扇区大小为512字节。(需要计算)
   wkB/s: 每秒写K字节数。是 wsect/s 的一半。(需要计算)
   avgrq-sz: 平均每次设备I/O操作的数据大小 (扇区)。delta(rsect+wsect)/delta(rio+wio)
   avgqu-sz: 平均I/O队列长度。即 delta(aveq)/s/1000 (因为aveq的单位为毫秒)。
   await: 平均每次设备I/O操作的等待时间 (毫秒)。即 delta(ruse+wuse)/delta(rio+wio)
   svctm: 平均每次设备I/O操作的服务时间 (毫秒)。即 delta(use)/delta(rio+wio)
   %util:    一秒中有百分之多少的时间用于 I/O 操作,或者说一秒中有多少时间 I/O 队列是非空的。即 delta(use)/s/1000 (因为use的单位为毫秒)
  
   如果 %util 接近 100%,说明产生的I/O请求太多,I/O系统已经满负荷,该磁盘
   可能存在瓶颈。
   idle小于70% IO压力就较大了,一般读取速度有较多的wait.
  
   同时可以结合vmstat 查看查看b参数(等待资源的进程数)和wa参数(IO等待所占用的CPU时间的百分比,高过30%时IO压力高)
  
   另外还可以参考
   一般:
   svctm < await (因为同时等待的请求的等待时间被重复计算了),
   svctm的大小一般和磁盘性能有关:CPU/内存的负荷也会对其有影响,请求过多也会间接导致 svctm 的增加。
   await: await的大小一般取决于服务时间(svctm) 以及 I/O 队列的长度和 I/O 请求的发出模式。
   如果 svctm 比较接近 await,说明I/O 几乎没有等待时间;
   如果 await 远大于 svctm,说明 I/O队列太长,应用得到的响应时间变慢,
   如果响应时间超过了用户可以容许的范围,这时可以考虑更换更快的磁盘,调整内核 elevator算法,优化应用,或者升级 CPU。
   队列长度(avgqu-sz)也可作为衡量系统 I/O 负荷的指标,但由于 avgqu-sz 是按照单位时间的平均值,所以不能反映瞬间的 I/O 洪水。
  
     别人一个不错的例子.(I/O 系统 vs. 超市排队)
   举一个例子,我们在超市排队 checkout 时,怎么决定该去哪个交款台呢? 首当是看排的队人数,5个人总比20人要快吧?除了数人头,我们也常常看看前面人购买的东西多少,如果前面有个采购了一星期食品的大妈,那么可以考虑换个队排了。还有就是收银员的速度了,如果碰上了连钱都点不清楚的新手,那就有的等了。另外,时机也很重要,可能 5分钟前还人满为患的收款台,现在已是人去楼空,这时候交款可是很爽啊,当然,前提是那过去的 5 分钟里所做的事情比排队要有意义(不过我还没发现什么事情比排队还无聊的)。
   I/O 系统也和超市排队有很多类似之处:
   r/s+w/s 类似于交款人的总数
   平均队列长度(avgqu-sz)类似于单位时间里平均排队人的个数
   平均服务时间(svctm)类似于收银员的收款速度
   平均等待时间(await)类似于平均每人的等待时间
   平均I/O数据(avgrq-sz)类似于平均每人所买的东西多少
   I/O 操作率 (%util)类似于收款台前有人排队的时间比例。
   我们可以根据这些数据分析出 I/O 请求的模式,以及 I/O 的速度和响应时间。
   下面是别人写的这个参数输出的分析
   # iostat -x 1
   avg-cpu:   %user %nice %sys %idle
   16.24 0.00 4.31 79.44
   Device: rrqm/s wrqm/s r/s w/s   rsec/s   wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await   svctm   %util
   /dev/cciss/c0d0
   0.00   44.90   1.02 27.55 8.16   579.59     4.08 289.80 20.57 22.35 78.21 5.00   14.29
   /dev/cciss/c0d0p1
   0.00   44.90   1.02 27.55 8.16   579.59     4.08 289.80 20.57 22.35 78.21 5.00   14.29
   /dev/cciss/c0d0p2
   0.00 0.00   0.00   0.00 0.00 0.00     0.00     0.00     0.00     0.00 0.00 0.00 0.00
   上面的 iostat 输出表明秒有 28.57 次设备 I/O 操作: 总IO(io)/s = r/s(读) +w/s(写) = 1.02+27.55 = 28.57 (次/秒) 其中写操作占了主体 (w:r = 27:1)。
   平均每次设备 I/O 操作只需要 5ms 就可以完成,但每个 I/O 请求却需要等上 78ms,为什么? 因为发出的 I/O 请求太多 (每秒钟约 29 个),假设这些请求是同时发出的,那么平均等待时间可以这样计算:
   平均等待时间 = 单个 I/O 服务时间 * ( 1 + 2 + ... + 请求总数-1) / 请求总数
   应用到上面的例子: 平均等待时间 = 5ms * (1+2+...+2/29 = 70ms,和 iostat 给出的78ms 的平均等待时间很接近。这反过来表明 I/O 是同时发起的。
   每秒发出的 I/O 请求很多 (约 29 个),平均队列却不长 (只有 2 个 左右),这表明这 29 个请求的到来并不均匀,大部分时间 I/O 是空闲的。
   一秒中有 14.29% 的时间 I/O 队列中是有请求的,也就是说,85.71% 的时间里 I/O 系统无事可做,所有 29 个 I/O 请求都在142毫秒之内处理掉了。
   delta(ruse+wuse)/delta(io) = await = 78.21 => delta(ruse+wuse)/s=78.21 * delta(io)/s = 78.21*28.57 =2232.8,表明每秒内的I/O请求总共需要等待2232.8ms。所以平均队列长度应为 2232.8ms/1000ms = 2.23,而iostat 给出的平均队列长度 (avgqu-sz) 却为 22.35,为什么?! 因为 iostat 中有 bug,avgqu-sz值应为 2.23,而不是 22.35。

从以下链接摘录

http://hi.baidu.com/coolhayy/blo ... 0f5ca7caefd0e9.html

http://www.dbanotes.net/arch/unix_linux_load.html

http://linux.die.net/man/1/iostat

http://cqfish.blog.51cto.com/622299/140776

http://www.teamquest.com/resources/gunther/display/5/

论坛徽章:
0
发表于 2011-08-18 16:39 |显示全部楼层
如果centos没有 iostat
请看这个
http://bbs.chinaunix.net/archiver/tid-2333177.html

论坛徽章:
59
2015七夕节徽章
日期:2015-08-24 11:17:25ChinaUnix专家徽章
日期:2015-07-20 09:19:30每周论坛发贴之星
日期:2015-07-20 09:19:42ChinaUnix元老
日期:2015-07-20 11:04:38荣誉版主
日期:2015-07-20 11:05:19巳蛇
日期:2015-07-20 11:05:26CU十二周年纪念徽章
日期:2015-07-20 11:05:27IT运维版块每日发帖之星
日期:2015-07-20 11:05:34操作系统版块每日发帖之星
日期:2015-07-20 11:05:36程序设计版块每日发帖之星
日期:2015-07-20 11:05:40数据库技术版块每日发帖之星
日期:2015-07-20 11:05:432015年辞旧岁徽章
日期:2015-07-20 11:05:44
发表于 2011-08-21 20:52 |显示全部楼层
好帖子学习了啊。

论坛徽章:
0
发表于 2015-11-07 11:03 |显示全部楼层
嘎嘎,,,好帖子学习了啊。

论坛徽章:
1
IT运维版块每日发帖之星
日期:2016-07-29 06:20:00
发表于 2015-11-11 14:32 |显示全部楼层
为什么看不到
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

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

清除 Cookies - ChinaUnix - Archiver - WAP - TOP