database_exporter

README

Prometheus Database Exporter

Database agnostic SQL exporter for Prometheus.

Overview

Database 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 MySQL, PostgreSQL, Oracle DB, Microsoft SQL Server and Clickhouse, but 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 Database Exporter packaged release or build it yourself:

go install github.com/Corundex/database_exporter/cmd/database_exporter

then run it from the command line:

./database_exporter

Use the -help flag to get help information.

./database_exporter -help

Usage of ./database_exporter:

  -config.file string
      Database Exporter configuration file name. (default "database_exporter.yml", you can use sample oracle_exporter.yml, postgres_exporter.yml, mssql_exporter.yml or mysql_exporter.yml)
  -web.listen-address string
      Address to listen on for web interface and telemetry. (default ":9285")
  -web.metrics-path string
      Path under which to expose metrics. (default "/metrics")
  [...]

Configuration

Database 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. Database Exporter process metrics are exported at /database_exporter_metrics.

The configuration examples listed here only cover some basic element. documentation/database_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.

./database_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

# The target to monitor and the list of collectors to execute on it.
target:
  # 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.
  collectors: [pricing_data_freshness]

# Collector definition files.
collector_files:
  - "*.collector.yml"

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
    values: [LastUpdateTime]
    query: |
      SELECT Market, max(UpdateTime) AS LastUpdateTime
      FROM MarketPrices
      GROUP BY Market

Data Source Names

To keep things simple and yet allow fully configurable database connections to be set up, SQL Exporter uses DSNs (like sqlserver://prom_user:prom_password@dbserver1.example.com:1433) to refer to database instances. However, because the Go sql library does not allow for automatic driver selection based on the DSN (i.e. an explicit driver name must be specified) SQL Exporter uses the schema part of the DSN (the part before the ://) to determine which driver to use.

While this works out of the box with the MS SQL Server and PostgreSQL drivers, Oracle OCI8 and MySQL driver DSNs format does not include a schema and the Clickhouse one uses tcp://. So SQL Exporter does a bit of massaging of DSNs for the latter two drivers in order for this to work:

DB	SQL Exporter expected DSN	Driver sees
MySQL	mysql://user:passw@protocol(host:port)/dbname	user:passw@protocol(host:port)/dbname
Oracle	oracle://user:password@host:port/sid	user:password@host:port/sid
PostgreSQL	postgres://user:passw@host:port/dbname	unchanged
SQL Server	sqlserver://user:passw@host:port/instance	unchanged
SQLite3	sqlite3://file:mybase.db?cache=shared&mode=rwc	file:mybase.db?cache=shared&mode=rwc
in-memory SQLite3	sqlite3://file::memory:?mode=memory&cache=shared	file::memory:?mode=memory&cache=shared
Clickhouse	clickhouse://host:port?username=user&password=passw&database=dbname	tcp://host:port?username=user&password=passw&database=dbname

Why It Exists

Database exporter started from SQL Exporter which started off as an exporter for Microsoft SQL Server, for which no reliable exporters exist. But what is the point of a configuration driven SQL exporter, if you're going to use it along with 2 more exporters with wholly different world views and configurations, because you also have MySQL, Oracle and PostgreSQL instances to monitor?

A couple of alternative database agnostic exporters are available: database_exporter prometheus-sql but they both do the collection at fixed intervals, independent of Prometheus scrapes. This is partly a philosophical issue, but practical issues are not all that difficult to imagine: jitter; duplicate data points; or collected but not scraped data points. The control they provide over which labels get applied is limited, and the base label set spammy. And finally, configurations are not easily reused without copy-pasting and editing across jobs and instances.

Documentation

Index

• func OpenConnection(ctx context.Context, logContext, dsn string, maxConns, maxIdleConns int) (*sql.DB, error)
• func PingDB(ctx context.Context, conn *sql.DB) error
• type Collector
  • func NewCollector(logContext string, cc *config.CollectorConfig, constLabels []*dto.LabelPair) (Collector, errors.WithContext)
• type Exporter
  • func NewExporter(configFile string) (Exporter, error)
• type Job
  • func NewJob(jc *config.JobConfig, gc *config.GlobalConfig) (Job, errors.WithContext)
• type Metric
  • func NewInvalidMetric(err errors.WithContext) Metric
  • func NewMetric(desc MetricDesc, value float64, labelValues ...string) Metric
• type MetricDesc
  • func NewAutomaticMetricDesc(logContext, name, help string, valueType prometheus.ValueType, ...) MetricDesc
• type MetricFamily
  • func NewMetricFamily(logContext string, mc *config.MetricConfig, constLabels []*dto.LabelPair) (*MetricFamily, errors.WithContext)
  • func (mf MetricFamily) Collect(row map[string]interface{}, ch chan<- Metric)
  • func (mf MetricFamily) ConstLabels() []*dto.LabelPair
  • func (mf MetricFamily) Help() string
  • func (mf MetricFamily) Labels() []string
  • func (mf MetricFamily) LogContext() string
  • func (mf MetricFamily) Name() string
  • func (mf MetricFamily) ValueType() prometheus.ValueType
• type Query
  • func NewQuery(logContext string, qc *config.QueryConfig, metricFamilies ...*MetricFamily) (*Query, errors.WithContext)
  • func (q *Query) Collect(ctx context.Context, conn *sql.DB, ch chan<- Metric)
• type Target
  • func NewTarget(logContext, name, dsn string, ccs []*config.CollectorConfig, ...) (Target, errors.WithContext)

Functions

func OpenConnection

func OpenConnection(ctx context.Context, logContext, dsn string, maxConns, maxIdleConns int) (*sql.DB, error)

OpenConnection extracts the driver name from the DSN (expected as the URI scheme), adjusts it where necessary (e.g. some driver supported DSN formats don't include a scheme), opens a DB handle ensuring early termination if the context is closed (this is actually prevented by `database/sql` implementation), sets connection limits and returns the handle.

Below is the list of supported databases (with built in drivers) and their DSN formats. Unfortunately there is no dynamic way of loading a third party driver library (as e.g. with Java classpaths), so any driver additions require a binary rebuild.

MySQL

Using the https://github.com/go-sql-driver/mysql driver, DSN format (passed to the driver stripped of the `mysql://` prefix):

mysql://username:password@protocol(host:port)/dbname?param=value

PostgreSQL

Using the https://godoc.org/github.com/lib/pq driver, DSN format (passed through to the driver unchanged):

postgres://username:password@host:port/dbname?param=value

MS SQL Server

Using the https://github.com/denisenkom/go-mssqldb driver, DSN format (passed through to the driver unchanged):

sqlserver://username:password@host:port/instance?param=value

Clickhouse

Using the https://github.com/kshvakov/clickhouse driver, DSN format (passed to the driver with the`clickhouse://` prefix replaced with `tcp://`):

clickhouse://host:port?username=username&password=password&database=dbname&param=value

Oracle

Using the https://github.com/mattn/go-oci8 driver, DSN format (passed to the driver with the `oci8://` or `oracle://“ prefix):

oci8://user:password@host:port/sid?param1=value1&param2=value2
oracle://user:password@host:port/sid?param1=value1&param2=value2

Currently the parameters supported is: 1 'loc' which sets the timezone to read times in as and to marshal to when writing times to Oracle date, 2 'isolation' =READONLY,SERIALIZABLE,DEFAULT 3 'prefetch_rows' 4 'prefetch_memory' 5 'questionph' =YES,NO,TRUE,FALSE enable question-mark placeholders, default to false

don't forget to install Oracle instant client and set variables pointing to the installed libs: export LD_LIBRARY_PATH=..../instantclient_12_2 export PKG_CONFIG_PATH=..../instantclient_12_2

SQLite3

Using the github.com/mattn/go-sqlite3 driver, DSN format (passed to the driver with the `sqlite3://“ prefix):

sqlite3://file:base.sqlite?param1=value1&param2=value2

f.e sqlite3://file:mybase.db?cache=shared&mode=rwc

func PingDB

func PingDB(ctx context.Context, conn *sql.DB) error

PingDB is a wrapper around sql.DB.PingContext() that terminates as soon as the context is closed.

sql.DB does not actually pass along the context to the driver when opening a connection (which always happens if the database is down) and the driver uses an arbitrary timeout which may well be longer than ours. So we run the ping call in a goroutine and terminate immediately if the context is closed.

Types

type Collector

type Collector interface {
	// Collect is the equivalent of prometheus.Collector.Collect() but takes a context to run in and a database to run on.
	Collect(context.Context, *sql.DB, chan<- Metric)
}

Collector is a self-contained group of SQL queries and metric families to collect from a specific database. It is conceptually similar to a prometheus.Collector.

func NewCollector

func NewCollector(logContext string, cc *config.CollectorConfig, constLabels []*dto.LabelPair) (Collector, errors.WithContext)

NewCollector returns a new Collector with the given configuration and database. The metrics it creates will all have the provided const labels applied.

type Exporter

type Exporter interface {
	prometheus.Gatherer

	// WithContext returns a (single use) copy of the Exporter, which will use the provided context for Gather() calls.
	WithContext(context.Context) Exporter
	// Config returns the Exporter's underlying Config object.
	Config() *config.Config
}

Exporter is a prometheus.Gatherer that gathers SQL metrics from targets and merges them with the default registry.

func NewExporter

func NewExporter(configFile string) (Exporter, error)

NewExporter returns a new Exporter with the provided config.

type Job

type Job interface {
	Targets() []Target
}

Job is a collection of targets with the same collectors applied.

func NewJob

func NewJob(jc *config.JobConfig, gc *config.GlobalConfig) (Job, errors.WithContext)

NewJob returns a new Job with the given configuration.

type Metric

type Metric interface {
	Desc() MetricDesc
	Write(out *dto.Metric) errors.WithContext
}

A Metric models a single sample value with its meta data being exported to Prometheus.

func NewInvalidMetric

func NewInvalidMetric(err errors.WithContext) Metric

NewInvalidMetric returns a metric whose Write method always returns the provided error.

func NewMetric

func NewMetric(desc MetricDesc, value float64, labelValues ...string) Metric

NewMetric returns a metric with one fixed value that cannot be changed.

NewMetric panics if the length of labelValues is not consistent with desc.labels().

type MetricDesc

type MetricDesc interface {
	Name() string
	Help() string
	ValueType() prometheus.ValueType
	ConstLabels() []*dto.LabelPair
	Labels() []string
	LogContext() string
}

MetricDesc is a descriptor for a family of metrics, sharing the same name, help, labes, type.

func NewAutomaticMetricDesc

func NewAutomaticMetricDesc(
	logContext, name, help string, valueType prometheus.ValueType, constLabels []*dto.LabelPair, labels ...string) MetricDesc

NewAutomaticMetricDesc creates a MetricDesc for automatically generated metrics.

type MetricFamily

type MetricFamily struct {
	// contains filtered or unexported fields
}

MetricFamily implements MetricDesc for SQL metrics, with logic for populating its labels and values from sql.Rows.

func NewMetricFamily

func NewMetricFamily(logContext string, mc *config.MetricConfig, constLabels []*dto.LabelPair) (*MetricFamily, errors.WithContext)

NewMetricFamily creates a new MetricFamily with the given metric config and const labels (e.g. job and instance).

func (MetricFamily) Collect

func (mf MetricFamily) Collect(row map[string]interface{}, ch chan<- Metric)

Collect is the equivalent of prometheus.Collector.Collect() but takes a Query output map to populate values from.

func (MetricFamily) ConstLabels

func (mf MetricFamily) ConstLabels() []*dto.LabelPair

ConstLabels implements MetricDesc.

func (MetricFamily) Help

func (mf MetricFamily) Help() string

Help implements MetricDesc.

func (MetricFamily) Labels

func (mf MetricFamily) Labels() []string

Labels implements MetricDesc.

func (MetricFamily) LogContext

func (mf MetricFamily) LogContext() string

LogContext implements MetricDesc.

func (MetricFamily) Name

func (mf MetricFamily) Name() string

Name implements MetricDesc.

func (MetricFamily) ValueType

func (mf MetricFamily) ValueType() prometheus.ValueType

ValueType implements MetricDesc.

type Query

type Query struct {
	// contains filtered or unexported fields
}

Query wraps a sql.Stmt and all the metrics populated from it. It helps extract keys and values from result rows.

func NewQuery

func NewQuery(logContext string, qc *config.QueryConfig, metricFamilies ...*MetricFamily) (*Query, errors.WithContext)

NewQuery returns a new Query that will populate the given metric families.

func (*Query) Collect

func (q *Query) Collect(ctx context.Context, conn *sql.DB, ch chan<- Metric)

Collect is the equivalent of prometheus.Collector.Collect() but takes a context to run in and a database to run on.

type Target

type Target interface {
	// Collect is the equivalent of prometheus.Collector.Collect(), but takes a context to run in.
	Collect(ctx context.Context, ch chan<- Metric)
}

Target collects SQL metrics from a single sql.DB instance. It aggregates one or more Collectors and it looks much like a prometheus.Collector, except its Collect() method takes a Context to run in.

func NewTarget

func NewTarget(
	logContext, name, dsn string, ccs []*config.CollectorConfig, constLabels prometheus.Labels, gc *config.GlobalConfig) (
	Target, errors.WithContext)

NewTarget returns a new Target with the given instance name, data source name, collectors and constant labels. An empty target name means the exporter is runnning in single target mode: no synthetic metrics will be exported.