collected metric ... was collected before with the same name and label values

[leshik]

Hello,

I've added pg_stat_statements metrics as per #35 and now some nodes report with errors like this:

...
collected metric pg_stat_statements_calls label:<name:"query" value:"SELECT  \"ar_internal_metadata\".* FROM \"ar_internal_metadata\" WHERE \"ar_internal_metadata\".\"key\" = $1 LIMIT $2" > counter:<value:1 >  was collected before with the same name and label values
...

Some others report normally though. What might be the reason?

[keithf4]

I think this may have to do with the privileges of the monitoring role. Not every non-superuser role can read all the fields in pg_stat_statements for security reasons. So it causes some of the fields that would distinguish a values as being unique to no longer be unique since it's left as NULL.

I've also found that trying to collect pg_stat_statements queries into prometheus (or any TSDB) can cause the storage requirements to increase drastically, especially if you have large queries. Prometheus isn't really made for storing large, non-repetitive text values like pg_stat_statements can produce efficiently. It's storing the entire query statement for each individual time interval collected.

[leshik]

Neither is true in my case – postgres exporter is accesing the database using the superuser (postgres), the database itself is quite small (custom dump is about 27 Mb, compressed), and requests aren’t heavy.

[keithf4]

I don't mean the size of the postgres database, I mean the size of the prometheus data store.

You may need to add additional labels then to make each collection metric more unique then. I don't see a dbname or user label in there, so if you ran the same query on multiple databases or by different users, those would be distinct rows in pg_stat_statements, but appear identical if those columns aren't separated out as different labels.

[srimaln91]

I'm experiencing the same issue. Please let me know if anyone has found a solution for this.

[keithf4]

As I said above, check the values coming back vs the labels you have set. You may need to add additional labels to make similar values distinct. Every time I've come across this so far, it was a valid error message that I had to correct myself with proper labeling.

[trezorg]

https://github.com/wrouesnel/postgres_exporter/blob/master/queries.yaml#L129
There should be grouping by LABEL(query) as there are possible duplicate rows from pg_stat_statements for same query.
I use next snippet:

pg_stat_statements:
  query: "SELECT query, sum(calls), sum(total_time) as total_time, min(min_time) as min_time, max(max_time) as max_time, sum(mean_time*calls) / sum(calls) as mean_time, avg(stddev_time) as stddev_time, avg(rows) as rows, avg(shared_blks_hit) as shared_blks_hit, avg(shared_blks_read) as shared_blks_read, avg(shared_blks_dirtied) as shared_blks_dirtied, avg(shared_blks_written) as shared_blks_written, avg(local_blks_hit) as local_blks_hit, avg(local_blks_read) as local_blks_read, avg(local_blks_dirtied) as local_blks_dirtied, avg(local_blks_written) as local_blks_written, avg(temp_blks_read) as temp_blks_read, avg(temp_blks_written) as temp_blks_written, avg(blk_read_time) as blk_read_time, avg(blk_write_time) as blk_write_time FROM pg_stat_statements group by query"
  metrics:
    - query:
        usage: "LABEL"
        description: "Query class"
    - calls:
        usage: "COUNTER"
        description: "Number of times executed"
    - total_time:
        usage: "COUNTER"
        description: "Total time spent in the statement, in milliseconds"
    - min_time:
        usage: "GAUGE"
        description: "Minimum time spent in the statement, in milliseconds"
    - max_time:
        usage: "GAUGE"
        description: "Maximum time spent in the statement, in milliseconds"
    - mean_time:
        usage: "GAUGE"
        description: "Mean time spent in the statement, in milliseconds"
    - stddev_time:
        usage: "GAUGE"
        description: "Population standard deviation of time spent in the statement, in milliseconds"
    - rows:
        usage: "COUNTER"
        description: "Total number of rows retrieved or affected by the statement"
    - shared_blks_hit:
        usage: "COUNTER"
        description: "Total number of shared block cache hits by the statement"
    - shared_blks_read:
        usage: "COUNTER"
        description: "Total number of shared blocks read by the statement"
    - shared_blks_dirtied:
        usage: "COUNTER"
        description: "Total number of shared blocks dirtied by the statement"
    - shared_blks_written:
        usage: "COUNTER"
        description: "Total number of shared blocks written by the statement"
    - local_blks_hit:
        usage: "COUNTER"
        description: "Total number of local block cache hits by the statement"
    - local_blks_read:
        usage: "COUNTER"
        description: "Total number of local blocks read by the statement"
    - local_blks_dirtied:
        usage: "COUNTER"
        description: "Total number of local blocks dirtied by the statement"
    - local_blks_written:
        usage: "COUNTER"
        description: "Total number of local blocks written by the statement"
    - temp_blks_read:
        usage: "COUNTER"
        description: "Total number of temp blocks read by the statement"
    - temp_blks_written:
        usage: "COUNTER"
        description: "Total number of temp blocks written by the statement"
    - blk_read_time:
        usage: "COUNTER"
        description: "Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"
    - blk_write_time:
        usage: "COUNTER"
        description: "Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"

[amenzhinsky]

Had to add grouping to the query from queries.yml plus adapt it to pg 14.0:

SELECT t2.rolname, t3.datname, queryid, SUM(calls) AS calls, SUM(total_exec_time) / 1000 AS total_time_seconds, SUM(min_exec_time) / 1000 AS min_time_seconds, SUM(max_exec_time) / 1000 AS max_time_seconds, SUM(mean_exec_time) / 1000 AS mean_time_seconds, SUM(stddev_exec_time) / 1000 AS stddev_time_seconds, SUM(rows) AS rows, SUM(shared_blks_hit) AS shared_blks_hit, SUM(shared_blks_read) AS shared_blks_read, SUM(shared_blks_dirtied) AS shared_blks_dirtied, SUM(shared_blks_written) AS shared_blks_written, SUM(local_blks_hit) AS local_blks_hit, SUM(local_blks_read) AS local_blks_read, SUM(local_blks_dirtied) AS local_blks_dirtied, SUM(local_blks_written) AS local_blks_written, SUM(temp_blks_read) AS temp_blks_read, SUM(temp_blks_written) AS temp_blks_written, SUM(blk_read_time) / 1000 AS blk_read_time_seconds, SUM(blk_write_time) / 1000 AS blk_write_time_seconds FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid = t3.oid) GROUP BY t2.rolname, t3.datname, queryid