MySQL: CPU information from SQL

Do you know that it’s possible to get information from the CPUs of your MySQL Server from SQL ?

If you enable the status for the  INNODB_METRICS table in INFORMATION_SCHEMA, you will be able to query CPU information.

First, check if those status are enabled:

MySQL> SELECT name, subsystem, status 
       FROM INFORMATION_SCHEMA.INNODB_METRICS where NAME like 'cpu%';
+---------------+-----------+----------+
| name          | subsystem | status   |
+---------------+-----------+----------+
| cpu_utime_abs | cpu       | disabled |
| cpu_stime_abs | cpu       | disabled |
| cpu_utime_pct | cpu       | disabled |
| cpu_stime_pct | cpu       | disabled |
| cpu_n         | cpu       | disabled |
+---------------+-----------+----------+
5 rows in set (0.00 sec)

By default, they are not, let’s enable them:

MySQL> SET GLOBAL innodb_monitor_enable='cpu%';
Query OK, 0 rows affected (0.00 sec)

MySQL> SELECT name, subsystem, status 
       FROM INFORMATION_SCHEMA.INNODB_METRICS 
       WHERE NAME like 'cpu%';
 +---------------+-----------+---------+
 | name          | subsystem | status  |
 +---------------+-----------+---------+
 | cpu_utime_abs | cpu       | enabled |
 | cpu_stime_abs | cpu       | enabled |
 | cpu_utime_pct | cpu       | enabled |
 | cpu_stime_pct | cpu       | enabled |
 | cpu_n         | cpu       | enabled |
 +---------------+-----------+---------+

Now, it’s very easy to see the content of these tables :

MySQL> SELECT * from information_schema.INNODB_METRICS 
       WHERE name LIKE 'cpu%'\G

This new feature can be very useful in cloud environment where this information is not always available or in environments where the DBA doesn’t have system access (I hope this is not a too popular environment).