将CPU使用情况导入MySQL表 _MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2083 | 回复: 0

主题： 将CPU使用情况导入MySQL表

barry

注册用户

等级：中校
经验：1534
发帖：236
精华：2
注册：2012-1-13
状态：离线
发送短消息息给barry

加好友发送短消息息给barry

发消息

发表于：

2015-8-24 11:43:05 | [全部帖] [楼主帖]

楼主

如果需要监控CPU的使用情况，可以通过加载/proc/stat来完成。首先需要建立一张监控CPU的表cpu_stat：

create table if not exists cpu_stat
(
id bigint auto_increment primary key,
value char(25) not null,
user bigint,
nice bigint,
system bigint,
idle bigint,
iowait bigint,
irq bigint,
softirq bigint,
steal bigint,
guest bigint,
other bigint,
time datetime
);

接着可以通过load data infile命令来加载/proc/stat文件，但需要对其中一些数值进行转化，命令如下所示：

load data infile '/proc/stat' ignore into table cpu_stat fields terminated by ' '
(@value, @val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10)
set
value = @value,
user = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val1, 0), ifnull(@val2, 0))),
nice = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val2, 0), ifnull(@val3, 0))),
system = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val3, 0), ifnull(@val4, 0))),
idle = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val4, 0), ifnull(@val5, 0))),
iowait = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val5, 0), ifnull(@val6, 0))),
irq = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val6, 0), ifnull(@val7, 0))),
softirq = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val7, 0), ifnull(@val8, 0))),
steal = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val8, 0), ifnull(@val9, 0))),
guest = if(@value not like 'cpu%', null, if(@value != 'cpu', ifnull(@val9, 0), ifnull(@val10, 0))),
other = if(@value not like 'cpu%', user + nice + system + idle + iowait + irq + softirq + steal + guest, @val1),
time = now();

接着可以设置一个定时器来让MySQL数据库自动地运行上述load data infile语句，这样就会有每个时间点的cpu信息被记录到表cpu_stat。执行下述语句就可以得到每个时间点上cpu的使用情况。

select
100 * ((new.user - old.user)/(new.other - old.other)) user,
100 * ((new.nice - old.nice)/(new.other - old.other)) nice,
100 * ((new.system - old.system)/(new.other - old.other)) system,
100 * ((new.idle - old.idle)/(new.other - old.other)) idle,
100 * ((new.iowait - old.iowait)/(new.other - old.other)) iowait,
100 * ((new.irq - old.irq)/(new.other - old.other)) irq,
100 * ((new.softirq - old.softirq)/(new.other - old.other)) softirq,
100 * ((new.steal - old.steal)/(new.other - old.other)) steal,
100 * ((new.guest - old.guest)/(new.other - old.other)) guest,
new.time
from cpu_stat old, cpu_stat new
where new.id - 15 = old.id
and old.value = 'cpu'
and new.value = old.value;

同样，还可以对/proc/diskstat文件执行如上所示的操作，这样就可以对磁盘进行监控操作了。

--转自