A Simple MySQL Plugin to Retrieve System Metrics

MySQL Plugin to Retrieve System MetricsEver wanted to extend MySQL and add some feature you think it is missing?  With MySQL plugins, you can do exactly that.  One thing that has bothered me for several years is that you cannot easily retrieve system metrics from within MySQL.  Whether I am connecting via a remote connection or looking to add features to monitoring without the need for another interface with the server, I have wanted to retrieve system metrics without leaving the MySQL interface.

So, I started a Proof of Concept for this.  My goal was to get metrics such as RAM (total, used, free), system load, CPU utilization, disk utilization for the file system containing the datadir, and more.  My objective was to do this as efficiently within MySQL as possible.  For this, I chose to utilize standard C libraries in as few lines of code as possible without having to scrape system files or run commands to get the data.  The data is pulled on demand so as not to increase the load on the system.

The MySQL plugin architecture is one of the most underutilized features in MySQL in my opinion.  It provides so much power, and I feel the MySQL ecosystem would be so much more powerful if more people took advantage of it.  Below is an example of a basic plugin I created to pull some system metrics.

For this plugin, I chose to access the data via INFORMATION_SCHEMA.OS_METRICS.  This is defined in the plugin in the following:

This defines the structure of the virtual table as having three columns: NAME, VALUE, and COMMENT.  NAME will be a string up to 15 characters long, followed by a float number for VALUE, and a text string for COMMENT up to 50 characters long.

By invoking the sysinfo() function in C, I am able to pull various metrics.  These metrics are returned in a structure.  These can then be passed into the OS_METRICS “table” with the following commands:

In the above case, I reference the element “totalram” from the sysinfo structure and store it in the table.  You can see where there is a line for each column of the table and the values are stored one by one.

Here is the most basic form of a plugin that only pulls RAM information and makes it available within INFORMATION_SCHEMA.OS_METRICS: