sql_exporter

SQL Exporter is a configuration-driven tool that gathers metrics from various Database Management Systems (DBMSs) and exposes them for Prometheus to scrape.

Supported Databases:

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

Any DBMS with a compatible Go driver can be supported by rebuilding the binary with that driver included.

Core Concepts:

• Collectors: Logical groups of SQL queries (e.g., _query_stats_) mapped to specific Prometheus metrics. Collectors can be DBMS-specific or custom for application-specific data quality monitoring.
• Scraping Model: Scrapes are synchronous (metrics are collected on every /metrics poll). To manage load, you can set a min_interval per collector to cache metrics if queried more frequently than the interval.

Unlike other database-agnostic exporters that collect data at fixed intervals independent of Prometheus, sql_exporter is designed to run collections in response to Prometheus scrapes. This approach helps avoid common issues found in interval-based collectors, such as:

• Jitter
• Duplicate data points
• Collected but not scraped data points

Additionally, sql_exporter provides better control over label application and avoids the 'spammy' base label sets common in other exporters. It also allows for easier configuration reuse across different jobs and instances.

You can reference secrets in different namespaces using the format k8ssecret://namespace/secret-name.

Warning: Accessing secrets from a different namespace is not recommended for production. The default Helm chart does not provide the necessary ClusterRole permissions for cross-namespace access. To enable this, you must manually create a ClusterRole with secrets: get permissions across all namespaces. It is best practice to store secrets in the same namespace as the SQL Exporter pod.

config:
  target:
    data_source_name: 'k8ssecret://monitoring/db-secret'
    collectors:
      - collector1

The Helm chart supports securing the metrics endpoint using TLS encryption and Basic Authentication via the webConfig object. The configuration file web-config.yml is automatically placed at /etc/sql_exporter/web-config.yml inside the container.

Key security features:

• TLS Encryption: Enable HTTPS by configuring webConfig.tls.secretName (supports TLS 1.3).
• Basic Authentication: Protect metrics using webConfig.basicAuth.enabled. You can provide bcrypt-hashed passwords directly in webConfig.basicAuth.users, or use an init container to automatically hash plaintext passwords from a Kubernetes secret using webConfig.basicAuth.initFromSecret.enabled.

When global.enable_query_metrics is set to true, the exporter emits two additional gauges for every configured query. These metrics inherit the same constant labels as up and scrape_duration_seconds (including target in multi-target mode).

• query_duration_seconds{query="<query_name>"}: Wall-clock time the query took during the most recent scrape, including row scanning. This is emitted even if the query errors.
• query_rows_returned{query="<query_name>"}: The number of rows returned by the database during the most recent scrape. Errored or skipped rows are not counted.

When using the Helm chart, you can embed your collector YAML definitions directly into the collectorFiles field of your configuration. This allows you to manage multiple collector files without mounting them as separate volumes.

collectorFiles:
  mssql_standard.collector.yml: |
    # Contents of mssql_standard.collector.yml

config:
  target:
    data_source_name: "sqlserver://username:password@hostname:1433?database=master"
  collector_files:
    - "*.collector.yml"

The monitoring user requires specific permissions to access statistics and connection views.

For PostgreSQL 10+ (Recommended): Grant the pg_monitor role to the user.

For PostgreSQL 9.6 and earlier: You must grant explicit SELECT access to specific system views (pg_stat_database, pg_stat_activity, and pg_stat_replication).

-- Create monitoring user
CREATE USER sql_exporter WITH PASSWORD 'secure_password';

-- Grant connection
GRANT CONNECT ON DATABASE postgres TO sql_exporter;

-- Grant read access to statistics views
GRANT pg_monitor TO sql_exporter;  -- PostgreSQL 10+

-- For PostgreSQL 9.6 and earlier:
GRANT SELECT ON pg_stat_database TO sql_exporter;
GRANT SELECT ON pg_stat_activity TO sql_exporter;
GRANT SELECT ON pg_stat_replication TO sql_exporter;