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:

You will need to have the MySQL source code available on a server along with the libraries needed to compile C code.  For me, I went with the most basic approach of manually compiling the plugin, although I need to update it with cmake so it is easier to compile.

I named my file, osmetricsplugin.cc.  Of course, in the example below, you will need to define the path for where your code lives where I have placed “{PATH_TO_YOUR_PLUGIN_CODE}”.

You can compile the plugin with a command such as the following:

If you are interested in seeing more of what can be done with the above, check out the GitHub page for the plugin I wrote.

Once you compile it, you should get an osmetricsplugin.so file which can be copied to your MySQL plugin directory with a command such as:

Once it is in place, you can tell MySQL to load the plugin with a command such as:

You can verify that the plugin is loaded correctly:

To query the data, execute a SQL command such as:

There is much more work to be done with the plugin and there is more that can be done to improve it.  I believe it is a very useful feature to be able to access system metrics from within MySQL, but am very interested to hear what others think.

If interested, please check out a more advanced version of the plugin, and here you can learn more about MySQL plugins.

Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.

Download PDF

Share this post

Leave a Reply