I am testing mongodb to see if it fits our needs. Heres db.stats(1024*1024*1024) of the database I’m testing with:
{
"db" : "Test",
"collections" : 7,
"views" : 0,
"objects" : 37078230,
"avgObjSize" : 52676.1415612881,
"dataSize" : 1819.00159672089,
"storageSize" : 418.299812316895,
"numExtents" : 0,
"indexes" : 25,
"indexSize" : 8.58806228637695,
"scaleFactor" : 1073741824.0,
"fsUsedSize" : 1568.61089706421,
"fsTotalSize" : 1862.3730430603,
"ok" : 1.0,
"$clusterTime" : {
"clusterTime" : Timestamp(1623739103, 1),
"signature" : {
"hash" : { "$binary" : "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "$type" : "00" },
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1623739103, 1)
}
As you can see, the database is about 418 GB, indexes are about 8.6 GB and average object size is about 53 KB.
The problem that I have is that queries are taking too long, even when using indexes.
The query I am running looks like this:
db.collection.find({
"timestamp": {
"$gte": ISODate(...),
"$lte": ISODate(...)
},
"status": { "$in": [..] }
}).limit(10000)
So following the ESR-Rule, I have an index { status: 1, timestamp: 1} that is being used.
explain("executionStats") shows that it uses that index and that this does not take too long (73 ms). But what takes too long is the FETCH-Stage (11896 ms).
Earlier, I had my database on a 15000 RPM SAS HDD which was slow, so I switched to SSD. However it still takes a lot of time to load documents.
Using the profiler I found out, that lots of data is being read from disk (~873 MB), and that this takes a lot of time (about 12 seconds).
I don’t know how I could make this faster. My indexes fit into RAM (32 GB). The database contains data from ~ 1.5 years which I am querying based on timestamp. The queries could ask for the last month, but they could also ask for the first month, so I think I dont really have that typical working set that mongo could keep in RAM (or I would need a huge amount of ram).
Maybe this is where I need sharding?
Documents of my collection I’m querying on:
{
"_id": ObjectId(".."),
"id_testbench": ObjectId(".."),
"timestamp": ISODate("..."),
"status": "OK",
...
"values": [
{
"type": "i",
"v_i": 15,
"key": "X"
},
... (730 times)
{
"type": "i",
"v_i": 4,
"key": "fXa"
}
]
}
Explain:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "Test.collection",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"status" : {
"$eq" : "OK"
}
},
{
"timestamp" : {
"$lte" : ISODate("2018-06-01T00:00:00.000+02:00")
}
},
{
"timestamp" : {
"$gte" : ISODate("2018-05-01T00:00:00.000+02:00")
}
}
]
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"status" : 1,
"timestamp" : 1
},
"indexName" : "status_timestamp",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : [ ],
"timestamp" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[\"OK\", \"OK\"]"
],
"timestamp" : [
"[new Date(1525125600000), new Date(1527804000000)]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "LIMIT",
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"status" : {
"$eq" : "OK"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"timestamp" : 1
},
"indexName" : "timestamp",
"isMultiKey" : false,
"multiKeyPaths" : {
"timestamp" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"timestamp" : [
"[new Date(1525125600000), new Date(1527804000000)]"
]
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"timestamp" : {
"$lte" : ISODate("2018-06-01T00:00:00.000+02:00")
}
},
{
"timestamp" : {
"$gte" : ISODate("2018-05-01T00:00:00.000+02:00")
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"status" : 1
},
"indexName" : "status",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[\"OK\", \"OK\"]"
]
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"status" : {
"$eq" : "OK"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"timestamp" : 1,
"material" : 1
},
"indexName" : "ts_timestamp_1_mat_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"timestamp" : [ ],
"material" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"timestamp" : [
"[new Date(1525125600000), new Date(1527804000000)]"
],
"material" : [
"[MinKey, MaxKey]"
]
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"status" : {
"$eq" : "OK"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"timestamp" : 1,
"shipping_code" : 1
},
"indexName" : "ts_timestamp_1_code_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"timestamp" : [ ],
"shipping_code" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"timestamp" : [
"[new Date(1525125600000), new Date(1527804000000)]"
],
"shipping_code" : [
"[MinKey, MaxKey]"
]
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"timestamp" : 1,
"material" : 1,
"serial" : 1,
"shipping_code" : 1,
"status" : 1
},
"indexName" : "start_mat_serial_code_state",
"isMultiKey" : false,
"multiKeyPaths" : {
"timestamp" : [ ],
"material" : [ ],
"serial" : [ ],
"shipping_code" : [ ],
"status" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"timestamp" : [
"[new Date(1525125600000), new Date(1527804000000)]"
],
"material" : [
"[MinKey, MaxKey]"
],
"serial" : [
"[MinKey, MaxKey]"
],
"shipping_code" : [
"[MinKey, MaxKey]"
],
"status" : [
"[\"OK\", \"OK\"]"
]
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"timestamp" : 1,
"serial" : 1,
"status" : 1
},
"indexName" : "start_serial",
"isMultiKey" : false,
"multiKeyPaths" : {
"timestamp" : [ ],
"serial" : [ ],
"status" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"timestamp" : [
"[new Date(1525125600000), new Date(1527804000000)]"
],
"serial" : [
"[MinKey, MaxKey]"
],
"status" : [
"[\"OK\", \"OK\"]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10000,
"executionTimeMillis" : 12525,
"totalKeysExamined" : 10000,
"totalDocsExamined" : 10000,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 11896,
"works" : 10001,
"advanced" : 10000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 378,
"restoreState" : 378,
"isEOF" : 1,
"limitAmount" : 10000,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 11896,
"works" : 10000,
"advanced" : 10000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 378,
"restoreState" : 378,
"isEOF" : 0,
"docsExamined" : 10000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 73,
"works" : 10000,
"advanced" : 10000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 378,
"restoreState" : 378,
"isEOF" : 0,
"keyPattern" : {
"status" : 1,
"timestamp" : 1
},
"indexName" : "status_timestamp",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : [ ],
"timestamp" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[\"OK\", \"OK\"]"
],
"timestamp" : [
"[new Date(1525125600000), new Date(1527804000000)]"
]
},
"keysExamined" : 10000,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"indexDef" : {
"indexName" : "status_timestamp",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : [ ],
"timestamp" : [ ]
},
"keyPattern" : {
"status" : 1,
"timestamp" : 1
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"direction" : "forward"
}
}
}
}
},
"serverInfo" : {
"host" : "localhost",
"port" : 27018,
"version" : "4.2.13",
"gitVersion" : "82dd40f60c55dae12426c08fd7150d79a0e28e23"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1623681999, 1),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : 0
}
},
"operationTime" : Timestamp(1623681999, 1)
}
Profiler:
{
"op": "command",
"ns": "Test.collection",
"command": {
"explain": {
"find": "collection",
"filter": {
"timestamp": {
"$gte": "2018-04-30T22:00:00.000Z",
"$lte": "2018-05-31T22:00:00.000Z"
},
"status": {
"$in": [
"OK"
]
}
},
"limit": 10000,
"singleBatch": false,
"projection": {}
},
"verbosity": "executionStats",
"$clusterTime": {
"clusterTime": "Timestamp(1623681989, 1)",
"signature": {
"hash": "UUID(\"00000000-0000-0000-0000-00000000000000000000\")",
"keyId": 0
}
},
"$db": "Test"
},
"numYield": 378,
"locks": {
"ParallelBatchWriterMode": {
"acquireCount": {
"r": "1"
}
},
"ReplicationStateTransition": {
"acquireCount": {
"w": "380"
}
},
"Global": {
"acquireCount": {
"r": "380"
}
},
"Database": {
"acquireCount": {
"r": "379"
}
},
"Collection": {
"acquireCount": {
"r": "379"
}
},
"Mutex": {
"acquireCount": {
"r": "1"
}
}
},
"flowControl": {},
"storage": {
"data": {
"bytesRead": "873074956",
"timeReadingMicros": "12086177"
}
},
"responseLength": 5630,
"protocol": "op_msg",
"millis": 12526,
"ts": "2021-06-14T14:46:46.183Z",
"client": "127.0.0.1",
"appName": "NoSQLBoosterV6.2.13_84940.861",
"allUsers": [],
"user": ""
}