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

Performance regression for counts post-sharding

XMLWordPrintableJSON

    • Type: Icon: Improvement Improvement
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 4.9.0
    • Affects Version/s: 4.0.5
    • Component/s: Sharding
    • Fully Compatible
    • Query 2020-10-05, Query 2020-10-19

      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 

        1. mongos_plan.txt
          13 kB
          Julien Moutte

            Assignee:
            ian.boros@mongodb.com Ian Boros
            Reporter:
            jmoutte Julien Moutte
            Votes:
            2 Vote for this issue
            Watchers:
            25 Start watching this issue

              Created:
              Updated:
              Resolved: