[SERVER-2006] Flag unused indexes on the database server Created: 26/Oct/10  Updated: 21/Mar/13  Resolved: 15/Jan/13

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Pramodkumar Sadalage Assignee: Unassigned
Resolution: Duplicate Votes: 27
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
duplicates SERVER-44432 Per index access stats Closed
is duplicated by SERVER-3271 Feature Request: Add index usage cou... Closed
Related
Participants:

 Description   

As we develop apps and have indexes, over a period of time the usage of the app will change and some of the indexes will not get used. If the server can tell us what indexes are being used and which are not being used then it would be great, so that we can removed UNUSED indexes thus saving on Index Updates during insert/update/delete and also more of the used indexes can be fit in RAM.

Oracle has this feature.



 Comments   
Comment by Eliot Horowitz (Inactive) [ 15/Jan/13 ]

SERVER-2227 will allow this to happen plus more

Comment by Zaid Masud [ 12/Nov/12 ]

Would suggest that this feature is configurable like profiling. In fact, if it were a new profiling option that would be ideal as we could turn it on for a representative period of time to analyze index usage, and turn it back off when we are done.

Comment by Zaid Masud [ 10/Oct/12 ]

Agree this would be a pretty useful feature, doesn't sound like it should be too hard to implement with the quality of the 10gen team

Comment by Thomas DeMille [ 08/Oct/12 ]

Any progress on this? Still can't tune my DB effectively. I know there are 3rd party tools like dex but don't work as well as should and this feature seems like it would be a BIG bang for the development buck, that is... not a ton of work but a huge feature for people with complicated ad-hoc query needs.

Comment by Thomas DeMille [ 24/May/12 ]

I don't feel like anyone over there realizes how critical this is to performance tuning. Also it seems pretty darn simple, just increment an integer in a collection in the admin side each time an index is used and write a function that parses that table for us and lets us see, by collection, index usage, over a time period. then we can determine... hey I have 22 indexes on that collection but I can drop 8 of them. Without this, performance tuning is a guessing game.

Comment by Eliot Horowitz (Inactive) [ 24/May/12 ]

Just because it hasn't been done in 2 years doesn't mean it won't be done.
We look at all open tickets when picking for a release.

Comment by Justice Wang [ 24/May/12 ]

hopeless +1, created 2 years the issue, still open & unplanned, is there miracle?

Comment by Thomas DeMille [ 20/Apr/12 ]

Still feel like this is CRUCIAL to any professional performance tuning. How do I know which of my indexes are never hit? We have ad-hoc queries with hundreds of variables, initiated by end user, no way we can test ahead of time. Just like Sql Server's DMV stats, MongoDB would really benefit from having usage stats so we can combine / drop indexes as needed, in order to keep our write times low.

Comment by Ronny Elkayam [ 20/Apr/12 ]

Any plans on this for the near future? +1

Comment by Roy Smith [ 21/Mar/12 ]

+1 on this. The kinds of queries we do change over time. We discover missing indexes pretty quickly, but rarely if ever notice if we no longer need an existing one. Having unused indexes is a wasted of resources.

Comment by Thomas DeMille [ 19/Aug/11 ]

any plans for this, this is crucial for anyone doing any serious DB tuning. We are using MongoDB for add-hoc searching of real estate MLS data, there are hundreds of add-hoc criteria that can be searched on, thus we need quite a few indexes. At the same time, we don't want to over-index and create issues, so knowing which indexes are being used, how often, and by what is crucial. The ad-hoc nature of the queries makes it very difficult to accurately predict or model or test all cases up front, thus we need to let MongoDB run for a while and then see how well the indexes are doing.

Comment by Thomas DeMille [ 16/Jun/11 ]

I created this by mistake on another ticket, totally agree.. As an easy first step, just showing usage stats would be uber informative... as a second step, showing needed indexes would be fantastic..

Comment by Scott Hernandez (Inactive) [ 27/Oct/10 ]

I'd go even farther and ask for index stats. That way we can know which indexes are used and how. Then it will be easy to find the least used indexes. Resetting these stats will allow you to find unused indexes in some period of time.

Comment by Kenny Gorman [ 26/Oct/10 ]

Great feature idea. Agreed it will be needed over time.

Generated at Thu Feb 08 02:58:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.