pangolin_exporter

1
// Copyright 2023 The Prometheus Authors
2
// Licensed under the Apache License, Version 2.0 (the "License");
3
// you may not use this file except in compliance with the License.
4
// You may obtain a copy of the License at
5
//
6
// http://www.apache.org/licenses/LICENSE-2.0
7
//
8
// Unless required by applicable law or agreed to in writing, software
9
// distributed under the License is distributed on an "AS IS" BASIS,
10
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
11
// See the License for the specific language governing permissions and
12
// limitations under the License.
13

14
package collector
15

16
import (
17
	"context"
18
	"database/sql"
19

20
	"github.com/blang/semver/v4"
21
	"github.com/go-kit/log"
22
	"github.com/prometheus/client_golang/prometheus"
23
)
24

25
const statementsSubsystem = "statements"
26

27
func init() {
28
	// WARNING:
29
	//   Disabled by default because this set of metrics can be quite expensive on a busy server
30
	//   Every unique query will cause a new timeseries to be created
31
	registerCollector(statementsSubsystem, defaultEnabled, NewPGstatementsCollector)
32
}
33

34
type PGstatementsCollector struct {
35
	log log.Logger
36
}
37

38
func NewPGstatementsCollector(config collectorConfig) (Collector, error) {
39
	return &PGstatementsCollector{log: config.logger}, nil
40
}
41

42
var (
43
	statSTatementsQueryInfo = prometheus.NewDesc(
44
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "query_info"),
45
		"Labeled info about statements has been executed.",
46
		[]string{"user", "database", "queryid", "query"},
47
		prometheus.Labels{},
48
	)
49
	statSTatementsCalls = prometheus.NewDesc(
50
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "calls_total"),
51
		"Total number of times statement has been executed.",
52
		[]string{"user", "database", "queryid"},
53
		prometheus.Labels{},
54
	)
55
	statSTatementsRowsTotal = prometheus.NewDesc(
56
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "rows_total"),
57
		"Total number of rows retrieved or affected by the statement.",
58
		[]string{"user", "database", "queryid"},
59
		prometheus.Labels{},
60
	)
61
	statSTatementsTimes = prometheus.NewDesc(
62
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "time_seconds_total"),
63
		"Time spent by the statement in each mode, in seconds.",
64
		[]string{"user", "database", "queryid", "mode"},
65
		prometheus.Labels{},
66
	)
67
	statSTatementsAllTimes = prometheus.NewDesc(
68
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "time_seconds_all_total"),
69
		"Total time spent by the statement, in seconds.",
70
		[]string{"user", "database", "queryid"},
71
		prometheus.Labels{},
72
	)
73
	statSTatementsSharedBuffersHitTotal = prometheus.NewDesc(
74
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "shared_buffers_hit_total"),
75
		"Total number of blocks have been found in shared buffers by the statement.",
76
		[]string{"user", "database", "queryid"},
77
		prometheus.Labels{},
78
	)
79
	statSTatementSharedBuffersReadBytesTotal = prometheus.NewDesc(
80
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "shared_buffers_read_bytes_total"),
81
		"Total number of bytes read from disk or OS page cache by the statement when block not found in shared buffers.",
82
		[]string{"user", "database", "queryid"},
83
		prometheus.Labels{},
84
	)
85
	statSTatementsSharedBuffersDirtiedTotal = prometheus.NewDesc(
86
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "shared_buffers_dirtied_total"),
87
		"Total number of blocks have been dirtied in shared buffers by the statement.",
88
		[]string{"user", "database", "queryid"},
89
		prometheus.Labels{},
90
	)
91
	statSTatementsSharedBuffersWrittenBytesTotal = prometheus.NewDesc(
92
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "shared_buffers_written_bytes_total"),
93
		"Total number of bytes written from shared buffers to disk by the statement.",
94
		[]string{"user", "database", "queryid"},
95
		prometheus.Labels{},
96
	)
97
	statSTatementsLocalBuffersHitTotal = prometheus.NewDesc(
98
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "local_buffers_hit_total"),
99
		"Total number of blocks have been found in local buffers by the statement.",
100
		[]string{"user", "database", "queryid"},
101
		prometheus.Labels{},
102
	)
103
	statSTatementsLocalBuffersReadBytesTotal = prometheus.NewDesc(
104
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "local_buffers_read_bytes_total"),
105
		"Total number of bytes read from disk or OS page cache by the statement when block not found in local buffers.",
106
		[]string{"user", "database", "queryid"},
107
		prometheus.Labels{},
108
	)
109
	statSTatementsLocalBuffersDirtiedTotal = prometheus.NewDesc(
110
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "local_buffers_dirtied_total"),
111
		"Total number of blocks have been dirtied in local buffers by the statement.",
112
		[]string{"user", "database", "queryid"},
113
		prometheus.Labels{},
114
	)
115
	statSTatementsLocalBuffersWrittenBytesTotal = prometheus.NewDesc(
116
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "local_buffers_written_bytes_total"),
117
		"Total number of bytes written from local buffers to disk by the statement.",
118
		[]string{"user", "database", "queryid"},
119
		prometheus.Labels{},
120
	)
121
	statSTatementsTempReadBytesTotal = prometheus.NewDesc(
122
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "temp_read_bytes_total"),
123
		"Total number of bytes read from temporary files by the statement.",
124
		[]string{"user", "database", "queryid"},
125
		prometheus.Labels{},
126
	)
127
	statSTatementsTempWrittenBytesTotal = prometheus.NewDesc(
128
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "temp_written_bytes_total"),
129
		"Total number of bytes written to temporary files by the statement.",
130
		[]string{"user", "database", "queryid"},
131
		prometheus.Labels{},
132
	)
133
	statSTatementsWalRecordsTotal = prometheus.NewDesc(
134
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "wal_records_total"),
135
		"Total number of WAL records generated by the statement.",
136
		[]string{"user", "database", "queryid"},
137
		prometheus.Labels{},
138
	)
139

140
	statSTatementsWalBytesAllTotal = prometheus.NewDesc(
141
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "wal_bytes_all_total"),
142
		"Total number of WAL generated by the statement, in bytes.",
143
		[]string{"user", "database", "queryid"},
144
		prometheus.Labels{},
145
	)
146
	statSTatementsWalWalBytesTotal = prometheus.NewDesc(
147
		prometheus.BuildFQName(namespace_pangolin, statementsSubsystem, "wal_bytes_total"),
148
		"Total number of WAL bytes generated by the statement, by type.",
149
		[]string{"user", "database", "queryid", "wal"},
150
		prometheus.Labels{},
151
	)
152

153
	// postgresStatementsQuery12 defines query for querying statements metrics for PG12 and older.
154
	postgresStatementsQuery12 = "SELECT d.datname AS database, pg_get_userbyid(p.userid) AS user, p.queryid, " +
155
		"p.query, p.calls, p.rows, p.total_time, p.blk_read_time, p.blk_write_time, " +
156
		"nullif(p.shared_blks_hit, 0) AS shared_blks_hit, nullif(p.shared_blks_read, 0) AS shared_blks_read, " +
157
		"nullif(p.shared_blks_dirtied, 0) AS shared_blks_dirtied, nullif(p.shared_blks_written, 0) AS shared_blks_written, " +
158
		"nullif(p.local_blks_hit, 0) AS local_blks_hit, nullif(p.local_blks_read, 0) AS local_blks_read, " +
159
		"nullif(p.local_blks_dirtied, 0) AS local_blks_dirtied, nullif(p.local_blks_written, 0) AS local_blks_written, " +
160
		"nullif(p.temp_blks_read, 0) AS temp_blks_read, nullif(p.temp_blks_written, 0) AS temp_blks_written " +
161
		"FROM pg_stat_statements p JOIN pg_database d ON d.oid=p.dbid;"
162

163
	// postgresStatementsQueryLatest defines query for querying statements metrics.
164
	// 1. use nullif(value, 0) to nullify zero values, NULL are skipped by stats method and metrics wil not be generated.
165
	postgresStatementsQueryLatest = `SELECT d.datname AS database, pg_get_userbyid(p.userid) AS user, p.queryid,
166
	p.query, p.calls, p.rows, p.total_exec_time, p.total_plan_time, p.blk_read_time, p.blk_write_time,
167
	nullif(p.shared_blks_hit, 0) AS shared_blks_hit, nullif(p.shared_blks_read, 0) AS shared_blks_read,
168
	nullif(p.shared_blks_dirtied, 0) AS shared_blks_dirtied, nullif(p.shared_blks_written, 0) AS shared_blks_written,
169
	nullif(p.local_blks_hit, 0) AS local_blks_hit, nullif(p.local_blks_read, 0) AS local_blks_read,
170
	nullif(p.local_blks_dirtied, 0) AS local_blks_dirtied, nullif(p.local_blks_written, 0) AS local_blks_written,
171
	nullif(p.temp_blks_read, 0) AS temp_blks_read, nullif(p.temp_blks_written, 0) AS temp_blks_written,
172
	nullif(p.wal_records, 0) AS wal_records, nullif(p.wal_fpi, 0) AS wal_fpi, nullif(p.wal_bytes, 0) AS wal_bytes
173
	FROM pg_stat_statements p JOIN pg_database d ON d.oid=p.dbid;`
174
)
175

176
func (c PGstatementsCollector) Update(ctx context.Context, instance *instance, ch chan<- prometheus.Metric) error {
177
	db := instance.getDB()
178

179
	query := postgresStatementsQuery12
180
	if instance.version.GE(semver.MustParse("13.0.0")) {
181
		query = postgresStatementsQueryLatest
182
	}
183

184
	rows1 := db.QueryRow(`SELECT setting FROM pg_settings WHERE name = 'block_size';`)
185
	var bsize sql.NullFloat64
186
	rows1.Scan(&bsize)
187
	blockSize := 0.0
188
	if bsize.Valid {
189
		blockSize = float64(bsize.Float64)
190
	}
191

192
	rows, err := db.QueryContext(ctx, query)
193
	if err != nil {
194
		return err
195
	}
196
	defer rows.Close()
197
	for rows.Next() {
198

199
		var database, user, queryid, query sql.NullString
200
		var calls, rows_, totalExecTime, totalPlanTime, blkReadTime, blkWriteTime, sharedBlksHit, sharedBlksRead sql.NullFloat64
201
		var sharedBlksDirtied, sharedBlksWritten, localBlksHit, localBlksRead, localBlksDirtied, localBlksWritten sql.NullFloat64
202
		var tempBlksRead, tempBlksWritten, walRecords, walFPI, walBytes sql.NullFloat64
203

204
		if err := rows.Scan(&database, &user, &queryid, &query, &calls, &rows_, &totalExecTime, &totalPlanTime, &blkReadTime, &blkWriteTime, &sharedBlksHit, &sharedBlksRead,
205
			&sharedBlksDirtied, &sharedBlksWritten, &localBlksHit, &localBlksRead, &localBlksDirtied, &localBlksWritten, &tempBlksRead, &tempBlksWritten, &walRecords,
206
			&walFPI, &walBytes); err != nil {
207
			return err
208
		}
209
		databaseLabel := "unknown"
210
		if database.Valid {
211
			databaseLabel = database.String
212
		}
213
		userLabel := "unknown"
214
		if user.Valid {
215
			userLabel = user.String
216
		}
217
		queryidLabel := "unknown"
218
		if queryid.Valid {
219
			queryidLabel = queryid.String
220
		}
221
		queryLabel := "unknown"
222
		if query.Valid {
223
			queryLabel = query.String
224
		}
225

226
		QueryInfoMetric := 1.0
227
		ch <- prometheus.MustNewConstMetric(statSTatementsQueryInfo, prometheus.CounterValue, QueryInfoMetric, databaseLabel, userLabel, queryidLabel, queryLabel)
228

229
		callsMetric := 0.0
230
		if calls.Valid {
231
			callsMetric = float64(calls.Float64)
232
		}
233
		ch <- prometheus.MustNewConstMetric(statSTatementsCalls, prometheus.CounterValue, callsMetric, databaseLabel, userLabel, queryidLabel)
234

235
		rowsMetric := 0.0
236
		if rows_.Valid {
237
			rowsMetric = float64(rows_.Float64)
238
		}
239
		ch <- prometheus.MustNewConstMetric(statSTatementsRowsTotal, prometheus.CounterValue, rowsMetric, databaseLabel, userLabel, queryidLabel)
240

241
		// total = planning + execution; execution already includes io time.
242
		totalExecTimeMetric := 0.0
243
		if totalExecTime.Valid {
244
			if totalPlanTime.Valid {
245
				totalExecTimeMetric = float64(totalPlanTime.Float64) + float64(totalExecTime.Float64)
246
			}
247
		}
248
		ch <- prometheus.MustNewConstMetric(statSTatementsAllTimes, prometheus.CounterValue, totalExecTimeMetric, databaseLabel, userLabel, queryidLabel)
249

250
		totalPlanTimeMetric := 0.0
251
		if totalPlanTime.Valid {
252
			totalPlanTimeMetric = float64(totalPlanTime.Float64)
253
		}
254

255
		ch <- prometheus.MustNewConstMetric(statSTatementsTimes, prometheus.CounterValue, totalPlanTimeMetric, databaseLabel, userLabel, queryidLabel, "planning")
256
		// execution time = execution - io times.
257
		executingeMetric := 0.0
258

259
		if totalExecTime.Valid {
260
			if blkReadTime.Valid {
261
				if blkWriteTime.Valid {
262
					executingeMetric = float64(totalExecTime.Float64) - (float64(blkReadTime.Float64) + float64(blkWriteTime.Float64))
263
				}
264
			}
265
		}
266
		ch <- prometheus.MustNewConstMetric(statSTatementsTimes, prometheus.CounterValue, executingeMetric, databaseLabel, userLabel, queryidLabel, "executing")
267

268
		// avoid metrics spamming and send metrics only if they greater than zero.
269
		blkReadTimeMetric := 0.0
270
		if blkReadTime.Valid {
271
			blkReadTimeMetric = float64(blkReadTime.Float64)
272
		}
273
		if blkReadTimeMetric > 0 {
274
			ch <- prometheus.MustNewConstMetric(statSTatementsTimes, prometheus.CounterValue, blkReadTimeMetric, databaseLabel, userLabel, queryidLabel, "ioread")
275
		}
276
		blkWriteTimeMetric := 0.0
277
		if blkWriteTime.Valid {
278
			blkWriteTimeMetric = float64(blkWriteTime.Float64)
279
		}
280
		if blkWriteTimeMetric > 0 {
281
			ch <- prometheus.MustNewConstMetric(statSTatementsTimes, prometheus.CounterValue, blkWriteTimeMetric, databaseLabel, userLabel, queryidLabel, "iowrite")
282
		}
283
		sharedBlksHitMetric := 0.0
284
		if sharedBlksHit.Valid {
285
			sharedBlksHitMetric = float64(sharedBlksHit.Float64)
286
		}
287
		if sharedBlksHitMetric > 0 {
288
			ch <- prometheus.MustNewConstMetric(statSTatementsSharedBuffersHitTotal, prometheus.CounterValue, sharedBlksHitMetric, databaseLabel, userLabel, queryidLabel)
289
		}
290
		sharedBlksReadMetric := 0.0
291
		if sharedBlksRead.Valid {
292
			sharedBlksReadMetric = float64(sharedBlksRead.Float64)
293
		}
294
		if sharedBlksReadMetric > 0 {
295
			ch <- prometheus.MustNewConstMetric(statSTatementSharedBuffersReadBytesTotal, prometheus.CounterValue, sharedBlksReadMetric, databaseLabel, userLabel, queryidLabel)
296
		}
297
		sharedBlksDirtiedMetric := 0.0
298
		if sharedBlksDirtied.Valid {
299
			sharedBlksDirtiedMetric = float64(sharedBlksDirtied.Float64)
300
		}
301
		if sharedBlksDirtiedMetric > 0 {
302
			ch <- prometheus.MustNewConstMetric(statSTatementsSharedBuffersDirtiedTotal, prometheus.CounterValue, sharedBlksDirtiedMetric, databaseLabel, userLabel, queryidLabel)
303
		}
304
		sharedBlksWrittenMetric := 0.0
305
		if sharedBlksWritten.Valid {
306
			sharedBlksWrittenMetric = float64(sharedBlksWritten.Float64)
307
		}
308
		if sharedBlksWrittenMetric > 0 {
309
			ch <- prometheus.MustNewConstMetric(statSTatementsSharedBuffersWrittenBytesTotal, prometheus.CounterValue, sharedBlksWrittenMetric, databaseLabel, userLabel, queryidLabel)
310
		}
311

312
		localBlksHitMetric := 0.0
313
		if localBlksHit.Valid {
314
			localBlksHitMetric = float64(localBlksHit.Float64)
315
		}
316
		if localBlksHitMetric > 0 {
317
			ch <- prometheus.MustNewConstMetric(statSTatementsLocalBuffersHitTotal, prometheus.CounterValue, localBlksHitMetric, databaseLabel, userLabel, queryidLabel)
318
		}
319
		localBlksReadMetric := 0.0
320
		if localBlksRead.Valid {
321
			localBlksReadMetric = float64(localBlksRead.Float64)
322
		}
323
		if localBlksReadMetric > 0 {
324
			ch <- prometheus.MustNewConstMetric(statSTatementsLocalBuffersReadBytesTotal, prometheus.CounterValue, localBlksReadMetric, databaseLabel, userLabel, queryidLabel)
325
		}
326
		localBlksDirtiedMetric := 0.0
327
		if localBlksDirtied.Valid {
328
			localBlksDirtiedMetric = float64(localBlksDirtied.Float64)
329
		}
330
		if localBlksDirtiedMetric > 0 {
331
			ch <- prometheus.MustNewConstMetric(statSTatementsLocalBuffersDirtiedTotal, prometheus.CounterValue, localBlksDirtiedMetric, databaseLabel, userLabel, queryidLabel)
332
		}
333
		localBlksWrittenMetric := 0.0
334
		if localBlksWritten.Valid {
335
			localBlksWrittenMetric = float64(localBlksWritten.Float64)
336
		}
337
		if localBlksReadMetric > 0 {
338
			ch <- prometheus.MustNewConstMetric(statSTatementsLocalBuffersWrittenBytesTotal, prometheus.CounterValue, localBlksWrittenMetric, databaseLabel, userLabel, queryidLabel)
339
		}
340
		tempBlksReadMetric := 0.0
341
		if tempBlksRead.Valid {
342
			tempBlksReadMetric = float64(tempBlksRead.Float64)
343
		}
344
		if tempBlksReadMetric > 0 {
345
			ch <- prometheus.MustNewConstMetric(statSTatementsTempReadBytesTotal, prometheus.CounterValue, tempBlksReadMetric, databaseLabel, userLabel, queryidLabel)
346
		}
347
		tempBlksWrittenMetric := 0.0
348
		if tempBlksWritten.Valid {
349
			tempBlksWrittenMetric = float64(tempBlksWritten.Float64)
350
		}
351
		if tempBlksWrittenMetric > 0 {
352
			ch <- prometheus.MustNewConstMetric(statSTatementsTempWrittenBytesTotal, prometheus.CounterValue, tempBlksWrittenMetric, databaseLabel, userLabel, queryidLabel)
353
		}
354

355
		walRecordsMetric := 0.0
356
		if walRecords.Valid {
357
			walRecordsMetric = float64(walRecords.Float64)
358
		}
359
		if walRecordsMetric > 0 {
360
			// WAL records
361
			ch <- prometheus.MustNewConstMetric(statSTatementsWalRecordsTotal, prometheus.CounterValue, walRecordsMetric, databaseLabel, userLabel, queryidLabel)
362
			// WAL total bytes
363
			if walFPI.Valid {
364
				if walBytes.Valid {
365
					walAllBytesMetric := (float64(walFPI.Float64) * blockSize) + float64(walBytes.Float64)
366
					ch <- prometheus.MustNewConstMetric(statSTatementsWalBytesAllTotal, prometheus.CounterValue, walAllBytesMetric, databaseLabel, userLabel, queryidLabel)
367
					walBytesMetric := (float64(walFPI.Float64) * blockSize)
368
					ch <- prometheus.MustNewConstMetric(statSTatementsWalWalBytesTotal, prometheus.CounterValue, walBytesMetric, databaseLabel, userLabel, queryidLabel, "fpi")
369
					ch <- prometheus.MustNewConstMetric(statSTatementsWalWalBytesTotal, prometheus.CounterValue, float64(walFPI.Float64), databaseLabel, userLabel, queryidLabel, "regular")
370

371
				}
372
			}
373

374
		}
375

376
	}
377
	if err := rows.Err(); err != nil {
378
		return err
379
	}
380
	return nil
381

382
}
383