Monitoring OS statistics with INFORMATION_SCHEMA plugins

With all the talk lately of the new INFORMATION_SCHEMA plugin API, I thought I’d have a go at making a couple. I’ve now made three different pluggable INFORMATION_SCHEMA tables – one that works from df -k – which will only work on UNIX like systems (other than AIX or HPUX) – and two which integrate the SIGAR library available from Hyperic.

I’ll post the df -k plugin in a couple of days, however I wanted to get the two I have created ‘out to the world’ for some feedback on them! They are currently a very rough prototype (they need a little more work on return checking etc.!) – however they are currently functional.

With all the talk lately of the new INFORMATION_SCHEMA plugin API, I thought I’d have a go at making a couple. I’ve now made three different pluggable INFORMATION_SCHEMA tables – one that works from df -k – which will only work on UNIX like systems (other than AIX or HPUX) – and two which integrate the SIGAR library available from Hyperic.

I’ll post the df -k plugin in a couple of days, however I wanted to get the two I have created based on the SIGAR library ‘out to the world’ for some feedback on them! They are currently a very rough prototype (they need a little more work on return checking etc.!) – however they are currently functional.

There are two INFORMATION_SCHEMA tables within the plugin library – INFORMATION_SCHEMA.OS_STATUS and INFORMATION_SCHEMA.OS_VARIABLES – much like MySQL’s current (well, within 5.1) INFORMATION_SCHEMA.GLOBAL|SESSION_STATUS and INFORMATION_SCHEMA.GLOBAL|SESSION_VARIABLES tables.

Here’s some sample output:

mysql> install plugin os_status soname 'libos_stats_info_schema.so';
Query OK, 0 rows affected (0.00 sec)

mysql> install plugin os_variables soname 'libos_stats_info_schema.so';
Query OK, 0 rows affected (0.00 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from os_variables;
+---------------------------+-------------------+----------------------------+---------------+
| VARIABLE_NAME             | VARIABLE_INSTANCE | VARIABLE_VALUE             | VARIABLE_TYPE |
+---------------------------+-------------------+----------------------------+---------------+
| Cpu_vendor                | cpu0              | Intel                      | VARCHAR       |
| Cpu_model                 | cpu0              | Xeon                       | VARCHAR       |
| Cpu_mhz                   | cpu0              | 3401                       | INTEGER       |
| Cpu_cache_size            | cpu0              | 1024                       | INTEGER       |
| Fs_name                   | /                 | /dev/md0                   | VARCHAR       |
| Fs_location               | /                 | local                      | VARCHAR       |
| Fs_type                   | /                 | ext3                       | VARCHAR       |
| Fs_name                   | /boot             | /dev/cciss/c0d0p1          | VARCHAR       |
| Fs_location               | /boot             | local                      | VARCHAR       |
| Fs_type                   | /boot             | ext3                       | VARCHAR       |
| Fs_name                   | /data0            | /dev/md10                  | VARCHAR       |
| Fs_location               | /data0            | local                      | VARCHAR       |
| Fs_type                   | /data0            | ext3                       | VARCHAR       |
| Fs_name                   | /users            | production:/usersnfs       | VARCHAR       |
| Fs_location               | /users            | remote                     | VARCHAR       |
| Fs_type                   | /users            | nfs                        | VARCHAR       |
| Fs_name                   | /nfstmp1          | nfssrva:/nfstmp1           | VARCHAR       |
| Fs_location               | /nfstmp1          | remote                     | VARCHAR       |
| Fs_type                   | /nfstmp1          | nfs                        | VARCHAR       |
| Rlimit_cpu_cur            |                   | 4294967295                 | INTEGER       |
| Rlimit_cpu_max            |                   | 4294967295                 | INTEGER       |
| Rlimit_file_size_cur      |                   | 4294967295                 | INTEGER       |
| Rlimit_file_size_max      |                   | 4294967295                 | INTEGER       |
| Rlimit_pipe_size_cur      |                   | 8                          | INTEGER       |
| Rlimit_pipe_size_max      |                   | 8                          | INTEGER       |
| Rlimit_data_cur           |                   | 4294967295                 | INTEGER       |
| Rlimit_data_max           |                   | 4294967295                 | INTEGER       |
| Rlimit_stack_cur          |                   | 10240                      | INTEGER       |
| Rlimit_stack_max          |                   | 4294967295                 | INTEGER       |
| Rlimit_core_cur           |                   | 0                          | INTEGER       |
| Rlimit_core_max           |                   | 4294967295                 | INTEGER       |
| Rlimit_memory_cur         |                   | 4294967295                 | INTEGER       |
| Rlimit_memory_max         |                   | 4294967295                 | INTEGER       |
| Rlimit_processes_cur      |                   | 16383                      | INTEGER       |
| Rlimit_processes_max      |                   | 16383                      | INTEGER       |
| Rlimit_open_files_cur     |                   | 1024                       | INTEGER       |
| Rlimit_open_files_max     |                   | 1024                       | INTEGER       |
| Rlimit_virtual_memory_cur |                   | 4294967295                 | INTEGER       |
| Rlimit_virtual_memory_max |                   | 4294967295                 | INTEGER       |
| Os_name                   |                   | Linux                      | VARCHAR       |
| Os_version                |                   | 2.6.9-22.ELsmp             | VARCHAR       |
| Os_architecture           |                   | i686                       | VARCHAR       |
| Os_machine                |                   | i686                       | VARCHAR       |
| Os_description            |                   | Red Hat Enterprise Linux 4 | VARCHAR       |
| Os_patch_level            |                   | unknown                    | VARCHAR       |
| Os_vendor                 |                   | Red Hat                    | VARCHAR       |
| Os_vendor_version         |                   | Enterprise Linux 4         | VARCHAR       |
| Os_vendor_name            |                   | Linux                      | VARCHAR       |
| Os_vendor_code_name       |                   | Nahant Update 2            | VARCHAR       |
+---------------------------+-------------------+----------------------------+---------------+
49 rows in set (0.01 sec)

mysql> select * from os_status;
+-------------------------+-------------------+----------------------+---------------+
| VARIABLE_NAME           | VARIABLE_INSTANCE | VARIABLE_VALUE       | VARIABLE_TYPE |
+-------------------------+-------------------+----------------------+---------------+
| Memory_total            |                   | 1059471360           | INTEGER       |
| Memory_used             |                   | 682172416            | INTEGER       |
| Memory_free             |                   | 377298944            | INTEGER       |
| Swap_total              |                   | 1002676224           | INTEGER       |
| Swap_used               |                   | 121491456            | INTEGER       |
| Swap_free               |                   | 881184768            | INTEGER       |
| Cpu_user_total          |                   | 695644430            | INTEGER       |
| Cpu_sys_total           |                   | 273173250            | INTEGER       |
| Cpu_nice_total          |                   | 14360                | INTEGER       |
| Cpu_idle_total          |                   | 46569287330          | INTEGER       |
| Cpu_wait_total          |                   | 809380330            | INTEGER       |
| Cpu_total_total         |                   | 48347499700          | INTEGER       |
| Cpu_user                | cpu0              | 695644430            | INTEGER       |
| Cpu_sys                 | cpu0              | 273173250            | INTEGER       |
| Cpu_nice                | cpu0              | 14350                | INTEGER       |
| Cpu_idle                | cpu0              | 46569287330          | INTEGER       |
| Cpu_wait                | cpu0              | 809380330            | INTEGER       |
| Cpu_total               | cpu0              | 48347499690          | INTEGER       |
| Load_average            | 0                 | 0.05                 | DECIMAL       |
| Load_average            | 1                 | 0.04                 | DECIMAL       |
| Load_average            | 2                 | 0.01                 | DECIMAL       |
| Fs_size                 | /                 | 9618148              | INTEGER       |
| Fs_free                 | /                 | 5002764              | INTEGER       |
| Fs_used                 | /                 | 4615384              | INTEGER       |
| Fs_avail                | /                 | 4514188              | INTEGER       |
| Fs_files                | /                 | 1221600              | INTEGER       |
| Fs_disk_reads           | /                 | 20785383             | INTEGER       |
| Fs_disk_read_bytes      | /                 | 176143946752         | INTEGER       |
| Fs_disk_writes          | /                 | 625659222            | INTEGER       |
| Fs_disk_write_bytes     | /                 | 363676917760         | INTEGER       |
| Fs_disk_queue           | /                 | 0                    | INTEGER       |
| Fs_size                 | /boot             | 98747                | INTEGER       |
| Fs_free                 | /boot             | 87464                | INTEGER       |
| Fs_used                 | /boot             | 11283                | INTEGER       |
| Fs_avail                | /boot             | 82365                | INTEGER       |
| Fs_files                | /boot             | 25584                | INTEGER       |
| Fs_disk_reads           | /boot             | 2053                 | INTEGER       |
| Fs_disk_read_bytes      | /boot             | 6922240              | INTEGER       |
| Fs_disk_writes          | /boot             | 350                  | INTEGER       |
| Fs_disk_write_bytes     | /boot             | 361472               | INTEGER       |
| Fs_disk_queue           | /boot             | 18446744073709551615 | INTEGER       |
| Fs_size                 | /data0            | 19228180             | INTEGER       |
| Fs_free                 | /data0            | 18804340             | INTEGER       |
| Fs_used                 | /data0            | 423840               | INTEGER       |
| Fs_avail                | /data0            | 17827596             | INTEGER       |
| Fs_files                | /data0            | 2443200              | INTEGER       |
| Fs_disk_reads           | /data0            | 13058321             | INTEGER       |
| Fs_disk_read_bytes      | /data0            | 485011117056         | INTEGER       |
| Fs_disk_writes          | /data0            | 68639632             | INTEGER       |
| Fs_disk_write_bytes     | /data0            | 281147932672         | INTEGER       |
| Fs_disk_queue           | /data0            | 0                    | INTEGER       |
| Fs_size                 | /users            | 373057344            | INTEGER       |
| Fs_free                 | /users            | 43121216             | INTEGER       |
| Fs_used                 | /users            | 329936128            | INTEGER       |
| Fs_avail                | /users            | 24170944             | INTEGER       |
| Fs_files                | /users            | 47382528             | INTEGER       |
| Fs_size                 | /nfstmp1          | 307583488            | INTEGER       |
| Fs_free                 | /nfstmp1          | 179642368            | INTEGER       |
| Fs_used                 | /nfstmp1          | 127941120            | INTEGER       |
| Fs_avail                | /nfstmp1          | 164017952            | INTEGER       |
| Fs_files                | /nfstmp1          | 39075840             | INTEGER       |
| Mysqld_threads          | mysqld            | 10                   | INTEGER       |
| Mysqld_mem_size         | mysqld            | 128045056            | INTEGER       |
| Mysqld_mem_resident     | mysqld            | 26636288             | INTEGER       |
| Mysqld_mem_share        | mysqld            | 5382144              | INTEGER       |
| Mysqld_mem_minor_faults | mysqld            | 8926                 | INTEGER       |
| Mysqld_mem_major_faults | mysqld            | 7                    | INTEGER       |
| Mysqld_mem_page_faults  | mysqld            | 8933                 | INTEGER       |
| Mysqld_cpu_user         | mysqld            | 440                  | INTEGER       |
| Mysqld_cpu_sys          | mysqld            | 180                  | INTEGER       |
| Mysqld_cpu_total        | mysqld            | 620                  | INTEGER       |
| Uptime                  |                   | 24173401.250000      | DECIMAL       |
+-------------------------+-------------------+----------------------+---------------+
72 rows in set (0.01 sec)

If you have multiple CPUs then you will have multiple returns for variables such as ‘Cpu_user’, ‘Cpu_nice’ – labelled cpu0, cpu1 etc. – just like there is with the ‘Fs*’ variables – specific to each CPU.

You can download a copy of the plugin code from here. Build and installation instructions are within the INSTALL file.

All feedback welcome!

2 thoughts on “Monitoring OS statistics with INFORMATION_SCHEMA plugins”

  1. Hi Mark,
    I have couple of university students looking for this project for further developments. Will you mind sharing the code you developed so far?. is it maintained in any version controls outside?

    Thanks,
    Jobin.

Got something to say?