[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: |
|
||||||||||||||||||||
| Issue Links: |
|
||||||||||||||||||||
| 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).
CPU utilization is observed by linux tool: top Network traffic is observed by linux tool: sar The testing data is:
Query requests are:
|
| Comments |
| Comment by James Wahlin [ 29/Apr/21 ] | |||||
|
pengzhenyi you are right. The issue reported by this ticket has been addressed under 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 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 | |||||
| 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:
| |||||
| 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:
prepare the documents in mongo shell, like this:
then I use the benchmark in the attachment, like this:
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:
One thing we will be looking at in particular is what bearing |