A Simple MySQL Plugin to Retrieve System Metrics

By
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:

static struct st_mysql_information_schema simple_table_info = { MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION };

static ST_FIELD_INFO simple_table_fields[]=
{
{"NAME", 15, MYSQL_TYPE_STRING, 0, 0, 0, 0},
{"VALUE", 6, MYSQL_TYPE_FLOAT, 0, MY_I_S_UNSIGNED, 0, 0},
{"COMMENT", 50, MYSQL_TYPE_STRING, 0, 0, 0, 0},
{0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}
};

static int simple_fill_table(THD *thd, TABLE_LIST *tables, Item *cond)
{
struct sysinfo info;
TABLE *table= tables->table;

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:

struct sysinfo info;
sysinfo(&info);

// Total usable main memory size
table->field[0]->store("TOTAL_RAM", 9, system_charset_info);
table->field[1]->store(info.totalram * info.mem_unit);
table->field[2]->store("Total usable main memory size", 29, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

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:

#include <sql_class.h>
#include <table.h>
#include <stdlib.h>
#include <ctype.h>
#include <mysql_version.h>
#include <mysql/plugin.h>
#include <my_global.h>
#include <sys/time.h>
#include <sys/resource.h>
#include <sys/sysinfo.h>

static struct st_mysql_information_schema simple_table_info = { MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION };

static ST_FIELD_INFO simple_table_fields[]=
{
{"NAME", 15, MYSQL_TYPE_STRING, 0, 0, 0, 0},
{"VALUE", 6, MYSQL_TYPE_FLOAT, 0, MY_I_S_UNSIGNED, 0, 0},
{"COMMENT", 50, MYSQL_TYPE_STRING, 0, 0, 0, 0},
{0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}
};

static int simple_fill_table(THD *thd, TABLE_LIST *tables, Item *cond)
{
struct sysinfo info;
TABLE *table= tables->table;

sysinfo(&info);

// Total usable main memory size
table->field[0]->store("TOTAL_RAM", 9, system_charset_info);
table->field[1]->store(info.totalram * info.mem_unit);
table->field[2]->store("Total usable main memory size", 29, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

// Available memory size
table->field[0]->store("FREE_RAM", 8, system_charset_info);
table->field[1]->store(info.freeram * info.mem_unit);
table->field[2]->store("Available memory size", 21, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

// Used memory size
table->field[0]->store("USED_RAM", 8, system_charset_info);
table->field[1]->store((info.totalram - info.freeram) * info.mem_unit);
table->field[2]->store("Used memory size", 16, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

// Available memory (percentage)
table->field[0]->store("FREE_RAM_PCT", 12, system_charset_info);
table->field[1]->store((float) info.freeram / info.totalram * 100 * info.mem_unit);
table->field[2]->store("Available memory as a percentage", 32, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

// Used memory (percentage)
table->field[0]->store("USED_RAM_PCT", 12, system_charset_info);
table->field[1]->store((float) (info.totalram - info.freeram) / info.totalram * 100 * info.mem_unit);
table->field[2]->store("Free memory as a percentage", 27, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

// Amount of shared memory
table->field[0]->store("SHARED_RAM", 10, system_charset_info);
table->field[1]->store(info.sharedram * info.mem_unit);
table->field[2]->store("Amount of shared memory", 23, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

// Memory used by buffers
table->field[0]->store("BUFFER_RAM", 10, system_charset_info);
table->field[1]->store(info.bufferram * info.mem_unit);
table->field[2]->store("Memory used by buffers", 22, system_charset_info);
if (schema_table_store_record(thd, table)) return 1;

return 0;
}

static int simple_table_init(void *ptr)
{
ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr;
schema_table->fields_info= simple_table_fields;
schema_table->fill_table= simple_fill_table;
return 0;
}

mysql_declare_plugin(os_metrics)
{
MYSQL_INFORMATION_SCHEMA_PLUGIN,
&simple_table_info, /* type-specific descriptor */
"OS_METRICS", /* table name */
"Michael Patrick", /* author */
"OS Metrics INFORMATION_SCHEMA table", /* description */
PLUGIN_LICENSE_GPL, /* license type */
simple_table_init, /* init function */
NULL,
0x0100, /* version = 1.0 */
NULL, /* no status variables */
NULL, /* no system variables */
NULL, /* no reserved information */
0 /* no flags */
}
mysql_declare_plugin_end;

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:

SRCBASE="../percona-server-5.7.24-27"
g++ -DMYSQL_DYNAMIC_PLUGIN -Wall -fPIC -shared \
-I/usr/include/mysql -m64 \
-I${SRCBASE}/sql \
-I${SRCBASE}/include \
-I${SRCBASE}/libbinlogevents/export \
-I${SRCBASE}/libbinlogevents/include \
-I{PATH_TO_YOUR_PLUGIN_CODE} \
-o osmetricsplugin.so osmetricsplugin.cc

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:

cp osmetricsplugin.so /usr/lib64/mysql/plugin/

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

mysql> INSTALL PLUGIN OS_METRICS SONAME 'osmetricsplugin.so';

You can verify that the plugin is loaded correctly:

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE "%OS%";;
+-------------+----------------+---------------+--------------------+---------------------+--------------------+------------------------+-----------------+-------------------------------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY     | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR   | PLUGIN_DESCRIPTION                  | PLUGIN_LICENSE | LOAD_OPTION |
+-------------+----------------+---------------+--------------------+---------------------+-------------------+------------------------+-----------------+-------------------------------------+----------------+-------------+
| OS_METRICS  | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50724.0             | osmetricsplugin.so | 1.7                    | Michael Patrick | OS Metrics INFORMATION_SCHEMA table | GPL            | ON          |
+-------------+----------------+---------------+--------------------+---------------------+--------------------+------------------------+-----------------+-------------------------------------+----------------+-------------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM information_schema.OS_METRICS;
+------------------------+-------------------+-------------------------------------------------+
| NAME                   | VALUE             | COMMENT                                         |
+------------------------+-------------------+-------------------------------------------------+
| TOTAL_RAM              |        1039118336 | Total usable main memory size                   |
| FREE_RAM               |         341049344 | Available memory size                           |
| USED_RAM               |         698068992 | Used memory size                                |
| FREE_RAM_PCT           | 32.82102966308594 | Available memory as a percentage                |
| USED_RAM_PCT           | 67.17897033691406 | Free memory as a percentage                     |
| SHARED_RAM             |                 0 | Amount of shared memory                         |
| BUFFER_RAM             |           2158592 | Memory used by buffers                          |
+------------------------+-------------------+-------------------------------------------------+
7 rows in set (0.00 sec)

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.


by Michael Patrick via Percona Database Performance Blog

Comments

Post a Comment