[Question] Multiple Values Usage

[bruhtus]

Hello there,
I am new to prometheus exporter in general and trying to implement sql exporter with query that return multiple values.

I tried with this collector metrics:

collector_name: postgresql

metrics:
  - metric_name: participants_weekly
    type: gauge
    help: 'Total participants this week'
    value_label: 'participants'
    values:
      - weekly
      - total_participants
    query: |
      select
      date_trunc('week', to_timestamp(created_at/1000)) as weekly,
      count(*) as total_participants
      from participants
      where attendance_status ilike 'attended'
      group by weekly
      order by weekly desc
      limit 1;

and then when i run curl localhost:9399/metrics, the metrics does not appear. I also try adding another metrics but only the above metrics does not appear.

So i want to ask, how do we use value label? or to be precise, how do we use multiple values with sql exporter?
Thank you!

[burningalchemist]

Hi @bruhtus, briefly looking over the snippet, I'd say the configuration seems correct. Expected output when using value_label in this scenario would be like that:

participants_weekly{participants="weekly"} 10
participants_weekly{participants="total_participants"} 100

Could you please share some logs? I'd expect some errors.

[bruhtus]

Hello @burningalchemist, thank you for the response!

I run the sql_exporter with these flags, in case this could help debugging:

./sql_exporter -config.file sql_exporter.yml -web.enable-reload

Is this the log you mentioned?

ts=2023-07-26T20:14:57.177Z caller=klog.go:108 level=warn func=Warningf msg="Starting SQL exporter (version=0.11.1, branch=HEAD, revision=6293d4bc7ffe9531c06779c49d9003b8c92082aa) (go=go1.20.4, platform=linux/amd64, user=root@7adcd09cb689, date=20230601-21:21:08, tags=netgo)"
ts=2023-07-26T20:14:57.178Z caller=klog.go:96 level=warn func=Warning msg="Listening on :9399"
ts=2023-07-26T20:14:57.178Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9399
ts=2023-07-26T20:14:57.178Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9399

The log above is after i run curl localhost:9399/metrics and i don't really see the change before and after i run curl localhost:9399/metrics, do i need to change the log level?

I also add a new query for comparison, the collector metrics would be like this:

collector_name: postgresql

metrics:
  - metric_name: db_size
    type: gauge
    help: 'Database size'
    values:
      - db_size
    query: |
      select pg_database_size('db-name') as db_size;

  - metric_name: participants_weekly
    type: gauge
    help: 'Total participants this weeks'
    value_label: 'participants'
    values:
      - weekly
      - total_participants
    query: |
      select
      date_trunc('week', to_timestamp(created_at/1000)) as weekly,
      count(*) as total_participants
      from participants
      where attendance_status ilike 'attended'
      group by weekly
      order by weekly desc
      limit 1;

and this is the screenshot when i run curl localhost:9399/metrics:

In case this could help debugging, this is my sql_exporter.yml:

# Global defaults.
global:
  # Subtracted from Prometheus' scrape_timeout to give us some headroom and prevent Prometheus from timing out first.
  scrape_timeout_offset: 500ms
  # Minimum interval between collector runs: by default (0s) collectors are executed on every scrape.
  min_interval: 0s
  # Maximum number of open connections to any one target. Metric queries will run concurrently on multiple connections,
  # as will concurrent scrapes.
  max_connections: 3
  # Maximum number of idle connections to any one target. Unless you use very long collection intervals, this should
  # always be the same as max_connections.
  max_idle_connections: 3
  # Maximum number of maximum amount of time a connection may be reused. Expired connections may be closed lazily before reuse.
  # If 0, connections are not closed due to a connection's age.
  max_connection_lifetime: 5m

# The target to monitor and the collectors to execute on it.
target:
  # Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)
  # the schema gets dropped or replaced to match the driver expected DSN format.
  data_source_name: 'postgresql://user:password@localhost:5432/db-name'

  # Collectors (referenced by name) to execute on the target.
  # Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
  collectors: [postgresql]

# Collector files specifies a list of globs. One collector definition is read from each matching file.
# Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
collector_files:
  - 'postgresql.collector.yml'

Let me know if you need another information.

[burningalchemist]

@bruhtus, thanks for all the information. Let's try to increase the log level with --log.level=debug. It hopefully should give us more information. If there's nothing new, please let me know. I'll try to reproduce it on my end. :)

[bruhtus]

@burningalchemist after increasing the log level to debug, now i see what's the problem. Seems like the metrics type and the query result does not match, here's the log:

ts=2023-07-26T22:04:21.079Z caller=klog.go:108 level=warn func=Warningf msg="Starting SQL exporter (version=0.11.1, branch=HEAD, revision=6293d4bc7ffe9531c06779c49d9003b8c92082aa) (go=go1.20.4, platform=linux/amd64, user=root@7adcd09cb689, date=20230601-21:21:08, tags=netgo)"
ts=2023-07-26T22:04:21.079Z caller=klog.go:84 level=debug func=Infof msg="Loading configuration from sql_exporter.yml"
ts=2023-07-26T22:04:21.080Z caller=klog.go:84 level=debug func=Infof msg="Loaded collector 'postgresql' from postgresql.collector.yml"
ts=2023-07-26T22:04:21.080Z caller=klog.go:96 level=warn func=Warning msg="Listening on :9399"
ts=2023-07-26T22:04:21.080Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9399
ts=2023-07-26T22:04:21.080Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9399
ts=2023-07-26T22:04:28.084Z caller=klog.go:84 level=debug func=Infof msg="Database handle successfully opened with 'postgres' driver"
ts=2023-07-26T22:04:28.099Z caller=klog.go:55 level=debug func=Verbose.Infof msg="returned_columns=\"[weekly total_participants]\"collector=\"postgresql\", query=\"participants_weekly\""
ts=2023-07-26T22:04:28.119Z caller=klog.go:55 level=debug func=Verbose.Infof msg="returned_columns=\"[db_size]\"collector=\"postgresql\", query=\"db_size\""
ts=2023-07-26T22:04:28.119Z caller=klog.go:84 level=debug func=Infof msg="Error gathering metrics: [from Gatherer #1] [collector=\"postgresql\", query=\"participants_weekly\"] scanning of query result failed: sql: Scan error on column index 0, name \"weekly\": converting driver.Value type time.Time (\"2023-07-17 00:00:00 +0700 WIB\") to a float64: invalid syntax"

After i changed the query, now the metrics appear, like this:

# HELP db_size Database size
# TYPE db_size gauge
db_size 2.8705571e+07
# HELP participants_weekly Total participants this weeks
# TYPE participants_weekly gauge
participants_weekly{participants="total_participants"} 1
participants_weekly{participants="weekly"} 1.689659744063e+12

I am not sure if it's still the same topics, let me know if you think i should open a new issue.
Now what i am curious is that, can we only assign one value to a variable?
For example, with this query:

      select
      created_at as weekly,
      count(*) as total_participants
      from participants
      where attendance_status ilike 'attended'
      group by weekly
      order by weekly desc
      limit 1;

we only get one value for weekly and total_participants, if we increase the limit to 2, what would happen? As we get 2 value for weekly and total_participants.

Anyway, thank you for your help!

[burningalchemist]

Hey @bruhtus! Ah yeah, I just noticed that the weekly result from the query was a timestamp. To become a metric value, the results should be of integer or float. 👍

As for multiple results (limit > 1), we might need to add some information to the metric entry so they aren't duplicated and overlapped. For example, you can add the timestamp column (or a team name, id, whatever) to the key_labels list. So the result would look like this:

participants_weekly{timestamp="2023-07-17 00:00:00 +0700 WIB",participants="total_participants"} 1
participants_weekly{timestamp="2023-07-17 00:00:00 +0700 WIB",participants="weekly"} 1.689659744063e+12
participants_weekly{timestamp="2023-07-16 00:00:00 +0700 WIB",participants="total_participants"} 1
participants_weekly{timestamp="2023-07-16 00:00:00 +0700 WIB",participants="weekly"} 1.789659744063e+12

Let me know if it helps.
Cheers!

[burningalchemist]

Also, I'll move it to Discussions if you don't mind. 🙂👍