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 statStatementsSubsystem = "stat_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(statStatementsSubsystem, defaultDisabled, NewPGStatStatementsCollector)
32
}
33

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

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

42
var (
43
	statSTatementsCallsTotal = prometheus.NewDesc(
44
		prometheus.BuildFQName(namespace, statStatementsSubsystem, "calls_total"),
45
		"Number of times executed",
46
		[]string{"user", "datname", "queryid"},
47
		prometheus.Labels{},
48
	)
49
	statStatementsSecondsTotal = prometheus.NewDesc(
50
		prometheus.BuildFQName(namespace, statStatementsSubsystem, "seconds_total"),
51
		"Total time spent in the statement, in seconds",
52
		[]string{"user", "datname", "queryid"},
53
		prometheus.Labels{},
54
	)
55
	statStatementsRowsTotal = prometheus.NewDesc(
56
		prometheus.BuildFQName(namespace, statStatementsSubsystem, "rows_total"),
57
		"Total number of rows retrieved or affected by the statement",
58
		[]string{"user", "datname", "queryid"},
59
		prometheus.Labels{},
60
	)
61
	statStatementsBlockReadSecondsTotal = prometheus.NewDesc(
62
		prometheus.BuildFQName(namespace, statStatementsSubsystem, "block_read_seconds_total"),
63
		"Total time the statement spent reading blocks, in seconds",
64
		[]string{"user", "datname", "queryid"},
65
		prometheus.Labels{},
66
	)
67
	statStatementsBlockWriteSecondsTotal = prometheus.NewDesc(
68
		prometheus.BuildFQName(namespace, statStatementsSubsystem, "block_write_seconds_total"),
69
		"Total time the statement spent writing blocks, in seconds",
70
		[]string{"user", "datname", "queryid"},
71
		prometheus.Labels{},
72
	)
73

74
	pgStatStatementsQuery = `SELECT
75
		pg_get_userbyid(userid) as user,
76
		pg_database.datname,
77
		pg_stat_statements.queryid,
78
		pg_stat_statements.calls as calls_total,
79
		pg_stat_statements.total_time / 1000.0 as seconds_total,
80
		pg_stat_statements.rows as rows_total,
81
		pg_stat_statements.blk_read_time / 1000.0 as block_read_seconds_total,
82
		pg_stat_statements.blk_write_time / 1000.0 as block_write_seconds_total
83
		FROM pg_stat_statements
84
	JOIN pg_database
85
		ON pg_database.oid = pg_stat_statements.dbid
86
	WHERE
87
		total_time > (
88
		SELECT percentile_cont(0.1)
89
			WITHIN GROUP (ORDER BY total_time)
90
			FROM pg_stat_statements
91
		)
92
	ORDER BY seconds_total DESC
93
	LIMIT 100;`
94

95
	pgStatStatementsNewQuery = `SELECT
96
		pg_get_userbyid(userid) as user,
97
		pg_database.datname,
98
		pg_stat_statements.queryid,
99
		pg_stat_statements.calls as calls_total,
100
		pg_stat_statements.total_exec_time / 1000.0 as seconds_total,
101
		pg_stat_statements.rows as rows_total,
102
		pg_stat_statements.blk_read_time / 1000.0 as block_read_seconds_total,
103
		pg_stat_statements.blk_write_time / 1000.0 as block_write_seconds_total
104
		FROM pg_stat_statements
105
	JOIN pg_database
106
		ON pg_database.oid = pg_stat_statements.dbid
107
	WHERE
108
		total_exec_time > (
109
		SELECT percentile_cont(0.1)
110
			WITHIN GROUP (ORDER BY total_exec_time)
111
			FROM pg_stat_statements
112
		)
113
	ORDER BY seconds_total DESC
114
	LIMIT 100;`
115
)
116

117
func (PGStatStatementsCollector) Update(ctx context.Context, instance *instance, ch chan<- prometheus.Metric) error {
118
	query := pgStatStatementsQuery
119
	if instance.version.GE(semver.MustParse("13.0.0")) {
120
		query = pgStatStatementsNewQuery
121
	}
122

123
	db := instance.getDB()
124
	rows, err := db.QueryContext(ctx, query)
125

126
	if err != nil {
127
		return err
128
	}
129
	defer rows.Close()
130
	for rows.Next() {
131
		var user, datname, queryid sql.NullString
132
		var callsTotal, rowsTotal sql.NullInt64
133
		var secondsTotal, blockReadSecondsTotal, blockWriteSecondsTotal sql.NullFloat64
134

135
		if err := rows.Scan(&user, &datname, &queryid, &callsTotal, &secondsTotal, &rowsTotal, &blockReadSecondsTotal, &blockWriteSecondsTotal); err != nil {
136
			return err
137
		}
138

139
		userLabel := "unknown"
140
		if user.Valid {
141
			userLabel = user.String
142
		}
143
		datnameLabel := "unknown"
144
		if datname.Valid {
145
			datnameLabel = datname.String
146
		}
147
		queryidLabel := "unknown"
148
		if queryid.Valid {
149
			queryidLabel = queryid.String
150
		}
151

152
		callsTotalMetric := 0.0
153
		if callsTotal.Valid {
154
			callsTotalMetric = float64(callsTotal.Int64)
155
		}
156
		ch <- prometheus.MustNewConstMetric(
157
			statSTatementsCallsTotal,
158
			prometheus.CounterValue,
159
			callsTotalMetric,
160
			userLabel, datnameLabel, queryidLabel,
161
		)
162

163
		secondsTotalMetric := 0.0
164
		if secondsTotal.Valid {
165
			secondsTotalMetric = secondsTotal.Float64
166
		}
167
		ch <- prometheus.MustNewConstMetric(
168
			statStatementsSecondsTotal,
169
			prometheus.CounterValue,
170
			secondsTotalMetric,
171
			userLabel, datnameLabel, queryidLabel,
172
		)
173

174
		rowsTotalMetric := 0.0
175
		if rowsTotal.Valid {
176
			rowsTotalMetric = float64(rowsTotal.Int64)
177
		}
178
		ch <- prometheus.MustNewConstMetric(
179
			statStatementsRowsTotal,
180
			prometheus.CounterValue,
181
			rowsTotalMetric,
182
			userLabel, datnameLabel, queryidLabel,
183
		)
184

185
		blockReadSecondsTotalMetric := 0.0
186
		if blockReadSecondsTotal.Valid {
187
			blockReadSecondsTotalMetric = blockReadSecondsTotal.Float64
188
		}
189
		ch <- prometheus.MustNewConstMetric(
190
			statStatementsBlockReadSecondsTotal,
191
			prometheus.CounterValue,
192
			blockReadSecondsTotalMetric,
193
			userLabel, datnameLabel, queryidLabel,
194
		)
195

196
		blockWriteSecondsTotalMetric := 0.0
197
		if blockWriteSecondsTotal.Valid {
198
			blockWriteSecondsTotalMetric = blockWriteSecondsTotal.Float64
199
		}
200
		ch <- prometheus.MustNewConstMetric(
201
			statStatementsBlockWriteSecondsTotal,
202
			prometheus.CounterValue,
203
			blockWriteSecondsTotalMetric,
204
			userLabel, datnameLabel, queryidLabel,
205
		)
206
	}
207
	if err := rows.Err(); err != nil {
208
		return err
209
	}
210
	return nil
211
}
212