Configure multiple database connections jobs to not start at the same second?

[qfritz]

Hi again,

We have a weird special setup in one of our environments where we have 100+ databases on the same server. We configured our one SQL exporter instance to connect to all databases to retrieve metrics through the jobs feature.

Therefore, we have 100+ targets. It seems that exporter starts all queries for those targets exactly at the same time (=at prom scrape for /metrics), effectively surging this little server. Would there be a way to tell the exporter to not run all jobs at the same time but instead slightly shift them from one another with an internal pool?

Since these are all separate connections, I would guess there is no polling possible at connection level.

Another option for us could be to split the load and have those jobs spread out between 2 instances of SQL exporter, but this sounds a bit hacky.

A last option would be to tell prometheus to schedule different the scrape of each job but due to the number of targets, this is not very scalable for us.

What do you think?

Best regards,

[burningalchemist]

Hey @qfritz, there is an issue #902 that describes exactly your problem (please correct me if i'm wrong). I have several ideas on how to resolve it. I would like to avoid introducing any disk-related operations, but I think we can add some kind of warm-up delays to address the 'thundering herd'.

I'll start working on this issue soon, and will share my thoughts with the original poster prior to that, feel free to join the discussion. 👍

[qfritz]

Hey @burningalchemist

Thanks for the answer!

Sorry to disappoint but it seems my issue is a little bit different as all jobs startup and run queries at scrape time, not only when the service SQL Exporter starts up. Therefore this surge of queries in more regular than expected (on the contrary, I would have been okay with a single surge at startup).

However, this #902 made me notice again the min_interval setting which could help me reduce the load on this server, at the cost of less frequent queries.

Am I clearer? Thanks again for your great support.

[burningalchemist]

Ah yes, this would be the way, you can configure min_interval for each collector, so the results are cached without calling the database each time.

I previously implemented a simple jitter feature flag that executes queries with small random interval. I wasn't sure whether it's a good approach or not (also didn't get any available feedback at that time), and eventually dropped it. I'll think about it, maybe it makes sense to revive it. 🤔

But for now, min_interval per collector seems more explicit and predictable. It's probably less user-friendly if you have many collectors.

---

My ultimate goal is to finish several feature requests, including histograms to hopefully declare v1.0, and I'll make a small documentation page to describe all the possible tweaks and fine-tuning. The capacity is unfortunately quite limited for the time being.