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

Improve skip performance in mongos when request is sent to a single shard

    • Type: Icon: Improvement Improvement
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization 2021-05-03

      There is big network traffic from mongod->mongos, when a find command is executed in mongos with big skip. like: db.collection.find({xxx}).sort({xxx}).skip(5000).limit(10)

      Mongos get all the documents from all shards with no skip, and then do skip in mongos, then return the final documents to client.

      In my opinion, this strategy is ok when the request is sent to multi-shards, because mongod s do not know how to skip.

      But the request is sent to a single shard,  it is a different situation. The target mongod knows how to skip correctly, so it is suitable to do skip in mongod, and no need to return too many redundancy documents,which would lead to less network traffic and less work for mongos.

      The idea is like below(the black area is what to be done):

      I tested this idea in mongos3.2 with Intel Xoen CPU and 10Gbps network, and I saw great performance impovement (10x).

      mongos version request num thread num total time cost(seconds) network traffic(MB/s) mongos-CPU(Peak) mongod-CPU(Peak)
      original 200 5 6.3 120 30% 13%
      after optimize 200 5 0.6 <1 1.7% 14%

      CPU utilization is observed by linux tool: top

      Network traffic is observed by linux tool: sar

      The testing data is:

      for (var i=0;i<10;i++) {db.testcoll.insert({a:1,b:i,c:"someBigString"}); sleep(10);}

      Query requests are: 

      db.testcoll.find({a:1}).skip(5000).limit(10)
      

        1. planExplainAfter.txt
          5 kB
        2. planExplainBefore.txt
          5 kB
        3. skipOptimize.png
          skipOptimize.png
          34 kB
        4. skiptest.go
          3 kB

            Assignee:
            james.wahlin@mongodb.com James Wahlin
            Reporter:
            pengzhenyi peng zhenyi
            Votes:
            4 Vote for this issue
            Watchers:
            17 Start watching this issue

              Created:
              Updated:
              Resolved: