Loading...

Type: Bug
Resolution: Duplicate
Priority: Major - P3
Fix Version/s: None
Affects Version/s: None
Component/s: None
Labels:
None

Operating System:
ALL
Confidence Status:
None

Aha! Reference:
None
Tracking Level:
None
Risk Status:
None
Exec Notes:
None
Goal Name:
None
Goal Link:
None

Would like to report an incident which happened on MongoDB 4.4.8 running on Atlas.

We have a large table "reservations" and we did an $in query on the "customer_ids" field for roughly 240 values.

The table has the following indexes available:

customer_ids: 1, shop_id: 1, deleted_at: 1
customer_ids: 1

Looking at the log below, for some reason, the QueryPlanner selected an index which did not include customer_ids at all (deleted_at: 1, shop_id: 1, st: 1). This caused a full table scan and apparently locked our database from getting replication updates.

To resolve the issue on our end, I will add an index for (customer_ids: 1, deleted_at: 1). However, given this situation (an "any" with 240 ID keys) I still feel the QueryPlanner should have given preference to the customer_ids field. Please let me know your thoughts.

Full query is below:

{
  "type": "command",
  "ns": "XXX_production.reservations",
  "appName": "XXX-worker-5-ap-northeast-1-production-6454d4c9b7-24x62",
  "command": {
    "getMore": {
      "$numberLong": "8607675802982378262"
    },
    "collection": "reservations",
    "$db": "XXX_production",
    "$clusterTime": {
      "clusterTime": {
        "$timestamp": {
          "t": 1628771231,
          "i": 37
        }
      },
      "signature": {
        "hash": {
          "$binary": "cV4XqTMxUfDx6OhwuVf2ombd7VM=",
          "$type": "00"
        },
        "keyId": {
          "$numberLong": "6952549997699137540"
        }
      }
    },
    "lsid": {
      "id": {
        "$uuid": "e5a5aabb-3b9e-4e74-b627-5f0545c5bc8c"
      }
    }
  },
  "originatingCommand": {
    "find": "reservations",
    "filter": {
      "deleted_at": null,
      "customer_ids": {
        "$in": [
          {
            "$oid": "5577e7c9342fc4dacf00049a"
          },
          {
            "$oid": "5465aa078a9c91d514000010"
          },
          {
            "$oid": "54cb29669ab0bb2a660009b4"
          },
          {
            "$oid": "518404e870f78ebe160009c0"
          },
          {
            "$oid": "518404e670f78ebe16000949"
          },
          {
            "$oid": "518404e570f78ebe160008d7"
          },
          {
            "$oid": "53c48fc19ab0bb8b1d000197"
          },
          {
            "$oid": "518404e570f78ebe160008fb"
          },
          {
            "$oid": "518404ea70f78ebe16000a46"
          },
          {
            "$oid": "546d6a3e9ab0bb9b6e000fae"
          },
          {
            "$oid": "546aa7cc342fc4b70800021c"
          },
          {
            "$oid": "518404e670f78ebe16000917"
          },
          {
            "$oid": "53e9896eb81b12ec84000085"
          },
          {
            "$oid": "518404e570f78ebe160008e8"
          },
          {
            "$oid": "551b94c9342fc400770004c5"
          },
          {
            "$oid": "53ee0b0fa0749e863f0000f2"
          },
          {
            "$oid": "548564fa342fc42db3000945"
          },
          {
            "$oid": "518404e470f78ebe160008b1"
          },
          {
            "$oid": "55b8e382a0749ef52b0012b6"
          },
          {
            "$oid": "55cd58a76c0b62afcb0002f2"
          },
          {
            "$oid": "55c43d6d342fc4bf6b00086a"
          },
          {
            "$oid": "55a4d5468a9c9136770000cd"
          },
          {
            "$oid": "55cb25d66c0b62fd82001091"
          },
          {
            "$oid": "561152a9a0749eebe300078f"
          },
          {
            "$oid": "561139158a9c91cd72001384"
          },
          {
            "$oid": "56c6a8846c0b628978000842"
          },
          {
            "$oid": "564ebe7433aed5936e003362"
          },
          {
            "$oid": "555bd7f1a0749ebdb1000065"
          },
          {
            "$oid": "56647e806c0b62d50700102b"
          },
          {
            "$oid": "566a889b342fc4884d002a17"
          },
          {
            "$oid": "5539a6309ab0bb54e3000182"
          },
          {
            "$oid": "56949fd733aed5a958000962"
          },
          {
            "$oid": "555440d98a9c91d9bf0003f6"
          },
          {
            "$oid": "56a87d819ab0bbe38c00061c"
          },
          {
            "$oid": "56c6bd3a6c0b6269700007b0"
          },
          {
            "$oid": "56c2afd5a932367402000827"
          },
          {
            "$oid": "56f20708b81b1274a6000ef1"
          },
          {
            "$oid": "56fcdcc5342fc4fd300040ed"
          },
          {
            "$oid": "5704788a8a9c91f8c60011fb"
          },
          {
            "$oid": "5705f6e8342fc414f5000550"
          },
          {
            "$oid": "570604d3342fc45f150008d8"
          },
          {
            "$oid": "5719b2ed30a9d7dc15000538"
          },
          {
            "$oid": "5732c2d06c0b62fecd001fd1"
          },
          {
            "$oid": "5747be78207a5899ca0024f5"
          },
          {
            "$oid": "5749a5046c0b621ee60063bf"
          },
          {
            "$oid": "5758ca86342fc49ce400047b"
          },
          {
            "$oid": "5768f9c29ab0bb9145000721"
          },
          {
            "$oid": "57a906b3c6c69b3ece000289"
          },
          {
            "$oid": "57a9b72c9ab0bbaca300286a"
          },
          {
            "$oid": "57c7e1c3a93236d2d502bb0a"
          },
          {
            "$oid": "57ce57a0342fc4dd0d00172d"
          },
          {
            "$oid": "57ea05e1b81b124ac70053e5"
          },
          {
            "$oid": "57f5b590207a58fa140005ef"
          },
          {
            "$oid": "5826f4d4b81b12fa1400a0b5"
          },
          {
            "$oid": "5827e2d830a9d749f7009e2a"
          },
          {
            "$oid": "5829b504b81b129352002f47"
          },
          {
            "$oid": "582a8fa4fa23b74bf5004d3e"
          },
          {
            "$oid": "584ebd0cb81b125f96027aeb"
          },
          {
            "$oid": "58819431664bf283bf00cd2b"
          },
          {
            "$oid": "588ee008207a58c458005625"
          },
          {
            "$oid": "58930fe5f619afebf60002a6"
          },
          {
            "$oid": "58bd20e2f619afed09001a95"
          },
          {
            "$oid": "58bfa836342fc437f2000700"
          },
          {
            "$oid": "58c175be664bf20753003d12"
          },
          {
            "$oid": "58c772c433aed5be92000e62"
          },
          {
            "$oid": "58ca37d2207a58fc8b002fcb"
          },
          {
            "$oid": "58e1d999a9323631f9005fbd"
          },
          {
            "$oid": "58e61bdf33aed54cda000c7f"
          },
          {
            "$oid": "58eb270833aed50dfb00027f"
          },
          {
            "$oid": "58ecbb8f9ab0bbb12100083a"
          },
          {
            "$oid": "58eef9e733aed5cc420003cb"
          },
          {
            "$oid": "58f71604016974e3c20002ed"
          },
          {
            "$oid": "58feac6a207a586ee4001a67"
          },
          {
            "$oid": "5900080530a9d7a90f00324e"
          },
          {
            "$oid": "590143b2207a5861d3001098"
          },
          {
            "$oid": "590c491ffa23b7b0f9010a43"
          },
          {
            "$oid": "59102c7db81b12fb4400104d"
          },
          {
            "$oid": "5915072733aed5e2ca000f89"
          },
          {
            "$oid": "59156ee99ab0bb1eee00005d"
          },
          {
            "$oid": "591e4eecc6c69b880401228a"
          },
          {
            "$oid": "5926c806207a58f4f90006e2"
          },
          {
            "$oid": "59282d7f664bf2592b007c49"
          },
          {
            "$oid": "592e6d8b342fc46117000a62"
          },
          {
            "$oid": "5938dd74664bf27e6f04484f"
          },
          {
            "$oid": "593dff86fa23b7718806a672"
          },
          {
            "$oid": "593e4359b81b127ced06d27d"
          },
          {
            "$oid": "594132fd9ab0bbb80f000446"
          },
          {
            "$oid": "594131e7b1b2a8fae2003dc9"
          },
          {
            "$oid": "5942238530a9d7d8fa00267e"
          },
          {
            "$oid": "595601c6342fc4fd9b000699"
          },
          {
            "$oid": "597e7c51342fc4cf5700037a"
          },
          {
            "$oid": "5987c347a0749e172e00054d"
          },
          {
            "$oid": "5989643a6c0b62e71f0003d2"
          },
          {
            "$oid": "598971c0664bf2450c022e17"
          },
          {
            "$oid": "5994e433a0749e6f670000d8"
          },
          {
            "$oid": "599d62cd3a4fafd84e0034af"
          },
          {
            "$oid": "59a680b2207a588e950007b5"
          },
          {
            "$oid": "59ad148933aed5f2e90005cf"
          },
          {
            "$oid": "59adfada01697406c7000664"
          },
          {
            "$oid": "59b9e1d36c0b625289000369"
          },
          {
            "$oid": "59d499c6b81b12198f017339"
          },
          {
            "$oid": "59d5cbd8f619afcd6100143e"
          },
          {
            "$oid": "59e81f7dc02c6cccdb002326"
          },
          {
            "$oid": "59f34e32af2dfff71200015b"
          },
          {
            "$oid": "59f9260543984267ca000a2e"
          },
          {
            "$oid": "5a1bf77b3c496e8b3b0000be"
          },
          {
            "$oid": "5a1e53f0af2dffcd1900006e"
          },
          {
            "$oid": "5a2916c9f94ffd779d02c53f"
          },
          {
            "$oid": "5a291578fc1229ea2602766a"
          },
          {
            "$oid": "5a2926a48e6bd526cc000640"
          },
          {
            "$oid": "5a3b829c705aac56db095be1"
          },
          {
            "$oid": "5a3b854fdf967501d908acf1"
          },
          {
            "$oid": "5a3f20ca8e6bd51454009bf7"
          },
          {
            "$oid": "5a40b5cdfce08787d8003717"
          },
          {
            "$oid": "5a56fffbfce087dad5002733"
          },
          {
            "$oid": "5a619945f94ffde8b9013baf"
          },
          {
            "$oid": "5a65794598a804b5a6000f18"
          },
          {
            "$oid": "5a6a73d28479d27313003ced"
          },
          {
            "$oid": "5a6fcd26cd869d307f003ee5"
          },
          {
            "$oid": "5a73f3c440cab511e8001940"
          },
          {
            "$oid": "5a7a984e8479d2e1e3001bed"
          },
          {
            "$oid": "5a7a9e62d784c0ff3d002220"
          },
          {
            "$oid": "5a7fce5ffce087a79d00959e"
          },
          {
            "$oid": "5a840af2fce087d0d2011715"
          },
          {
            "$oid": "5a854ce2c4088d28ab00078c"
          },
          {
            "$oid": "5a864760c4088d1329000baa"
          },
          {
            "$oid": "5a8a5351fce08754b8006141"
          },
          {
            "$oid": "5a8a5719fce087f315006f9d"
          },
          {
            "$oid": "5a8d6e1298a804f22c003670"
          },
          {
            "$oid": "5a9547b2c4088d3180001890"
          },
          {
            "$oid": "5a9ca1f9be1af73717037eb0"
          },
          {
            "$oid": "5a9e2488be1af7db04006912"
          },
          {
            "$oid": "5aa883b2cd869d4881004dcd"
          },
          {
            "$oid": "5ab098f58e6bd5b37600143e"
          },
          {
            "$oid": "5ab09f258e6bd5d81a007930"
          },
          {
            "$oid": "5ab9bf77c2cf3fa3b9002f11"
          },
          {
            "$oid": "5abc6fdcd5d72c3e1a0005dd"
          },
          {
            "$oid": "5abc906797acdab1e8001955"
          },
          {
            "$oid": "5ac5b0b1c4088d4082000a65"
          },
          {
            "$oid": "5acdb09ac2cf3f6e72003c3a"
          },
          {
            "$oid": "5ace1de88e6bd599b7004a8c"
          },
          {
            "$oid": "5acec2a27102dac338004a5d"
          },
          {
            "$oid": "5afb1cd68e6bd508220014be"
          },
          {
            "$oid": "5b051541d784c0cba100f52a"
          },
          {
            "$oid": "5b0f8820cd869da4a003414c"
          },
          {
            "$oid": "5b1f6dca1437b6fc0b00034b"
          },
          {
            "$oid": "5b2077766ccbcdb3b302aec5"
          },
          {
            "$oid": "5b20904bf1404ab61400e171"
          },
          {
            "$oid": "5b29e761616a3017ce08a954"
          },
          {
            "$oid": "5b2a33b1f1404aafb300308f"
          },
          {
            "$oid": "5b3af81fd38b0edbfd001c99"
          },
          {
            "$oid": "5b4624f61670dcc4be002e59"
          },
          {
            "$oid": "5b4858fa2a892ee308003ceb"
          },
          {
            "$oid": "5b4ed99fa9d215275100635c"
          },
          {
            "$oid": "5b4ed99476d09532a70060e1"
          },
          {
            "$oid": "5b59473f9b92b045b0001583"
          },
          {
            "$oid": "5b5ae3d7a9d21578f00083db"
          },
          {
            "$oid": "5b6167272a892e992601da05"
          },
          {
            "$oid": "5b63b0f22a892e7c1400097f"
          },
          {
            "$oid": "5b67a9b8fab9664f30013370"
          },
          {
            "$oid": "5b72832e05ccbfadb90246c5"
          },
          {
            "$oid": "5b78fe16ad9f54028b0028c2"
          },
          {
            "$oid": "5b7cfeccf1404a08930038d7"
          },
          {
            "$oid": "5b7e38b3f1404a03450023d4"
          },
          {
            "$oid": "5b83d3ccf1404a10a9001472"
          },
          {
            "$oid": "5b8f9ce24af4b82fbe0003b2"
          },
          {
            "$oid": "5b91e52f6dbb1e9e20000b14"
          },
          {
            "$oid": "5b961e616df94a45c80007fc"
          },
          {
            "$oid": "5b9897bd6ecab49b66000003"
          },
          {
            "$oid": "5ba08df56df94a690e00080e"
          },
          {
            "$oid": "5ba34c2eb8b58d2617001a15"
          },
          {
            "$oid": "5ba45a36f6cec42b39000fcc"
          },
          {
            "$oid": "5bac982009161189890002c4"
          },
          {
            "$oid": "5bb448b74af4b82f67000522"
          },
          {
            "$oid": "5bb5dada6df94a394e0010ed"
          },
          {
            "$oid": "5bbffb146df94a29f60008b7"
          },
          {
            "$oid": "5bc698b1fc3c5cdcf2000222"
          },
          {
            "$oid": "5bc6de2cf0644e9142001c1b"
          },
          {
            "$oid": "5bc6de0bf0644e9210001dc0"
          },
          {
            "$oid": "5bd2c278fc3c5c675a0024ec"
          },
          {
            "$oid": "5bdbbb346df94a40490011c6"
          },
          {
            "$oid": "5bdfee18ac6cb1b287004033"
          },
          {
            "$oid": "5be00248f6cec4b284000d85"
          },
          {
            "$oid": "5be122f4cd516505b4000981"
          },
          {
            "$oid": "5be4315afc3c5cc820001def"
          },
          {
            "$oid": "5bf281072c5c61b696000b3a"
          },
          {
            "$oid": "5bf53a2cabb322879e000c8a"
          },
          {
            "$oid": "5bf6300e40b70f719f000dc0"
          },
          {
            "$oid": "5bfb7e9d40b70f53d2000386"
          },
          {
            "$oid": "5bfba65e0ee3820d5f00091f"
          },
          {
            "$oid": "5bffd2635c20eca338002257"
          },
          {
            "$oid": "5c061f745c31ca9b430001c3"
          },
          {
            "$oid": "5c062752b7b9ae46e5000d89"
          },
          {
            "$oid": "5c08ea51dc0e16270c00063e"
          },
          {
            "$oid": "5c09ec1567806d9781000fee"
          },
          {
            "$oid": "5c0a4187c5659d178f0031ae"
          },
          {
            "$oid": "5c0dd51502fffe22a5000050"
          },
          {
            "$oid": "5c0deb0840b70f1b880023cc"
          },
          {
            "$oid": "5c0dec0e40b70f0c55001b6a"
          },
          {
            "$oid": "5c0de9a6465d0a466a000a31"
          },
          {
            "$oid": "5c0df25b5c20ec283c002282"
          },
          {
            "$oid": "5c0e19ec07eb5b2595000a58"
          },
          {
            "$oid": "5c0f40c14661fe1721006aff"
          },
          {
            "$oid": "5c0f4874c533bf46580012e6"
          },
          {
            "$oid": "5c11c5894eee2220f3000149"
          },
          {
            "$oid": "5c11f43f40b70f5224001aed"
          },
          {
            "$oid": "5c12119841ffd86177000ca3"
          },
          {
            "$oid": "5c123416b60bdd4d670024ec"
          },
          {
            "$oid": "5c131e1440b70f59ef0018dc"
          },
          {
            "$oid": "5c16fcbca74543315f00075c"
          },
          {
            "$oid": "5c1728660ee3821ffb000ea4"
          },
          {
            "$oid": "5c189e9b8ccb99fab7001e69"
          },
          {
            "$oid": "5c1af4a00ee382e7f5000115"
          },
          {
            "$oid": "5c1afec340b70f1f21000adc"
          },
          {
            "$oid": "5c1c5dab62f8cb938f001898"
          },
          {
            "$oid": "5c231769c63f7d410c001304"
          },
          {
            "$oid": "5c2321dd58c9051d02004996"
          },
          {
            "$oid": "5c23232f51f3e2dda7002299"
          },
          {
            "$oid": "5c2329e140b70ffab100282a"
          },
          {
            "$oid": "5c25e7177b4016d169000217"
          },
          {
            "$oid": "5c25f35758c905f128009c2e"
          },
          {
            "$oid": "5c25f49158c905f27a00d114"
          },
          {
            "$oid": "5c3076ea415ee3e27b0003ee"
          },
          {
            "$oid": "5c3307da58c90558a80092eb"
          },
          {
            "$oid": "5c3317e14661fe2360000489"
          },
          {
            "$oid": "5c3317fd7ea3800cbf003c07"
          },
          {
            "$oid": "5c3319504661fe23bf00088b"
          },
          {
            "$oid": "5c3456cb506058094b008a76"
          },
          {
            "$oid": "5c355ba441ffd882db0005a2"
          },
          {
            "$oid": "5c356104267f9c34180013f2"
          },
          {
            "$oid": "5c357a870ee3828bf2000c9a"
          },
          {
            "$oid": "5c35873c4661feed6b001e79"
          },
          {
            "$oid": "5c35888658c9052154007d3d"
          },
          {
            "$oid": "5c3589d9c99752c21c00364f"
          },
          {
            "$oid": "5c358a914661feedd5002457"
          },
          {
            "$oid": "5c358b2b58c9052154007fee"
          },
          {
            "$oid": "5c358b954661feed6b00249d"
          },
          {
            "$oid": "5c35b2b00ee382f87e00035f"
          },
          {
            "$oid": "5c35b64d20d972269c0004cc"
          },
          {
            "$oid": "5c36f98f40b70fde4c001260"
          },
          {
            "$oid": "5c36f84541ffd8ad86001729"
          },
          {
            "$oid": "5c37131f1374327f67000b3b"
          },
          {
            "$oid": "5c3801df4661fe58d90037a4"
          },
          {
            "$oid": "5c38042458c90502120023bf"
          },
          {
            "$oid": "5c380398506058130f002455"
          },
          {
            "$oid": "5c381940d66bd74e29000c2a"
          },
          {
            "$oid": "5c3d41b019e429f0cb000be5"
          },
          {
            "$oid": "5c3d41b15c20ec1483000781"
          },
          {
            "$oid": "5c3d822b0ee382ef1f000eb8"
          },
          {
            "$oid": "5c3d8cc807eb5ba3910003f6"
          },
          {
            "$oid": "5c3d8e55daa0cec9ad001764"
          },
          {
            "$oid": "5c3e8f747d74a94047000b85"
          },
          {
            "$oid": "5c3ea1b440b70f5cf5000cdb"
          },
          {
            "$oid": "5c4016816b673a5ae40003cd"
          },
          {
            "$oid": "5c4173e141ffd8812f000967"
          },
          {
            "$oid": "5c4186dc6b673aeaf3000e20"
          },
          {
            "$oid": "5c4431944ab5f8b5a5001803"
          },
          {
            "$oid": "5c46bc8b41ffd849f6000780"
          },
          {
            "$oid": "5c46d7125c20ec037900172f"
          },
          {
            "$oid": "5c47f75a4661fe137d006e26"
          },
          {
            "$oid": "5c480cba1947355ce80005bb"
          },
          {
            "$oid": "5c482782ef2ea65d4e000202"
          },
          {
            "$oid": "5c4827a607eb5b76ae00189e"
          },
          {
            "$oid": "5c483052c812a2585200018a"
          },
          {
            "$oid": "5c484ffec812a2581c000ecb"
          },
          {
            "$oid": "5c496a0e0ee382f84000001b"
          },
          {
            "$oid": "5c4ab6960ee3824094000ab3"
          },
          {
            "$oid": "5c4abba20ee3824094000c09"
          },
          {
            "$oid": "5c4d48f4fd2cb93368000275"
          },
          {
            "$oid": "5c4eba550ee382c36a000bd2"
          },
          {
            "$oid": "5c4fe0b841ffd89490000ba2"
          },
          {
            "$oid": "5c50283dcf1cae84900003c9"
          },
          {
            "$oid": "5c502b2007eb5b9443001665"
          },
          {
            "$oid": "5c5036941387255e320008bb"
          },
          {
            "$oid": "5c50530d40b70f6406002668"
          },
          {
            "$oid": "5c510f070ee382c4ee000490"
          },
          {
            "$oid": "5c510efb40b70fdf9c001070"
          },
          {
            "$oid": "5c5165734406a5585a001c54"
          },
          {
            "$oid": "5c516c364406a5586f002274"
          },
          {
            "$oid": "5c517e08c421ec5973000177"
          },
          {
            "$oid": "5c517e7a0172675bfc0001bc"
          },
          {
            "$oid": "5c517dc307eb5b2657000e7f"
          }
        ]
      }
    }
  },
  "planSummary": "IXSCAN \{ deleted_at: 1, shop_id: 1, st: 1 }, IXSCAN \{ deleted_at: 1, shop_id: 1, st: 1 }",
  "cursorid": {
    "$numberLong": "8607675802982378262"
  },
  "keysExamined": 23229740,
  "docsExamined": 23229740,
  "fromMultiPlanner": true,
  "numYields": 433327,
  "nreturned": 12478,
  "reslen": 16776510,
  "locks": {
    "ReplicationStateTransition": {
      "acquireCount": {
        "w": 433328
      }
    },
    "Global": {
      "acquireCount": {
        "r": 433328
      }
    },
    "Database": {
      "acquireCount": {
        "r": 433328
      }
    },
    "Collection": {
      "acquireCount": {
        "r": 433328
      }
    },
    "Mutex": {
      "acquireCount": {
        "r": 1
      }
    }
  },
  "storage": {
    "data": {
      "bytesRead": {
        "$numberLong": "543712288884"
      },
      "bytesWritten": 8159393,
      "timeReadingMicros": {
        "$numberLong": "2303346467"
      },
      "timeWritingMicros": 12094801
    },
    "timeWaitingMicros": {
      "cache": 32903002
    }
  },
  "protocol": "op_msg",
  "durationMillis": 5104820,
  "v": "4.4.8"
}

Edited by MongoDB for formatting purposes

duplicates

SERVER-21697 Plan ranking should take query and index keys into consideration for breaking ties

Closed

Details

Description

Attachments

Issue Links

Activity

People

Dates