My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Add to Memories Share Next Entry
Its a cheat! Get Linux performance information from your MySQL database without shell access.
swanhart
System administrators familiar with the Linux operating system use the tools in the 'procps' toolset all the time. Tools which read from /proc include top, iostat, vmstat, sar and others. The files in /proc contain useful information about the performance of the system. Most of the files are documented in the Linux kernel documentation. You can also check man 5 proc.

Most performance monitoring tools invoke other tools like iostat to collect performance information instead of reading from the /proc filesytem itself. This begs the question, what can you do if you don't have access to those tools? Perhaps you are using a hosted Linux database and have no access to the underlying shell to execute tools like iostat or top? How could you gather information about the performance of the actual system without being allowed to run the tools?

MySQL includes a command called LOAD DATA INFILE which can read the contents of a delimited text file and store the contents into a database table. The contents of /proc are world readable, so your MySQL database should have access to this information as long as it is running on a Linux server.

Lets start by collecting and reporting on some CPU performance information.
CREATE TEMPORARY TABLE 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 other = IF(@the_key like 'cpu%', NULL, @val1),  
    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)));

Depending on your kernel version you may get 1 or more warnings about unexpected numbers of columns. You can safely ignore these.

mysql> select * from test.proc_stat;
+-----+---------------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+
| seq | the_key       | user   | nice  | system | idle       | iowait | irq  | softirq | steal | guest | other      |
+-----+---------------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+
|   1 | cpu           | 378340 | 33588 |  82489 | 1838257830 |  75444 |  750 |   23065 |     0 |     0 |       NULL |
|   2 | cpu0          |   4152 |   125 |   1613 |  114920899 |    624 |    0 |     869 |     0 |     0 |       NULL | 
|   3 | cpu1          |   2182 |    78 |   1474 |  114924477 |     50 |    2 |       3 |     0 |     0 |       NULL | 
|   4 | cpu2          |   6037 |  5418 |   2289 |  114914024 |     55 |   34 |     401 |     0 |     0 |       NULL | 
|   5 | cpu3          |   3519 |    55 |    842 |  114923794 |     37 |    1 |       1 |     0 |     0 |       NULL | 
|   6 | cpu4          |  71851 |  5443 |   6656 |  114840363 |   3197 |   11 |     720 |     0 |     0 |       NULL | 
|   7 | cpu5          |   2435 |     5 |    801 |  114924963 |     29 |    2 |       0 |     0 |     0 |       NULL | 
|   8 | cpu6          | 136246 |  4711 |  36628 |  114690032 |  46119 |   20 |   14471 |     0 |     0 |       NULL | 
|   9 | cpu7          |   1119 |     2 |    366 |  114926691 |     40 |    1 |       0 |     0 |     0 |       NULL | 
|  10 | cpu8          |   4126 |    34 |   2772 |  114920032 |     92 |    1 |    1153 |     0 |     0 |       NULL | 
|  11 | cpu9          |   1618 |     2 |    694 |  114925811 |     77 |    1 |       0 |     0 |     0 |       NULL | 
|  12 | cpu10         |  18096 |  8735 |   6823 |  114891588 |    396 |  179 |    2379 |     0 |     0 |       NULL | 
|  13 | cpu11         |   7243 |  2583 |   3559 |  114914559 |    241 |    1 |       2 |     0 |     0 |       NULL | 
|  14 | cpu12         |   5215 |  2380 |   2776 |  114915814 |    417 |  342 |    1237 |     0 |     0 |       NULL | 
|  15 | cpu13         |   3224 |    28 |   1507 |  114923336 |     77 |    2 |       0 |     0 |     0 |       NULL | 
|  16 | cpu14         | 109818 |  3979 |  13071 |  114775431 |  23901 |  143 |    1823 |     0 |     0 |       NULL | 
|  17 | cpu15         |   1450 |     1 |    612 |  114926010 |     83 |    1 |       0 |     0 |     0 |       NULL | 
|  18 | intr          |   NULL |  NULL |   NULL |       NULL |   NULL | NULL |    NULL |  NULL |  NULL | 1176485951 | 
|  19 | ctxt          |   NULL |  NULL |   NULL |       NULL |   NULL | NULL |    NULL |  NULL |  NULL |  171220339 | 
|  20 | btime         |   NULL |  NULL |   NULL |       NULL |   NULL | NULL |    NULL |  NULL |  NULL | 1267061074 | 
|  21 | processes     |   NULL |  NULL |   NULL |       NULL |   NULL | NULL |    NULL |  NULL |  NULL |     168510 | 
|  22 | procs_running |   NULL |  NULL |   NULL |       NULL |   NULL | NULL |    NULL |  NULL |  NULL |          1 | 
|  23 | procs_blocked |   NULL |  NULL |   NULL |       NULL |   NULL | NULL |    NULL |  NULL |  NULL |          0 | 
+-----+---------------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+
23 rows in set (0.00 sec)

Now that you know you can collect that information, then you can emulate top to calculate the current total CPU usage. I'll show you how to do that in my next blog post.

Wonderful!

(Anonymous)

2010-03-10 12:05 pm (UTC)

Very nice hack!

very useful

(Anonymous)

2010-03-11 12:58 am (UTC)

look forward to your next post

swanhart, рекомендую скачать фильм Форсаж 5, отличный фильм.

You are viewing swanhart