CODEWITHSUNDEEP
mongodbindexing
Serge
Serge

Reputation: 2006

MongoDB: very slow queries inside index

I've seen number of similar questions, but they didn't resolve my issue (1, 2, 3).

I have collection with almost 2M documents. My problem: very simple query (find({id: 53})) runs almost 5 minutes (see everything related info below).

I tried to add index by the "id" field, tried to hint, scanAndOrder is false. I have anough memory (for now no swap and only 600MB totaly is used on server (still about 1GB is free)). It is debian 7 and mongodb 2.4.7.

Maybe this matters: yesterday I ran repairDatabase and after that noticed the issue (but I cannot say for sure if it was before). But anyway I ran full validate on the collection and everything seems to be ok. Also yesterday I added one more index ({id: 1}). Also today I reindexed the collection.

During all such long queries my disk is used very extensively.

> db.results.count()
1819411

> db.results.stats()
{
    "ns" : "spider.results",
    "count" : 1819411,
    "size" : 26662218768,
    "avgObjSize" : 14654.313273911172,
    "storageSize" : 28309077856,
    "numExtents" : 34,
    "nindexes" : 4,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 0,
    "userFlags" : 0,
    "totalIndexSize" : 207245248,
    "indexSizes" : {
        "_id_" : 53144000,
        "datetime_-1" : 45793776,
        "id_1_datetime_1" : 62513696,
        "id_1" : 45793776
    },
    "ok" : 1
}

> db.results.getIndices()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "ns" : "spider.results",
        "name" : "_id_"
    },
    {
        "v" : 1,
        "key" : {
            "datetime" : -1
        },
        "ns" : "spider.results",
        "name" : "datetime_-1"
    },
    {
        "v" : 1,
        "key" : {
            "id" : 1,
            "datetime" : 1
        },
        "unique" : true,
        "ns" : "spider.results",
        "name" : "id_1_datetime_1"
    },
    {
        "v" : 1,
        "key" : {
            "id" : 1
        },
        "ns" : "spider.results",
        "name" : "id_1"
    }
]

> db.results.validate(true)
{
    "ns" : "spider.results",
    "firstExtent" : "0:258000 ns:spider.results",
    "lastExtent" : "18:2000 ns:spider.results",
    "extentCount" : 34,
    "extents" : [
        {
            "loc" : "0:258000",
            "xnext" : "0:25a000",
            "xprev" : "null",
            "nsdiag" : "spider.results",
            "size" : 8192,
            "firstRecord" : "0:2580b0",
            "lastRecord" : "0:2598b0"
        },
        {
            "loc" : "0:25a000",
            "xnext" : "0:29d000",
            "xprev" : "0:258000",
            "nsdiag" : "spider.results",
            "size" : 274432,
            "firstRecord" : "0:25a0b0",
            "lastRecord" : "0:29c6b0"
        },
        {
            "loc" : "0:29d000",
            "xnext" : "0:3a9000",
            "xprev" : "0:25a000",
            "nsdiag" : "spider.results",
            "size" : 1097728,
            "firstRecord" : "0:29d0b0",
            "lastRecord" : "0:3a6c30"
        },
        {
            "loc" : "0:3a9000",
            "xnext" : "0:7d9000",
            "xprev" : "0:29d000",
            "nsdiag" : "spider.results",
            "size" : 4390912,
            "firstRecord" : "0:3a90b0",
            "lastRecord" : "0:7d8ab0"
        },
        {
            "loc" : "0:7d9000",
            "xnext" : "0:d81000",
            "xprev" : "0:3a9000",
            "nsdiag" : "spider.results",
            "size" : 5931008,
            "firstRecord" : "0:7d90b0",
            "lastRecord" : "0:d7fcb0"
        },
        {
            "loc" : "0:d81000",
            "xnext" : "0:1524000",
            "xprev" : "0:7d9000",
            "nsdiag" : "spider.results",
            "size" : 8007680,
            "firstRecord" : "0:d810b0",
            "lastRecord" : "0:15238b0"
        },
        {
            "loc" : "0:1524000",
            "xnext" : "0:1f74000",
            "xprev" : "0:d81000",
            "nsdiag" : "spider.results",
            "size" : 10813440,
            "firstRecord" : "0:15240b0",
            "lastRecord" : "0:1f73ab0"
        },
        {
            "loc" : "0:1f74000",
            "xnext" : "1:2000",
            "xprev" : "0:1524000",
            "nsdiag" : "spider.results",
            "size" : 14598144,
            "firstRecord" : "0:1f740b0",
            "lastRecord" : "0:2d5f6b0"
        },
        {
            "loc" : "1:2000",
            "xnext" : "1:12ce000",
            "xprev" : "0:1f74000",
            "nsdiag" : "spider.results",
            "size" : 19709952,
            "firstRecord" : "1:20b0",
            "lastRecord" : "1:12cd8b0"
        },
        {
            "loc" : "1:12ce000",
            "xnext" : "1:2c2f000",
            "xprev" : "1:2000",
            "nsdiag" : "spider.results",
            "size" : 26611712,
            "firstRecord" : "1:12ce0b0",
            "lastRecord" : "1:2c2eab0"
        },
        {
            "loc" : "1:2c2f000",
            "xnext" : "1:4e72000",
            "xprev" : "1:12ce000",
            "nsdiag" : "spider.results",
            "size" : 35926016,
            "firstRecord" : "1:2c2f0b0",
            "lastRecord" : "1:4e719b0"
        },
        {
            "loc" : "1:4e72000",
            "xnext" : "2:2000",
            "xprev" : "1:2c2f000",
            "nsdiag" : "spider.results",
            "size" : 48500736,
            "firstRecord" : "1:4e720b0",
            "lastRecord" : "1:7cb27b0"
        },
        {
            "loc" : "2:2000",
            "xnext" : "2:3e74000",
            "xprev" : "1:4e72000",
            "nsdiag" : "spider.results",
            "size" : 65478656,
            "firstRecord" : "2:20b0",
            "lastRecord" : "2:3e71d30"
        },
        {
            "loc" : "2:3e74000",
            "xnext" : "3:2000",
            "xprev" : "2:2000",
            "nsdiag" : "spider.results",
            "size" : 88399872,
            "firstRecord" : "2:3e740b0",
            "lastRecord" : "2:92c1ab0"
        },
        {
            "loc" : "3:2000",
            "xnext" : "3:71d2000",
            "xprev" : "2:3e74000",
            "nsdiag" : "spider.results",
            "size" : 119341056,
            "firstRecord" : "3:20b0",
            "lastRecord" : "3:71d1ab0"
        },
        {
            "loc" : "3:71d2000",
            "xnext" : "3:10b78000",
            "xprev" : "3:2000",
            "nsdiag" : "spider.results",
            "size" : 161112064,
            "firstRecord" : "3:71d20b0",
            "lastRecord" : "3:10b70ab0"
        },
        {
            "loc" : "3:10b78000",
            "xnext" : "4:2000",
            "xprev" : "3:71d2000",
            "nsdiag" : "spider.results",
            "size" : 217501696,
            "firstRecord" : "3:10b780b0",
            "lastRecord" : "3:1dae29b0"
        },
        {
            "loc" : "4:2000",
            "xnext" : "4:11809000",
            "xprev" : "3:10b78000",
            "nsdiag" : "spider.results",
            "size" : 293629952,
            "firstRecord" : "4:20b0",
            "lastRecord" : "4:118088b0"
        },
        {
            "loc" : "4:11809000",
            "xnext" : "5:2000",
            "xprev" : "4:2000",
            "nsdiag" : "spider.results",
            "size" : 396402688,
            "firstRecord" : "4:118090b0",
            "lastRecord" : "4:29212930"
        },
        {
            "loc" : "5:2000",
            "xnext" : "5:1fe5d000",
            "xprev" : "4:11809000",
            "nsdiag" : "spider.results",
            "size" : 535146496,
            "firstRecord" : "5:20b0",
            "lastRecord" : "5:1fe5ca30"
        },
        {
            "loc" : "5:1fe5d000",
            "xnext" : "6:2000",
            "xprev" : "5:2000",
            "nsdiag" : "spider.results",
            "size" : 722448384,
            "firstRecord" : "5:1fe5d0b0",
            "lastRecord" : "5:4af553b0"
        },
        {
            "loc" : "6:2000",
            "xnext" : "7:2000",
            "xprev" : "5:1fe5d000",
            "nsdiag" : "spider.results",
            "size" : 975306752,
            "firstRecord" : "6:20b0",
            "lastRecord" : "6:3a21d6b0"
        },
        {
            "loc" : "7:2000",
            "xnext" : "8:2000",
            "xprev" : "6:2000",
            "nsdiag" : "spider.results",
            "size" : 1316667392,
            "firstRecord" : "7:20b0",
            "lastRecord" : "7:4e7ab0b0"
        },
        {
            "loc" : "8:2000",
            "xnext" : "9:2000",
            "xprev" : "7:2000",
            "nsdiag" : "spider.results",
            "size" : 1777504256,
            "firstRecord" : "8:20b0",
            "lastRecord" : "8:69f278b0"
        },
        {
            "loc" : "9:2000",
            "xnext" : "10:2000",
            "xprev" : "8:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "9:20b0",
            "lastRecord" : "9:7fefdab0"
        },
        {
            "loc" : "10:2000",
            "xnext" : "11:2000",
            "xprev" : "9:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "10:20b0",
            "lastRecord" : "10:7feff630"
        },
        {
            "loc" : "11:2000",
            "xnext" : "12:2000",
            "xprev" : "10:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "11:20b0",
            "lastRecord" : "11:7feffa30"
        },
        {
            "loc" : "12:2000",
            "xnext" : "13:2000",
            "xprev" : "11:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "12:20b0",
            "lastRecord" : "12:7feffb30"
        },
        {
            "loc" : "13:2000",
            "xnext" : "14:2000",
            "xprev" : "12:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "13:20b0",
            "lastRecord" : "13:7fefb7b0"
        },
        {
            "loc" : "14:2000",
            "xnext" : "15:2000",
            "xprev" : "13:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "14:20b0",
            "lastRecord" : "14:7feffb30"
        },
        {
            "loc" : "15:2000",
            "xnext" : "16:2000",
            "xprev" : "14:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "15:20b0",
            "lastRecord" : "15:7fefba30"
        },
        {
            "loc" : "16:2000",
            "xnext" : "17:2000",
            "xprev" : "15:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "16:20b0",
            "lastRecord" : "16:7feff9b0"
        },
        {
            "loc" : "17:2000",
            "xnext" : "18:2000",
            "xprev" : "16:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "17:20b0",
            "lastRecord" : "17:7feff9b0"
        },
        {
            "loc" : "18:2000",
            "xnext" : "null",
            "xprev" : "17:2000",
            "nsdiag" : "spider.results",
            "size" : 2146426864,
            "firstRecord" : "18:20b0",
            "lastRecord" : "18:1f8328b0"
        }
    ],
    "datasize" : 26662218768,
    "nrecords" : 1819411,
    "lastExtentSize" : 2146426864,
    "padding" : 1,
    "firstExtentDetails" : {
        "loc" : "0:258000",
        "xnext" : "0:25a000",
        "xprev" : "null",
        "nsdiag" : "spider.results",
        "size" : 8192,
        "firstRecord" : "0:2580b0",
        "lastRecord" : "0:2598b0"
    },
    "lastExtentDetails" : {
        "loc" : "18:2000",
        "xnext" : "null",
        "xprev" : "17:2000",
        "nsdiag" : "spider.results",
        "size" : 2146426864,
        "firstRecord" : "18:20b0",
        "lastRecord" : "18:1f8328b0"
    },
    "objectsFound" : 1819411,
    "invalidObjects" : 0,
    "bytesWithHeaders" : 26691329344,
    "bytesWithoutHeaders" : 26662218768,
    "deletedCount" : 14,
    "deletedSize" : 1617742528,
    "nIndexes" : 4,
    "keysPerIndex" : {
        "spider.results.$_id_" : 1819411,
        "spider.results.$datetime_-1" : 1819411,
        "spider.results.$id_1_datetime_1" : 1819411,
        "spider.results.$id_1" : 1819411
    },
    "valid" : true,
    "errors" : [ ],
    "ok" : 1
}

> db.results.find({id:6}).explain()
{
    "cursor" : "BtreeCursor id_1_datetime_1",
    "isMultiKey" : false,
    "n" : 47402,
    "nscannedObjects" : 47402,
    "nscanned" : 47402,
    "nscannedObjectsAllPlans" : 47402,
    "nscannedAllPlans" : 47402,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 248,
    "nChunkSkips" : 0,
    "millis" : 254485,
    "indexBounds" : {
        "id" : [
            [
                6,
                6
            ]
        ],
        "datetime" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : "ip-10-137-56-52:27017"
}

The only thing I haven't done yet is backup with repair and then restore. But I am not sure it will help...

Any suggestions?

Upvotes: 3

Views: 901

Answers (1)

Bruce Lucas
Bruce Lucas

Reputation: 959

It looks like the example query you give for an id of 6 matches 47402 documents:

> db.results.find({id:6}).explain()
    "n" : 47402,

and the average document size is about 14654 bytes:

> db.results.stats()
    "avgObjSize" : 14654.313273911172,

Does this match your view of the situation?

This means that the results from this query will be about 662 MB in size. If the 662 MB of documents returned by the query are not in memory and must be fetched from disk then a lot of disk activity will result and the query will take a long time.

There are a few ways you could improve performance, depending on your application requirements:

  • use a more specific query that matches fewer documents, for example use _id to fetch a single document or specify a datetime as well as an id in your query.

  • refactor your document schema to make the documents you wish to query smaller.

  • use a projection in the query to select only a subset of the 14KB document to return and build an index that contains the fields in that query projection, so that the query can be satisfied entirely from the index. Note however that this will only work if you select a small enough subset of the document so that your index does not grow to be large, putting you back in the same situation of having to page the index in from disk. The db.results.stats() output tells you how big your indexes are.

  • equip your server with enough memory so that the collection will remain entirely in memory. The collection size is about 26 GB so you would need at least that much memory for this approach:

    db.results.stats() "size" : 26662218768,

Hope this helps,

Bruce

Upvotes: 3

Related Questions