[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: |
|
||||||||||||||||||||
| 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 ] |
|
|
| 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. |
| 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. |