Tags

database, DB, MariaDB, MySQL, Open Telemetry, OpenTelemetry, Oracle, OTel, OTLP, OTTL, Postgres, queries, sql

When it comes to application observability, it is easy to lose sight of the challenges of tracking how our data flows in and out of the database. Considerations include query performance as the database sees it, when SQL gets held up or fails because of database locks, and database calls that get rejected for various reasons such as privileges (particularly when joining data sets). How impactful these things can be is dependent upon how the application is built, and where the weight of data manipulation resides. For example, a multi-tiered application that uses object-relational mapping frameworks such as Hibernate (Java), Gorm (Go), SQLAlchemy (Python), or tracing methods (even if you don’t instrument the libraries) lets you infer and extrapolate database performance details. However, retrieving the explicit information is always better. But for other solutions, such as ERPs like E-Business Suite and parts of General Dynamics where the business logic is within the database as stored procedures, the ability to extract more detail from the database is going to be needed.

This means our observability agent needs to formulate, execute, and transform the results of SQL queries. This leads us to:

Ideally, plugins that understand how to access the relevant database data and retrieve it in a consumable manner will make the process easier. In other words, having pre-canned logic or configuration that understands how to retrieve core metrics like slow queries is desirable.
Having the ability to run SQL is also freeing, as it allows for application-specific queries to also be incorporated and retrieve metrics and details from where stored procedures have recorded logs. For example, one of Oracle’s IdM products records its logs in a table (see here).

If we can query the database, caching or pooling connections is highly beneficial, since establishing a connection is costly and we don’t want to incur that cost every time we need to execute the queries. We also don’t want to leak connections or rely on the database’s connection manager to decide that connections have been inactive and to reclaim them.

This leads us to ideally having three capabilities:

Pre-canned queries for mainstream database technologies
Ability to add custom queries
Ability to pool connections

Capability matrix

The following shows which observability tools can work with which databases, the nature of the query capabilities and the ability to cache. For full transparency, I am not an expert in every one of these products, so I used AI to help with the analysis. That said, I have checked what the AI has pointed to.

The language used is explicitly defined below the table if you need to confirm it. The one variation is Fluent Bit, which doesn’t provide an out-of-the-box query-related plugin, but we can use Lua for a script that can work with any database.

Database/ Observability agent	OpenTelemetry Collector	Telegraf	Logstash	Metricbeat	Grafana Alloy	Vector	Fluentd	Fluent Bit
Oracle	Config Pool ✓	Config Pool ✓	Config Pool ✓	Config Pool?	Both Pool ✓	—	—	via Lua Pool ✓
PostgreSQL	Both Pool ✓	Both Pool ✓	Config Pool ✓	Both Pool?	Both Pool?	Predefined Pool?	Limited Pool?	via Lua Pool ✓
MySQL	Both Pool ✓	Both Pool ✓	Config Pool ✓	Both Pool?	Predefined Pool?	—	Limited Pool?	via Lua Pool ✓
MariaDB	Config Pool ✓	Both Pool ✓	Config Pool ✓	MySQL-compatible Pool?	MySQL-compatible Pool?	—	Limited Pool?	via Lua Pool ✓
SQL Server	Both Pool ✓	Both Pool ✓	Config Pool ✓	Both Pool?	Both Pool?	—	—	via Lua Pool ✓
ClickHouse	Config Pool ✓	Config Pool ✓	Config, JDBC driver Pool ✓	—	—	—	—	via Lua Pool ✓
SAP HANA	Config Pool ✓	Config Pool ✓	Config, JDBC driver Pool ✓	—	—	—	—	via Lua ODBC Pool ✓
MongoDB	Predefined Pool?	Predefined Pool?	Limited, JDBC adapter Pool ✓*	Predefined Pool?	Predefined Pool?	Predefined Pool?	—	via Lua Pool ✓
Other	Snowflake, SAP ASE Config, Pool ✓	SQLite, CockroachDB, TiDB, Vertica, Netezza, FlightSQL Config, Pool ✓	Other JDBC databases Config, Pool ✓	CockroachDB Config, Pool?	Redis, Snowflake Predefined, Pool?	Other specialised sources Pool?	Other ActiveRecord adapters: Limited Pool?	External commands Pool: n/a

Legend

Both — predefined monitoring queries and configurable SQL queries are available, potentially through separate plugins.
Config — supports user-defined SQL queries.
Predefined — built-in database metrics or fixed queries, without needing general-purpose SQL configuration.
Limited — partial functionality, such as incremental table polling.
— — no native database-querying capability established.
Pool ✓ — connection reuse/pooling is explicitly documented.
Pool? — connection reuse is not sufficiently established by documentation.
Pool n/a — no native database query connection.

Important qualifications

OpenTelemetry: A Both designation can represent the combination of the generic sqlquery receiver and a separate database-specific receiver. Connection pooling is documented, but controls differ between receivers. The generic SQL receiver exposes max_open_conn; certain specialized receivers offer more extensive pooling controls.
Telegraf: Its generic SQL input supports configurable pooling using maximum open and idle connections, maximum idle time and connection lifetime. Its database-specific plugins provide additional predefined monitoring capabilities.
Grafana Alloy: Oracle is a particularly strong option because its exporter explicitly documents max_idle_conns, max_open_conns, predefined metrics and custom metric definitions. Equivalent pooling guarantees have not been established for all other embedded exporters.
Logstash: JDBC connection pooling is supported, but you generally need to install the database-specific JDBC driver separately. Its SQL input produces events rather than directly modelling each result as an OpenTelemetry metric.
Metricbeat: Both means its database-specific metric sets can be combined with its generic SQL module. This does not mean that the generic SQL module itself provides predefined slow-query statements.
Vector, Fluentd and Fluent Bit: Their narrower entries are significant. Vector has native database-specific metrics inputs, Fluentd’s SQL plugin focuses on incremental record ingestion, and Fluent Bit requires database connectivity through a Lua script.

Additional resources

What is clear is that the number of observability agents that provide built-in understanding of databases is limited, and that you need to provide configuration for the queries – which is understandable; if you’re able to at least enable user-defined queries, the user can research how to retrieve data like slow queries. To that end, we’ve started compiling some documents that identify queries that will help for that database. You can find the documentation for the tooling we’re putting together to simplify creating an end-to-end pipeline in GitHub (https://github.com/opamp-observe/config-generator/tree/main/docs/db-query-analysis).

Alternate Strategies

Obviously, we’ve taken an observability agent lead approach, as this means that the data is immediately being routed onwards to where it needs to be. But this polling, while some metrics need to be counts per interval, not all data needs to be that way, and as any integration person will tell you, being event-driven or pushing (as long as we’re not creating unnecessary additional latency in a process) is desirable. This points to: do the databases have built-in push mechanisms?

Built-in in OLTP emitters

In a perfect world, databases would make all their information available through OTLP, including allowing applications to provide additional metadata for application-level logs, metrics and traces. Today, there is varying support for built-in OTLP payload emission, as the following table shows:

Database	What can be sent to an OTLP endpoint?	What can be tailored?
Oracle	Oracle AI Database 26ai can export database observability logs associated with traces. For metrics, Oracle’s separate Oracle AI Database Metrics Exporter publishes metrics over OTLP/gRPC. It can also export the alert log as JSON for a log aggregator. docs.oracle.com	The exporter lets you define custom metrics in YAML or TOML and set collection intervals and query timeouts. Native observability has configurable endpoints and service options. oracle.github.io
PostgreSQL	PostgreSQL has no built-in OTLP metrics exporter in the sources reviewed. The open-source `pg_otel` module can send server log messages to an OTLP endpoint. For metrics, the OpenTelemetry Collector can query PostgreSQL’s statistics. github.com	`pg_otel` lets you set the endpoint and resource attributes. The Collector receiver lets you select metrics; its SQL Query Receiver can turn custom query results into metrics or logs. github.com
MySQL	MySQL’s telemetry component supports OTLP metrics export. Its supplied log exporter sends OTLP logs, but requires MySQL Enterprise Edition or HeatWave; the logging interface alone does not provide an exporter. dev.mysql.com Other vendor-supported variants of MySQL also support OTLP, such as Percona Server for MySQL 9.7	Configure the signal endpoints and protocols, then enable or disable meters and loggers. Logging support depends on edition. dev.mysql.com
MariaDB	No built-in server OTLP exporter was identified. The OpenTelemetry Collector’s MySQL receiver can collect metrics from MySQL and MariaDB servers. You can collect them separately or map query results to log records. github.com	The Collector can select metrics or use custom SQL queries and map their results to metrics or logs. github.com
SQL Server	No built-in server OTLP exporter was identified. The OpenTelemetry Collector’s SQL Server receiver connects to the database to collect metrics; log files or selected query results can be collected separately. github.com	Configure which receiver metrics are emitted, or use custom SQL queries and signal mappings in the Collector. github.com
ClickHouse	No built-in server exporter for OTLP metrics or logs was identified. A Collector can query ClickHouse, read its log files, and forward the results to an OTLP endpoint. github.com	The Collector’s SQL Query Receiver supports custom queries, collection intervals, and mappings from query results to metrics or logs. github.com
MongoDB	MongoDB Ops Manager’s Agent can push monitored metrics to an OTLP/HTTP endpoint. MongoDB Atlas can export selected system and audit logs to OTLP endpoints. These exports are provided by MongoDB’s management services, rather than by the database process itself. www.mongodb.com	Ops Manager lets you configure the endpoint and export cadence. Atlas lets you choose log types and configure up to 10 destinations. For self-managed MongoDB, the Collector can query metrics and collect query-related events. www.mongodb.com

While some databases don’t yet provide support for this, it is likely to become the norm. For example, key members of the PostgreSQL ecosystem, Craig Ringer and Gabriele Bartolini, are actively advocating the native adoption of OTel (see here). Other database solutions we’ve found that include OTLP support include Databricks Lakebase (a managed DB built on Postgres) and SurrealDB.

File Staging

Where there aren’t built-in OTLP capabilities, another approach is to incorporate stored procedures that can write to an externally visible temporary file location. Then the observability tool can use file tailing plugins to ingest the data into the observability pipeline. This means we should take a look at what databases offer in this direction:

Database	Stored procedure ability to write to an external filesystem?	Can it run SQL on a schedule?
Oracle	Yes. PL/SQL can use `UTL_FILE` to write files in permitted directories. For a remote host, `DBMS_SCHEDULER.PUT_FILE` can transfer files using a Scheduler agent.	Yes. `DBMS_SCHEDULER` schedules PL/SQL jobs, among other job types. oracle.hydrogen.sagittarius.connect.product.adaptavist.com
PostgreSQL	Yes, with elevated access. A procedure written in an untrusted language such as `PL/Python` can access operating-system resources. SQL `COPY` can also write a server-side file, but requires superuser rights or the `pg_write_server_files` role.	Yes, with an extension or external scheduler. PostgreSQL has no built-in general job scheduler; `pg_cron` is a widely used extension that runs scheduled SQL inside the database. Python Procedural Language
MySQL	Yes. A stored program can use `SELECT ... INTO OUTFILE` to write query results on the server host. The `FILE` privilege is required, and `secure_file_priv` can restrict the permitted directory.	Yes. The built-in Event Scheduler runs scheduled SQL events; an event can also call a stored procedure. dev.mysql.com
MariaDB	Yes. `SELECT ... INTO OUTFILE` writes query results to a server-side file. As with MySQL, the database service account must be able to write to the destination.	Yes. MariaDB’s built-in Event Scheduler runs scheduled SQL, including calls to stored procedures. mariadb.com
SQL Server	Yes, through privileged features. A stored procedure can invoke `xp_cmdshell` to run an operating-system command, or use CLR code with external access. `xp_cmdshell` is disabled by default.	Yes. SQL Server Agent schedules jobs with T-SQL steps, including calls to stored procedures. The Agent service is disabled by default on a new installation. Microsoft Learn
ClickHouse	No stored-procedure mechanism in the usual sense. ClickHouse does support executable user-defined functions: a query can invoke an external script, which could write files if the script and server account have permission. That is external code execution, not a stored procedure.	For a specific use case, yes. A refreshable materialized view reruns its query on a schedule and stores the result. It is not a general scheduler for arbitrary SQL jobs. ClickHouse Documentation
MongoDB	No general database-side stored procedure with access to the database host’s filesystem. MongoDB server-side JavaScript features are not equivalent to operating-system procedures and are deprecated starting in MongoDB 8.0. Atlas Functions run in a separate managed runtime, not in the database process.	Not SQL: MongoDB Atlas Scheduled Triggers can run server-side functions on a schedule. For self-managed MongoDB, you generally handle scheduling externally.

Conclusion – Understanding what to retrieve

So, there are several different strategies that can be pursued to monitor a database: several different strategies can

Observability plugin
- DB-variety aware.
- Generic connector options, but configuration must provide context for the DB type.
Databases can push or be scraped for telemetry.
The database can externalize data that can then be picked up by the observability tool using mechanisms such as file tailing.

What is clear is that most options require a good understanding of what to retrieve from the database. As a result, to support our work on automating pipeline creation, we have captured documents that bring together recommended practices for what data to retrieve and how to do so, and compiled the information as documents in the repository.