1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
|
# Prometheus SQL Exporter [](https://travis-ci.org/justwatchcom/sql_exporter)
[](https://hub.docker.com/r/justwatch/sql_exporter)
[](https://goreportcard.com/report/github.com/justwatchcom/sql_exporter)
This repository contains a service that runs user-defined SQL queries at flexible intervals and exports the resulting metrics via HTTP for Prometheus consumption.
Status
======
Actively used with PostgreSQL in production. We'd like to eventually support all databases for which stable Go database [drivers](https://github.com/golang/go/wiki/SQLDrivers) are available. Contributions welcome.
Currently supported:
- Postgres
- ClickHouse
- AWS Athena
- MS-SQL
- MySQL
- Snowflake
- Vertica
What does it look like?
=======================
Getting Started
===============
Create a _config.yml_ and run the service:
```
go get github.com/justwatchcom/sql_exporter
cp config.yml.dist config.yml
./prom-sql-exporter
```
Running in Docker:
```bash
docker run \
-v `pwd`/config.yml:/config/config.yml \
-e CONFIG=/config/config.yml \
-d \
-p 9237:9237 \
--name sql_exporter \
justwatch/sql_exporter
```
Manual `scrape_configs` snippet:
```yaml
scrape_configs:
- job_name: sql_exporter
static_configs:
- targets: ['localhost:9237']
```
Flags
-----
Name | Description
--------|------------
`version` | Print version information
`web.listen-address` | Address to listen on for web interface and telemetry
`web.telemetry-path` | Path under which to expose metrics
`config.file` | SQL Exporter configuration file name
Environment Variables
---------------------
Name | Description
--------|------------
`CONFIG` | Location of Configuration File (yaml)
Usage
=====
We recommend to deploy and run the SQL exporter in Kubernetes.
Kubernetes
----------
See [examples/kubernetes](https://github.com/justwatchcom/sql_exporter/tree/master/examples/kubernetes).
Grafana
-------
See [examples/grafana](https://github.com/justwatchcom/sql_exporter/tree/master/examples/grafana).
Prometheus
----------
Example recording and alerting rules are available in [examples/prometheus](https://github.com/justwatchcom/sql_exporter/tree/master/examples/prometheus).
Configuration
-------------
When writing queries for this exporter please keep in mind that Prometheus data
model assigns exactly one `float` to a metric, possibly further identified by a
set of zero or more labels. These labels need to be of type `string` or `text`.
If your SQL dialect supports explicit type casts, you should always cast your
label columns to `text` and the metric colums to `float`. The SQL exporter will
try hard to support other types or drivers w/o support for explicit cast as well,
but the results may not be what you expect.
Below is a documented configuration example showing all available options.
For a more realistic example please have a look at [examples/kubernetes/configmap.yml](https://github.com/justwatchcom/sql_exporter/blob/master/examples/kubernetes/configmap.yml).
```yaml
---
# jobs is a map of jobs, define any number but please keep the connection usage on the DBs in mind
jobs:
# each job needs a unique name, it's used for logging and as an default label
- name: "example"
# interval defined the pause between the runs of this job
interval: '5m'
# cron_schedule when to execute the job in the standard CRON syntax
# if specified, the interval is ignored
cron_schedule: "0 0 * * *"
# connections is an array of connection URLs
# each query will be executed on each connection
connections:
- 'postgres://postgres@localhost/postgres?sslmode=disable'
# startup_sql is an array of SQL statements
# each statements is executed once after connecting
startup_sql:
- 'SET lock_timeout = 1000'
- 'SET idle_in_transaction_session_timeout = 100'
# queries is a map of Metric/Query mappings
queries:
# name is prefied with sql_ and used as the metric name
- name: "running_queries"
# help is a requirement of the Prometheus default registry, currently not
# used by the Prometheus server. Important: Must be the same for all metrics
# with the same name!
help: "Number of running queries"
# Labels is an array of columns which will be used as additional labels.
# Must be the same for all metrics with the same name!
# All labels columns should be of type text, varchar or string
labels:
- "datname"
- "usename"
# Values is an array of columns used as metric values. All values should be
# of type float
values:
- "count"
# Query is the SQL query that is run unalterted on the each of the connections
# for this job
query: |
SELECT datname::text, usename::text, COUNT(*)::float AS count
FROM pg_stat_activity GROUP BY datname, usename;
# Consider the query failed if it returns zero rows
allow_zero_rows: false
```
Running as non-superuser on PostgreSQL
--------------------------------------
Some queries require superuser privileges on PostgreSQL.
If you prefer not to run the exporter with superuser privileges, you can use some views/functions to get around this limitation.
```sql
CREATE USER postgres_exporter PASSWORD 'pw';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;
CREATE SCHEMA postgres_exporter AUTHORIZATION postgres_exporter;
CREATE FUNCTION postgres_exporter.f_select_pg_stat_activity()
RETURNS setof pg_catalog.pg_stat_activity
LANGUAGE sql
SECURITY DEFINER
AS $$
SELECT * from pg_catalog.pg_stat_activity;
$$;
CREATE FUNCTION postgres_exporter.f_select_pg_stat_replication()
RETURNS setof pg_catalog.pg_stat_replication
LANGUAGE sql
SECURITY DEFINER
AS $$
SELECT * from pg_catalog.pg_stat_replication;
$$;
CREATE VIEW postgres_exporter.pg_stat_replication
AS
SELECT * FROM postgres_exporter.f_select_pg_stat_replication();
CREATE VIEW postgres_exporter.pg_stat_activity
AS
SELECT * FROM postgres_exporter.f_select_pg_stat_activity();
GRANT SELECT ON postgres_exporter.pg_stat_replication TO postgres_exporter;
GRANT SELECT ON postgres_exporter.pg_stat_activity TO postgres_exporter;
```
Logging
-------
You can change the loglevel by setting the `LOGLEVEL` variable in the exporters
environment.
```
LOGLEVEL=info ./sql_exporter
```
Why this exporter exists
========================
The other projects with similar goals did not meet our requirements on either
maturity or flexibility. This exporter does not rely on any other service and
runs in production for some time already.
License
=======
MIT License
|
