Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-39191

Performance regression for counts post-sharding

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: 4.0.5
    • Fix Version/s: 4.9.0
    • Component/s: Sharding
    • Labels:
    • Backwards Compatibility:
      Fully Compatible
    • Sprint:
      Query 2020-10-05, Query 2020-10-19
    • Case:

      Description

      To filter orphans during counts, sharded clusters avoid COUNT_SCAN in favor of an IXSCAN, and examine documents. Especially for queries that do not include the shard key, this may be difficult to avoid. But, the impact can be noticeable for some workloads.

      Original report follows:


      I have a simple collection with 40M documents distributed ver 2 shards. The documents contain a field named COUNTRY with a sparse index on it.

      I am trying to count documents with a specific value for COUNTRY. When counting on the shards directly the plan looks correct and uses COUNT_SCAN with the index, returns 6M docs on each shard without loading them.

      {{2019-01-11T16:03:02.643+0000 I COMMAND [conn102] command segmentation.contacts appName: "MongoDB Shell" command: count { count: "contacts", query:{ COUNTRY: "US" }, fields: {}, lsid: \{ id: UUID("60be11b5-6299-416b-9ead-8c58fd3656b8") }, $clusterTime: { clusterTime: Timestamp(1547221085, 2), signature:{ hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 }}, $db: "segmentation" } planSummary: COUNT_SCAN \{ COUNTRY: 1 } keysExamined:6923922 docsExamined:0 numYields:54093 reslen:340 locks:{ Global: { acquireCount:{ r: 54094 }}, Database: { acquireCount:{ r: 54094 }}, Collection: { acquireCount:{ r: 54094 }} } protocol:op_msg 2747ms}}
      

      Running the exact same query on MONGOS, the count is distributed to the shards but I can see in the logs of each shard that the plan changed and it used IDX_SCAN instead and loaded all the documents which was much slower.

      {{2019-01-11T16:04:13.104+0000 I COMMAND [conn111] command segmentation.contacts appName: "MongoDB Shell" command: count { count: "contacts", query:{ COUNTRY: "US" }, allowImplicitCollectionCreation: false, shardVersion: [ Timestamp(474, 1),ObjectId('5c37917aa0f162a86b270897') ], lsid: \{ id: UUID("9be8fb16-f158-4099-bf25-a3a0c97a33c2"), uid: BinData(0, E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855) }, $clusterTime: { clusterTime: Timestamp(1547222609, 1), signature:{ hash: BinData(0, 1F79735A80E06C35800DE9CDC4E92608B2759F1F), keyId: 6644928741854150685 }}, $client: { application:{ name: "MongoDB Shell" }, driver: \{ name: "MongoDB Internal Client", version: "4.0.5" }, os: \{ type: "Linux", name: "Ubuntu", architecture: "x86_64", version: "18.04" }, mongos: \{ host: "ip-192-168-169-237:27019", client: "127.0.0.1:59430", version: "4.0.5" } }, $configServerState: { opTime:{ ts: Timestamp(1547222609, 1), t: 1 }}, $db: "segmentation" } planSummary: IXSCAN \{ COUNTRY: 1 } keysExamined:6923921 docsExamined:6923921 numYields:54093 reslen:320 locks:{ Global: { acquireCount:{ r: 54094 }}, Database: { acquireCount:{ r: 54094 }}, Collection: { acquireCount:{ r: 54094 }} } protocol:op_msg 34784ms}}
      

      Can someone explain me why the plan changed or why the docs have to be scanned ? The performance impact is huge...

      Attached execution plan from MONGOS. This issue was also referenced in StackExchange 

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              ian.boros Ian Boros
              Reporter:
              jmoutte Julien Moutte
              Participants:
              Votes:
              2 Vote for this issue
              Watchers:
              23 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: