| 1 | // SPDX-License-Identifier: GPL-3.0-or-later |
| 2 | |
| 3 | package pgbouncer |
| 4 | |
| 5 | import ( |
| 6 | "context" |
| 7 | "fmt" |
| 8 | "regexp" |
| 9 | "strconv" |
| 10 | "strings" |
| 11 | "time" |
| 12 | |
| 13 | "github.com/blang/semver/v4" |
| 14 | "github.com/jackc/pgx/v5" |
| 15 | "github.com/jackc/pgx/v5/stdlib" |
| 16 | |
| 17 | "github.com/netdata/netdata/go/plugins/plugin/go.d/pkg/sqlquery" |
| 18 | ) |
| 19 | |
| 20 | // 'SHOW STATS;' response was changed significantly in v1.8.0 |
| 21 | // v1.8.0 was released in 2015 - no need to complicate the code to support the old version. |
| 22 | var minSupportedVersion = semver.Version{Major: 1, Minor: 8, Patch: 0} |
| 23 | |
| 24 | const ( |
| 25 | queryShowVersion = "SHOW VERSION;" |
| 26 | queryShowConfig = "SHOW CONFIG;" |
| 27 | queryShowDatabases = "SHOW DATABASES;" |
| 28 | queryShowStats = "SHOW STATS;" |
| 29 | queryShowPools = "SHOW POOLS;" |
| 30 | ) |
| 31 | |
| 32 | func (c *Collector) collect() (map[string]int64, error) { |
| 33 | if c.db == nil { |
| 34 | if err := c.openConnection(); err != nil { |
| 35 | return nil, err |
| 36 | } |
| 37 | } |
| 38 | if c.version == nil { |
| 39 | ver, err := c.queryVersion() |
| 40 | if err != nil { |
| 41 | return nil, err |
| 42 | } |
| 43 | c.Debugf("connected to PgBouncer v%s", ver) |
| 44 | if ver.LE(minSupportedVersion) { |
| 45 | return nil, fmt.Errorf("unsupported version: v%s, required v%s+", ver, minSupportedVersion) |
| 46 | } |
| 47 | c.version = ver |
| 48 | } |
| 49 | |
| 50 | now := time.Now() |
| 51 | if now.Sub(c.recheckSettingsTime) > c.recheckSettingsEvery { |
| 52 | v, err := c.queryMaxClientConn() |
| 53 | if err != nil { |
| 54 | return nil, err |
| 55 | } |
| 56 | c.maxClientConn = v |
| 57 | } |
| 58 | |
| 59 | // http://www.pgbouncer.org/usage.html |
| 60 | |
| 61 | c.resetMetrics() |
| 62 | |
| 63 | if err := c.collectDatabases(); err != nil { |
| 64 | return nil, err |
| 65 | } |
| 66 | if err := c.collectStats(); err != nil { |
| 67 | return nil, err |
| 68 | } |
| 69 | if err := c.collectPools(); err != nil { |
| 70 | return nil, err |
| 71 | } |
| 72 | |
| 73 | mx := make(map[string]int64) |
| 74 | c.collectMetrics(mx) |
| 75 | |
| 76 | return mx, nil |
| 77 | } |
| 78 | |
| 79 | func (c *Collector) collectMetrics(mx map[string]int64) { |
| 80 | var clientConns int64 |
| 81 | for name, db := range c.metrics.dbs { |
| 82 | if !db.updated { |
| 83 | delete(c.metrics.dbs, name) |
| 84 | c.removeDatabaseCharts(name) |
| 85 | continue |
| 86 | } |
| 87 | if !db.hasCharts { |
| 88 | db.hasCharts = true |
| 89 | c.addNewDatabaseCharts(name, db.pgDBName) |
| 90 | } |
| 91 | |
| 92 | mx["db_"+name+"_total_xact_count"] = db.totalXactCount |
| 93 | mx["db_"+name+"_total_xact_time"] = db.totalXactTime |
| 94 | mx["db_"+name+"_avg_xact_time"] = db.avgXactTime |
| 95 | |
| 96 | mx["db_"+name+"_total_query_count"] = db.totalQueryCount |
| 97 | mx["db_"+name+"_total_query_time"] = db.totalQueryTime |
| 98 | mx["db_"+name+"_avg_query_time"] = db.avgQueryTime |
| 99 | |
| 100 | mx["db_"+name+"_total_wait_time"] = db.totalWaitTime |
| 101 | mx["db_"+name+"_maxwait"] = db.maxWait*1e6 + db.maxWaitUS |
| 102 | |
| 103 | mx["db_"+name+"_cl_active"] = db.clActive |
| 104 | mx["db_"+name+"_cl_waiting"] = db.clWaiting |
| 105 | mx["db_"+name+"_cl_cancel_req"] = db.clCancelReq |
| 106 | clientConns += db.clActive + db.clWaiting + db.clCancelReq |
| 107 | |
| 108 | mx["db_"+name+"_sv_active"] = db.svActive |
| 109 | mx["db_"+name+"_sv_idle"] = db.svIdle |
| 110 | mx["db_"+name+"_sv_used"] = db.svUsed |
| 111 | mx["db_"+name+"_sv_tested"] = db.svTested |
| 112 | mx["db_"+name+"_sv_login"] = db.svLogin |
| 113 | |
| 114 | mx["db_"+name+"_total_received"] = db.totalReceived |
| 115 | mx["db_"+name+"_total_sent"] = db.totalSent |
| 116 | |
| 117 | mx["db_"+name+"_sv_conns_utilization"] = calcPercentage(db.currentConnections, db.maxConnections) |
| 118 | } |
| 119 | |
| 120 | mx["cl_conns_utilization"] = calcPercentage(clientConns, c.maxClientConn) |
| 121 | } |
| 122 | |
| 123 | func (c *Collector) collectDatabases() error { |
| 124 | q := queryShowDatabases |
| 125 | c.Debugf("executing query: %v", q) |
| 126 | |
| 127 | var db string |
| 128 | return c.collectQuery(q, func(column, value string) { |
| 129 | switch column { |
| 130 | case "name": |
| 131 | db = value |
| 132 | c.getDBMetrics(db).updated = true |
| 133 | case "database": |
| 134 | c.getDBMetrics(db).pgDBName = value |
| 135 | case "max_connections": |
| 136 | c.getDBMetrics(db).maxConnections = parseInt(value) |
| 137 | case "current_connections": |
| 138 | c.getDBMetrics(db).currentConnections = parseInt(value) |
| 139 | case "paused": |
| 140 | c.getDBMetrics(db).paused = parseInt(value) |
| 141 | case "disabled": |
| 142 | c.getDBMetrics(db).disabled = parseInt(value) |
| 143 | } |
| 144 | }) |
| 145 | } |
| 146 | |
| 147 | func (c *Collector) collectStats() error { |
| 148 | q := queryShowStats |
| 149 | c.Debugf("executing query: %v", q) |
| 150 | |
| 151 | var db string |
| 152 | return c.collectQuery(q, func(column, value string) { |
| 153 | switch column { |
| 154 | case "database": |
| 155 | db = value |
| 156 | c.getDBMetrics(db).updated = true |
| 157 | case "total_xact_count": |
| 158 | c.getDBMetrics(db).totalXactCount = parseInt(value) |
| 159 | case "total_query_count": |
| 160 | c.getDBMetrics(db).totalQueryCount = parseInt(value) |
| 161 | case "total_received": |
| 162 | c.getDBMetrics(db).totalReceived = parseInt(value) |
| 163 | case "total_sent": |
| 164 | c.getDBMetrics(db).totalSent = parseInt(value) |
| 165 | case "total_xact_time": |
| 166 | c.getDBMetrics(db).totalXactTime = parseInt(value) |
| 167 | case "total_query_time": |
| 168 | c.getDBMetrics(db).totalQueryTime = parseInt(value) |
| 169 | case "total_wait_time": |
| 170 | c.getDBMetrics(db).totalWaitTime = parseInt(value) |
| 171 | case "avg_xact_time": |
| 172 | c.getDBMetrics(db).avgXactTime = parseInt(value) |
| 173 | case "avg_query_time": |
| 174 | c.getDBMetrics(db).avgQueryTime = parseInt(value) |
| 175 | } |
| 176 | }) |
| 177 | } |
| 178 | |
| 179 | func (c *Collector) collectPools() error { |
| 180 | q := queryShowPools |
| 181 | c.Debugf("executing query: %v", q) |
| 182 | |
| 183 | // an entry is made for each couple of (database, user). |
| 184 | var db string |
| 185 | return c.collectQuery(q, func(column, value string) { |
| 186 | switch column { |
| 187 | case "database": |
| 188 | db = value |
| 189 | c.getDBMetrics(db).updated = true |
| 190 | case "cl_active": |
| 191 | c.getDBMetrics(db).clActive += parseInt(value) |
| 192 | case "cl_waiting": |
| 193 | c.getDBMetrics(db).clWaiting += parseInt(value) |
| 194 | case "cl_cancel_req": |
| 195 | c.getDBMetrics(db).clCancelReq += parseInt(value) |
| 196 | case "sv_active": |
| 197 | c.getDBMetrics(db).svActive += parseInt(value) |
| 198 | case "sv_idle": |
| 199 | c.getDBMetrics(db).svIdle += parseInt(value) |
| 200 | case "sv_used": |
| 201 | c.getDBMetrics(db).svUsed += parseInt(value) |
| 202 | case "sv_tested": |
| 203 | c.getDBMetrics(db).svTested += parseInt(value) |
| 204 | case "sv_login": |
| 205 | c.getDBMetrics(db).svLogin += parseInt(value) |
| 206 | case "maxwait": |
| 207 | c.getDBMetrics(db).maxWait += parseInt(value) |
| 208 | case "maxwait_us": |
| 209 | c.getDBMetrics(db).maxWaitUS += parseInt(value) |
| 210 | } |
| 211 | }) |
| 212 | } |
| 213 | |
| 214 | func (c *Collector) queryMaxClientConn() (int64, error) { |
| 215 | q := queryShowConfig |
| 216 | c.Debugf("executing query: %v", q) |
| 217 | |
| 218 | var v int64 |
| 219 | var key string |
| 220 | err := c.collectQuery(q, func(column, value string) { |
| 221 | switch column { |
| 222 | case "key": |
| 223 | key = value |
| 224 | case "value": |
| 225 | if key == "max_client_conn" { |
| 226 | v = parseInt(value) |
| 227 | } |
| 228 | } |
| 229 | }) |
| 230 | return v, err |
| 231 | } |
| 232 | |
| 233 | var reVersion = regexp.MustCompile(`\d+\.\d+\.\d+`) |
| 234 | |
| 235 | func (c *Collector) queryVersion() (*semver.Version, error) { |
| 236 | q := queryShowVersion |
| 237 | c.Debugf("executing query: %v", q) |
| 238 | |
| 239 | var resp string |
| 240 | ctx, cancel := context.WithTimeout(context.Background(), c.Timeout.Duration()) |
| 241 | defer cancel() |
| 242 | if err := c.db.QueryRowContext(ctx, q).Scan(&resp); err != nil { |
| 243 | return nil, err |
| 244 | } |
| 245 | |
| 246 | if !strings.Contains(resp, "PgBouncer") { |
| 247 | return nil, fmt.Errorf("not PgBouncer instance: version response: %s", resp) |
| 248 | } |
| 249 | |
| 250 | ver := reVersion.FindString(resp) |
| 251 | if ver == "" { |
| 252 | return nil, fmt.Errorf("couldn't parse version string '%s' (expected pattern '%s')", resp, reVersion) |
| 253 | } |
| 254 | |
| 255 | v, err := semver.New(ver) |
| 256 | if err != nil { |
| 257 | return nil, fmt.Errorf("couldn't parse version string '%s': %v", ver, err) |
| 258 | } |
| 259 | |
| 260 | return v, nil |
| 261 | } |
| 262 | |
| 263 | func (c *Collector) openConnection() error { |
| 264 | cfg, err := pgx.ParseConfig(c.DSN) |
| 265 | if err != nil { |
| 266 | return err |
| 267 | } |
| 268 | |
| 269 | cfg.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol |
| 270 | |
| 271 | db := stdlib.OpenDB(*cfg, stdlib.OptionShouldPing(func(_ context.Context, _ stdlib.ShouldPingParams) bool { |
| 272 | return false |
| 273 | })) |
| 274 | |
| 275 | db.SetMaxOpenConns(1) |
| 276 | db.SetMaxIdleConns(1) |
| 277 | db.SetConnMaxLifetime(10 * time.Minute) |
| 278 | |
| 279 | c.db = db |
| 280 | |
| 281 | return nil |
| 282 | } |
| 283 | |
| 284 | func (c *Collector) collectQuery(query string, assign func(column, value string)) error { |
| 285 | ctx, cancel := context.WithTimeout(context.Background(), c.Timeout.Duration()) |
| 286 | defer cancel() |
| 287 | _, err := sqlquery.QueryRows(ctx, c.db, query, func(column, value string, _ bool) { |
| 288 | assign(column, value) |
| 289 | }) |
| 290 | return err |
| 291 | } |
| 292 | |
| 293 | func (c *Collector) getDBMetrics(dbname string) *dbMetrics { |
| 294 | db, ok := c.metrics.dbs[dbname] |
| 295 | if !ok { |
| 296 | db = &dbMetrics{name: dbname} |
| 297 | c.metrics.dbs[dbname] = db |
| 298 | } |
| 299 | return db |
| 300 | } |
| 301 | |
| 302 | func (c *Collector) resetMetrics() { |
| 303 | for name, db := range c.metrics.dbs { |
| 304 | c.metrics.dbs[name] = &dbMetrics{ |
| 305 | name: db.name, |
| 306 | pgDBName: db.pgDBName, |
| 307 | hasCharts: db.hasCharts, |
| 308 | } |
| 309 | } |
| 310 | } |
| 311 | |
| 312 | func parseInt(s string) int64 { |
| 313 | v, _ := strconv.ParseInt(s, 10, 64) |
| 314 | return v |
| 315 | } |
| 316 | |
| 317 | func calcPercentage(value, total int64) int64 { |
| 318 | if total == 0 { |
| 319 | return 0 |
| 320 | } |
| 321 | return value * 100 / total |
| 322 | } |