Percona ProcFS UDF MySQLWhen you look at database performance, it does not only depend on internal database operations but also on things going on in the operating system, network, storage system, hardware, etc. With that, if you’re building software to help analyze and optimize database performance, such as Percona Monitoring and Management (PMM), you need access to information from both the database software level and operating system level. The operating system tends to provide an interface to the best available “hardware” metrics, too.

A classical approach to access both is to install some kind of agent on every monitored host to provide the required data.  The problem with this approach is that it may not always be available.

One of the most common use cases is Database as a Service (DBaaS) deployment, where the user can’t run additional software on database hosts. Although, it also could be internal politics and separation of duties with the database team, who is interested in the best database observability and does not decide which software can be run on the database hosts.  It also might be a question of additional complexity at scale.

There is an OS Metrics plugin for MySQL as well as PGNodeMX plugin for PostgreSQL, the two most popular open source databases, which do a great job exposing the most important metrics in their own format.

This approach creates two problems; first, the number of metrics exposed by Linux Kernel always growths, and second, it may not even be 100 percent compatible between different Linux distributions, as those plugins would always need to play catchup.  You have to settle only for some of the information available, or most likely both.

Introducing ProcFS UDF

We are taking another approach with ProcFS UDF, a plugin available with Percona Server for MySQL 8 (currently in Tech Preview).

Instead of writing a plugin that can parse all varieties of ProcFS and SysFS files (the most standard way to access system metrics on Linux), we have a plugin that simply exposes the content of those files:

If you want to extract particular values you can write a stored function, which is probably easier to maintain than a bunch of C code.

You can use well-maintained code from the libraries specializing in parsing the ProcFS format such as GoLang ProcFS Package.

This package is great as it can read contents from ProcFS and SysFS mounted at any location, meaning you can materialize content provided by a ProcFS MySQL package on an in-memory file system and do it very quickly and dirty with reasonable overhead.

Security

You may say WOW, this is great, but what about security?  Does this plugin provide a backdoor to read any file on my filesystem?  Of course not!

First, only files that you specifically allowed by including in the “procfs_files_spec” configuration variable are allowed.  For additional security, this is NOT a dynamic variable, so you can’t change it without restarting MySQL  AND it only allows files with names starting at  “/proc” and “/sys”.

The default configuration as of the time of this writing is this:

Note: Additionally, you may need to check your SELinux/AppArmor configuration to ensure the MySQL process can access those files.

Second, accessing data produced by the plugin requires ACCESS_PROCFS dynamic privilege so by default even the “root” user is not able to access this data.

(More information about security and how to grant access)

Using  Prometheus Node Exporter with MySQL ProcFS Plugin

As I mentioned, Prometheus Node Exporter relies on the fantastic ProcFS Library which is rather easy to hack to use information retrieved through the MySQL ProcFS Plugin.

Here is a prototype, created by one of the wonderful Percona engineers, Nickolay Ihalainen.

You can build and run it this way:

Or alternatively, you could run it in a container, that we pre-built for you:

The --path.sysfs and --path.procfs are already pre-configured in the image so you shouldn’t worry about it.

And you can see the same Node Exporter stats you got used to, but coming from a remote MySQL host rather than a local operating system!

As you will query metrics from Exporter, you may see some errors:

You can go ahead and add files you consider appropriate to ProcFS UDF configuration file:

Next steps

This is a Preview solution, so your feedback is extremely valuable. Let us know if we’re on the right track here or should approach this problem in an entirely different way instead. Give the plugin and prototype Exporter a try and let us know how it is working for you

From our side, we are looking to release a more polished version of Node Exporter which can fetch data from the MySQL ProcFS Plugin for agentless monitoring. We will make our patches available to the upstream team, but I’m not sure they will align with the Node Exporter focus. This will make this solution useful with any dashboards, and any software that uses Prometheus Node Exporter as a data source.

We’re also considering seamlessly integrating it with Percona Monitoring and Management “Agentless” so if the ProcFS Plugin is detected and accessible, operating system stats will become available together with database stats, with no action needed.

And if this approach proves to be useful for MySQL, we’re looking to provide the same functionality for PostgreSQL and MongoDB, as well as work with communities looking to integrate such an approach with other open source database technologies Percona currently does not support.

Finally, if this approach is generally useless for many monitoring applications, we will advocate cloud vendors to make such plugins optionally available with their DBaaS services. Currently, the only way to get OS data with those systems is CloudWatch, StackDriver, and Azure Monitor, which have only supported a limited number of metrics and limited resolution, at an extra cost.

What do you think?

Learn more about ProcFS UDF

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Pierre

I have coded something similar for PostgreSQL:
https://github.com/pierreforstmann/pg_procfs

0
Reply