[SERVER-15196] Hashed index causes 'Overflow hashed AND stage buffered data usage' error Created: 10/Sep/14  Updated: 10/Dec/14  Resolved: 10/Sep/14

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

Type: Bug Priority: Major - P3
Reporter: Nic Cottrell (Personal) Assignee: J Rassi
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-14961 Plan ranker favors intersection plans... Closed
Operating System: ALL
Participants:

 Description   

I have documents like:

{
  "_id": ObjectId("4cf567c4e4b08fffc3947252"),
  "kodoId": 1167286,
  "p": "/term/Japanese/Tanzania",
  "pl": "/term/japanese/tanzania",
  "r": "/viewWord.jsp?wordId=12083307",
  "l": "eng",
  "cd": new Date("2010-11-30T22:08:20+0100")
}

and a hashed index on 'pl' added yesterday. Some unit tests now fail with:

Overflow hashed AND stage buffered data usage of 33554468 bytes exceeds internal limit of 33554432 bytes

on query

{pl: 'blah', l: 'eng'}

with sort

{cd: -1}

for some reason if I switch to sort {_id: -1} the error goes away.



 Comments   
Comment by J Rassi [ 10/Sep/14 ]

I can confirm that this ticket is a duplicate of SERVER-14961. The getPlansByQuery() output shows that the problematic query shape uses and caches a plan that intersects the results of an index scan on {pl: "hashed"} with the results of an index scan on {l: 1, r:1}, when one of the index ranges is empty. If the same query shape is run on large non-overlapping ranges after the bad plan is cached, then the "hashed intersection" query stage will buffer a large amount of key data and potentially hit the 32MB per-stage buffer limit.

Resolving as a dup of SERVER-14961.

Comment by Nic Cottrell (Personal) [ 10/Sep/14 ]

Note sure which node in the cluster this died at, so using a mongos for now...

mongos> db.PathAlias.getIndexes()
[
	{
		"v" : 1,
		"name" : "_id_",
		"key" : {
			"_id" : 1
		},
		"ns" : "jerome5.PathAlias"
	},
	{
		"v" : 1,
		"name" : "p_1_l_1",
		"key" : {
			"p" : 1,
			"l" : 1
		},
		"unique" : true,
		"ns" : "jerome5.PathAlias"
	},
	{
		"v" : 1,
		"name" : "r_hashed",
		"key" : {
			"r" : "hashed"
		},
		"ns" : "jerome5.PathAlias"
	},
	{
		"v" : 1,
		"name" : "kodoId_1",
		"key" : {
			"kodoId" : 1
		},
		"ns" : "jerome5.PathAlias"
	},
	{
		"v" : 1,
		"name" : "l_1_r_1",
		"key" : {
			"l" : 1,
			"r" : 1
		},
		"ns" : "jerome5.PathAlias"
	},
	{
		"v" : 1,
		"name" : "r_1_cd_-1",
		"key" : {
			"r" : 1,
			"cd" : -1
		},
		"ns" : "jerome5.PathAlias"
	},
	{
		"v" : 1,
		"key" : {
			"p" : "hashed"
		},
		"name" : "p_hashed",
		"ns" : "jerome5.PathAlias"
	},
	{
		"v" : 1,
		"key" : {
			"pl" : "hashed"
		},
		"name" : "pl_hashed",
		"ns" : "jerome5.PathAlias"
	}
]

find({pl: 1, l: 1}).sort({cd: -1}).itcount()
0

db.PathAlias.getPlanCache().getPlansByQuery({pl: 1, l: 1}, {}, {cd: -1})
[
	{
		"details" : {
			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { l: 1.0, r: 1.0 }, pos: 0\n---Leaf { pl: \"hashed\" }, pos: 0\n)"
		},
		"reason" : {
			"score" : 1.0000999999999998,
			"stats" : {
				"type" : "KEEP_MUTATIONS",
				"works" : 6,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 0,
				"needTime" : 5,
				"needFetch" : 0,
				"isEOF" : 1,
				"children" : [
					{
						"type" : "OR",
						"works" : 6,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 0,
						"needTime" : 5,
						"needFetch" : 0,
						"isEOF" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"locsForgotten" : 0,
						"matchTested_0" : 0,
						"matchTested_1" : 0,
						"children" : [
							{
								"type" : "SORT",
								"works" : 3,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 0,
								"needTime" : 1,
								"needFetch" : 0,
								"isEOF" : 1,
								"forcedFetches" : 0,
								"memUsage" : 0,
								"memLimit" : 33554432,
								"children" : [
									{
										"type" : "SHARDING_FILTER",
										"works" : 1,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 0,
										"needTime" : 0,
										"needFetch" : 0,
										"isEOF" : 1,
										"chunkSkips" : 0,
										"children" : [
											{
												"type" : "FETCH",
												"works" : 1,
												"yields" : 0,
												"unyields" : 0,
												"invalidates" : 0,
												"advanced" : 0,
												"needTime" : 0,
												"needFetch" : 0,
												"isEOF" : 1,
												"alreadyHasObj" : 0,
												"forcedFetches" : 0,
												"matchTested" : 0,
												"children" : [
													{
														"type" : "AND_HASH",
														"works" : 1,
														"yields" : 0,
														"unyields" : 0,
														"invalidates" : 0,
														"advanced" : 0,
														"needTime" : 0,
														"needFetch" : 0,
														"isEOF" : 1,
														"flaggedButPassed" : 0,
														"flaggedInProgress" : 0,
														"memUsage" : 0,
														"memLimit" : 33554432,
														"children" : [
															{
															"type" : "IXSCAN",
															"works" : 2,
															"yields" : 0,
															"unyields" : 0,
															"invalidates" : 0,
															"advanced" : 0,
															"needTime" : 1,
															"needFetch" : 0,
															"isEOF" : 1,
															"keyPattern" : "{ l: 1.0, r: 1.0 }",
															"isMultiKey" : 0,
															"boundsVerbose" : "field #0['l']: [\"eng_US\", \"eng_US\"], field #1['r']: [MinKey, MaxKey]",
															"yieldMovedCursor" : 0,
															"dupsTested" : 0,
															"dupsDropped" : 0,
															"seenInvalidated" : 0,
															"matchTested" : 0,
															"keysExamined" : 0,
															"children" : [ ]
															},
															{
															"type" : "IXSCAN",
															"works" : 0,
															"yields" : 0,
															"unyields" : 0,
															"invalidates" : 0,
															"advanced" : 0,
															"needTime" : 0,
															"needFetch" : 0,
															"isEOF" : 0,
															"keyPattern" : "{}",
															"isMultiKey" : 0,
															"boundsVerbose" : "field #0['pl']: [3158721823503437376, 3158721823503437376]",
															"yieldMovedCursor" : 0,
															"dupsTested" : 0,
															"dupsDropped" : 0,
															"seenInvalidated" : 0,
															"matchTested" : 0,
															"keysExamined" : 0,
															"children" : [ ]
															}
														]
													}
												]
											}
										]
									}
								]
							},
							{
								"type" : "SORT",
								"works" : 3,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 0,
								"needTime" : 1,
								"needFetch" : 0,
								"isEOF" : 1,
								"forcedFetches" : 0,
								"memUsage" : 0,
								"memLimit" : 33554432,
								"children" : [
									{
										"type" : "SHARDING_FILTER",
										"works" : 1,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 0,
										"needTime" : 0,
										"needFetch" : 0,
										"isEOF" : 1,
										"chunkSkips" : 0,
										"children" : [
											{
												"type" : "FETCH",
												"works" : 1,
												"yields" : 0,
												"unyields" : 0,
												"invalidates" : 0,
												"advanced" : 0,
												"needTime" : 0,
												"needFetch" : 0,
												"isEOF" : 1,
												"alreadyHasObj" : 0,
												"forcedFetches" : 0,
												"matchTested" : 0,
												"children" : [
													{
														"type" : "AND_HASH",
														"works" : 1,
														"yields" : 0,
														"unyields" : 0,
														"invalidates" : 0,
														"advanced" : 0,
														"needTime" : 0,
														"needFetch" : 0,
														"isEOF" : 1,
														"flaggedButPassed" : 0,
														"flaggedInProgress" : 0,
														"memUsage" : 0,
														"memLimit" : 33554432,
														"children" : [
															{
															"type" : "IXSCAN",
															"works" : 2,
															"yields" : 0,
															"unyields" : 0,
															"invalidates" : 0,
															"advanced" : 0,
															"needTime" : 1,
															"needFetch" : 0,
															"isEOF" : 1,
															"keyPattern" : "{ l: 1.0, r: 1.0 }",
															"isMultiKey" : 0,
															"boundsVerbose" : "field #0['l']: [\"eng_US\", \"eng_US\"], field #1['r']: [MinKey, MaxKey]",
															"yieldMovedCursor" : 0,
															"dupsTested" : 0,
															"dupsDropped" : 0,
															"seenInvalidated" : 0,
															"matchTested" : 0,
															"keysExamined" : 0,
															"children" : [ ]
															},
															{
															"type" : "IXSCAN",
															"works" : 0,
															"yields" : 0,
															"unyields" : 0,
															"invalidates" : 0,
															"advanced" : 0,
															"needTime" : 0,
															"needFetch" : 0,
															"isEOF" : 0,
															"keyPattern" : "{}",
															"isMultiKey" : 0,
															"boundsVerbose" : "field #0['pl']: [3158721823503437376, 3158721823503437376]",
															"yieldMovedCursor" : 0,
															"dupsTested" : 0,
															"dupsDropped" : 0,
															"seenInvalidated" : 0,
															"matchTested" : 0,
															"keysExamined" : 0,
															"children" : [ ]
															}
														]
													}
												]
											}
										]
									}
								]
							}
						]
					}
				]
			}
		},
		"feedback" : {
			"nfeedback" : 20,
			"averageScore" : 1.0001,
			"stdDevScore" : 0,
			"scores" : [
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				},
				{
					"score" : 1.0001
				}
			]
		},
		"filterSet" : false
	},
	{
		"details" : {
			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { l: 1.0, r: 1.0 }, pos: 0\n---Leaf \n)"
		},
		"reason" : {
			"score" : 1.0002,
			"stats" : {
				"type" : "KEEP_MUTATIONS",
				"works" : 6,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 0,
				"needTime" : 6,
				"needFetch" : 0,
				"isEOF" : 0,
				"children" : [
					{
						"type" : "OR",
						"works" : 6,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 0,
						"needTime" : 6,
						"needFetch" : 0,
						"isEOF" : 0,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"locsForgotten" : 0,
						"matchTested_0" : 0,
						"matchTested_1" : 0,
						"children" : [
							{
								"type" : "SORT",
								"works" : 4,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 0,
								"needTime" : 2,
								"needFetch" : 0,
								"isEOF" : 1,
								"forcedFetches" : 0,
								"memUsage" : 0,
								"memLimit" : 33554432,
								"children" : [
									{
										"type" : "SHARDING_FILTER",
										"works" : 2,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 0,
										"needTime" : 1,
										"needFetch" : 0,
										"isEOF" : 1,
										"chunkSkips" : 0,
										"children" : [
											{
												"type" : "FETCH",
												"works" : 1,
												"yields" : 0,
												"unyields" : 0,
												"invalidates" : 0,
												"advanced" : 0,
												"needTime" : 1,
												"needFetch" : 0,
												"isEOF" : 1,
												"alreadyHasObj" : 0,
												"forcedFetches" : 0,
												"matchTested" : 0,
												"children" : [
													{
														"type" : "IXSCAN",
														"works" : 1,
														"yields" : 0,
														"unyields" : 0,
														"invalidates" : 0,
														"advanced" : 0,
														"needTime" : 1,
														"needFetch" : 0,
														"isEOF" : 1,
														"keyPattern" : "{ l: 1.0, r: 1.0 }",
														"isMultiKey" : 0,
														"boundsVerbose" : "field #0['l']: [\"eng_US\", \"eng_US\"], field #1['r']: [MinKey, MaxKey]",
														"yieldMovedCursor" : 0,
														"dupsTested" : 0,
														"dupsDropped" : 0,
														"seenInvalidated" : 0,
														"matchTested" : 0,
														"keysExamined" : 0,
														"children" : [ ]
													}
												]
											}
										]
									}
								]
							},
							{
								"type" : "SORT",
								"works" : 2,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 0,
								"needTime" : 1,
								"needFetch" : 0,
								"isEOF" : 0,
								"forcedFetches" : 0,
								"memUsage" : 0,
								"memLimit" : 33554432,
								"children" : [
									{
										"type" : "SHARDING_FILTER",
										"works" : 1,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 0,
										"needTime" : 1,
										"needFetch" : 0,
										"isEOF" : 1,
										"chunkSkips" : 0,
										"children" : [
											{
												"type" : "FETCH",
												"works" : 1,
												"yields" : 0,
												"unyields" : 0,
												"invalidates" : 0,
												"advanced" : 0,
												"needTime" : 1,
												"needFetch" : 0,
												"isEOF" : 1,
												"alreadyHasObj" : 0,
												"forcedFetches" : 0,
												"matchTested" : 0,
												"children" : [
													{
														"type" : "IXSCAN",
														"works" : 1,
														"yields" : 0,
														"unyields" : 0,
														"invalidates" : 0,
														"advanced" : 0,
														"needTime" : 1,
														"needFetch" : 0,
														"isEOF" : 1,
														"keyPattern" : "{ l: 1.0, r: 1.0 }",
														"isMultiKey" : 0,
														"boundsVerbose" : "field #0['l']: [\"eng_US\", \"eng_US\"], field #1['r']: [MinKey, MaxKey]",
														"yieldMovedCursor" : 0,
														"dupsTested" : 0,
														"dupsDropped" : 0,
														"seenInvalidated" : 0,
														"matchTested" : 0,
														"keysExamined" : 0,
														"children" : [ ]
													}
												]
											}
										]
									}
								]
							}
						]
					}
				]
			}
		},
		"feedback" : {
			
		},
		"filterSet" : false
	},
	{
		"details" : {
			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { pl: \"hashed\" }, pos: 0\n)"
		},
		"reason" : {
			"score" : 1.0002,
			"stats" : {
				"type" : "KEEP_MUTATIONS",
				"works" : 6,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 0,
				"needTime" : 6,
				"needFetch" : 0,
				"isEOF" : 0,
				"children" : [
					{
						"type" : "OR",
						"works" : 6,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 0,
						"needTime" : 6,
						"needFetch" : 0,
						"isEOF" : 0,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"locsForgotten" : 0,
						"matchTested_0" : 0,
						"matchTested_1" : 0,
						"children" : [
							{
								"type" : "SORT",
								"works" : 6,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 0,
								"needTime" : 4,
								"needFetch" : 0,
								"isEOF" : 1,
								"forcedFetches" : 0,
								"memUsage" : 0,
								"memLimit" : 33554432,
								"children" : [
									{
										"type" : "SHARDING_FILTER",
										"works" : 4,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 0,
										"needTime" : 3,
										"needFetch" : 0,
										"isEOF" : 1,
										"chunkSkips" : 0,
										"children" : [
											{
												"type" : "FETCH",
												"works" : 3,
												"yields" : 0,
												"unyields" : 0,
												"invalidates" : 0,
												"advanced" : 0,
												"needTime" : 3,
												"needFetch" : 0,
												"isEOF" : 1,
												"alreadyHasObj" : 0,
												"forcedFetches" : 0,
												"matchTested" : 0,
												"children" : [
													{
														"type" : "IXSCAN",
														"works" : 3,
														"yields" : 0,
														"unyields" : 0,
														"invalidates" : 0,
														"advanced" : 2,
														"needTime" : 1,
														"needFetch" : 0,
														"isEOF" : 1,
														"keyPattern" : "{ pl: \"hashed\" }",
														"isMultiKey" : 0,
														"boundsVerbose" : "field #0['pl']: [3158721823503437376, 3158721823503437376]",
														"yieldMovedCursor" : 0,
														"dupsTested" : 0,
														"dupsDropped" : 0,
														"seenInvalidated" : 0,
														"matchTested" : 0,
														"keysExamined" : 2,
														"children" : [ ]
													}
												]
											}
										]
									}
								]
							},
							{
								"type" : "SORT",
								"works" : 0,
								"yields" : 0,
								"unyields" : 0,
								"invalidates" : 0,
								"advanced" : 0,
								"needTime" : 0,
								"needFetch" : 0,
								"isEOF" : 0,
								"forcedFetches" : 0,
								"memUsage" : 0,
								"memLimit" : 33554432,
								"children" : [
									{
										"type" : "SHARDING_FILTER",
										"works" : 0,
										"yields" : 0,
										"unyields" : 0,
										"invalidates" : 0,
										"advanced" : 0,
										"needTime" : 0,
										"needFetch" : 0,
										"isEOF" : 0,
										"chunkSkips" : 0,
										"children" : [
											{
												"type" : "FETCH",
												"works" : 0,
												"yields" : 0,
												"unyields" : 0,
												"invalidates" : 0,
												"advanced" : 0,
												"needTime" : 0,
												"needFetch" : 0,
												"isEOF" : 0,
												"alreadyHasObj" : 0,
												"forcedFetches" : 0,
												"matchTested" : 0,
												"children" : [
													{
														"type" : "IXSCAN",
														"works" : 0,
														"yields" : 0,
														"unyields" : 0,
														"invalidates" : 0,
														"advanced" : 0,
														"needTime" : 0,
														"needFetch" : 0,
														"isEOF" : 0,
														"keyPattern" : "{}",
														"isMultiKey" : 0,
														"boundsVerbose" : "field #0['pl']: [3158721823503437376, 3158721823503437376]",
														"yieldMovedCursor" : 0,
														"dupsTested" : 0,
														"dupsDropped" : 0,
														"seenInvalidated" : 0,
														"matchTested" : 0,
														"keysExamined" : 0,
														"children" : [ ]
													}
												]
											}
										]
									}
								]
							}
						]
					}
				]
			}
		},
		"feedback" : {
			
		},
		"filterSet" : false
	}
]

mongos> db.PathAlias.find({pl: 1, l: 1}).sort({cd: -1}).explain(true)
{
	"clusteredType" : "ParallelSort",
	"shards" : {
		"sprawk/bruyere.sprawk.com:27018,n4-rs1.mongo.sprawk.com:27018" : [
			{
				"cursor" : "BtreeCursor l_1_r_1",
				"isMultiKey" : false,
				"n" : 0,
				"nscannedObjects" : 0,
				"nscanned" : 0,
				"nscannedObjectsAllPlans" : 0,
				"nscannedAllPlans" : 0,
				"scanAndOrder" : true,
				"indexOnly" : false,
				"nYields" : 0,
				"nChunkSkips" : 0,
				"millis" : 0,
				"indexBounds" : {
					"l" : [
						[
							1,
							1
						]
					],
					"r" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"allPlans" : [
					{
						"cursor" : "BtreeCursor l_1_r_1",
						"isMultiKey" : false,
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"scanAndOrder" : true,
						"indexOnly" : false,
						"nChunkSkips" : 0,
						"indexBounds" : {
							"l" : [
								[
									1,
									1
								]
							],
							"r" : [
								[
									{
										"$minElement" : 1
									},
									{
										"$maxElement" : 1
									}
								]
							]
						}
					},
					{
						"cursor" : "BtreeCursor pl_hashed",
						"isMultiKey" : false,
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"scanAndOrder" : true,
						"indexOnly" : false,
						"nChunkSkips" : 0,
						"indexBounds" : {
							"pl" : [
								[
									NumberLong("5902408780260971510"),
									NumberLong("5902408780260971510")
								]
							]
						}
					},
					{
						"cursor" : "Complex Plan",
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"nChunkSkips" : 0
					}
				],
				"server" : "bruyere.sprawk.com:27018",
				"filterSet" : false,
				"stats" : {
					"type" : "SORT",
					"works" : 4,
					"yields" : 0,
					"unyields" : 0,
					"invalidates" : 0,
					"advanced" : 0,
					"needTime" : 2,
					"needFetch" : 0,
					"isEOF" : 1,
					"forcedFetches" : 0,
					"memUsage" : 0,
					"memLimit" : 33554432,
					"children" : [
						{
							"type" : "KEEP_MUTATIONS",
							"works" : 2,
							"yields" : 0,
							"unyields" : 0,
							"invalidates" : 0,
							"advanced" : 0,
							"needTime" : 1,
							"needFetch" : 0,
							"isEOF" : 1,
							"children" : [
								{
									"type" : "SHARDING_FILTER",
									"works" : 2,
									"yields" : 0,
									"unyields" : 0,
									"invalidates" : 0,
									"advanced" : 0,
									"needTime" : 1,
									"needFetch" : 0,
									"isEOF" : 1,
									"chunkSkips" : 0,
									"children" : [
										{
											"type" : "FETCH",
											"works" : 1,
											"yields" : 0,
											"unyields" : 0,
											"invalidates" : 0,
											"advanced" : 0,
											"needTime" : 1,
											"needFetch" : 0,
											"isEOF" : 1,
											"alreadyHasObj" : 0,
											"forcedFetches" : 0,
											"matchTested" : 0,
											"children" : [
												{
													"type" : "IXSCAN",
													"works" : 1,
													"yields" : 0,
													"unyields" : 0,
													"invalidates" : 0,
													"advanced" : 0,
													"needTime" : 1,
													"needFetch" : 0,
													"isEOF" : 1,
													"keyPattern" : "{ l: 1.0, r: 1.0 }",
													"isMultiKey" : 0,
													"boundsVerbose" : "field #0['l']: [1.0, 1.0], field #1['r']: [MinKey, MaxKey]",
													"yieldMovedCursor" : 0,
													"dupsTested" : 0,
													"dupsDropped" : 0,
													"seenInvalidated" : 0,
													"matchTested" : 0,
													"keysExamined" : 0,
													"children" : [ ]
												}
											]
										}
									]
								}
							]
						}
					]
				}
			}
		],
		"sprawk2/n1-rs2.mongo.sprawk.com:27018,n3-rs2.mongo.sprawk.com:27018" : [
			{
				"cursor" : "BtreeCursor l_1_r_1",
				"isMultiKey" : false,
				"n" : 0,
				"nscannedObjects" : 0,
				"nscanned" : 0,
				"nscannedObjectsAllPlans" : 0,
				"nscannedAllPlans" : 0,
				"scanAndOrder" : true,
				"indexOnly" : false,
				"nYields" : 0,
				"nChunkSkips" : 0,
				"millis" : 0,
				"indexBounds" : {
					"l" : [
						[
							1,
							1
						]
					],
					"r" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"allPlans" : [
					{
						"cursor" : "BtreeCursor l_1_r_1",
						"isMultiKey" : false,
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"scanAndOrder" : true,
						"indexOnly" : false,
						"nChunkSkips" : 0,
						"indexBounds" : {
							"l" : [
								[
									1,
									1
								]
							],
							"r" : [
								[
									{
										"$minElement" : 1
									},
									{
										"$maxElement" : 1
									}
								]
							]
						}
					},
					{
						"cursor" : "BtreeCursor pl_hashed",
						"isMultiKey" : false,
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"scanAndOrder" : true,
						"indexOnly" : false,
						"nChunkSkips" : 0,
						"indexBounds" : {
							"pl" : [
								[
									NumberLong("5902408780260971510"),
									NumberLong("5902408780260971510")
								]
							]
						}
					},
					{
						"cursor" : "Complex Plan",
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"nChunkSkips" : 0
					}
				],
				"server" : "tor:27018",
				"filterSet" : false,
				"stats" : {
					"type" : "SORT",
					"works" : 4,
					"yields" : 0,
					"unyields" : 0,
					"invalidates" : 0,
					"advanced" : 0,
					"needTime" : 2,
					"needFetch" : 0,
					"isEOF" : 1,
					"forcedFetches" : 0,
					"memUsage" : 0,
					"memLimit" : 33554432,
					"children" : [
						{
							"type" : "KEEP_MUTATIONS",
							"works" : 2,
							"yields" : 0,
							"unyields" : 0,
							"invalidates" : 0,
							"advanced" : 0,
							"needTime" : 1,
							"needFetch" : 0,
							"isEOF" : 1,
							"children" : [
								{
									"type" : "SHARDING_FILTER",
									"works" : 2,
									"yields" : 0,
									"unyields" : 0,
									"invalidates" : 0,
									"advanced" : 0,
									"needTime" : 1,
									"needFetch" : 0,
									"isEOF" : 1,
									"chunkSkips" : 0,
									"children" : [
										{
											"type" : "FETCH",
											"works" : 1,
											"yields" : 0,
											"unyields" : 0,
											"invalidates" : 0,
											"advanced" : 0,
											"needTime" : 1,
											"needFetch" : 0,
											"isEOF" : 1,
											"alreadyHasObj" : 0,
											"forcedFetches" : 0,
											"matchTested" : 0,
											"children" : [
												{
													"type" : "IXSCAN",
													"works" : 1,
													"yields" : 0,
													"unyields" : 0,
													"invalidates" : 0,
													"advanced" : 0,
													"needTime" : 1,
													"needFetch" : 0,
													"isEOF" : 1,
													"keyPattern" : "{ l: 1.0, r: 1.0 }",
													"isMultiKey" : 0,
													"boundsVerbose" : "field #0['l']: [1.0, 1.0], field #1['r']: [MinKey, MaxKey]",
													"yieldMovedCursor" : 0,
													"dupsTested" : 0,
													"dupsDropped" : 0,
													"seenInvalidated" : 0,
													"matchTested" : 0,
													"keysExamined" : 0,
													"children" : [ ]
												}
											]
										}
									]
								}
							]
						}
					]
				}
			}
		],
		"sprawk3/n1-rs3.mongo.sprawk.com:27018,navarin:27018" : [
			{
				"cursor" : "BtreeCursor l_1_r_1",
				"isMultiKey" : false,
				"n" : 0,
				"nscannedObjects" : 0,
				"nscanned" : 0,
				"nscannedObjectsAllPlans" : 0,
				"nscannedAllPlans" : 0,
				"scanAndOrder" : true,
				"indexOnly" : false,
				"nYields" : 0,
				"nChunkSkips" : 0,
				"millis" : 0,
				"indexBounds" : {
					"l" : [
						[
							1,
							1
						]
					],
					"r" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"allPlans" : [
					{
						"cursor" : "BtreeCursor l_1_r_1",
						"isMultiKey" : false,
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"scanAndOrder" : true,
						"indexOnly" : false,
						"nChunkSkips" : 0,
						"indexBounds" : {
							"l" : [
								[
									1,
									1
								]
							],
							"r" : [
								[
									{
										"$minElement" : 1
									},
										{
										"$maxElement" : 1
									}
								]
							]
						}
					},
					{
						"cursor" : "BtreeCursor pl_hashed",
						"isMultiKey" : false,
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"scanAndOrder" : true,
						"indexOnly" : false,
						"nChunkSkips" : 0,
						"indexBounds" : {
							"pl" : [
								[
									NumberLong("5902408780260971510"),
									NumberLong("5902408780260971510")
								]
							]
						}
					},
					{
						"cursor" : "Complex Plan",
						"n" : 0,
						"nscannedObjects" : 0,
						"nscanned" : 0,
						"nChunkSkips" : 0
					}
				],
				"server" : "navarin:27018",
				"filterSet" : false,
				"stats" : {
					"type" : "SORT",
					"works" : 4,
					"yields" : 0,
					"unyields" : 0,
					"invalidates" : 0,
					"advanced" : 0,
					"needTime" : 2,
					"needFetch" : 0,
					"isEOF" : 1,
					"forcedFetches" : 0,
					"memUsage" : 0,
					"memLimit" : 33554432,
					"children" : [
						{
							"type" : "KEEP_MUTATIONS",
							"works" : 2,
							"yields" : 0,
							"unyields" : 0,
							"invalidates" : 0,
							"advanced" : 0,
							"needTime" : 1,
							"needFetch" : 0,
							"isEOF" : 1,
							"children" : [
								{
									"type" : "SHARDING_FILTER",
									"works" : 2,
									"yields" : 0,
									"unyields" : 0,
									"invalidates" : 0,
									"advanced" : 0,
									"needTime" : 1,
									"needFetch" : 0,
									"isEOF" : 1,
									"chunkSkips" : 0,
									"children" : [
										{
											"type" : "FETCH",
											"works" : 1,
											"yields" : 0,
											"unyields" : 0,
											"invalidates" : 0,
											"advanced" : 0,
											"needTime" : 1,
											"needFetch" : 0,
											"isEOF" : 1,
											"alreadyHasObj" : 0,
											"forcedFetches" : 0,
											"matchTested" : 0,
											"children" : [
												{
													"type" : "IXSCAN",
													"works" : 1,
													"yields" : 0,
													"unyields" : 0,
													"invalidates" : 0,
													"advanced" : 0,
													"needTime" : 1,
													"needFetch" : 0,
													"isEOF" : 1,
													"keyPattern" : "{ l: 1.0, r: 1.0 }",
													"isMultiKey" : 0,
													"boundsVerbose" : "field #0['l']: [1.0, 1.0], field #1['r']: [MinKey, MaxKey]",
													"yieldMovedCursor" : 0,
													"dupsTested" : 0,
													"dupsDropped" : 0,
													"seenInvalidated" : 0,
													"matchTested" : 0,
													"keysExamined" : 0,
													"children" : [ ]
												}
											]
										}
									]
								}
							]
						}
					]
				}
			}
		]
	},
	"cursor" : "BtreeCursor l_1_r_1",
	"n" : 0,
	"nChunkSkips" : 0,
	"nYields" : 0,
	"nscanned" : 0,
	"nscannedAllPlans" : 0,
	"nscannedObjects" : 0,
	"nscannedObjectsAllPlans" : 0,
	"millisShardTotal" : 0,
	"millisShardAvg" : 0,
	"numQueries" : 3,
	"numShards" : 3,
	"millis" : 2
}

Comment by J Rassi [ 10/Sep/14 ]

Could you please paste the output of running the following against this mongod with the mongo shell:

db.getSiblingDB("<db>").getCollection("<collection>").getIndexes()
db.getSiblingDB("<db>").getCollection("<collection>").find({pl: 1, l: 1}).sort({cd: -1}).itcount()
db.getSiblingDB("<db>").getCollection("<collection>").getPlanCache().getPlansByQuery({pl: 1, l: 1}, {}, {cd: -1})
db.getSiblingDB("<db>").getCollection("<collection>").find({pl: 1, l: 1}).sort({cd: -1}).explain(true)

In addition, could you upload the mongod log for this member as an attachment to the ticket? If you are unable to attach the entire log file, please paste just the generated excerpt starting with "ERROR: Runner error, stats:" from the log when you run the problematic query (find({pl: 'blah', l: 'eng'}).sort({cd: -1})). For example:

2014-09-10T05:41:55.289-0700 [conn1] ERROR: Runner error, stats:
{ "type" : "SORT",
  "works" : 8,
  "yields" : 0,
  "unyields" : 0,
  "invalidates" : 0,
  "advanced" : 0,
  "needTime" : 6,
  "needFetch" : 0,
  "isEOF" : 0,
  "forcedFetches" : 0,
  "memUsage" : 33554614,
  "memLimit" : 33554432,
  "children" : [
    { "type" : "COLLSCAN",
      "works" : 6,
      "yields" : 0,
      "unyields" : 0,
      "invalidates" : 0,
      "advanced" : 5,
      "needTime" : 1,
      "needFetch" : 0,
      "isEOF" : 0,
      "docsTested" : 5,
      "children" : [] } ] }

Thanks.

Generated at Thu Feb 08 03:37:15 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.