2

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?

  • You probably don't need all those indexes...try running important queries with .explain() to find out which indexes are actually being chosen and remove the others. – LowlyDBA Feb 10 '15 at 15:48
1

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.

  • Yeah, the field is present but contains a null value so I don't think sparse will help in this case. We're not running a replica set at the moment but we are going to move to one for redundancy purposes Soon[tm] so it sounds like that will help when we do. – Tim B Feb 11 '15 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.