将CPU利用率转换为SQL

Linux下查看CPU利用率的命令有sar,top,vmstat,iostat

针对多核CPU,在查看CPU利用时sar与top会发生错误:

sar:CPU空闲一直为0

top:看到的进程会有利用率超过100%的情况发生

为了统计CPU使用信息在这里选择iostat查看CPU利用率,这个命令可以记录统计时间

下面的这段话摘自Linux manpage

The iostat command generates two types of reports, the CPU Utilization report and the Device Utilization report.

 CPU Utilization Report

              The first report generated by the iostat command is

              the CPU Utilization Report. For multiprocessor sys-

              tems, the CPU values are global averages among  all

              processors.

收集CPU利用率信息

在crontab中加入下面的内容

 00 13 * * * iostat -ct  300 144 > iostat.log

iostat.log的内容为:

Linux 2.6.9-55.ELsmp (TEST)  03/13/09

Time: 13:00:01

avg-cpu:  %user   %nice    %sys %iowait   %idle

           0.41    0.00    0.14    0.13   99.32

……

……

Time: 00:55:01

avg-cpu:  %user   %nice    %sys %iowait   %idle

           0.17    0.00    0.10    0.01   99.72

对iostat..log进行处理,为转化为SQL做准备

grep -v '\' iostat.log | grep -v '\' | sed '/^$/d' | sed '$!N;s/\n/ /' | sed 's/Time: //g' > iostat

处理如果如下:

[root@TEST oracle]# cat iostat

13:00:01            0.41    0.00    0.14    0.13   99.32

13:05:01            0.83    0.00    0.24    0.35   98.58

13:10:01            0.31    0.00    0.12    0.04   99.53

13:15:01            0.20    0.00    0.10    0.01   99.68

将iostat转化为SQL:

awk '{print "insert into checkcpu values('\''10.182.15.55'\'', '\''"$1"'\'', "$2", "$3", "$4", "$5", "$6");"}' iostat > iostat.sql

[root@TEST oracle]# cat iostat.sql

insert into checkcpu values('10.182.15.55', '13:00:01', 0.41, 0.00, 0.14, 0.13, 99.32);

insert into checkcpu values('10.182.15.55', '13:05:01', 0.83, 0.00, 0.24, 0.35, 98.58);

insert into checkcpu values('10.182.15.55', '13:10:01', 0.31, 0.00, 0.12, 0.04, 99.53);

insert into checkcpu values('10.182.15.55', '13:15:01', 0.20, 0.00, 0.10, 0.01, 99.68);