[SERVER-41329] Improve skip performance in mongos when request is sent to a single shard Created: 28/May/19  Updated: 29/Apr/21  Resolved: 29/Apr/21

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: peng zhenyi Assignee: James Wahlin
Resolution: Duplicate Votes: 4
Labels: pull-request
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Text File planExplainAfter.txt     Text File planExplainBefore.txt     PNG File skipOptimize.png     File skiptest.go    
Issue Links:
Duplicate
duplicates SERVER-36290 find command on unsharded collection ... Closed
Related
is related to SERVER-10844 Single shard queries can be optimized... Closed
is related to SERVER-36290 find command on unsharded collection ... Closed
Sprint: Query Optimization 2021-05-03
Participants:

 Description   

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)



 Comments   
Comment by James Wahlin [ 29/Apr/21 ]

pengzhenyi you are right. The issue reported by this ticket has been addressed under SERVER-36290. This change is available currently in MongoDB 4.4

Thank you again for reporting this issue and for your pull request.

Comment by peng zhenyi [ 16/Apr/21 ]

But I also noticed the related issue SERVER-36290(https://jira.mongodb.org/browse/SERVER-36290) has already be merged to master since v4.3.

So, is this pull request duplicated on master? 

Comment by peng zhenyi [ 16/Apr/21 ]

Hi @James Wahlin,

I am still interested in this issue.

Comment by James Wahlin [ 15/Apr/21 ]

Hi pengzhenyi,

My apologies that we have been silent on this ticket for so long. The MongoDB Query team is now in a position to move forward with this issue. Are you still interested in working with us on it? Please let us know either way. If yes our next request would be to update your pull request for current master and we can proceed from there.

Thanks,

James

Comment by AN D [ 13/May/20 ]

Hi all, is there any plan for this improvement? It looks quite useful.

Comment by peng zhenyi [ 30/Sep/19 ]

Hi, Eric
I created a pull request for v4.0, which has been running in my private clusters for several months.
Would you please review this pull request again?
Pull request for master has been closed, because conflicts are frequent as time move on.

Comment by Eric Sedor [ 30/May/19 ]

Thanks pengzhenyi; We're passing this on to the appropriate team for further evaluation. You can watch this ticket for updates.

Comment by peng zhenyi [ 30/May/19 ]

Hi Eric , the explain plan results are in the attachments

Comment by Eric Sedor [ 29/May/19 ]

Thanks for the information so far, pengzhenyi.

Can you please also provide the results of an explain plan on one mongos of your test cluster before and after your optimization:

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

Comment by peng zhenyi [ 29/May/19 ]

The pull request is here: https://github.com/mongodb/mongo/pull/1311/commits/09d7faf954a0e633d45dc29f4e4ac556073992c1

Comment by peng zhenyi [ 29/May/19 ]

Hi Eric , I tested the idea again, with mongodb4.0 cluster: 3 mongos nodes, 3 mongod nodes( 1 shard), 3 cfgSvr nodes

create shardCollection like this:

 

use ycsb
sh.enableSharding("ycsb")
sh.shardCollection("ycsb.testcoll",{"a":"hashed"})

prepare the documents in mongo shell, like this:

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

then I use the benchmark in the attachment, like this:

./skiptest -collection='testcoll' -database='ycsb' -host='xxxx' -port=xxxx -username='xxxx' -password='xxxx' -limit=10 -skip=5000 -target=200 -threadnum=5

before optimize, this test cost 7.6 seconds, the network traffic is ~14MB/s

after optimize, this test cost 5.6 seconds, the network traffic is ~860KB/s

 

 

Comment by Eric Sedor [ 28/May/19 ]

Hi pengzhenyi. MongoDB 3.2 reached end of life in September of 2018. To help us reproduce what you are seeing in a supported version, can you please clarify:

  • how is the sharded cluster is configured (including number of shards, and shard key)?
  • what changes did you make in your optimization (that is, are you pointing to a pull request of any kind?)

One thing we will be looking at in particular is what bearing SERVER-10844 has on this.

Generated at Thu Feb 08 04:57:25 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.