SQL Exporter for Prometheus

Overview

SQL Exporter is a configuration driven exporter that exposes metrics gathered from DBMSs, for use by the Prometheus monitoring system. Out of the box, it provides support for the following databases and compatible interfaces:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database
• Clickhouse
• Snowflake
• Vertica

In fact, any DBMS for which a Go driver is available may be monitored after rebuilding the binary with the DBMS driver included.

The collected metrics and the queries that produce them are entirely configuration defined. SQL queries are grouped into collectors -- logical groups of queries, e.g. query stats or I/O stats, mapped to the metrics they populate. Collectors may be DBMS-specific (e.g. MySQL InnoDB stats) or custom, deployment specific (e.g. pricing data freshness). This means you can quickly and easily set up custom collectors to measure data quality, whatever that might mean in your specific case.

Per the Prometheus philosophy, scrapes are synchronous (metrics are collected on every /metrics poll) but, in order to keep load at reasonable levels, minimum collection intervals may optionally be set per collector, producing cached metrics when queried more frequently than the configured interval.

Usage

Get Prometheus SQL Exporter, either as a packaged release, as a Docker image.

Use the -help flag to get help information.

$ ./sql_exporter -help
Usage of ./sql_exporter:
  -config.file string
      SQL Exporter configuration file path. (default "sql_exporter.yml")
  -config.check
      Check configuration and exit.
  -web.listen-address string
      Address to listen on for web interface and telemetry. (default ":9399")
  -web.metrics-path string
      Path under which to expose metrics. (default "/metrics")
  [...]

Build

Prerequisites:

• Go Compiler
• GNU Make

By default we produce a binary with all the supported drivers with the following command:

make build

It's also possible to reduce the size of the binary by only including specific set of drivers like Postgres, MySQL and MSSQL. In this case we need to update drivers.go. To avoid manual manipulation there is a helper code generator available, so we can run the following commands:

make drivers-minimal
make build

The first command will regenerate drivers.go file with a minimal set of imported drivers using drivers_gen.go.

Running make drivers-all will regenerate driver set back to the current defaults.

Feel free to revisit and add more drivers as required. There's also the custom list that allows managing a separate list of drivers for special needs.

Configuration

SQL Exporter is deployed alongside the DB server it collects metrics from. If both the exporter and the DB server are on the same host, they will share the same failure domain: they will usually be either both up and running or both down. When the database is unreachable, /metrics responds with HTTP code 500 Internal Server Error, causing Prometheus to record up=0 for that scrape. Only metrics defined by collectors are exported on the /metrics endpoint. SQL Exporter process metrics are exported at /sql_exporter_metrics.

The configuration examples listed here only cover the core elements. For a comprehensive and comprehensively documented configuration file check out documentation/sql_exporter.yml. You will find ready to use "standard" DBMS-specific collector definitions in the examples directory. You may contribute your own collector definitions and metric additions if you think they could be more widely useful, even if they are merely different takes on already covered DBMSs.

./sql_exporter.yml

# Global settings and defaults.
global:
  # Subtracted from Prometheus' scrape_timeout to give us some headroom and prevent Prometheus from
  # timing out first.
  scrape_timeout_offset: 500ms
  # Minimum interval between collector runs: by default (0s) collectors are executed on every scrape.
  min_interval: 0s
  # Maximum number of open connections to any one target. Metric queries will run concurrently on
  # multiple connections.
  max_connections: 3
  # Maximum number of idle connections to any one target.
  max_idle_connections: 3
  # Maximum amount of time a connection may be reused to any one target. Infinite by default.
  max_connection_lifetime: 10m
  # Expose per-query `query_duration_seconds` and `query_rows_returned` gauges, labelled with the
  # `query` name (and `target` in multi-target mode). Off by default to keep the metric surface stable.
  enable_query_metrics: false

# The target to monitor and the list of collectors to execute on it.
target:
  # Target name (optional). Setting this field enables extra metrics e.g. `up` and `scrape_duration` with
  # the `target` label that are always returned on a scrape.
  name: "prices_db"
  # Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)
  # the schema gets dropped or replaced to match the driver expected DSN format.
  data_source_name: 'sqlserver://prom_user:prom_password@dbserver1.example.com:1433'

  # Collectors (referenced by name) to execute on the target.
  # Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
  collectors: [pricing_data_freshness, pricing_*]

  # In case you need to connect to a backend that only responds to a limited set of commands (e.g. pgbouncer) or
  # a data warehouse you don't want to keep online all the time (due to the extra cost), you might want to disable `ping`
  # enable_ping: true

# Collector definition files.
# Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
collector_files:
  - "*.collector.yml"

[!NOTE] The collectors and collector_files configurations support Glob pattern matching. To match names with literal pattern terms in them, e.g. collector_*1*, these must be escaped: collector_\*1\*.

Collectors

Collectors may be defined inline, in the exporter configuration file, under collectors, or they may be defined in separate files and referenced in the exporter configuration by name, making them easy to share and reuse.

The collector definition below generates gauge metrics of the form pricing_update_time{market="US"}.

./pricing_data_freshness.collector.yml

# This collector will be referenced in the exporter configuration as `pricing_data_freshness`.
collector_name: pricing_data_freshness

# A Prometheus metric with (optional) additional labels, value and labels populated from one query.
metrics:
  - metric_name: pricing_update_time
    type: gauge
    help: 'Time when prices for a market were last updated.'
    key_labels:
      # Populated from the `market` column of each row.
      - Market
    static_labels:
      # Arbitrary key/value pair
      portfolio: income
    values: [LastUpdateTime]
    # Static metric value (optional). Useful in case we are interested in string data (key_labels) only. It's mutually
    # exclusive with `values` field.
    # static_value: 1
    # Timestamp value (optional). Should point at the existing column containing valid timestamps to return a metric
    # with an explicit timestamp.
    # timestamp_value: CreatedAt
    query: |
      SELECT Market, max(UpdateTime) AS LastUpdateTime
      FROM MarketPrices
      GROUP BY Market

Database URLs (URL-format DSNs)

To keep things simple and yet allow fully configurable database connections, SQL Exporter uses database URLs (URL-format DSNs) (like sqlserver://prom_user:prom_password@dbserver1.example.com:1433) to refer to database instances.

This exporter relies on xo/dburl package for parsing URL-format DSNs. The goal is to have a unified way to specify DSNs across all supported databases. This can potentially affect your connection to certain databases like MySQL, so you might want to adjust your connection string accordingly:

mysql://user:pass@localhost/dbname - for TCP connection
mysql:/var/run/mysqld/mysqld.sock - for Unix socket connection

[!IMPORTANT] If your DSN contains special characters in any part of your connection string (including passwords), you might need to apply URL encoding (percent-encoding) to them. For example, p@$$w0rd#abc then becomes p%40%24%24w0rd%23abc.

For additional details please refer to xo/dburl documentation.

Security Features

The Helm chart provides enterprise-grade security capabilities for protecting your metrics endpoint:

TLS/HTTPS Encryption

Secure metrics transport using TLS certificates from Kubernetes secrets. Supports TLS 1.3 with configurable cipher suites. See tls-only example.

Basic Authentication

Password-protected metrics endpoint with bcrypt-hashed credentials. Passwords are automatically hashed during pod initialization from plaintext secrets. See auth-only example.

Combined Security

TLS and authentication can be used together, with support for shared or separate Kubernetes secrets for maximum flexibility. See tls-auth example.

Prometheus Integration

Kubernetes-native ServiceMonitor automatically configures Prometheus for HTTPS scraping and basic authentication when enabled.

Miscellaneous

Per-query observability metrics

When global.enable_query_metrics is set to true, every scrape emits two additional gauges per query in the configuration:

• query_duration_seconds{query="<query_name>"} — wall-clock time the query took during the most recent scrape, including row scanning. Emitted even when the query errors, so spikes preceding a failure remain visible.
• query_rows_returned{query="<query_name>"} — number of rows the database returned during the most recent scrape. Errored or skipped rows are not counted.

Both metrics inherit the same constant labels as up / scrape_duration_seconds (notably target in multi-target / jobs mode), so they can be aggregated by target the same way. The feature is off by default to keep the existing metric surface unchanged.

Handling NULL values

Queries that return NULL values are supported, but they are not rendered as metrics. It's useful for situations, when the result set depends on some conditions, so it may be empty. Whenever a query returns NULL values, the exporter logs a message at the Debug level. If your query constantly returns NULL values, it most likely means that you need to revisit your query logic.

Multiple database connections

It is possible to run a single exporter instance against multiple database connections. In this case we need to configure jobs list instead of the target section as in the following example:

jobs:
  - job_name: db_targets
    collectors: [pricing_data_freshness, pricing_*]
    enable_ping: true # Optional, true by default. Set to `false` in case you connect to pgbouncer or a data warehouse
    static_configs:
      - targets:
          pg1: 'pg://db1@127.0.0.1:25432/postgres?sslmode=disable'
          pg2: 'postgresql://username:password@pg-host.example.com:5432/dbname?sslmode=disable'
        labels:  # Optional, arbitrary key/value pair for all targets
          cluster: cluster1

, where DSN strings are assigned to the arbitrary instance names (i.e. pg1 and pg2).

We can also define multiple jobs to run different collectors against different target sets.

Since v0.14, sql_exporter can be passed an optional list of job names to filter out metrics. The jobs[] query parameter may be used multiple times. In Prometheus configuration we can use this syntax under the scrape config:

  params:
    jobs[]:
      - db_targets1
      - db_targets2

This might be useful for scraping targets with different intervals or any other advanced use cases, when calling all jobs at once is undesired.

Scraping PgBouncer, ProxySQL, Clickhouse or Snowflake

Given that PgBouncer is a connection pooler, it doesn't support all the commands that a regular SQL database does, so we need to make some adjustments to the configuration:

• add enable_ping: false to the metric/job configuration as PgBouncer doesn't support the ping command;
• add no_prepared_statement: true to the metric/job configuration as PgBouncer doesn't support the extended query protocol;

For libpq (postgres) driver we only need to set no_prepared_statement: true parameter. For pgx driver, we also need to add default_query_exec_mode=simple_protocol p