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 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!