CODEWITHSUNDEEP
mongodbindexing
Weslor
Weslor

Reputation: 22450

First query after creating index is slow

I added an index in a collection. The first query that I launch is slower as the same one without index. The following ones are faster as the ones without index, so that makes sense.

I am wondering why this could be happening, is it because the index had to go from disk to memory? Then, even more difficult to understand for me is that I drop the index, restart the mongod, I create the index again and it does go fast, not like the first time. In case I restart my computer behaves like the first time, so it goes slowly only the first time with the index.

Could anybody explain this behaviour clearly?

Following I give some information about the documents, index and queries information. The documents inside the collection look like:

> db.posts.findOne()
{
        "_id" : ObjectId("557d73e1fab73211b00f3080"),
        "title" : "aaa",
        "author" : "nuevo",
        "body" : "aaa",
        "permalink" : "aaa",
        "tags" : [
                "a"
        ],
        "comments" : [ ],
        "date" : ISODate("2015-06-14T12:30:25.733Z")
}

Size of the collection:

> db.posts.find().count()
1008

Query without index, it takes 3 ms (I don't put all the output of explain, only the relevant parts):

> db.posts.explain("executionStats").find({ permalink: "ambzrbxvnorazgnqvzbw"});

{
....
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 1,
                "executionTimeMillis" : 3,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 1008,
....
}

Creating index:

> db.posts.createIndex({permalink:1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 3,
        "numIndexesAfter" : 4,
        "ok" : 1
}

Query with index created (71 ms):

> db.posts.explain("executionStats").find({ permalink: "ambzrbxvnorazgnqvzbw"});

{
....
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 1,
                "executionTimeMillis" : 71,
                "totalKeysExamined" : 1,
                "totalDocsExamined" : 1,
....
}

Relaunch the same query with other permalink to avoid that takes it from memory (or something similar). It takes 0 ms:

> db.posts.explain("executionStats").find({ permalink: "orrjnueekntvjegzvbjk"});

{
....
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 1,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 1,
                "totalDocsExamined" : 1,
....
}

Upvotes: 3

Views: 868

Answers (1)

Bartosz Bilicki
Bartosz Bilicki

Reputation: 13275

Are you on Linux? Linux uses all free memory as disk cache. Even after you restart mongo that cache stays until system will need it for something else. Queries that hit cache will be fast even without indeks - because they hit memory. There are commands to confirm that- check cache hit and misses.

Unbuffered read (one that must hit the plates on hard drive) takes hunderd or so times longer than memory read (no matter how small amount of data you are reading; drive cache, memory buffers, etc will read megabytes ahead, even if you are interested in single byte).

See https://gist.github.com/jboner/2841832 for some actual numbers.

I think if you look into http://docs.mongodb.org/manual/administration/analyzing-mongodb-performance/#administration-monitoring-page-faults and http://docs.mongodb.org/manual/reference/glossary/#term-page-fault you will be able to confirm that slow access is basically 100% page fault (everything needs to be read from hard disk), where fast access will be near 100% hits (cached reads).

Upvotes: 2

Related Questions