swanhart

In my last blog post, I showed how we can get some raw performance information from /proc into the MySQL database using a LOAD DATA INFILE (LDI) command.

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);