Set statement_timeout in configuration?

[qfritz]

Hi! Would it be possible to set the statement_timeout for the exporter? Or is there a default fixed value ?

Or did i not understand the global scrape_timeout configuration?

Context: we're seeing some sql-exporter queries being cancelled by the app itself:

ERROR:  canceling statement due to user request

This correlate with a bit of load on our database so we're wondering if we could mitigate those by adding a more restrictive statement timeout.

Thanks and best regards,

[burningalchemist]

Hey @qfritz, it's possible to set the statement_timeout as a parameter in the DSN, as in:

postgres://user:pass@host/db?statement_timeout=25000 (it's in milliseconds)

But it's per connection and would not apply to specific queries/metrics.

I think it's likely to be related to the scrape timeouts combo on Prometheus/sql_exporter side.

From the docs:

Scrape timeouts ensure that:
  #   (i)  scraping completes in reasonable time and
  #   (ii) slow queries are canceled early when the database is already under heavy load
  # Prometheus informs targets of its own scrape timeout (via the "X-Prometheus-Scrape-Timeout-Seconds" request header)
  # so the actual timeout is computed as:
  #   min(scrape_timeout, X-Prometheus-Scrape-Timeout-Seconds - scrape_timeout_offset)

So I'd check (or if you may share the numbers here):

• Prometheus's scrape_timeout;
• sql_exporter's scrape_timeout and the offset.

If you can also afford some experiments on your end:

• set scrape_timeout to 0 on the sql_exporter side. Make sure to adjust on the Prometheus side params like scrape_timeout and scrape_interval (just to avoid clogging?);
• if you use a single target mode in sql_exporter, there is an optional field name. So if targets name is set, there should appear scrape_duration_seconds additional metric. That might also give some extra insight on the adjusted scrape timeout value. For jobs mode it's available out of the box because of the configuration format.

Please have a look at my suggestions and let me know. Happy to assist. 👍

[qfritz]

Thank you so much! I'll look as soon as I can. 🙏

[qfritz]

Very clear, thank you. It seems that it worked (didn't solve my issue because i have a different problem, but this gives me the tool to tweak the timeout).

[burningalchemist]

Cool! I guess you imply the thundering herd issue right? If so, I'll be on it soon as mentioned in the other thread. 👍