Postgresql_exporter

[IndGirl6]

Has any one successfully implemented monitoring of postgresql DB?

can you share your Queries.yml file?

I got the sample from "https://github.com/wrouesnel/postgres_exporter/blob/master/queries.yaml"

but looking to scrape more metrics.

[Ben Kochie]

We monitor our postgresql databases with this exporter. You can find a number of good examples here:

https://gitlab.com/gitlab-cookbooks/gitlab-exporters/blob/master/files/default/postgres_exporter/queries.yaml

As noted in the file, we don't use the postgres_exporter to monitor our application-specific metrics.  We use a separate custom SQL exporter for this.

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-use...@googlegroups.com.
To post to this group, send email to promethe...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/44cd2b53-bfec-4f82-a4f0-a7aff5b59248%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[IndGirl6]

Thanks a lot for sharing this Ben!

Do you use Grafana to graph the metrics? If so, would you be able to share your dash?

I installed a bunch of dash from grafana, and combined all the unique graphs to create one dash, but my DBA's said its not very useful to them. 

What would be more helpful to them  would be to see stats like the following:

1) Seeing which users are connected (on the active session graph)

2) a graph or display showing the top 10 Queries using max CPU / Mem / IO etc..

does any one know how we can graph that?

[Ben Kochie]

Yes, we use Grafana.

All of our dashboards are on https://monitor.gitlab.net/, you can search for the postgres tag.

--

You received this message because you are subscribed to the Google Groups "Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-use...@googlegroups.com.
To post to this group, send email to promethe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/c17bf2ab-8e37-44b7-bc4e-08530d4acf39%40googlegroups.com.

[IndGirl6]

Thanks a LOT Ben!!

This is very useful stuff! You have the exact dash i was looking for with the top queries.

Were you able to get that running with the queries from the queries.yml file from gitlab?

[Ben Kochie]

Yes, the top queries comes from pg_stat_statements[0]. You might need to enable this in postgres.

[0]: https://www.postgresql.org/docs/current/static/pgstatstatements.html

To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/8be9dfdc-5cfc-46a2-a4fd-89142d5cf3cd%40googlegroups.com.

[Kavita]

Thanks again Ben. I will try this out on Monday.

[IndGirl6]

Hi Ben,

Sorry to be a bother again, i included the "pg_stat_statement" in the queries.yml file, had the DBA enable/ install the extension for pg_stat_statements. When i run the postgres_exporter, i see the following messages which i believe indicates that the config is OK(?):

time="2018-06-26T09:33:07-04:00" level=debug msg="Querying namespace:  pg_stat_statements" source="postgres_exporter.go:888"

time="2018-06-26T09:33:22-04:00" level=debug msg="Querying namespace:  pg_stat_statements" source="postgres_exporter.go:888"

However, on the metrics page, i dont see those metrics being collected, i searched for the following keyworks on the metrics page (to name a few):

pg_stat_statements_calls

pg_stat_statements_time_milliseconds

pg_stat_statements_shared_blks_dirtied

Below is what i put in the queries.yml file:

pg_stat_statements:

  query: " SELECT userid, dbid, queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time FROM pg_stat_statements"

  metrics:

    - userid:

        usage: "LABEL"

        description: "OID of user who executed the statement"

    - dbid:

        usage: "LABEL"

        description: "OID of database in which the statement was executed"

    - queryid:

        usage: "LABEL"

        description: "Internal hash code, computed from the statement's parse tree"

    - query:

        usage: "LABEL"

        description: "Text of a representative statement"

    - calls:

        usage: "LABEL"

        description: "Number of times executed"

    - total_time:

        usage: "LABEL"

        description: "Total time spent in the statement, in milliseconds"

    - min_time:

        usage: "LABEL"

        description: "Minimum time spent in the statement, in milliseconds"

    - max_time:

        usage: "LABEL"

        description: "Maximum time spent in the statement, in milliseconds"

    - mean_time:

        usage: "LABEL"

        description: "Mean time spent in the statement, in milliseconds"

    - stddev_time:

        usage: "LABEL"

        description: "Population standard deviation of time spent in the statement, in milliseconds"

    - rows:

        usage: "LABEL"

        description: "Total number of rows retrieved or affected by the statement"

    - shared_blks_hit:

        usage: "LABEL"

        description: "Total number of shared block cache hits by the statement"

    - shared_blks_read:

        usage: "LABEL"

        description: "Total number of shared blocks read by the statement"

    - shared_blks_dirtied:

        usage: "LABEL"

        description: "Total number of shared blocks dirtied by the statement"

    - shared_blks_written:

        usage: "LABEL"

        description: "Total number of shared blocks written by the statement"

    - local_blks_hit:

        usage: "LABEL"

        description: "Total number of local block cache hits by the statement"

    - local_blks_read:

        usage: "LABEL"

        description: "Total number of local blocks read by the statement"

    - local_blks_dirtied:

        usage: "LABEL"

        description: "Total number of local blocks dirtied by the statement"

    - local_blks_written:

        usage: "LABEL"

        description: "Total number of local blocks written by the statement"

    - temp_blks_read:

        usage: "LABEL"

        description: "Total number of temp blocks read by the statement"

    - temp_blks_written:

        usage: "LABEL"

        description: "Total number of temp blocks written by the statement"

    - blk_read_time:

        usage: "LABEL"

        description: "Total time the statement spent reading blocks in milliseconds if track_io_timing is enabled otherwise zero"

    - blk_write_time:

        usage: "LABEL"

        description: "Total time the statement spent writing blocks in milliseconds if track_io_timing is enabled otherwise zero"

[Ben Kochie]

It doesn't like you're using the exact query we're using in our config. I'm no expert in these postgres metrics, so I don't know how your query differences would impact the results.

The easiest way to debug is to use `curl` to query the postgres exporter directly and look at the output to see if the results are as you would expect.

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-use...@googlegroups.com.
To post to this group, send email to promethe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/c6d730ae-9e22-4e52-92fd-5bff3360a5ac%40googlegroups.com.