Improving MariaDB Observability with OpenSearch and Grafana

When dealing with queries in MariaDB, there are several approaches, such as the general query log, the slow query log, and the performance_schema.

The general query log is not recommended as it doesn’t contain much valuable information and can use a lot of resources when writing to the file on busy systems.

The slow query log is a much better option, as it contains many metrics and can be tuned. But if you want to collect everything, writing to the disk can also be an expensive operation.

On some systems, such as cloud environments, those files on the filesystem are not always available. Hopefully, it’s also possible to store their content in a table. But this is a global setting for both logs: log_output.

But I will start with another method, easy to integrate with external tools: querying performance_schema.events_statements_history_long.

Performance Schema

Performance_Schema is a system database that provides detailed, low-level insights into server execution, helping you diagnose performance bottlenecks and optimize your database.

To be able to use it and let MariaDB Server populate the events_statements_history_long, we need to add the following lines to the configuration file:

[mariadb]
performance_schema=ON
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON

The events_statements_history_long table, by default, contains the ten thousand most recent completed statement events. This number can be adjusted by setting the performance_schema_events_statements_history_long_size system variable at server startup.

And this is the content of this table we will send to OpenSearch.

OpenSearch

OpenSearch is an open-source search and analytics platform for indexing, searching, and analyzing large volumes of data in real time.
It’s commonly used for log analysis, full-text search, monitoring, and dashboards.

We will create an index per day and send the content of our statement history into it.

Once populated, we can also verify the content of such an index, using http://localhost:9200/mariadb-*/_search?pretty in a browser:

This provides an overview of what is available per document stored in OpenSearch.

Collector

To collect data from Performance_Schema and load it into OpenSearch, we need to use a script.

I’ve created such a script in Python; you can find it here: https://github.com/lefred/mariadb-opensearch-grafana/blob/main/collector/pfs2opensearch.py

The script reads all entries in the table, sends them to OpenSearch, and truncates the table.

Grafana

Grafana is a visualization and monitoring platform that lets you build dashboards and charts from data sources like Prometheus, OpenSearch, and databases.
It’s widely used to track system performance, application metrics, and logs in one place.

Now that we have the data we need in OpenSearch, we can add it as a data source for Grafana:

And we can create a dashboard like this one:

I’ve created two interactive dashboards that you can already use: https://github.com/lefred/mariadb-opensearch-grafana/tree/main/grafana/dashboards

This is an overview:

And you can get the detailed statistics for a specific query only:

Quick Deployment

If you want to test, the easiest way is to deploy such an environment using Docker (or podman)

I’ve created a Docker Compose environment that you can use to deploy all services in 5 commands:

$ git clone https://github.com/lefred/mariadb-opensearch-grafana.git
$ cd mariadb-opensearch-grafana
$ cp .env_sample .env
$ vi .env     # change the passwords
$ docker compose up --build -d

And that’s it. Everything is ready:

You can reach Grafana directly at http://localhost:3030

Conclusion

I have to say that OpenSearch and Grafana are a really nice match for MariaDB statement observability. OpenSearch makes it very simple to store and explore statements collected from Performance Schema, and Grafana is excellent for transforming that data into useful, interactive dashboards. The result is a solution that is not only efficient but also highly user-friendly for investigating query activity and performance issues.

With this setup, MariaDB provides the data, OpenSearch organizes it, and Grafana makes it immediately readable. It’s open, flexible, and easy to deploy, especially with the provided container environment.

Enjoy “dashboarding” 👀 MariaDB!