diff options
Diffstat (limited to '')
18 files changed, 1798 insertions, 0 deletions
diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/README.md b/src/go/collectors/go.d.plugin/modules/pgbouncer/README.md new file mode 120000 index 000000000..3bfcaba0b --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/README.md @@ -0,0 +1 @@ +integrations/pgbouncer.md
\ No newline at end of file diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/charts.go b/src/go/collectors/go.d.plugin/modules/pgbouncer/charts.go new file mode 100644 index 000000000..bd94f0fd5 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/charts.go @@ -0,0 +1,247 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +package pgbouncer + +import ( + "fmt" + "strings" + + "github.com/netdata/netdata/go/go.d.plugin/agent/module" +) + +const ( + prioClientConnectionsUtilization = module.Priority + iota + prioDBClientConnections + prioDBServerConnections + prioDBServerConnectionsUtilization + prioDBClientsWaitTime + prioDBClientsWaitMaxTime + prioDBTransactions + prioDBTransactionsTime + prioDBTransactionsAvgTime + prioDBQueries + prioDBQueriesTime + prioDBQueryAvgTime + prioDBNetworkIO +) + +var ( + globalCharts = module.Charts{ + clientConnectionsUtilization.Copy(), + } + + clientConnectionsUtilization = module.Chart{ + ID: "client_connections_utilization", + Title: "Client connections utilization", + Units: "percentage", + Fam: "client connections", + Ctx: "pgbouncer.client_connections_utilization", + Priority: prioClientConnectionsUtilization, + Dims: module.Dims{ + {ID: "cl_conns_utilization", Name: "used"}, + }, + } +) + +var ( + dbChartsTmpl = module.Charts{ + dbClientConnectionsTmpl.Copy(), + + dbServerConnectionsUtilizationTmpl.Copy(), + dbServerConnectionsTmpl.Copy(), + + dbClientsWaitTimeChartTmpl.Copy(), + dbClientMaxWaitTimeChartTmpl.Copy(), + + dbTransactionsChartTmpl.Copy(), + dbTransactionsTimeChartTmpl.Copy(), + dbTransactionAvgTimeChartTmpl.Copy(), + + dbQueriesChartTmpl.Copy(), + dbQueriesTimeChartTmpl.Copy(), + dbQueryAvgTimeChartTmpl.Copy(), + + dbNetworkIOChartTmpl.Copy(), + } + + dbClientConnectionsTmpl = module.Chart{ + ID: "db_%s_client_connections", + Title: "Database client connections", + Units: "connections", + Fam: "client connections", + Ctx: "pgbouncer.db_client_connections", + Priority: prioDBClientConnections, + Type: module.Stacked, + Dims: module.Dims{ + {ID: "db_%s_cl_active", Name: "active"}, + {ID: "db_%s_cl_waiting", Name: "waiting"}, + {ID: "db_%s_cl_cancel_req", Name: "cancel_req"}, + }, + } + + dbServerConnectionsTmpl = module.Chart{ + ID: "db_%s_server_connections", + Title: "Database server connections", + Units: "connections", + Fam: "server connections", + Ctx: "pgbouncer.db_server_connections", + Priority: prioDBServerConnections, + Type: module.Stacked, + Dims: module.Dims{ + {ID: "db_%s_sv_active", Name: "active"}, + {ID: "db_%s_sv_idle", Name: "idle"}, + {ID: "db_%s_sv_used", Name: "used"}, + {ID: "db_%s_sv_tested", Name: "tested"}, + {ID: "db_%s_sv_login", Name: "login"}, + }, + } + + dbServerConnectionsUtilizationTmpl = module.Chart{ + ID: "db_%s_server_connections_utilization", + Title: "Database server connections utilization", + Units: "percentage", + Fam: "server connections limit", + Ctx: "pgbouncer.db_server_connections_utilization", + Priority: prioDBServerConnectionsUtilization, + Dims: module.Dims{ + {ID: "db_%s_sv_conns_utilization", Name: "used"}, + }, + } + + dbClientsWaitTimeChartTmpl = module.Chart{ + ID: "db_%s_clients_wait_time", + Title: "Database clients wait time", + Units: "seconds", + Fam: "clients wait time", + Ctx: "pgbouncer.db_clients_wait_time", + Priority: prioDBClientsWaitTime, + Dims: module.Dims{ + {ID: "db_%s_total_wait_time", Name: "time", Algo: module.Incremental, Div: 1e6}, + }, + } + dbClientMaxWaitTimeChartTmpl = module.Chart{ + ID: "db_%s_client_max_wait_time", + Title: "Database client max wait time", + Units: "seconds", + Fam: "client max wait time", + Ctx: "pgbouncer.db_client_max_wait_time", + Priority: prioDBClientsWaitMaxTime, + Dims: module.Dims{ + {ID: "db_%s_maxwait", Name: "time", Div: 1e6}, + }, + } + + dbTransactionsChartTmpl = module.Chart{ + ID: "db_%s_transactions", + Title: "Database pooled SQL transactions", + Units: "transactions/s", + Fam: "transactions", + Ctx: "pgbouncer.db_transactions", + Priority: prioDBTransactions, + Dims: module.Dims{ + {ID: "db_%s_total_xact_count", Name: "transactions", Algo: module.Incremental}, + }, + } + dbTransactionsTimeChartTmpl = module.Chart{ + ID: "db_%s_transactions_time", + Title: "Database transactions time", + Units: "seconds", + Fam: "transactions time", + Ctx: "pgbouncer.db_transactions_time", + Priority: prioDBTransactionsTime, + Dims: module.Dims{ + {ID: "db_%s_total_xact_time", Name: "time", Algo: module.Incremental, Div: 1e6}, + }, + } + dbTransactionAvgTimeChartTmpl = module.Chart{ + ID: "db_%s_transactions_average_time", + Title: "Database transaction average time", + Units: "seconds", + Fam: "transaction avg time", + Ctx: "pgbouncer.db_transaction_avg_time", + Priority: prioDBTransactionsAvgTime, + Dims: module.Dims{ + {ID: "db_%s_avg_xact_time", Name: "time", Algo: module.Incremental, Div: 1e6}, + }, + } + + dbQueriesChartTmpl = module.Chart{ + ID: "db_%s_queries", + Title: "Database pooled SQL queries", + Units: "queries/s", + Fam: "queries", + Ctx: "pgbouncer.db_queries", + Priority: prioDBQueries, + Dims: module.Dims{ + {ID: "db_%s_total_query_count", Name: "queries", Algo: module.Incremental}, + }, + } + dbQueriesTimeChartTmpl = module.Chart{ + ID: "db_%s_queries_time", + Title: "Database queries time", + Units: "seconds", + Fam: "queries time", + Ctx: "pgbouncer.db_queries_time", + Priority: prioDBQueriesTime, + Dims: module.Dims{ + {ID: "db_%s_total_query_time", Name: "time", Algo: module.Incremental, Div: 1e6}, + }, + } + dbQueryAvgTimeChartTmpl = module.Chart{ + ID: "db_%s_query_average_time", + Title: "Database query average time", + Units: "seconds", + Fam: "query avg time", + Ctx: "pgbouncer.db_query_avg_time", + Priority: prioDBQueryAvgTime, + Dims: module.Dims{ + {ID: "db_%s_avg_query_time", Name: "time", Algo: module.Incremental, Div: 1e6}, + }, + } + + dbNetworkIOChartTmpl = module.Chart{ + ID: "db_%s_network_io", + Title: "Database traffic", + Units: "B/s", + Fam: "traffic", + Ctx: "pgbouncer.db_network_io", + Priority: prioDBNetworkIO, + Type: module.Area, + Dims: module.Dims{ + {ID: "db_%s_total_received", Name: "received", Algo: module.Incremental}, + {ID: "db_%s_total_sent", Name: "sent", Algo: module.Incremental, Mul: -1}, + }, + } +) + +func newDatabaseCharts(dbname, pgDBName string) *module.Charts { + charts := dbChartsTmpl.Copy() + for _, c := range *charts { + c.ID = fmt.Sprintf(c.ID, dbname) + c.Labels = []module.Label{ + {Key: "database", Value: dbname}, + {Key: "postgres_database", Value: pgDBName}, + } + for _, d := range c.Dims { + d.ID = fmt.Sprintf(d.ID, dbname) + } + } + return charts +} + +func (p *PgBouncer) addNewDatabaseCharts(dbname, pgDBName string) { + charts := newDatabaseCharts(dbname, pgDBName) + if err := p.Charts().Add(*charts...); err != nil { + p.Warning(err) + } +} + +func (p *PgBouncer) removeDatabaseCharts(dbname string) { + prefix := fmt.Sprintf("db_%s_", dbname) + for _, c := range *p.Charts() { + if strings.HasPrefix(c.ID, prefix) { + c.MarkRemove() + c.MarkNotCreated() + } + } +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/collect.go b/src/go/collectors/go.d.plugin/modules/pgbouncer/collect.go new file mode 100644 index 000000000..c0e4bf2da --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/collect.go @@ -0,0 +1,354 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +package pgbouncer + +import ( + "context" + "database/sql" + "fmt" + "regexp" + "strconv" + "strings" + "time" + + "github.com/blang/semver/v4" + "github.com/jackc/pgx/v4" + "github.com/jackc/pgx/v4/stdlib" +) + +// 'SHOW STATS;' response was changed significantly in v1.8.0 +// v1.8.0 was released in 2015 - no need to complicate the code to support the old version. +var minSupportedVersion = semver.Version{Major: 1, Minor: 8, Patch: 0} + +const ( + queryShowVersion = "SHOW VERSION;" + queryShowConfig = "SHOW CONFIG;" + queryShowDatabases = "SHOW DATABASES;" + queryShowStats = "SHOW STATS;" + queryShowPools = "SHOW POOLS;" +) + +func (p *PgBouncer) collect() (map[string]int64, error) { + if p.db == nil { + if err := p.openConnection(); err != nil { + return nil, err + } + } + if p.version == nil { + ver, err := p.queryVersion() + if err != nil { + return nil, err + } + p.Debugf("connected to PgBouncer v%s", ver) + if ver.LE(minSupportedVersion) { + return nil, fmt.Errorf("unsupported version: v%s, required v%s+", ver, minSupportedVersion) + } + p.version = ver + } + + now := time.Now() + if now.Sub(p.recheckSettingsTime) > p.recheckSettingsEvery { + v, err := p.queryMaxClientConn() + if err != nil { + return nil, err + } + p.maxClientConn = v + } + + // http://www.pgbouncer.org/usage.html + + p.resetMetrics() + + if err := p.collectDatabases(); err != nil { + return nil, err + } + if err := p.collectStats(); err != nil { + return nil, err + } + if err := p.collectPools(); err != nil { + return nil, err + } + + mx := make(map[string]int64) + p.collectMetrics(mx) + + return mx, nil +} + +func (p *PgBouncer) collectMetrics(mx map[string]int64) { + var clientConns int64 + for name, db := range p.metrics.dbs { + if !db.updated { + delete(p.metrics.dbs, name) + p.removeDatabaseCharts(name) + continue + } + if !db.hasCharts { + db.hasCharts = true + p.addNewDatabaseCharts(name, db.pgDBName) + } + + mx["db_"+name+"_total_xact_count"] = db.totalXactCount + mx["db_"+name+"_total_xact_time"] = db.totalXactTime + mx["db_"+name+"_avg_xact_time"] = db.avgXactTime + + mx["db_"+name+"_total_query_count"] = db.totalQueryCount + mx["db_"+name+"_total_query_time"] = db.totalQueryTime + mx["db_"+name+"_avg_query_time"] = db.avgQueryTime + + mx["db_"+name+"_total_wait_time"] = db.totalWaitTime + mx["db_"+name+"_maxwait"] = db.maxWait*1e6 + db.maxWaitUS + + mx["db_"+name+"_cl_active"] = db.clActive + mx["db_"+name+"_cl_waiting"] = db.clWaiting + mx["db_"+name+"_cl_cancel_req"] = db.clCancelReq + clientConns += db.clActive + db.clWaiting + db.clCancelReq + + mx["db_"+name+"_sv_active"] = db.svActive + mx["db_"+name+"_sv_idle"] = db.svIdle + mx["db_"+name+"_sv_used"] = db.svUsed + mx["db_"+name+"_sv_tested"] = db.svTested + mx["db_"+name+"_sv_login"] = db.svLogin + + mx["db_"+name+"_total_received"] = db.totalReceived + mx["db_"+name+"_total_sent"] = db.totalSent + + mx["db_"+name+"_sv_conns_utilization"] = calcPercentage(db.currentConnections, db.maxConnections) + } + + mx["cl_conns_utilization"] = calcPercentage(clientConns, p.maxClientConn) +} + +func (p *PgBouncer) collectDatabases() error { + q := queryShowDatabases + p.Debugf("executing query: %v", q) + + var db string + return p.collectQuery(q, func(column, value string) { + switch column { + case "name": + db = value + p.getDBMetrics(db).updated = true + case "database": + p.getDBMetrics(db).pgDBName = value + case "max_connections": + p.getDBMetrics(db).maxConnections = parseInt(value) + case "current_connections": + p.getDBMetrics(db).currentConnections = parseInt(value) + case "paused": + p.getDBMetrics(db).paused = parseInt(value) + case "disabled": + p.getDBMetrics(db).disabled = parseInt(value) + } + }) +} + +func (p *PgBouncer) collectStats() error { + q := queryShowStats + p.Debugf("executing query: %v", q) + + var db string + return p.collectQuery(q, func(column, value string) { + switch column { + case "database": + db = value + p.getDBMetrics(db).updated = true + case "total_xact_count": + p.getDBMetrics(db).totalXactCount = parseInt(value) + case "total_query_count": + p.getDBMetrics(db).totalQueryCount = parseInt(value) + case "total_received": + p.getDBMetrics(db).totalReceived = parseInt(value) + case "total_sent": + p.getDBMetrics(db).totalSent = parseInt(value) + case "total_xact_time": + p.getDBMetrics(db).totalXactTime = parseInt(value) + case "total_query_time": + p.getDBMetrics(db).totalQueryTime = parseInt(value) + case "total_wait_time": + p.getDBMetrics(db).totalWaitTime = parseInt(value) + case "avg_xact_time": + p.getDBMetrics(db).avgXactTime = parseInt(value) + case "avg_query_time": + p.getDBMetrics(db).avgQueryTime = parseInt(value) + } + }) +} + +func (p *PgBouncer) collectPools() error { + q := queryShowPools + p.Debugf("executing query: %v", q) + + // an entry is made for each couple of (database, user). + var db string + return p.collectQuery(q, func(column, value string) { + switch column { + case "database": + db = value + p.getDBMetrics(db).updated = true + case "cl_active": + p.getDBMetrics(db).clActive += parseInt(value) + case "cl_waiting": + p.getDBMetrics(db).clWaiting += parseInt(value) + case "cl_cancel_req": + p.getDBMetrics(db).clCancelReq += parseInt(value) + case "sv_active": + p.getDBMetrics(db).svActive += parseInt(value) + case "sv_idle": + p.getDBMetrics(db).svIdle += parseInt(value) + case "sv_used": + p.getDBMetrics(db).svUsed += parseInt(value) + case "sv_tested": + p.getDBMetrics(db).svTested += parseInt(value) + case "sv_login": + p.getDBMetrics(db).svLogin += parseInt(value) + case "maxwait": + p.getDBMetrics(db).maxWait += parseInt(value) + case "maxwait_us": + p.getDBMetrics(db).maxWaitUS += parseInt(value) + } + }) +} + +func (p *PgBouncer) queryMaxClientConn() (int64, error) { + q := queryShowConfig + p.Debugf("executing query: %v", q) + + var v int64 + var key string + err := p.collectQuery(q, func(column, value string) { + switch column { + case "key": + key = value + case "value": + if key == "max_client_conn" { + v = parseInt(value) + } + } + }) + return v, err +} + +var reVersion = regexp.MustCompile(`\d+\.\d+\.\d+`) + +func (p *PgBouncer) queryVersion() (*semver.Version, error) { + q := queryShowVersion + p.Debugf("executing query: %v", q) + + var resp string + ctx, cancel := context.WithTimeout(context.Background(), p.Timeout.Duration()) + defer cancel() + if err := p.db.QueryRowContext(ctx, q).Scan(&resp); err != nil { + return nil, err + } + + if !strings.Contains(resp, "PgBouncer") { + return nil, fmt.Errorf("not PgBouncer instance: version response: %s", resp) + } + + ver := reVersion.FindString(resp) + if ver == "" { + return nil, fmt.Errorf("couldn't parse version string '%s' (expected pattern '%s')", resp, reVersion) + } + + v, err := semver.New(ver) + if err != nil { + return nil, fmt.Errorf("couldn't parse version string '%s': %v", ver, err) + } + + return v, nil +} + +func (p *PgBouncer) openConnection() error { + cfg, err := pgx.ParseConfig(p.DSN) + if err != nil { + return err + } + cfg.PreferSimpleProtocol = true + + db, err := sql.Open("pgx", stdlib.RegisterConnConfig(cfg)) + if err != nil { + return fmt.Errorf("error on opening a connection with the PgBouncer database [%s]: %v", p.DSN, err) + } + + db.SetMaxOpenConns(1) + db.SetMaxIdleConns(1) + db.SetConnMaxLifetime(10 * time.Minute) + + p.db = db + + return nil +} + +func (p *PgBouncer) collectQuery(query string, assign func(column, value string)) error { + ctx, cancel := context.WithTimeout(context.Background(), p.Timeout.Duration()) + defer cancel() + rows, err := p.db.QueryContext(ctx, query) + if err != nil { + return err + } + defer func() { _ = rows.Close() }() + + columns, err := rows.Columns() + if err != nil { + return err + } + + values := makeNullStrings(len(columns)) + for rows.Next() { + if err := rows.Scan(values...); err != nil { + return err + } + for i, v := range values { + assign(columns[i], valueToString(v)) + } + } + return rows.Err() +} + +func (p *PgBouncer) getDBMetrics(dbname string) *dbMetrics { + db, ok := p.metrics.dbs[dbname] + if !ok { + db = &dbMetrics{name: dbname} + p.metrics.dbs[dbname] = db + } + return db +} + +func (p *PgBouncer) resetMetrics() { + for name, db := range p.metrics.dbs { + p.metrics.dbs[name] = &dbMetrics{ + name: db.name, + pgDBName: db.pgDBName, + hasCharts: db.hasCharts, + } + } +} + +func valueToString(value any) string { + v, ok := value.(*sql.NullString) + if !ok || !v.Valid { + return "" + } + return v.String +} + +func makeNullStrings(size int) []any { + vs := make([]any, size) + for i := range vs { + vs[i] = &sql.NullString{} + } + return vs +} + +func parseInt(s string) int64 { + v, _ := strconv.ParseInt(s, 10, 64) + return v +} + +func calcPercentage(value, total int64) int64 { + if total == 0 { + return 0 + } + return value * 100 / total +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/config_schema.json b/src/go/collectors/go.d.plugin/modules/pgbouncer/config_schema.json new file mode 100644 index 000000000..d8d08bc51 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/config_schema.json @@ -0,0 +1,47 @@ +{ + "jsonSchema": { + "$schema": "http://json-schema.org/draft-07/schema#", + "title": "PgBouncer collector configuration.", + "type": "object", + "properties": { + "update_every": { + "title": "Update every", + "description": "Data collection interval, measured in seconds.", + "type": "integer", + "minimum": 1, + "default": 1 + }, + "dsn": { + "title": "DSN", + "description": "PgBouncer server Data Source Name in [key/value string](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-KEYWORD-VALUE) or [URI](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS) format.", + "type": "string", + "default": "postgres://netdata:password@127.0.0.1:6432/pgbouncer" + }, + "timeout": { + "title": "Timeout", + "description": "Timeout for queries, in seconds.", + "type": "number", + "minimum": 0.5, + "default": 1 + } + }, + "required": [ + "dsn" + ], + "additionalProperties": false, + "patternProperties": { + "^name$": {} + } + }, + "uiSchema": { + "uiOptions": { + "fullPage": true + }, + "dsn": { + "ui:placeholder": "postgres://username:password@host:port/dbname" + }, + "timeout": { + "ui:help": "Accepts decimals for precise control (e.g., type 1.5 for 1.5 seconds)." + } + } +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/init.go b/src/go/collectors/go.d.plugin/modules/pgbouncer/init.go new file mode 100644 index 000000000..146335085 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/init.go @@ -0,0 +1,12 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +package pgbouncer + +import "errors" + +func (p *PgBouncer) validateConfig() error { + if p.DSN == "" { + return errors.New("DSN not set") + } + return nil +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/integrations/pgbouncer.md b/src/go/collectors/go.d.plugin/modules/pgbouncer/integrations/pgbouncer.md new file mode 100644 index 000000000..ca8e020ee --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/integrations/pgbouncer.md @@ -0,0 +1,254 @@ +<!--startmeta +custom_edit_url: "https://github.com/netdata/netdata/edit/master/src/go/collectors/go.d.plugin/modules/pgbouncer/README.md" +meta_yaml: "https://github.com/netdata/netdata/edit/master/src/go/collectors/go.d.plugin/modules/pgbouncer/metadata.yaml" +sidebar_label: "PgBouncer" +learn_status: "Published" +learn_rel_path: "Collecting Metrics/Databases" +most_popular: False +message: "DO NOT EDIT THIS FILE DIRECTLY, IT IS GENERATED BY THE COLLECTOR'S metadata.yaml FILE" +endmeta--> + +# PgBouncer + + +<img src="https://netdata.cloud/img/postgres.svg" width="150"/> + + +Plugin: go.d.plugin +Module: pgbouncer + +<img src="https://img.shields.io/badge/maintained%20by-Netdata-%2300ab44" /> + +## Overview + +This collector monitors PgBouncer servers. + +Executed queries: + +- `SHOW VERSION;` +- `SHOW CONFIG;` +- `SHOW DATABASES;` +- `SHOW STATS;` +- `SHOW POOLS;` + +Information about the queries can be found in the [PgBouncer Documentation](https://www.pgbouncer.org/usage.html). + + + + +This collector is supported on all platforms. + +This collector supports collecting metrics from multiple instances of this integration, including remote instances. + + +### Default Behavior + +#### Auto-Detection + +This integration doesn't support auto-detection. + +#### Limits + +The default configuration for this integration does not impose any limits on data collection. + +#### Performance Impact + +The default configuration for this integration is not expected to impose a significant performance impact on the system. + + +## Metrics + +Metrics grouped by *scope*. + +The scope defines the instance that the metric belongs to. An instance is uniquely identified by a set of labels. + + + +### Per PgBouncer instance + +These metrics refer to the entire monitored application. + +This scope has no labels. + +Metrics: + +| Metric | Dimensions | Unit | +|:------|:----------|:----| +| pgbouncer.client_connections_utilization | used | percentage | + +### Per database + +These metrics refer to the database. + +Labels: + +| Label | Description | +|:-----------|:----------------| +| database | database name | +| postgres_database | Postgres database name | + +Metrics: + +| Metric | Dimensions | Unit | +|:------|:----------|:----| +| pgbouncer.db_client_connections | active, waiting, cancel_req | connections | +| pgbouncer.db_server_connections | active, idle, used, tested, login | connections | +| pgbouncer.db_server_connections_utilization | used | percentage | +| pgbouncer.db_clients_wait_time | time | seconds | +| pgbouncer.db_client_max_wait_time | time | seconds | +| pgbouncer.db_transactions | transactions | transactions/s | +| pgbouncer.db_transactions_time | time | seconds | +| pgbouncer.db_transaction_avg_time | time | seconds | +| pgbouncer.db_queries | queries | queries/s | +| pgbouncer.db_queries_time | time | seconds | +| pgbouncer.db_query_avg_time | time | seconds | +| pgbouncer.db_network_io | received, sent | B/s | + + + +## Alerts + +There are no alerts configured by default for this integration. + + +## Setup + +### Prerequisites + +#### Create netdata user + +Create a user with `stats_users` permissions to query your PgBouncer instance. + +To create the `netdata` user: + +- Add `netdata` user to the `pgbouncer.ini` file: + + ```text + stats_users = netdata + ``` + +- Add a password for the `netdata` user to the `userlist.txt` file: + + ```text + "netdata" "<PASSWORD>" + ``` + +- To verify the credentials, run the following command + + ```bash + psql -h localhost -U netdata -p 6432 pgbouncer -c "SHOW VERSION;" >/dev/null 2>&1 && echo OK || echo FAIL + ``` + + When it prompts for a password, enter the password you added to `userlist.txt`. + + + +### Configuration + +#### File + +The configuration file name for this integration is `go.d/pgbouncer.conf`. + + +You can edit the configuration file using the `edit-config` script from the +Netdata [config directory](/docs/netdata-agent/configuration/README.md#the-netdata-config-directory). + +```bash +cd /etc/netdata 2>/dev/null || cd /opt/netdata/etc/netdata +sudo ./edit-config go.d/pgbouncer.conf +``` +#### Options + +The following options can be defined globally: update_every, autodetection_retry. + + +<details open><summary>Config options</summary> + +| Name | Description | Default | Required | +|:----|:-----------|:-------|:--------:| +| update_every | Data collection frequency. | 5 | no | +| autodetection_retry | Recheck interval in seconds. Zero means no recheck will be scheduled. | 0 | no | +| dsn | PgBouncer server DSN (Data Source Name). See [DSN syntax](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING). | postgres://postgres:postgres@127.0.0.1:6432/pgbouncer | yes | +| timeout | Query timeout in seconds. | 1 | no | + +</details> + +#### Examples + +##### TCP socket + +An example configuration. + +<details open><summary>Config</summary> + +```yaml +jobs: + - name: local + dsn: 'postgres://postgres:postgres@127.0.0.1:6432/pgbouncer' + +``` +</details> + +##### Unix socket + +An example configuration. + +<details open><summary>Config</summary> + +```yaml +jobs: + - name: local + dsn: 'host=/tmp dbname=pgbouncer user=postgres port=6432' + +``` +</details> + +##### Multi-instance + +> **Note**: When you define multiple jobs, their names must be unique. + +Local and remote instances. + + +<details open><summary>Config</summary> + +```yaml +jobs: + - name: local + dsn: 'postgres://postgres:postgres@127.0.0.1:6432/pgbouncer' + + - name: remote + dsn: 'postgres://postgres:postgres@203.0.113.10:6432/pgbouncer' + +``` +</details> + + + +## Troubleshooting + +### Debug Mode + +To troubleshoot issues with the `pgbouncer` collector, run the `go.d.plugin` with the debug option enabled. The output +should give you clues as to why the collector isn't working. + +- Navigate to the `plugins.d` directory, usually at `/usr/libexec/netdata/plugins.d/`. If that's not the case on + your system, open `netdata.conf` and look for the `plugins` setting under `[directories]`. + + ```bash + cd /usr/libexec/netdata/plugins.d/ + ``` + +- Switch to the `netdata` user. + + ```bash + sudo -u netdata -s + ``` + +- Run the `go.d.plugin` to debug the collector: + + ```bash + ./go.d.plugin -d -m pgbouncer + ``` + + diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/metadata.yaml b/src/go/collectors/go.d.plugin/modules/pgbouncer/metadata.yaml new file mode 100644 index 000000000..e4a098bc2 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/metadata.yaml @@ -0,0 +1,239 @@ +plugin_name: go.d.plugin +modules: + - meta: + id: collector-go.d.plugin-pgbouncer + plugin_name: go.d.plugin + module_name: pgbouncer + monitored_instance: + name: PgBouncer + link: https://www.pgbouncer.org/ + icon_filename: postgres.svg + categories: + - data-collection.database-servers + keywords: + - pgbouncer + related_resources: + integrations: + list: [] + info_provided_to_referring_integrations: + description: "" + most_popular: false + overview: + data_collection: + metrics_description: | + This collector monitors PgBouncer servers. + + Executed queries: + + - `SHOW VERSION;` + - `SHOW CONFIG;` + - `SHOW DATABASES;` + - `SHOW STATS;` + - `SHOW POOLS;` + + Information about the queries can be found in the [PgBouncer Documentation](https://www.pgbouncer.org/usage.html). + method_description: "" + supported_platforms: + include: [] + exclude: [] + multi_instance: true + additional_permissions: + description: "" + default_behavior: + auto_detection: + description: "" + limits: + description: "" + performance_impact: + description: "" + setup: + prerequisites: + list: + - title: Create netdata user + description: | + Create a user with `stats_users` permissions to query your PgBouncer instance. + + To create the `netdata` user: + + - Add `netdata` user to the `pgbouncer.ini` file: + + ```text + stats_users = netdata + ``` + + - Add a password for the `netdata` user to the `userlist.txt` file: + + ```text + "netdata" "<PASSWORD>" + ``` + + - To verify the credentials, run the following command + + ```bash + psql -h localhost -U netdata -p 6432 pgbouncer -c "SHOW VERSION;" >/dev/null 2>&1 && echo OK || echo FAIL + ``` + + When it prompts for a password, enter the password you added to `userlist.txt`. + configuration: + file: + name: go.d/pgbouncer.conf + options: + description: | + The following options can be defined globally: update_every, autodetection_retry. + folding: + title: Config options + enabled: true + list: + - name: update_every + description: Data collection frequency. + default_value: 5 + required: false + - name: autodetection_retry + description: Recheck interval in seconds. Zero means no recheck will be scheduled. + default_value: 0 + required: false + - name: dsn + description: PgBouncer server DSN (Data Source Name). See [DSN syntax](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING). + default_value: postgres://postgres:postgres@127.0.0.1:6432/pgbouncer + required: true + - name: timeout + description: Query timeout in seconds. + default_value: 1 + required: false + examples: + folding: + title: Config + enabled: true + list: + - name: TCP socket + description: An example configuration. + config: | + jobs: + - name: local + dsn: 'postgres://postgres:postgres@127.0.0.1:6432/pgbouncer' + - name: Unix socket + description: An example configuration. + config: | + jobs: + - name: local + dsn: 'host=/tmp dbname=pgbouncer user=postgres port=6432' + - name: Multi-instance + description: | + > **Note**: When you define multiple jobs, their names must be unique. + + Local and remote instances. + config: | + jobs: + - name: local + dsn: 'postgres://postgres:postgres@127.0.0.1:6432/pgbouncer' + + - name: remote + dsn: 'postgres://postgres:postgres@203.0.113.10:6432/pgbouncer' + troubleshooting: + problems: + list: [] + alerts: [] + metrics: + folding: + title: Metrics + enabled: false + description: "" + availability: [] + scopes: + - name: global + description: These metrics refer to the entire monitored application. + labels: [] + metrics: + - name: pgbouncer.client_connections_utilization + description: Client connections utilization + unit: percentage + chart_type: line + dimensions: + - name: used + - name: database + description: These metrics refer to the database. + labels: + - name: database + description: database name + - name: postgres_database + description: Postgres database name + metrics: + - name: pgbouncer.db_client_connections + description: Database client connections + unit: connections + chart_type: line + dimensions: + - name: active + - name: waiting + - name: cancel_req + - name: pgbouncer.db_server_connections + description: Database server connections + unit: connections + chart_type: line + dimensions: + - name: active + - name: idle + - name: used + - name: tested + - name: login + - name: pgbouncer.db_server_connections_utilization + description: Database server connections utilization + unit: percentage + chart_type: line + dimensions: + - name: used + - name: pgbouncer.db_clients_wait_time + description: Database clients wait time + unit: seconds + chart_type: line + dimensions: + - name: time + - name: pgbouncer.db_client_max_wait_time + description: Database client max wait time + unit: seconds + chart_type: line + dimensions: + - name: time + - name: pgbouncer.db_transactions + description: Database pooled SQL transactions + unit: transactions/s + chart_type: line + dimensions: + - name: transactions + - name: pgbouncer.db_transactions_time + description: Database transactions time + unit: seconds + chart_type: line + dimensions: + - name: time + - name: pgbouncer.db_transaction_avg_time + description: Database transaction average time + unit: seconds + chart_type: line + dimensions: + - name: time + - name: pgbouncer.db_queries + description: Database pooled SQL queries + unit: queries/s + chart_type: line + dimensions: + - name: queries + - name: pgbouncer.db_queries_time + description: Database queries time + unit: seconds + chart_type: line + dimensions: + - name: time + - name: pgbouncer.db_query_avg_time + description: Database query average time + unit: seconds + chart_type: line + dimensions: + - name: time + - name: pgbouncer.db_network_io + description: Database traffic + unit: B/s + chart_type: area + dimensions: + - name: received + - name: sent diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/metrics.go b/src/go/collectors/go.d.plugin/modules/pgbouncer/metrics.go new file mode 100644 index 000000000..eaac52771 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/metrics.go @@ -0,0 +1,47 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +package pgbouncer + +type metrics struct { + dbs map[string]*dbMetrics +} + +// dbMetrics represents PgBouncer database (not the PostgreSQL database of the outgoing connection). +type dbMetrics struct { + name string + pgDBName string + + updated bool + hasCharts bool + + // command 'SHOW DATABASES;' + maxConnections int64 + currentConnections int64 + paused int64 + disabled int64 + + // command 'SHOW STATS;' + // https://github.com/pgbouncer/pgbouncer/blob/9a346b0e451d842d7202abc3eccf0ff5a66b2dd6/src/stats.c#L76 + totalXactCount int64 // v1.8+ + totalQueryCount int64 // v1.8+ + totalReceived int64 + totalSent int64 + totalXactTime int64 // v1.8+ + totalQueryTime int64 + totalWaitTime int64 // v1.8+ + avgXactTime int64 // v1.8+ + avgQueryTime int64 + + // command 'SHOW POOLS;' + // https://github.com/pgbouncer/pgbouncer/blob/9a346b0e451d842d7202abc3eccf0ff5a66b2dd6/src/admin.c#L804 + clActive int64 + clWaiting int64 + clCancelReq int64 + svActive int64 + svIdle int64 + svUsed int64 + svTested int64 + svLogin int64 + maxWait int64 + maxWaitUS int64 // v1.8+ +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/pgbouncer.go b/src/go/collectors/go.d.plugin/modules/pgbouncer/pgbouncer.go new file mode 100644 index 000000000..a77b35a35 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/pgbouncer.go @@ -0,0 +1,115 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +package pgbouncer + +import ( + "database/sql" + _ "embed" + "errors" + "time" + + "github.com/netdata/netdata/go/go.d.plugin/agent/module" + "github.com/netdata/netdata/go/go.d.plugin/pkg/web" + + "github.com/blang/semver/v4" + _ "github.com/jackc/pgx/v4/stdlib" +) + +//go:embed "config_schema.json" +var configSchema string + +func init() { + module.Register("pgbouncer", module.Creator{ + JobConfigSchema: configSchema, + Create: func() module.Module { return New() }, + Config: func() any { return &Config{} }, + }) +} + +func New() *PgBouncer { + return &PgBouncer{ + Config: Config{ + Timeout: web.Duration(time.Second), + DSN: "postgres://postgres:postgres@127.0.0.1:6432/pgbouncer", + }, + charts: globalCharts.Copy(), + recheckSettingsEvery: time.Minute * 5, + metrics: &metrics{ + dbs: make(map[string]*dbMetrics), + }, + } +} + +type Config struct { + UpdateEvery int `yaml:"update_every,omitempty" json:"update_every"` + DSN string `yaml:"dsn" json:"dsn"` + Timeout web.Duration `yaml:"timeout,omitempty" json:"timeout"` +} + +type PgBouncer struct { + module.Base + Config `yaml:",inline" json:""` + + charts *module.Charts + + db *sql.DB + + version *semver.Version + recheckSettingsTime time.Time + recheckSettingsEvery time.Duration + maxClientConn int64 + + metrics *metrics +} + +func (p *PgBouncer) Configuration() any { + return p.Config +} + +func (p *PgBouncer) Init() error { + err := p.validateConfig() + if err != nil { + p.Errorf("config validation: %v", err) + return err + } + + return nil +} + +func (p *PgBouncer) Check() error { + mx, err := p.collect() + if err != nil { + p.Error(err) + return err + } + if len(mx) == 0 { + return errors.New("no metrics collected") + } + return nil +} + +func (p *PgBouncer) Charts() *module.Charts { + return p.charts +} + +func (p *PgBouncer) Collect() map[string]int64 { + mx, err := p.collect() + if err != nil { + p.Error(err) + } + + if len(mx) == 0 { + return nil + } + return mx +} + +func (p *PgBouncer) Cleanup() { + if p.db == nil { + return + } + if err := p.db.Close(); err != nil { + p.Warningf("cleanup: error on closing the PgBouncer database [%s]: %v", p.DSN, err) + } + p.db = nil +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/pgbouncer_test.go b/src/go/collectors/go.d.plugin/modules/pgbouncer/pgbouncer_test.go new file mode 100644 index 000000000..988d406c1 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/pgbouncer_test.go @@ -0,0 +1,364 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +package pgbouncer + +import ( + "bufio" + "bytes" + "database/sql/driver" + "errors" + "fmt" + "os" + "strings" + "testing" + + "github.com/netdata/netdata/go/go.d.plugin/agent/module" + + "github.com/DATA-DOG/go-sqlmock" + "github.com/stretchr/testify/assert" + "github.com/stretchr/testify/require" +) + +var ( + dataConfigJSON, _ = os.ReadFile("testdata/config.json") + dataConfigYAML, _ = os.ReadFile("testdata/config.yaml") + + dataVer170Version, _ = os.ReadFile("testdata/v1.7.0/version.txt") + dataVer1170Version, _ = os.ReadFile("testdata/v1.17.0/version.txt") + dataVer1170Config, _ = os.ReadFile("testdata/v1.17.0/config.txt") + dataVer1170Databases, _ = os.ReadFile("testdata/v1.17.0/databases.txt") + dataVer1170Pools, _ = os.ReadFile("testdata/v1.17.0/pools.txt") + dataVer1170Stats, _ = os.ReadFile("testdata/v1.17.0/stats.txt") +) + +func Test_testDataIsValid(t *testing.T) { + for name, data := range map[string][]byte{ + "dataConfigJSON": dataConfigJSON, + "dataConfigYAML": dataConfigYAML, + "dataVer170Version": dataVer170Version, + "dataVer1170Version": dataVer1170Version, + "dataVer1170Config": dataVer1170Config, + "dataVer1170Databases": dataVer1170Databases, + "dataVer1170Pools": dataVer1170Pools, + "dataVer1170Stats": dataVer1170Stats, + } { + require.NotNil(t, data, name) + } +} + +func TestPgBouncer_ConfigurationSerialize(t *testing.T) { + module.TestConfigurationSerialize(t, &PgBouncer{}, dataConfigJSON, dataConfigYAML) +} + +func TestPgBouncer_Init(t *testing.T) { + tests := map[string]struct { + wantFail bool + config Config + }{ + "Success with default": { + wantFail: false, + config: New().Config, + }, + "Fail when DSN not set": { + wantFail: true, + config: Config{DSN: ""}, + }, + } + + for name, test := range tests { + t.Run(name, func(t *testing.T) { + p := New() + p.Config = test.config + + if test.wantFail { + assert.Error(t, p.Init()) + } else { + assert.NoError(t, p.Init()) + } + }) + } +} + +func TestPgBouncer_Charts(t *testing.T) { + assert.NotNil(t, New().Charts()) +} + +func TestPgBouncer_Check(t *testing.T) { + tests := map[string]struct { + prepareMock func(t *testing.T, m sqlmock.Sqlmock) + wantFail bool + }{ + "Success when all queries are successful (v1.17.0)": { + wantFail: false, + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpect(t, m, queryShowVersion, dataVer1170Version) + mockExpect(t, m, queryShowConfig, dataVer1170Config) + mockExpect(t, m, queryShowDatabases, dataVer1170Databases) + mockExpect(t, m, queryShowStats, dataVer1170Stats) + mockExpect(t, m, queryShowPools, dataVer1170Pools) + }, + }, + "Fail when querying version returns an error": { + wantFail: true, + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpectErr(m, queryShowVersion) + }, + }, + "Fail when querying version returns unsupported version": { + wantFail: true, + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpect(t, m, queryShowVersion, dataVer170Version) + }, + }, + "Fail when querying config returns an error": { + wantFail: true, + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpect(t, m, queryShowVersion, dataVer1170Version) + mockExpectErr(m, queryShowConfig) + }, + }, + } + + for name, test := range tests { + t.Run(name, func(t *testing.T) { + db, mock, err := sqlmock.New( + sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual), + ) + require.NoError(t, err) + p := New() + p.db = db + defer func() { _ = db.Close() }() + + require.NoError(t, p.Init()) + + test.prepareMock(t, mock) + + if test.wantFail { + assert.Error(t, p.Check()) + } else { + assert.NoError(t, p.Check()) + } + assert.NoError(t, mock.ExpectationsWereMet()) + }) + } +} + +func TestPgBouncer_Collect(t *testing.T) { + type testCaseStep struct { + prepareMock func(t *testing.T, m sqlmock.Sqlmock) + check func(t *testing.T, p *PgBouncer) + } + tests := map[string][]testCaseStep{ + "Success on all queries (v1.17.0)": { + { + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpect(t, m, queryShowVersion, dataVer1170Version) + mockExpect(t, m, queryShowConfig, dataVer1170Config) + mockExpect(t, m, queryShowDatabases, dataVer1170Databases) + mockExpect(t, m, queryShowStats, dataVer1170Stats) + mockExpect(t, m, queryShowPools, dataVer1170Pools) + }, + check: func(t *testing.T, p *PgBouncer) { + mx := p.Collect() + + expected := map[string]int64{ + "cl_conns_utilization": 47, + "db_myprod1_avg_query_time": 575, + "db_myprod1_avg_xact_time": 575, + "db_myprod1_cl_active": 15, + "db_myprod1_cl_cancel_req": 0, + "db_myprod1_cl_waiting": 0, + "db_myprod1_maxwait": 0, + "db_myprod1_sv_active": 15, + "db_myprod1_sv_conns_utilization": 0, + "db_myprod1_sv_idle": 5, + "db_myprod1_sv_login": 0, + "db_myprod1_sv_tested": 0, + "db_myprod1_sv_used": 0, + "db_myprod1_total_query_count": 12683170, + "db_myprod1_total_query_time": 7223566620, + "db_myprod1_total_received": 809093651, + "db_myprod1_total_sent": 1990971542, + "db_myprod1_total_wait_time": 1029555, + "db_myprod1_total_xact_count": 12683170, + "db_myprod1_total_xact_time": 7223566620, + "db_myprod2_avg_query_time": 581, + "db_myprod2_avg_xact_time": 581, + "db_myprod2_cl_active": 12, + "db_myprod2_cl_cancel_req": 0, + "db_myprod2_cl_waiting": 0, + "db_myprod2_maxwait": 0, + "db_myprod2_sv_active": 11, + "db_myprod2_sv_conns_utilization": 0, + "db_myprod2_sv_idle": 9, + "db_myprod2_sv_login": 0, + "db_myprod2_sv_tested": 0, + "db_myprod2_sv_used": 0, + "db_myprod2_total_query_count": 12538544, + "db_myprod2_total_query_time": 7144226450, + "db_myprod2_total_received": 799867464, + "db_myprod2_total_sent": 1968267687, + "db_myprod2_total_wait_time": 993313, + "db_myprod2_total_xact_count": 12538544, + "db_myprod2_total_xact_time": 7144226450, + "db_pgbouncer_avg_query_time": 0, + "db_pgbouncer_avg_xact_time": 0, + "db_pgbouncer_cl_active": 2, + "db_pgbouncer_cl_cancel_req": 0, + "db_pgbouncer_cl_waiting": 0, + "db_pgbouncer_maxwait": 0, + "db_pgbouncer_sv_active": 0, + "db_pgbouncer_sv_conns_utilization": 0, + "db_pgbouncer_sv_idle": 0, + "db_pgbouncer_sv_login": 0, + "db_pgbouncer_sv_tested": 0, + "db_pgbouncer_sv_used": 0, + "db_pgbouncer_total_query_count": 45, + "db_pgbouncer_total_query_time": 0, + "db_pgbouncer_total_received": 0, + "db_pgbouncer_total_sent": 0, + "db_pgbouncer_total_wait_time": 0, + "db_pgbouncer_total_xact_count": 45, + "db_pgbouncer_total_xact_time": 0, + "db_postgres_avg_query_time": 2790, + "db_postgres_avg_xact_time": 2790, + "db_postgres_cl_active": 18, + "db_postgres_cl_cancel_req": 0, + "db_postgres_cl_waiting": 0, + "db_postgres_maxwait": 0, + "db_postgres_sv_active": 18, + "db_postgres_sv_conns_utilization": 0, + "db_postgres_sv_idle": 2, + "db_postgres_sv_login": 0, + "db_postgres_sv_tested": 0, + "db_postgres_sv_used": 0, + "db_postgres_total_query_count": 25328823, + "db_postgres_total_query_time": 72471882827, + "db_postgres_total_received": 1615791619, + "db_postgres_total_sent": 3976053858, + "db_postgres_total_wait_time": 50439622253, + "db_postgres_total_xact_count": 25328823, + "db_postgres_total_xact_time": 72471882827, + } + + assert.Equal(t, expected, mx) + }, + }, + }, + "Fail when querying version returns an error": { + { + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpectErr(m, queryShowVersion) + }, + check: func(t *testing.T, p *PgBouncer) { + mx := p.Collect() + var expected map[string]int64 + assert.Equal(t, expected, mx) + }, + }, + }, + "Fail when querying version returns unsupported version": { + { + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpect(t, m, queryShowVersion, dataVer170Version) + }, + check: func(t *testing.T, p *PgBouncer) { + mx := p.Collect() + var expected map[string]int64 + assert.Equal(t, expected, mx) + }, + }, + }, + "Fail when querying config returns an error": { + { + prepareMock: func(t *testing.T, m sqlmock.Sqlmock) { + mockExpect(t, m, queryShowVersion, dataVer1170Version) + mockExpectErr(m, queryShowConfig) + }, + check: func(t *testing.T, p *PgBouncer) { + mx := p.Collect() + var expected map[string]int64 + assert.Equal(t, expected, mx) + }, + }, + }, + } + + for name, test := range tests { + t.Run(name, func(t *testing.T) { + db, mock, err := sqlmock.New( + sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual), + ) + require.NoError(t, err) + p := New() + p.db = db + defer func() { _ = db.Close() }() + + require.NoError(t, p.Init()) + + for i, step := range test { + t.Run(fmt.Sprintf("step[%d]", i), func(t *testing.T) { + step.prepareMock(t, mock) + step.check(t, p) + }) + } + assert.NoError(t, mock.ExpectationsWereMet()) + }) + } +} + +func mockExpect(t *testing.T, mock sqlmock.Sqlmock, query string, rows []byte) { + mock.ExpectQuery(query).WillReturnRows(mustMockRows(t, rows)).RowsWillBeClosed() +} + +func mockExpectErr(mock sqlmock.Sqlmock, query string) { + mock.ExpectQuery(query).WillReturnError(fmt.Errorf("mock error (%s)", query)) +} + +func mustMockRows(t *testing.T, data []byte) *sqlmock.Rows { + rows, err := prepareMockRows(data) + require.NoError(t, err) + return rows +} + +func prepareMockRows(data []byte) (*sqlmock.Rows, error) { + r := bytes.NewReader(data) + sc := bufio.NewScanner(r) + + var numColumns int + var rows *sqlmock.Rows + + for sc.Scan() { + s := strings.TrimSpace(sc.Text()) + if s == "" || strings.HasPrefix(s, "---") { + continue + } + + parts := strings.Split(s, "|") + for i, v := range parts { + parts[i] = strings.TrimSpace(v) + } + + if rows == nil { + numColumns = len(parts) + rows = sqlmock.NewRows(parts) + continue + } + + if len(parts) != numColumns { + return nil, fmt.Errorf("prepareMockRows(): columns != values (%d/%d)", numColumns, len(parts)) + } + + values := make([]driver.Value, len(parts)) + for i, v := range parts { + values[i] = v + } + rows.AddRow(values...) + } + + if rows == nil { + return nil, errors.New("prepareMockRows(): nil rows result") + } + + return rows, nil +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/config.json b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/config.json new file mode 100644 index 000000000..ed8b72dcb --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/config.json @@ -0,0 +1,5 @@ +{ + "update_every": 123, + "dsn": "ok", + "timeout": 123.123 +} diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/config.yaml b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/config.yaml new file mode 100644 index 000000000..caff49039 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/config.yaml @@ -0,0 +1,3 @@ +update_every: 123 +dsn: "ok" +timeout: 123.123 diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/config.txt b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/config.txt new file mode 100644 index 000000000..da1aba609 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/config.txt @@ -0,0 +1,86 @@ + key | value | default | changeable +---------------------------+--------------------------------------------------------+--------------------------------------------------------+------------ + admin_users | postgres | | yes + application_name_add_host | 0 | 0 | yes + auth_file | /etc/pgbouncer/userlist.txt | | yes + auth_hba_file | | | yes + auth_query | SELECT usename, passwd FROM pg_shadow WHERE usename=$1 | SELECT usename, passwd FROM pg_shadow WHERE usename=$1 | yes + auth_type | md5 | md5 | yes + auth_user | | | yes + autodb_idle_timeout | 3600 | 3600 | yes + client_idle_timeout | 0 | 0 | yes + client_login_timeout | 60 | 60 | yes + client_tls_ca_file | | | yes + client_tls_cert_file | | | yes + client_tls_ciphers | fast | fast | yes + client_tls_dheparams | auto | auto | yes + client_tls_ecdhcurve | auto | auto | yes + client_tls_key_file | | | yes + client_tls_protocols | secure | secure | yes + client_tls_sslmode | disable | disable | yes + conffile | /etc/pgbouncer/pgbouncer.ini | | yes + default_pool_size | 20 | 20 | yes + disable_pqexec | 0 | 0 | no + dns_max_ttl | 15 | 15 | yes + dns_nxdomain_ttl | 15 | 15 | yes + dns_zone_check_period | 0 | 0 | yes + idle_transaction_timeout | 0 | 0 | yes + ignore_startup_parameters | extra_float_digits | | yes + job_name | pgbouncer | pgbouncer | no + listen_addr | 0.0.0.0 | | no + listen_backlog | 128 | 128 | no + listen_port | 6432 | 6432 | no + log_connections | 1 | 1 | yes + log_disconnections | 1 | 1 | yes + log_pooler_errors | 1 | 1 | yes + log_stats | 1 | 1 | yes + logfile | | | yes + max_client_conn | 100 | 100 | yes + max_db_connections | 0 | 0 | yes + max_packet_size | 2147483647 | 2147483647 | yes + max_user_connections | 0 | 0 | yes + min_pool_size | 0 | 0 | yes + pidfile | | | no + pkt_buf | 4096 | 4096 | no + pool_mode | session | session | yes + query_timeout | 0 | 0 | yes + query_wait_timeout | 120 | 120 | yes + reserve_pool_size | 0 | 0 | yes + reserve_pool_timeout | 5 | 5 | yes + resolv_conf | | | no + sbuf_loopcnt | 5 | 5 | yes + server_check_delay | 30 | 30 | yes + server_check_query | select 1 | select 1 | yes + server_connect_timeout | 15 | 15 | yes + server_fast_close | 0 | 0 | yes + server_idle_timeout | 600 | 600 | yes + server_lifetime | 3600 | 3600 | yes + server_login_retry | 15 | 15 | yes + server_reset_query | DISCARD ALL | DISCARD ALL | yes + server_reset_query_always | 0 | 0 | yes + server_round_robin | 0 | 0 | yes + server_tls_ca_file | | | yes + server_tls_cert_file | | | yes + server_tls_ciphers | fast | fast | yes + server_tls_key_file | | | yes + server_tls_protocols | secure | secure | yes + server_tls_sslmode | disable | disable | yes + so_reuseport | 0 | 0 | no + stats_period | 60 | 60 | yes + stats_users | | | yes + suspend_timeout | 10 | 10 | yes + syslog | 0 | 0 | yes + syslog_facility | daemon | daemon | yes + syslog_ident | pgbouncer | pgbouncer | yes + tcp_defer_accept | 1 | 1 | yes + tcp_keepalive | 1 | 1 | yes + tcp_keepcnt | 0 | 0 | yes + tcp_keepidle | 0 | 0 | yes + tcp_keepintvl | 0 | 0 | yes + tcp_socket_buffer | 0 | 0 | yes + tcp_user_timeout | 0 | 0 | yes + unix_socket_dir | | /tmp | no + unix_socket_group | | | no + unix_socket_mode | 511 | 0777 | no + user | postgres | | no + verbose | 0 | | yes
\ No newline at end of file diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/databases.txt b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/databases.txt new file mode 100644 index 000000000..9e8f14695 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/databases.txt @@ -0,0 +1,6 @@ + name | host | port | database | force_user | pool_size | min_pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled +-----------+-----------+------+-----------+------------+-----------+---------------+--------------+-----------+-----------------+---------------------+--------+---------- + myprod1 | 127.0.0.1 | 5432 | myprod1 | postgres | 20 | 0 | 0 | | 0 | 20 | 0 | 0 + myprod2 | 127.0.0.1 | 5432 | myprod2 | postgres | 20 | 0 | 0 | | 0 | 20 | 0 | 0 + pgbouncer | | 6432 | pgbouncer | pgbouncer | 2 | 0 | 0 | statement | 0 | 0 | 0 | 0 + postgres | 127.0.0.1 | 5432 | postgres | postgres | 20 | 0 | 0 | | 0 | 20 | 0 | 0
\ No newline at end of file diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/pools.txt b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/pools.txt new file mode 100644 index 000000000..dec3326ad --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/pools.txt @@ -0,0 +1,6 @@ + database | user | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode +-----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+----------- + myprod1 | postgres | 15 | 0 | 0 | 15 | 5 | 0 | 0 | 0 | 0 | 0 | session + myprod2 | postgres | 12 | 0 | 0 | 11 | 9 | 0 | 0 | 0 | 0 | 0 | session + pgbouncer | pgbouncer | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement + postgres | postgres | 18 | 0 | 0 | 18 | 2 | 0 | 0 | 0 | 0 | 0 | session
\ No newline at end of file diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/stats.txt b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/stats.txt new file mode 100644 index 000000000..3b66fc323 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/stats.txt @@ -0,0 +1,6 @@ + database | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time +-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+--------------- + myprod1 | 12683170 | 12683170 | 809093651 | 1990971542 | 7223566620 | 7223566620 | 1029555 | 900 | 900 | 57434 | 141358 | 575 | 575 | 3 + myprod2 | 12538544 | 12538544 | 799867464 | 1968267687 | 7144226450 | 7144226450 | 993313 | 885 | 885 | 56511 | 139050 | 581 | 581 | 14 + pgbouncer | 45 | 45 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 + postgres | 25328823 | 25328823 | 1615791619 | 3976053858 | 72471882827 | 72471882827 | 50439622253 | 1901 | 1901 | 121329 | 298556 | 2790 | 2790 | 3641761
\ No newline at end of file diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/version.txt b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/version.txt new file mode 100644 index 000000000..fa2c806a2 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.17.0/version.txt @@ -0,0 +1,3 @@ + version +------------------ + PgBouncer 1.17.0
\ No newline at end of file diff --git a/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.7.0/version.txt b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.7.0/version.txt new file mode 100644 index 000000000..ff0fd70a8 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/pgbouncer/testdata/v1.7.0/version.txt @@ -0,0 +1,3 @@ + version +------------------ + PgBouncer 1.7.0
\ No newline at end of file |