Performance tuning MongoDB query/update?

Question

So I have a MongoDB instance where I am trying to update data in one collection with data from another collection. The two collections are participants with about 180k documents and questions with about 95k documents.

Documents in participants typically look something like this:

{
    "_id" : ObjectId("52f90b8bbab16dd8594b82b4"),
    "answers" : [
        {
            "_id" : ObjectId("52f90b8bbab16dd8594b82b9"),
            "question_id" : 2081,
            "sub_id" : null,
            "values" : [
                "Yes"
            ]
        },
        {
            "_id" : ObjectId("52f90b8bbab16dd8594b82b8"),
            "question_id" : 2082,
            "sub_id" : 123,
            "values" : [
                "Would prefer to go alone"
            ]
        },
        {
            "_id" : ObjectId("52f90b8bbab16dd8594b82b7"),
            "question_id" : 2082,
            "sub_id" : 456,
            "values" : [
                "Yes"
            ]
        }
    ],
    "created" : ISODate("2012-03-01T17:40:21Z"),
    "email" : "anonymous",
    "id" : 65,
    "survey" : ObjectId("52f41d579af1ff4221399a7b"),
    "survey_id" : 374
}

I am using the query below to perform the update:

db.participants.ensureIndex({"answers.question_id": 1, "answers.sub_id": 1});
print("created index for answer arrays!")
db.questions.find().forEach(function(doc){
    db.participants.update(
        {
            "answers.question_id": doc.id,
            "answers.sub_id": doc.sub_id
        },
        {
            $set:
            {
                "answers.$.question": doc._id
            }

        },
        false,
        true
    );
});
db.participants.dropIndex({"answers.question_id": 1, "answers.sub_id": 1});

But this takes about 20 minutes to run. I was hoping that adding the index would help with the performance, but it is still pretty slow. Is this index setup correctly considering that I am indexing fields in an array of objects? Can anyone see anything that I am doing that would cause the slowness? Suggestions on where to start looking to improve the performance of this query?

What's the point of invoking dropIndex after the query? How about the result of "explain()" of your query? db.participants.find({ "answers.question_id": doc.id, "answers.sub_id": doc.sub_id }).explain() — yaoxing
– yaoxing, Commented Feb 12, 2014 at 2:07
I think this would be a lot faster if you could express it as a mapreduce job, that way it would all execute in the database server without involving the network. — leif
– leif, Commented Feb 12, 2014 at 4:27
Just to clarify, there is not a separate application that is executing this script. This is a pure MongoDB script that I am executing from the shell. With that said, do you still think the network is involved? — Abe Miessler
– Abe Miessler, Commented Feb 12, 2014 at 5:34
@AbeMiessler The comments are on the answer. This script is not running on the server — Neil Lunn
– Neil Lunn, Commented Feb 12, 2014 at 6:34

Neil Lunn · Accepted Answer · 2014-02-12 02:43:20Z

1

I think you need to consider what you are actually doing here in order to understand why the index is not helping and indeed why this operation takes so long.

The first part of the answer is explained by what you are doing here:

db.questions.find()

Now that part alone basically says that you are asking to retrieve every document in your questions collection. So we can see what you are trying to do is exactly that, as you want to update that content into your participants collection, particularly the document _id for the "question". But here, by definition of getting all documents, no index will be used.

So what you are doing is looping every document in the questions, then asking with your update operation to match the participants record with data from the "question". And what that means is you are pulling "over the wire" all of your 95K documents and sending back "over the wire" your update operation, 95K times. This is not happening on the server and there is network traffic between your application and your MongoDB.

The index itself is not going to do much other than improve the search of each participants record, which is better than scanning and you should be getting the match. But that's not the part that taking the time, its the fetching of the questions that will be the largest issue. Also note that if you were updating

So if it's possible to run your update process on a machine that is as close as possible in networking terms to the MongoDB server then that is going to be your best performance improvement. You could also wind back your Write Concern if you want to be a little daring and/or can live with checking the integrity in another opertation, and that will reduce your network traffic and waiting for a response to the update (which is actually happening) if you put it in "fire and forget" mode.

Also see the guide if you are not sure of the concepts:

http://docs.mongodb.org/manual/core/write-concern/

answered Feb 12, 2014 at 2:43

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abe Miessler Over a year ago

Just to clarify, there is not a separate application that is executing this script. This is a pure MongoDB script that I am executing from the shell. With that said, do your "over the wire" concerns still apply?

Neil Lunn Over a year ago

Yes. The shell is just another client. You are not executing on the server, which is what I thought you assumed you were doing.

Abe Miessler Over a year ago

Oh interesting, so is it ever possible to execute on the server? My client and server are running on the same machine...

Abe Miessler Over a year ago

Also it seems like a piece of your answer may have been cut off here: Also note that if you were updating. Was there something that was supposed to go after that?

Abe Miessler · Accepted Answer · 2014-02-12 18:51:47Z

0

In case anyone is interested I was able to take the run time of this update query from 20 minutes down to about a minute and a half by using projection when selecting the questions documents. Since I am only using the _id, id and sub_id fields I was able to do the following:

db.questions.find({},{_id: 1, id: 1, sub_id: 1}).forEach(function(doc){
    ....

Which drastically improved performance. Hope this helps someone!

answered Feb 12, 2014 at 18:51

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

Collectives™ on Stack Overflow

Performance tuning MongoDB query/update?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related