[SERVER-31280] Implementing paging using range queries, latency of last query is much higher Created: 27/Sep/17 Updated: 27/Oct/23 Resolved: 12/Jan/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 3.4.9 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Alessandro Gherardi | Assignee: | Backlog - Query Team (Inactive) |
| Resolution: | Gone away | Votes: | 1 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||||||
| Issue Links: |
|
||||||||||||
| Assigned Teams: |
Query
|
||||||||||||
| Operating System: | ALL | ||||||||||||
| Participants: | |||||||||||||
| Description |
|
I'm trying to implement paging using range queries. I have a collection called events that has, among others, a time attribute (type date). I defined a compound index with key {"time" : 1, "_id" : 1}. I use the script below to retrieve the events in pages. The query latency is very good (< 20 ms) for all queries except the last one. An explain shows that, unlike the other queries, the last query does not use the compound index. It looks like this has something to do with the value of startTime being equal to or very close to the value of lastTime.
|
| Comments |
| Comment by Alessandro Gherardi [ 02/Jan/18 ] | ||||||||||||||||
|
Thanks. Feel free to close. | ||||||||||||||||
| Comment by David Storch [ 07/Nov/17 ] | ||||||||||||||||
|
Hi agherardi,
Best, | ||||||||||||||||
| Comment by Alessandro Gherardi [ 31/Oct/17 ] | ||||||||||||||||
|
Hi Kelsey, Thanks, | ||||||||||||||||
| Comment by Alessandro Gherardi [ 13/Oct/17 ] | ||||||||||||||||
|
Hi Kelsey, Is there any chance that Thanks, | ||||||||||||||||
| Comment by Kelsey Schubert [ 13/Oct/17 ] | ||||||||||||||||
|
Hi agherardi, MongoDB 3.6.0-rc0 was announced today, which is the first release candidate for MongoDB 3.6. Please feel free to use it in testing environments. Kind regards, | ||||||||||||||||
| Comment by Alessandro Gherardi [ 09/Oct/17 ] | ||||||||||||||||
|
Hi Kelsey, Is the 3.5.X development branch what's going to become 3.6? Do you have an estimate for when 3.6 is going to be released? Alessandro | ||||||||||||||||
| Comment by Kelsey Schubert [ 09/Oct/17 ] | ||||||||||||||||
|
Hi agherardi, Thank you for uploading the data, we're able to reproduce this issue and are continuing to investigate. When mongod first executes query it will evaluate different plans (e.g. consider using different indexes) to retrieve the requested documents. To improve performance, it will cache the best plan and use this going forward. If a query is slower than expected, than it will reevaluate its plan. In MongoDB 3.4.2, we introduced In this case, it appears that mongod is not reevaluating its plan while executing the last query in your script. If you execute one additional final query at the end of your script,
I expect that it will cause mongod to reevaluate its cached query plan and cache a new plan in its place, and subsequent runs should not have large discrepancy in performance. Please note that the queries you are executing are much more performant in MongoDB 3.6, which generates tighter index bounds ( Kind regards, | ||||||||||||||||
| Comment by Alessandro Gherardi [ 09/Oct/17 ] | ||||||||||||||||
|
Hi Kelsey, Thank you, | ||||||||||||||||
| Comment by Alessandro Gherardi [ 29/Sep/17 ] | ||||||||||||||||
|
Thank you, Kelsey. I uploaded the data to the portal, as a zipped mongodump export. | ||||||||||||||||
| Comment by Kelsey Schubert [ 29/Sep/17 ] | ||||||||||||||||
|
Hi agherardi, Certainly, I've created a secure upload portal for you to use. Files uploaded to this portal are only visible to MongoDB employees investigating this issue and are routinely deleted after some time. Thanks again, | ||||||||||||||||
| Comment by Alessandro Gherardi [ 29/Sep/17 ] | ||||||||||||||||
|
Hi Kelsey, I attached the explain(true) outputs for both the last and the second-to-last queries. I can definitely provide you with our test data set. For privacy reasons, I'd prefer to upload the data to a non-public place that only Mongo support can access. Is that possible? If it is, can you please email host + credentials info to alessandro_DOT_gherardi_AT_yahoo_DOT_com. Thank you, | ||||||||||||||||
| Comment by Kelsey Schubert [ 28/Sep/17 ] | ||||||||||||||||
|
Hi agherardi, Thank you for reporting this behavior. So we can better understand what is going on here. Would you please provide the output of explain(true) for penultimate query as well as the problematic final query? Additionally, if you can provide a data set that we can run your script against that showcases this issue, it'll speed our investigation. Thanks again for your help, | ||||||||||||||||
| Comment by Alessandro Gherardi [ 27/Sep/17 ] | ||||||||||||||||
|
Here's the script output on my test DB:
|