query text is cut off within postgres_exporter

[mzwettler2]

the query text is cut off within postgres_exporter / grafana.

this makes it hard or even impossible to inspect suspect queries.

question: any way to get the whole query text from there?

[mzwettler2]

I found that the queries are already cutted to 40 chars by the postgres_exporter queries.

Is there any reason why you did that?

ccp_pg_stat_statements_top_max: query: "SELECT pg_get_userbyid(s.userid) as role, d.datname AS dbname, s.queryid, btrim(replace(left(s.query, 40), '\n', '')) AS query, s.max_exec_time AS exec_time_ms FROM public.pg_stat_statements s JOIN pg_catalog.pg_database d ON d.oid = s.dbid ORDER BY 5 DESC LIMIT #PG_STAT_STATEMENTS_LIMIT#"

[mzwettler2]

@benjaminjb May I ask you for your input? Do you have any hint for the following behavior?

what I did:

1. I reworked "queries_pg_stat_statements.yml" and removed all these "left(s.query,40)" clauses: => btrim(replace(s.query, '\n', '')) AS query
2. I rebuild the postgres_exporter container and copied-in the reworked yamls: => COPY ./queries_pg_stat_statements.yml /opt/cpm/conf/pg13/queries_pg_stat_statements.yml
3. I also enabled full debugging (CRUNCHY_DEBUG=true, postgres_exporter --log.level=debug)

what I got:

1. postgres_exporter was started
2. the reworked queries were copied to "/tmp/queries.yaml"
3. postgres_exporter did not generate any of the "ccp_" metrics anymore (only the standard "pg_" metrics were still generated)
4. postgres_exporter simply ignored all "ccp_" metrics
5. postgres_exporter did not generate an error or any log or debug message telling why it ignored the "ccp_" metrics

Any idea?

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.1.1-0 as final

USER 0

ENV CRUNCHY_DEBUG=true

COPY ./queries_pg_stat_statements.yml /opt/cpm/conf/pg13/queries_pg_stat_statements.yml
COPY ./queries_pg_stat_statements_full.yml /opt/cpm/conf/pg13/queries_pg_stat_statements_full.yml
COPY ./start.sh /opt/cpm/bin/start.sh

RUN chgrp -R 0 /opt/cpm/bin /opt/cpm/conf && \
        chmod -R g=u /opt/cpm/bin/ opt/cpm/conf

# postgres_exporter
EXPOSE 9187

# The VOLUME directive must appear after all RUN directives to ensure the proper
# volume permissions are applied when building the image
VOLUME ["/conf"]

USER 2

CMD ["/opt/cpm/bin/start.sh"]

[mzwettler2]

@benjaminjb

Our Kubernetes people scolded me today that it is harmful to use long text strings (SQL statements) as Exporter/Prometheus metrics. This would kill our Prometheus performance.

I guess Crunchy Data truncated the SQL statements to 40 characters for this reason.

It is important to have historical, resourceintensive, complete sql statements for performance analysis and tuning. Has Crunchy Data already thought of anything on this topic yet?

[tony-landreth]

Hi @mzwettler2! The queryid column on the Query Statistics dashboard can normally be used to retrieve the full query from pg_stat_statements. Unfortunately, there may be a bug in that dashboard, causing it to sometimes present inaccurate queryid's. We'll be looking into that. Thanks for asking the question!

[mzwettler2]

Hi @mzwettler2! The queryid column on the Query Statistics dashboard can normally be used to retrieve the full query from pg_stat_statements. Unfortunately, there may be a bug in that dashboard, causing it to sometimes present inaccurate queryid's. We'll be looking into that. Thanks for asking the question!

@tony-landreth Yes, but only if it is still included in pg_stat_statements. The later I look, the less likely it is to find the statement there. That's the problem.

[tony-landreth]

@mzwettler2 Good point! We do have something in our backlog to address this. Thanks for the reminder!

[tfidfwastaken]

Hi @tony-landreth!

We seem to have bumped into this same issue on our setup—do you have any recommendations for a workaround? Or perhaps if you have thoughts on how to address this upstream, we could try to see if we can be of any help.