Monitoring CPU Steal Time with command line in Ubuntu

Bash command that writes CPU Steal Time along with other CPU usage statistics from top command header once in a second to MySQL database:

top -b -d 1 | ts ‘%Y-%m-%d %H:%M:%S’ | sed -rn “s/(.{19} )Cpu\(s\):\s+([0-9]+\.[0-9]+)%us,\s+([0-9]+\.[0-9]+)%sy,\s+([0-9]+\.[0-9]+)%ni,\s+([0-9]+\.[0-9]+)%id,\s+([0-9]+\.[0-9]+)%wa,\s+([0-9]+\.[0-9]+)%hi,\s+([0-9]+\.[0-9]+)%si,\s+([0-9]+\.[0-9]+)%st(.*)/INSERT INTO m_perf_stat (CDate, cpu_us,  cpu_sy,  cpu_ni, cpu_id,  cpu_wa,  cpu_hi,  cpu_si,  cpu_st) VALUES (‘\1′, \2, \3, \4, \5, \6, \7, \8, \9);/p” | mysql -u <username> -p<password> <database>

that’s the longest bash command I ever written in my life Улыбка

This command uses the following MySQL table:

CREATE TABLE IF NOT EXISTS `m_perf_stat` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `CDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cpu_us` FLOAT NOT NULL,
  `cpu_sy` FLOAT NOT NULL,
  `cpu_ni` FLOAT NOT NULL,
  `cpu_id` FLOAT NOT NULL,
  `cpu_wa` FLOAT NOT NULL,
  `cpu_hi` FLOAT NOT NULL,
  `cpu_si` FLOAT NOT NULL,
  `cpu_st` FLOAT NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

MySQL command line client composes batches of INSERT statements and then executes entire batch at once so CDate should be generated by ts command but not by MySQL, otherwise entire batch will have the same CDate.

To run this command in the background I saved it as perf.sh and then used nohup:

nohup perf.sh &

Now I can analyze my CPU Steal Time using SQL:

SELECT DATE(CDate) d, MAX(`cpu_st`) m FROM `m_perf_stat` GROUP BY d

or probably something like this:

SELECT DATE(CDate) d, HOUR(CDate) h, MAX(`cpu_st`) m FROM `m_perf_stat` GROUP BY d, h
SELECT * FROM `m_perf_stat` WHERE `cpu_st` > 20 ORDER BY `CDate`
SELECT * FROM `m_perf_stat` WHERE TIMESTAMPDIFF(SECOND, '2013-08-30 08:09:06', CDate ) BETWEEN -15 and 15 ORDER BY CDate

Statistics for the last hour:

SELECT DATE( CDate ) d, HOUR( CDate ) h, MINUTE( CDate ) m, MAX( `cpu_st` ) st FROM `m_perf_stat` WHERE ServerId = 2 AND TIMESTAMPDIFF(MINUTE, CDate, Now()) < 60 GROUP BY d, h, m

Leave a Reply

Your email address will not be published. Required fields are marked *