Expose query duration via histogram

The following PR introduces histogram for query duration. Doing so allow to use sql_exporter for synthetic monitoring. A user could the following configuration

misc:
  histogram_buckets:
    - .25
    - .5
    - 1
    - 2
    - 4
jobs:
  - connections:
      - postgres://localhost:5432/canary?sslmode=require
    interval: 1s
    name: mydb
    queries:
      - allow_zero_rows: true
        help: ""
        name: delete_from_canary
        query: delete from canary_check;
        values: []
      - allow_zero_rows: true
        help: ""
        name: insert_into_canary
        query: insert into canary_check values(now()) on conflict do nothing;
        values: []
      - allow_zero_rows: true
        help: ""
        name: select_from_canary
        query: select 1 as up from canary_check;
        values:
          - up
    startup_sql:
      - SET lock_timeout = 1000
      - SET idle_in_transaction_session_timeout = 100

The resulting histogram looks like this

sql_exporter_queries_total{query="delete_from_canary",sql_job="mydb"} 550
sql_exporter_queries_total{query="insert_into_canary",sql_job="mydb"} 550
sql_exporter_queries_total{query="select_from_canary",sql_job="mydb"} 550
sql_exporter_query_duration_seconds_bucket{query="delete_from_canary",sql_job="mydb",le="0.25"} 550
sql_exporter_query_duration_seconds_bucket{query="delete_from_canary",sql_job="mydb",le="0.5"} 550
sql_exporter_query_duration_seconds_bucket{query="delete_from_canary",sql_job="mydb",le="1"} 550
sql_exporter_query_duration_seconds_bucket{query="delete_from_canary",sql_job="mydb",le="2"} 550
sql_exporter_query_duration_seconds_bucket{query="delete_from_canary",sql_job="mydb",le="4"} 550
sql_exporter_query_duration_seconds_bucket{query="delete_from_canary",sql_job="mydb",le="+Inf"} 550
sql_exporter_query_duration_seconds_sum{query="delete_from_canary",sql_job="mydb"} 5.9034834720000005
sql_exporter_query_duration_seconds_count{query="delete_from_canary",sql_job="mydb"} 550
sql_exporter_query_duration_seconds_bucket{query="insert_into_canary",sql_job="mydb",le="0.25"} 550
sql_exporter_query_duration_seconds_bucket{query="insert_into_canary",sql_job="mydb",le="0.5"} 550
sql_exporter_query_duration_seconds_bucket{query="insert_into_canary",sql_job="mydb",le="1"} 550
sql_exporter_query_duration_seconds_bucket{query="insert_into_canary",sql_job="mydb",le="2"} 550
sql_exporter_query_duration_seconds_bucket{query="insert_into_canary",sql_job="mydb",le="4"} 550
sql_exporter_query_duration_seconds_bucket{query="insert_into_canary",sql_job="mydb",le="+Inf"} 550
sql_exporter_query_duration_seconds_sum{query="insert_into_canary",sql_job="mydb"} 1.3082169589999986
sql_exporter_query_duration_seconds_count{query="insert_into_canary",sql_job="mydb"} 550
sql_exporter_query_duration_seconds_bucket{query="select_from_canary",sql_job="mydb",le="0.25"} 550
sql_exporter_query_duration_seconds_bucket{query="select_from_canary",sql_job="mydb",le="0.5"} 550
sql_exporter_query_duration_seconds_bucket{query="select_from_canary",sql_job="mydb",le="1"} 550
sql_exporter_query_duration_seconds_bucket{query="select_from_canary",sql_job="mydb",le="2"} 550
sql_exporter_query_duration_seconds_bucket{query="select_from_canary",sql_job="mydb",le="4"} 550
sql_exporter_query_duration_seconds_bucket{query="select_from_canary",sql_job="mydb",le="+Inf"} 550
sql_exporter_query_duration_seconds_sum{query="select_from_canary",sql_job="mydb"} 0.7355683239999996
sql_exporter_query_duration_seconds_count{query="select_from_canary",sql_job="mydb"} 550
sql_select_from_canary{col="up",database="canary_postgresql",driver="postgres",host="localhost:5432",sql_job="mydb",user="someuser"} 1

This is of great help to monitor the latency from a client point of view and build SLO around that. See also: https://prometheus.io/docs/practices/histograms/

justwatchcom / sql_exporter

Expose query duration via histogram #121