I have a MongoDB Server with just over 2 million documents in one collection. Individual documents can contain large amounts of data (some are several megabytes each) but none of that data is in indexed columns.
There is an index on that collection, and when I restart the server it spends an incredibly long time doing what I assume is rebuilding that index.
Virtually all commands given to the server just stall with no error but also do nothing, about the only thing that works is db.currentOp(). (The results from that are at the end of this question).
There are a lot of indexes on this collection, could that be part of the problem or is it being caused by one specific index?
There are 4 compound indexes:
name="type_fields.key_fields.value_idx", def="{'type':1, 'fields.key':1, 'fields.value':1}"
name="type_filename_fields_idx", def="{'type':1, 'filename':1, 'fields':1}"
name="fields.key_fields.value_idx", def="{'fields.key':1, 'fields.value':1}"
name="creationDate_fields.key_fields.value_type_idx", def="{'creationDate': 1, 'fields.key':1, 'fields.value':1, 'type':1}"
There are also non-compound indexes on four more:
fields,
fileName,
expiryDate,
purgeDate
db.currentOp() results:
"inprog" : [
{
"opid" : 44,
"active" : false,
"op" : "query",
"ns" : "",
"query" : {
"expireAfterSeconds" : {
"$exists" : true
}
},
"client" : "0.0.0.0:0",
"desc" : "TTLMonitor",
"threadId" : "0x7f0d2d902700",
"locks" : {
"^apollo" : "R"
},
"waitingForLock" : true,
"numYields" : 0,
"lockStats" : {
"timeLockedMicros" : {
},
"timeAcquiringMicros" : {
}
}
},
{
"opid" : 43,
"active" : true,
"secs_running" : 2594,
"op" : "insert",
"ns" : "apollo.system.indexes",
"insert" : {
"v" : 1,
"key" : {
"purgeDate" : 1
},
"ns" : "apollo.documents",
"name" : "purgeDate",
"dropDups" : false,
"sparse" : false,
"background" : false
},
"client" : "127.0.0.1:47980",
"desc" : "conn5",
"threadId" : "0x7d665a200700",
"connectionId" : 5,
"locks" : {
"^" : "w",
"^apollo" : "W"
},
"waitingForLock" : false,
"msg" : "index: (1/3) external sort Index: (1/3) External Sort Progress: 1238748/2182224 56%",
"progress" : {
"done" : 1238748,
"total" : 2182224
},
"numYields" : 0,
"lockStats" : {
"timeLockedMicros" : {
},
"timeAcquiringMicros" : {
"r" : NumberLong(0),
"w" : NumberLong(1)
}
}
},
{
"opid" : 50,
"active" : true,
"secs_running" : 2502,
"op" : "query",
"ns" : "",
"query" : {
"serverStatus" : 1
},
"client" : "127.0.0.1:47985",
"desc" : "conn6",
"threadId" : "0x7d6658907700",
"connectionId" : 6,
"locks" : {
"^apollo" : "R"
},
"waitingForLock" : true,
"numYields" : 0,
"lockStats" : {
"timeLockedMicros" : {
"r" : NumberLong(4),
"w" : NumberLong(0)
},
"timeAcquiringMicros" : {
"r" : NumberLong(3),
"w" : NumberLong(0)
}
}
},
{
"opid" : 53,
"active" : true,
"secs_running" : 2443,
"op" : "query",
"ns" : "",
"query" : {
"serverStatus" : 1
},
"client" : "127.0.0.1:47987",
"desc" : "conn8",
"threadId" : "0x7f0d26000700",
"connectionId" : 8,
"locks" : {
"^apollo" : "R"
},
"waitingForLock" : true,
"numYields" : 0,
"lockStats" : {
"timeLockedMicros" : {
"r" : NumberLong(5),
"w" : NumberLong(0)
},
"timeAcquiringMicros" : {
"r" : NumberLong(2),
"w" : NumberLong(0)
}
}
}
]
So the first question is whether I can do anything to speed up this building of the index or cause it to be saved so it doesn't need rebuilding whenever the server restarts.
The second question is whether any of these indexes are redundant – I don't think they are as the columns in questions can all be queries on in any combination but there may be a better way to organise this. Once the server has finished starting performance is excellent, it's just a problem when we reboot as it takes over an hour to start the MongoDB.
I did notice that "purgeDate" is mentioned in the currentOp results, that column will contain mostly null values. Could that be part of the problem?
Best Answer
First question: You can't if you are not running on a replica set. On a replica set you may follow that guide http://docs.mongodb.org/manual/tutorial/build-indexes-on-replica-sets/. Indexes are persistent, upon an index is created it doesn't rebuild on start-up unless something trigger it (maybe your application?)
Second question: I don't see any index overlap
About "purgeDate": If the field is missing from majority of documents use {"sparse" : true} to create the index. Be careful, null is different than missing field.