本文共 2451 字,大约阅读时间需要 8 分钟。
(内容来自书本《MySQL技术内幕innodb存储引擎》)
如果需要监控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文件执行如上所示的操作,这样就可以对磁盘进行监控操作了。转载地址:http://yvcqj.baihongyu.com/