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!