[SERVER-15802] Query optimizer should always use equality predicate over unique index when possible Created: 24/Oct/14 Updated: 25/Jun/15 Resolved: 16/Jan/15 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.5 |
| Fix Version/s: | 2.6.8, 3.0.0-rc6 |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Migalin Danila | Assignee: | David Storch |
| Resolution: | Done | Votes: | 8 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||
| Backport Completed: | |||||||||||||||||||||
| Participants: | |||||||||||||||||||||
| Description |
|
After upgrading sharded cluster (shard key {uid:1}) from 2.4.9 to 2.6.5 I've began to notice periodic and sudden significant performance drops caused by huge read queue and lock on different shards. Logs shows slow queries like this:
This collection has indexes {uid: 1} and {_id: 1}, and in normal situation, queries like that uses "_id" index that is the fastest one. This problem seems to be very similar to |
| Comments |
| Comment by Githook User [ 06/Feb/15 ] | ||||||
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: (cherry picked from commit c3da2fc6642f143111d104c871f420d523f949b5) Conflicts: | ||||||
| Comment by Githook User [ 16/Jan/15 ] | ||||||
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: | ||||||
| Comment by David Storch [ 13/Jan/15 ] | ||||||
|
Hi miga, We appreciate that there are index selection problems in MongoDB's query optimizer that affect non-unique indices. The assessment of our development team about this case was that
If the fix for the unique index case is not sufficient, feel free to raise a new ticket describing a separate scenario. Best, | ||||||
| Comment by Migalin Danila [ 13/Jan/15 ] | ||||||
|
One small remark: original ticket was named "Wrong index selection", and non-unique indexes are affected by this issue too. | ||||||
| Comment by Migalin Danila [ 12/Nov/14 ] | ||||||
|
Thank you, Ramon, that's it.
| ||||||
| Comment by Ramon Fernandez Marina [ 12/Nov/14 ] | ||||||
|
miga, the behavior you're observing arises in the following scenario:
The way to address this in the server is not immediately clear, as a fix for this particular scenario may have undersired side-effects in other cases, so we're investigating a general solution. Until this issue is addressed, the workaround is to hint() on the desired index. Feel free to watch this ticket for progress in this matter. | ||||||
| Comment by Andrey Godin [ 11/Nov/14 ] | ||||||
|
Hi Ramon! | ||||||
| Comment by Ofer Cohen [ 11/Nov/14 ] | ||||||
|
See also this thread in the forum: | ||||||
| Comment by Migalin Danila [ 30/Oct/14 ] | ||||||
|
That's great. Anyway, I've already collected information you asked, you can check it here (logs are pretty big) https://yadi.sk/d/gXGjgvglcPxhB | ||||||
| Comment by Ramon Fernandez Marina [ 30/Oct/14 ] | ||||||
|
Hi miga, I think I'm able to reproduce the same behavior you're observing, so no need to provide additional information for now. Please watch this ticket for status updates. | ||||||
| Comment by Ramon Fernandez Marina [ 29/Oct/14 ] | ||||||
|
Sorry, my mistake – please try again | ||||||
| Comment by Migalin Danila [ 29/Oct/14 ] | ||||||
|
Ramon, ugh, sorry, but your link leads to this ticket and I don't see any of your comments before this one:
Mb your comment is under lock? | ||||||
| Comment by Ramon Fernandez Marina [ 29/Oct/14 ] | ||||||
|
miga, here's the link with the instructions on what additional information to provide. I agree having to use hints is not a good general solution, but we need this addition information to make progress investigating this ticket. Thanks, | ||||||
| Comment by Migalin Danila [ 29/Oct/14 ] | ||||||
|
Ramon, sorry, can you post a link to instructions?
I've just deployed hotfix that uses hints for this kind of queries, but it doesn't looks like a good general solution. | ||||||
| Comment by Ramon Fernandez Marina [ 29/Oct/14 ] | ||||||
|
Hi miga; please see my previous post with detailed instructions on what additional information to collect. The purpose of using explain() is not to force the right index to be used (that would be hint()), but to gather more information on why the query is using the wrong index altogether. | ||||||
| Comment by Migalin Danila [ 29/Oct/14 ] | ||||||
|
"Status:Waiting For User Input Waiting For User Input" I can confirm that explain() doesn't fix the problem, mongodb continues to use wrong index; the only way is to stepDown() master - mb just because most of queries moves to another mongodb instance that is not affected by subj. | ||||||
| Comment by Ramon Fernandez Marina [ 29/Oct/14 ] | ||||||
|
Hi miga, we'll need more information to diagnose this problem. Could you please follow the steps below and upload the resulting information? Note that your logs show two different collections, user_data and hidden_data – I've chosen one of them as the problem seems to appear in both, but please adjust if needed.
You can put each piece of information in a file, zip all files along with the mongod.log log files from your cluster and post it to this ticket. If the resulting file is too large (hundreds of megabytes) please let me know before and I'll send you different upload instructions. Thanks, | ||||||
| Comment by Migalin Danila [ 27/Oct/14 ] | ||||||
|
Well, looks like something is completely wrong - while observing subj on some shard, I've got this in slow-logs:
For some reason, {uid:1, data.mt:1}index was choosen to serve {_id: xxxx, uid: yyyy} queries. I've also tried to run db.coll.find(problem-query).explain() to re-evaluate query-plan as described in docs, but that didn't change anything. |