I've modified that LDI call slightly to set the `other` column to equal the sum total of the CPU counters for those rows which begin with 'cpu'.
original:
other = IF(@the_key like 'cpu%', NULL , @val1);
new:
other = IF(@the_key like 'cpu%', user + nice + system + idle + iowait + irq + softirq + steal + guest, @val1);
Top provides a useful output that looks something like the following:
top - 04:59:14 up 14 days, 3:34, 1 user, load average: 0.00, 0.00, 0.00 Tasks: 216 total, 1 running, 215 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8172108k total, 5115388k used, 3056720k free, 315180k buffers Swap: 2097144k total, 0k used, 2097144k free, 3630748k cached The information I'm currently concerned with presenting is the CPU summary: Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
In order to emulate this display, we will need to sample two data points from /proc/stat.
- Load the data from proc_stat
- Sleep 1 second
- Load the data again
- Compare the values
You should end up with something similar to the following:
mysql> select * from test.proc_stat where the_key = 'cpu'; +-----+---------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+ | seq | the_key | user | nice | system | idle | iowait | irq | softirq | steal | guest | other | +-----+---------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+ | 1 | cpu | 440022 | 36207 | 94583 | 1976124562 | 89082 | 858 | 27243 | 0 | 0 | 1976812557 | | 24 | cpu | 440024 | 36207 | 94583 | 1976130493 | 89082 | 858 | 27243 | 0 | 0 | 1976818490 | +-----+---------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+ 2 rows in set (0.00 sec)
To display the CPU utilization, run the following query:
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 ) ) softer,
100 * ( ( new.steal - old.steal ) / ( new.other - old.other ) ) steal,
100 * ( ( new.guest - old.guest ) / ( new.other - old.other ) ) guest
from test.proc_stat old,
test.proc_stat new
where new.seq > old.seq
and old.the_key = 'cpu'
and new.the_key = old.the_key;
+--------+--------+--------+---------+--------+--------+--------+--------+--------+
| user | nice | system | idle | iowait | irq | softer | steal | guest |
+--------+--------+--------+---------+--------+--------+--------+--------+--------+
| 0.0337 | 0.0000 | 0.0000 | 99.9663 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 |
+--------+--------+--------+---------+--------+--------+--------+--------+--------+
1 row in set (0.01 sec)
edit:
for completeness sake, here is the SQL script I execute to load the data from proc:
CREATE TABLE IF NOT EXISTS test.proc_stat (
seq tinyint auto_increment primary key,
the_key char(25) NOT NULL,
user bigint,
nice bigint,
system bigint,
idle bigint,
iowait bigint,
irq bigint,
softirq bigint,
steal bigint,
guest bigint,
other bigint
);
/* MySQL treats consecutive delimiters as separate fields, so some fancy footwork
is required to load the file successfully. The file includes a cpu field followed
by two spaces which is the sum of all the individual CPUs in the system.
To account for this each row is read into some MySQL variables. Those variables
are examined to determine which field holds the correct value.
*/
LOAD DATA INFILE '/proc/stat'
IGNORE INTO TABLE test.proc_stat
FIELDS TERMINATED BY ' '
(@the_key, @val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10)
SET
the_key = @the_key,
user = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val1, 0), IFNULL(@val2,0))),
nice = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val2, 0), IFNULL(@val3,0))),
system = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val3, 0), IFNULL(@val4,0))),
idle = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val4, 0), IFNULL(@val5,0))),
iowait = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val5, 0), IFNULL(@val6,0))),
irq = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val6, 0), IFNULL(@val7,0))),
softirq = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val7, 0), IFNULL(@val8,0))),
steal = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val8, 0), IFNULL(@val9,0))),
guest = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val9, 0), IFNULL(@val10,0))),
other = IF(@the_key like 'cpu%', user + nice + system + idle + iowait + irq + softirq + steal + guest, @val1);
PlanetMySQL Voting: Vote UP / Vote DOWN