Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for metrics from pg_stat_user_indexes? #1065

Open
lsunsi opened this issue Sep 7, 2024 · 5 comments
Open

Add support for metrics from pg_stat_user_indexes? #1065

lsunsi opened this issue Sep 7, 2024 · 5 comments

Comments

@lsunsi
Copy link

lsunsi commented Sep 7, 2024

Proposal

Stats from the pg_stat_user_indexes group are very useful in general.
One particular use case I have is figuring out unused indexes, which is hard to do without metrics over time.

I'm secretly hoping I'm missing something, because as soon as I turned this exporter on I expected to see information about indexes and I couldn't find any and couldn't find where to enable them.

@michael-todorovic
Copy link

michael-todorovic commented Oct 3, 2024

I was searching for that as well and the closest we have is statio_user_indexes (disabled by default from the flags doc).
I started to look at it from the pg view:

select * from pg_statio_user_indexes order by idx_blks_read desc;

I believe this can be a starting point until we can get pg_stat_user_indexes in the exporter 😄

Edit: Looking at statio_user_indexes code from the exporter, I could likely provide a PR for pg_stat_user_indexes

@michael-todorovic
Copy link

I added a PR to add support for pg_stat_user_indexes. I tested it on pg15 and 16.
This also supports new field last_idx_scan from pg16

@thomasgl-orange
Copy link

Thanks @michael-todorovic, came here looking for exactly this feature, was glad to see there was already a recent PR!

I've tested #1071 on a pg14 database. It worked fine, I got the _idx_scan_total, _idx_tup_fetch and _idx_tup_read metrics I was looking for. Some notes:

  • on my DB (gitlab schema), that's a significant number of additional metrics (3 x 3900 = 11700), so it's good I think that this new collector is disabled by default
  • when I've first deployed this patched version (with this collector enabled), I've been worried by a significant increase of the collectors scrape time (as in sum (pg_scrape_collector_duration_seconds), and also by (collector) overall). It turned out that this postgres-exporter pod was already slightly CPU-throttled before (I had not noticed), and the extra work from this collector added only to the throttled time. Increasing CPU limit solved that, and now I can say that pg_scrape_collector_duration_seconds{collector="stat_user_indexes"} is nothing unusual compared to the other ones, which is good.

So, yes, 👍 from a random happy beta-tester.

@michael-todorovic
Copy link

@thomasgl-orange glad this works for you 🎉
I'm currently patching again the PR, this wasn't sufficient enough in my case. I'm adding informations about indexes, such as is_primary, is_unique, is_ready, is_valid and size. With a bit of promql, we would be able to identify big indexes that aren't for primary keys and that are rarely used (but still hurting iops 😅 ). It's almost done.
On my side, I'm switching to multi-target mode and I have one basic exporter with "living" data which is scraped often and another one with only slower metrics (like indexes here) which is scraped once in a while

@michael-todorovic
Copy link

I just completed the PR with new code and a promql example to find unused indexes. Any feedback is welcome!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants