Mongodb: slow range queries and too many page faults

Question

I've started mongodb instance on single server with 96Gb of RAM. CollStats of the largest collection:

"count" : 513025635,
    "size" : 131860720528, //≈123Gb
    "avgObjSize" : 257.02559781052656,
    "storageSize" : 141246233632,
    "numExtents" : 87,
    "nindexes" : 1,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 27554330048,
    "indexSizes" : {
            "_id_" : 27554330048 //≈25.7 Gb
    }

I've decided to replace ObjectId with my own _id of type long. I'm smoothly confused about index size. Is it too long or is it ok?

The most serious problem is extremely slow range queries.

Explain:

{
        "cursor" : "BtreeCursor _id_",
        "isMultiKey" : false,
        "n" : 3935,
        "nscannedObjects" : 3935,
        "nscanned" : 3935,
        "nscannedObjectsAllPlans" : 3935,
        "nscannedAllPlans" : 3935,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 33,
        "nChunkSkips" : 0,
        "millis" : 32444,
        "indexBounds" : {
                "_id" : [
                        [
                                137607112986000,
                                137609013786000
                        ]
                ]
        },
        "server" : "csdb:27017"
}

Slow query in log:

getmore csdb.archive query: { _id: { $gt: 1540584014914176, $lt: 1540584641314176 } }
cursorid:88959318045397 ntoreturn:0 keyUpdates:0 numYields: 2858 locks(micros) r:81292194 
nreturned:21332 reslen:554652 415116ms

Too many locks?

WorkingSet:

     db.serverStatus({"workingSet":1}).workingSet
{
        "note" : "thisIsAnEstimate",
        "pagesInMemory" : 244491,
        "computationTimeMicros" : 42233,
        "overSeconds" : 3
}

It means cached data are evicted every 3 seconds, isn't it?

There're too many page faults in mongostat's output:

insert  query update delete getmore command flushes mapped  vsize    res  faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time 
    *0     *0     *0     *0       0     1|0       0   162g   324g  64.7g    1719  csdb:0.0%          0       0|0     3|0    62b     2k    52   14:59:04 
    *0     *0     *0     *0       0     1|0       0   162g   324g  64.7g    1657  csdb:0.0%          0       0|0     3|0    62b     2k    52   14:59:05 
    *0     *0     *0     *0       0     1|0       0   162g   324g  64.7g    1743  csdb:0.0%          0       0|0     2|0    62b     2k    52   14:59:06 
    *0      2     *0     *0       0     1|0       0   162g   324g  64.7g    1878  csdb:0.0%          0       0|0     3|0   203b    99k    52   14:59:07 
    *0      1     *0     *0       2     1|0       0   162g   324g  64.7g    1629  csdb:0.0%          0       0|0     5|0   299b   120k    52   14:59:08 
    *0      2     *0     *0       1     1|0       0   162g   324g  64.7g    1733  csdb:0.0%          0       0|0     3|0   248b   995k    52   14:59:09 
    *0      1     *0     *0       2     1|0       0   162g   324g  64.8g    1694  csdb:0.0%          0       0|0     4|0   299b   547k    52   14:59:10 
    *0     *0     *0     *0       0     1|0       0   162g   324g  64.8g    1519  csdb:0.0%          0       0|0     7|0    62b     2k    52   14:59:11 
    *0      5     *0     *0       0     1|0       1   162g   324g  64.8g    1504  csdb:0.0%          0       0|0     6|0   203b   327k    52   14:59:12 
    *0     *0     *0     *0       1     1|0       0   162g   324g  64.8g    1744  csdb:0.0%          0       0|0     3|0   158b     5k    52   14:59:13

What's wrong with queries? Have I reorganise index in some way for better performance of range queries?

Mongodb: slow range queries and too many page faults

0 Answers0