数据库IO很高,通过重新建表解决.[@more@]开发人员报应用程序慢,用top,sar查看系统状况,发现iowait为56.0%磁盘读为27600.00 rd_sec/s,磁盘读成为系统瓶颈.处理过程如下:
1.查IO高的sql语句,发现IO最高的为一个存储过程.
2.查看该存储过程,长时间执行的是下面的语句:
SELECT a.company_id, a.operator_id, a.task_id, a.phone, a.content_id,
a.transactionid, a.send_type, a.servercode, nvl(a.subject_product, )
AS subject_product, nvl(a.url, ) AS url, a.investigate_id, nvl(
b.content, ) AS content, nvl(c.ip, ) AS ip, nvl(c.port, 0) AS port,
nvl(c.app_id, 0) app_id
FROM mid_send_queue a, mid_send_content b, mid_company_route c
WHERE a.status = :b1
AND a.content_id = b.content_id (+)
AND c.company_id (+) = a.company_id
AND c.operator_id (+) = a.operator_id
AND c.send_type (+) = a.send_type
ORDER BY a.transactionid ASC
该语句要不停循环执行,对mid_send_content的查询成为最长执行时间的语句.至此确定了IO最大的语句.
3.查看mid_send_content表,该表有27万条语句,是发送的内容表,当天以前的数据没有用,决定删除旧数据,备份数据 到临时表,删除旧数据,共删除23 万条记录,提交,发现数据库扫描的块还是27600black,数据库性能没有改善.
4.决定重建该表.
将该表的内容备份到临时表,删除该表,rename临时表为该表.再查磁盘IO,已经降为650.00 rd_sec/s,数据库恢复 正常.命令如下:
create table mid_send_content_tmp as select * from mid_send_content;
drop table mid_send_content cascade;
rename mid_send_content_tmp to mid_send_content;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/62484/viewspace-926718/,如需转载,请注明出处,否则将追究法律责任。