DOCSEARCHbyAlgolia
Submit Docs

sqlstats

repository·main·Indexed 41 days ago

https://github.com/dlmiddlecote/sqlstats

A Go library that collects statistics from a sql.DB connection pool and exports them as Prometheus metrics. It supports any object implementing the StatsGetter interface, including standard *sql.DB instances and *sqlx.DB wrappers, to monitor connection pool health and configuration.

Tokens
1.4K
Snippets
4
Records
4
Agent score
2%

What's inside sqlstats

  2. Export sql.DBStats to Prometheus

    main

    You can collect statistics from a *sql.DB object and export them to Prometheus by following these steps:

    1. Create a new collector using sqlstats.NewStatsCollector(name, db). The name provided will be used as the db_name label on all exported metrics.
    2. Register the collector with the Prometheus registry using prometheus.MustRegister(collector).
    3. Expose the /metrics endpoint using promhttp.Handler() to allow Prometheus to scrape the data.

    This allows you to monitor connection pool settings like SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime through Prometheus.

    package main

import (
	"database/sql"
	"net/http"

	_ "github.com/lib/pq"
	"github.com/dlmiddlecote/sqlstats"
	"github.com/prometheus/client_golang/prometheus"
	"github.com/prometheus/client_golang/prometheus/promhttp"
)

func main() {
	if err := run(); err != nil {
		panic(err)
	}
}

func run() error {
	// Open connection to a DB
	db, err := sql.Open("postgres", "postgres://postgres:postgres@localhost:5432/postgres")
	if err != nil {
		return err
	}

	// Create a new collector, the name will be used as a label on the metrics
	collector := sqlstats.NewStatsCollector("db_name", db)

	// Register it with Prometheus
	prometheus.MustRegister(collector)

	// Register the metrics handler
	http.Handle("/metrics", promhttp.Handler())

	// Run the web server
	return http.ListenAndServe(":8080", nil)
}

  3. Reference: Exposed sqlstats Prometheus metrics

    main

    The following metrics are exported by sqlstats. All metrics include the db_name label.

    | Name | Description | Labels | Go Version |
|-----------------------------------------------|-------------------------------------------------------------------|-----------------|---------|
| go_sql_stats_connections_max_open | Maximum number of open connections to the database. | db_name | 1.11+ |
| go_sql_stats_connections_open | The number of established connections both in use and idle. | db_name | 1.11+ |
| go_sql_stats_connections_in_use | The number of connections currently in use. | db_name | 1.11+ |
| go_sql_stats_connections_idle | The number of idle connections. | db_name | 1.11+ |
| go_sql_stats_connections_waited_for | The total number of connections waited for. | db_name | 1.11+ |
| go_sql_stats_connections_blocked_seconds | The total time blocked waiting for a new connection. | db_name | 1.11+ |
| go_sql_stats_connections_closed_max_idle | The total number of connections closed due to SetMaxIdleConns. | db_name | 1.11+ |
| go_sql_stats_connections_closed_max_lifetime | The total number of connections closed due to SetConnMaxLifetime. | db_name | 1.11+ |
| go_sql_stats_connections_closed_max_idle_time | The total number of connections closed due to SetConnMaxIdleTime. | db_name | 1.15+ |

  4. Use the StatsGetter interface to collect SQL metrics

    main

    The StatsGetter interface allows the sqlstats collector to retrieve database statistics from any object that implements a Stats() method returning sql.DBStats. This interface is compatible with standard *sql.DB instances and common wrappers like *sqlx.DB.

    // Any type implementing this interface can be used with sqlstats
type StatsGetter interface {
	Stats() sql.DBStats
}