I have a mongodb replica set with a lot of databases, collections & indexes.
We did a lot of refactor and optimization and, of course, I have a lot of "creative queries" from the consumers.
I would like to clean up the unused indexes. just wanna save some space.
How can I check if an index is being used? I can afford to check index by index and drop the unused ones.
Running an "explain" in all the possible queries is not an option :)
EDIT: SOLUTION BASED ON THE ACCEPTED ANSWER
The script was bugged. I am not a javascript expert, but I put the corrected script. I hope will be useful for someone:
DB.prototype.indexStats = function() {
var queries = [];
var collections = db.getCollectionNames();
var findQuery = function(q) {
for(entryIdx in queries) {
if(q == queries[entryIdx].query) {
return entryIdx;
}
}
return -1;
}
for(cIdx in collections) {
var cName = collections[cIdx];
var nsName = db.getName()+"."+cName;
if(cName.indexOf("system") == -1) {
var i = 1;
var count = db.system.profile.count({ns:nsName});
print('scanning profile {ns:"'+nsName+'"} with '+count+' records... this could take a while...');
db.system.profile.find({ns:nsName}).addOption(16).batchSize(10000).forEach(function(profileDoc) {
if(profileDoc.query && !profileDoc.query["$explain"]) {
var qIdx = findQuery(profileDoc.query);
if(qIdx == -1 && profileDoc.query["query"] ) {
var size = queries.push({query:profileDoc.query, count:1, index:""});
var explain = db[cName].find(queries[size-1].query).explain();
if(profileDoc.query && profileDoc.query["query"]) {
queries[size-1].sort = profileDoc.query["orderby"];
if(queries[size-1].sort) {
explain = db[cName].find(queries[size-1].query.query).sort(queries[size-1].sort).explain();
}
}
queries[size-1].cursor = explain.cursor;
queries[size-1].millis = explain.millis;
queries[size-1].nscanned = explain.nscanned;
queries[size-1].n = explain.n;
queries[size-1].scanAndOrder = explain.scanAndOrder ? true : false;
if(explain.cursor && explain.cursor != "BasicCursor") {
queries[size-1].index = explain.cursor.split(" ")[1];
} else {
print('warning, no index for query {ns:"'+nsName+'"}: ');
printjson(profileDoc.query);
print('... millis: ' + queries[size-1].millis);
print('... nscanned/n: ' + queries[size-1].nscanned + '/' + queries[size-1].n);
print('... scanAndOrder: ' + queries[size-1].scanAndOrder);
}
} else if ( qIdx != -1 ) {
queries[qIdx].count++;
}
}
});
}
}
for(cIdx in collections) {
var cName = collections[cIdx];
if(cName.indexOf("system") == -1) {
print('checking for unused indexes in: ' + cName);
for(iIdx in db[cName].getIndexes()) {
var iName = db[cName].getIndexes()[iIdx].name;
if(iName.indexOf("system") == -1) {
var stats = db[cName].stats();
var found = false;
for(qIdx in queries) {
if(queries[qIdx].index == iName) {
found = true;
break;
}
}
if(!found) {
print('this index is not being used: ');
printjson(iName);
}
}
}
}
}
}
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…