Mongodb has gone through the index and is still a slow query. Please look at the execution plan.

  mongodb, question

According to the index in query, scanning the index 3 keys discovered 3, returning the number of documents 1 nReturned 1, which took millis 452 milliseconds, and the executionTimeMillisEstimate in execStats is all 0.
Question 1: ExecutionTimeMilliseconds is 0. Why does the entire query take 452 milliseconds?
Question 2: After going through the index, only 3 indexes were scanned and 1 document was returned, which took 452 milliseconds. How to optimize or adjust it?
Question 3: In other execution plans, I see that the inputStage.stage.FETCH operation takes a very long time, ranging from 200 to 1000 milliseconds. Why does FETCH need so much time to take down documents according to the index?
seek help@Mongoing Chinese Community @bguo

{
 "op": "query",
 "ns": "webDevice",
 "query": {
 "find": "webDevice",
 "filter": {
 "lid": {
 "$in": [
 "40CnwyHkVmnA9kbScMLNLneaxuS4Tcj",
 "140CnwyHkVmnA9kbScMLNLneaxuS4Tcj"
 ]
 }
 },
 "projection": {
 "$sortKey": {
 "$meta": "sortKey"
 }
 },
 "sort": {
 "createTime": -1
 },
 "limit": 1,
 "shardVersion": [
 {
 "$timestamp": {
 "t": 106,
 "i": 0
 }
 },
 {
 "$oid": "59b0039e9b5e66530435be05"
 }
 ]
 },
 "keysExamined": 3,
 "docsExamined": 1,
 "hasSortStage": true,
 "cursorExhausted": true,
 "keyUpdates": 0,
 "writeConflicts": 0,
 "numYield": 0,
 "locks": {
 "Global": {
 "acquireCount": {
 "r": 2
 }
 },
 "Database": {
 "acquireCount": {
 "r": 1
 }
 },
 "Collection": {
 "acquireCount": {
 "r": 1
 }
 }
 },
 "nreturned": 1,
 "responseLength": 1267,
 "protocol": "op_command",
 "millis": 452,
 "execStats": {
 "stage": "CACHED_PLAN",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 2,
 "advanced": 1,
 "needTime": 0,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "invalidates": 0,
 "inputStage": {
 "stage": "PROJECTION",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 5,
 "advanced": 1,
 "needTime": 4,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "invalidates": 0,
 "transformBy": {
 "$sortKey": {
 "$meta": "sortKey"
 }
 },
 "inputStage": {
 "stage": "SORT",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 5,
 "advanced": 1,
 "needTime": 4,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "invalidates": 0,
 "sortPattern": {
 "createTime": -1
 },
 "memUsage": 1031,
 "memLimit": 33554432,
 "limitAmount": 1,
 "inputStage": {
 "stage": "SORT_KEY_GENERATOR",
 "nReturned": 0,
 "executionTimeMillisEstimate": 0,
 "works": 4,
 "advanced": 0,
 "needTime": 2,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "invalidates": 0,
 "inputStage": {
 "stage": "SHARDING_FILTER",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 3,
 "advanced": 1,
 "needTime": 1,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "invalidates": 0,
 "chunkSkips": 0,
 "inputStage": {
 "stage": "FETCH",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 3,
 "advanced": 1,
 "needTime": 1,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "invalidates": 0,
 "docsExamined": 1,
 "alreadyHasObj": 0,
 "inputStage": {
 "stage": "IXSCAN",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 3,
 "advanced": 1,
 "needTime": 1,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "invalidates": 0,
 "keyPattern": {
 "lid": -1
 },
 "indexName": "lid_-1",
 "isMultiKey": false,
 "isUnique": false,
 "isSparse": false,
 "isPartial": false,
 "indexVersion": 1,
 "direction": "forward",
 "indexBounds": {
 "lid": [
 "[\"40CnwyHkVmnA9kbScMLNLneaxuS4Tcj\", \"40CnwyHkVmnA9kbScMLNLneaxuS4Tcj\"]",
 "[\"140CnwyHkVmnA9kbScMLNLneaxuS4Tcj\", \"140CnwyHkVmnA9kbScMLNLneaxuS4Tcj\"]"
 ]
 },
 "keysExamined": 3,
 "dupsTested": 0,
 "dupsDropped": 0,
 "seenInvalidated": 0
 }
 }
 }
 }
 }
 }
 },
 "ts": {
 "$date": 1514285478923
 },
 "client": "10.105.122.126",
 "allUsers": [
 {
 "user": "__system",
 "db": "local"
 }
 ],
 "user": "__system@local",
 "_id": "c044e94198e245f3e61b39d230feb393-20171226105118923-200109374"
 }

additional remarks

Let me add to my environment: the machine is 8 cores and 16 memories. There are 5 mongodb instances (in the docker container), 1 mongos,1 config,3 shard(1 master, 1 slave, 1 arbiter) deployed on the machine.
The following is the memory usage of docker.

cpu使用情况 内存使用情况
arbiter实例 1.83% 80.18MiB / 15.51GiB
shard2从 3.09% 5.306GiB / 15.51GiB
config 1.81% 1.449GiB / 15.51GiB
shard3主 2.56% 5.025GiB / 15.51GiB
mongos 0.37% 188.3MiB / 15.51GiB

Config,shard master, shard slave. all 3 instances have CacheSizeGB of 3.
At present, judging from the usage of resources, CPU usage is very low. Although the memory config limits 3GB, the entire docker container only uses 1.5GB of memory.

  1. Question 1: How to reduce the cacheSizeGB according to what you said is more reasonable
  2. Question2 2: config only uses 1.5GB of memory (CacheSizeGB3GB is limited). can you explain that indexes are loaded into memory?
  3. Question 3: What is the situation when the query becomes slower and slower after MongoDB Remove data?

Next time, remember to send out the original query, so we can look at it more conveniently. From the execution plan to push back, the query is about

db.webDevice.find({
 lid: {
 $in: [
 "40CnwyHkVmnA9kbScMLNLneaxuS4Tcj",
 "140CnwyHkVmnA9kbScMLNLneaxuS4Tcj"
 ]
 }
 }).sort({createTime: -1}).limit(1);

Because it hits the index, this query actually gets the data faster. You also mentioned that the second query will get faster in a period of time. This is an obvious feature, representing insufficient memory. MongoDB, like other databases, uses free memory to buffer indexes and data. When there is not enough memory, it uses LRU algorithm to clean up old data and replace it with new data before continuing the query. Because this process involves disk data exchange, the speed will be greatly reduced. One characteristic of this situation is that the second execution will be faster in a period of time (because the data is already in memory). However, if it is executed after a period of time, the speed will be slowed down again (because the memory is replaced again). So your situation is actually limited by hardware.
In this case, the most direct solution is:

  1. Use faster hard drives;
  2. Use more memory;

If it cannot be solved from the aspect of hardware, one thing that can be tried is to replace the memory with CPU. The approach is to keep it as small as possible.cacheSizeGB(Yes, that’s right, it’s smaller). The free memory operating system will be used to buffer the disk data, which is compressed and smaller, so more data can be buffered into memory. But in exchange, when using this data, it needs to be decompressed again by the CPU, thus consuming additional CPU. Even so, the effect is much better than reading from disk. The whole process is automatic, all you need to do is to reduce itcacheSizeGB.
This approach can alleviate the problem of insufficient memory to some extent, but it is not omnipotent:

  • First of all, it will increase CPU consumption. If your system has no remaining CPU resources, this is not appropriate.
  • Secondly, due to the compression ratio, the memory will not hold much more data than before, so it is not omnipotent.

Supplementary answer

Based on your new questions, the following points are added:

Question 1: How to reduce the cacheSizeGB according to what you said is more reasonable

As I mentioned above, the effect is limited and is limited by the compression ratio. Therefore, the data contained more is actually compressed data. For example, how much data can a 1G memory hold?

  • If not compressed (compression rate 100%), 1GB of data can be stored in 1GB of memory.
  • If the compression rate is 90%, 1G of memory can hold 10/9GB~=1.11 data;
  • If the compression rate is 80%, 1G of memory can hold 10/8 = 1.25GB of data;
  • And so on.

Therefore, the amount of adjustment actually depends on how much data you want to put into the memory, which is often an uncertain value. There will be another concept called working set, which is the data you often use. For example, your database has a total of 100GB of data, but the part you often use is only 10GB, so long as your memory can hold 10GB of data, it can be applied very quickly in most of the time, and the rest can be ignored.
Based on the above analysis, you should be able to calculate the size of your working set and the compression rate of data, so you need tocacheSizeGBHow small can you accommodate the working set (or how small can you accommodate the working set). Your situation is that there is not enough memory and the CPU is free, so if you are too lazy to calculate, just put thecacheSizeGBJust set it to the minimum.

Question2 2: config uses only 1.5GB of memory (limitedCacheSizeGB3GB). Can you explain that all indexes are loaded into memory?

This indicates the index of the config database (metadata)And dataIt’s all loaded into memory. Note that the index of the data must be in shard, independent of config. And the big head is on shard.
By the way, if it were not for experimental purposes, there would be no need to divide so many pieces. Since there are only so many hardware resources in a single machine, there is no significance for performance, but there will be additional transmission overhead.

Question 3: What is the situation when the query becomes slower and slower after MongoDB Remove data?

After remove, there is no essential connection with the query. It may just happen to happen together. If you have enough evidence to think that there is a real connection between the two, please state clearly the context of the problem and the situation you find in a separate question, and you can also turn to if necessary.MongoDB JIRA. If you must make an unfounded guess, I think it may be caused by the hot data being replaced out of memory when remove (note that remove also needs to find the data that meets the conditions before deleting), causing the following query to need to reload the data from the disk.