[SERVER-15092] count is greater than itcount Created: 29/Aug/14  Updated: 29/Aug/14  Resolved: 29/Aug/14

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

Type: Bug Priority: Major - P3
Reporter: Kay Agahd Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-3645 Sharded collection counts (on primary... Closed
Operating System: ALL
Participants:

 Description   

We are running a sharded mongodb cluster (v2.6.1).
Counting and iterating returns different results.
For example:

mongos> db.offer.count({shopId:6160})
520472
mongos> db.offer.find({shopId:6160}).itcount()
516564

Projection of _id returns correctly the same:

mongos> db.offer.find({shopId:6160},{_id:1}).itcount()
516564

The indexes are as follows:

mongos> db.offer.getIndexKeys()
[
	{
		"_id" : 1
	},
	{
		"bokey" : 1
	},
	{
		"categoryBokey" : 1
	},
	{
		"mappedCatalogCategory" : 1
	},
	{
		"clickCount" : 1
	},
	{
		"missingSince" : 1
	},
	{
		"shopId" : 1,
		"merchantId" : 1
	},
	{
		"asin" : 1
	},
	{
		"importGroup" : 1,
		"importId" : 1,
		"missingSince" : 1
	},
	{
		"shopId" : 1,
		"_id" : 1
	}
]

Using explicitly either index shopId, _id or shopId, merchantId returns the same number of documents as itcount:

mongos> db.offer.find({shopId:6160},{_id:1}).hint({shopId:1, _id:1}).explain()
{
	"clusteredType" : "ParallelSort",
	"shards" : {
		"offerStoreDE1/s479:27017,s480:27017,s483:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1__id_1",
				"isMultiKey" : false,
				"n" : 94115,
				"nscannedObjects" : 94115,
				"nscanned" : 94115,
				"nscannedObjectsAllPlans" : 94115,
				"nscannedAllPlans" : 94115,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 735,
				"nChunkSkips" : 0,
				"millis" : 695,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"_id" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s480:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE2/s127:27017,s131:27017,s136:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1__id_1",
				"isMultiKey" : false,
				"n" : 46393,
				"nscannedObjects" : 48801,
				"nscanned" : 48801,
				"nscannedObjectsAllPlans" : 48801,
				"nscannedAllPlans" : 48801,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 381,
				"nChunkSkips" : 2408,
				"millis" : 454,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"_id" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s131:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE3/s124:27017,s129:27017,s132:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1__id_1",
				"isMultiKey" : false,
				"n" : 55811,
				"nscannedObjects" : 57100,
				"nscanned" : 57100,
				"nscannedObjectsAllPlans" : 57100,
				"nscannedAllPlans" : 57100,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 446,
				"nChunkSkips" : 1289,
				"millis" : 526,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"_id" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s129:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE4/s125:27017,s191:27017,s216:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1__id_1",
				"isMultiKey" : false,
				"n" : 46573,
				"nscannedObjects" : 46784,
				"nscanned" : 46784,
				"nscannedObjectsAllPlans" : 46784,
				"nscannedAllPlans" : 46784,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 365,
				"nChunkSkips" : 211,
				"millis" : 397,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"_id" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s216:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE5/s484:27017,s487:27017,s488:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1__id_1",
				"isMultiKey" : false,
				"n" : 125006,
				"nscannedObjects" : 125006,
				"nscanned" : 125006,
				"nscannedObjectsAllPlans" : 125006,
				"nscannedAllPlans" : 125006,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 976,
				"nChunkSkips" : 0,
				"millis" : 1577,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"_id" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s487:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE6/s561:27017,s562:27017,s563:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1__id_1",
				"isMultiKey" : false,
				"n" : 148666,
				"nscannedObjects" : 148666,
				"nscanned" : 148666,
				"nscannedObjectsAllPlans" : 148666,
				"nscannedAllPlans" : 148666,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 1161,
				"nChunkSkips" : 0,
				"millis" : 2274,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"_id" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s563:27017",
				"filterSet" : false
			}
		]
	},
	"cursor" : "BtreeCursor shopId_1__id_1",
	"n" : 516564,
	"nChunkSkips" : 3908,
	"nYields" : 4064,
	"nscanned" : 520472,
	"nscannedAllPlans" : 520472,
	"nscannedObjects" : 520472,
	"nscannedObjectsAllPlans" : 520472,
	"millisShardTotal" : 5923,
	"millisShardAvg" : 987,
	"numQueries" : 6,
	"numShards" : 6,
	"millis" : 2276
}
mongos> db.offer.find({shopId:6160},{_id:1}).hint({shopId:1, merchantId:1}).explain()
{
	"clusteredType" : "ParallelSort",
	"shards" : {
		"offerStoreDE1/s479:27017,s480:27017,s483:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1_merchantId_1",
				"isMultiKey" : false,
				"n" : 94115,
				"nscannedObjects" : 94115,
				"nscanned" : 94115,
				"nscannedObjectsAllPlans" : 94115,
				"nscannedAllPlans" : 94115,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 735,
				"nChunkSkips" : 0,
				"millis" : 723,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"merchantId" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s480:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE2/s127:27017,s131:27017,s136:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1_merchantId_1",
				"isMultiKey" : false,
				"n" : 46393,
				"nscannedObjects" : 48801,
				"nscanned" : 48801,
				"nscannedObjectsAllPlans" : 48801,
				"nscannedAllPlans" : 48801,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 381,
				"nChunkSkips" : 2408,
				"millis" : 438,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"merchantId" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s131:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE3/s124:27017,s129:27017,s132:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1_merchantId_1",
				"isMultiKey" : false,
				"n" : 55811,
				"nscannedObjects" : 57100,
				"nscanned" : 57100,
				"nscannedObjectsAllPlans" : 57100,
				"nscannedAllPlans" : 57100,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 446,
				"nChunkSkips" : 1289,
				"millis" : 526,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"merchantId" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s129:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE4/s125:27017,s191:27017,s216:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1_merchantId_1",
				"isMultiKey" : false,
				"n" : 46573,
				"nscannedObjects" : 46784,
				"nscanned" : 46784,
				"nscannedObjectsAllPlans" : 46784,
				"nscannedAllPlans" : 46784,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 365,
				"nChunkSkips" : 211,
				"millis" : 389,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"merchantId" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s216:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE5/s484:27017,s487:27017,s488:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1_merchantId_1",
				"isMultiKey" : false,
				"n" : 125006,
				"nscannedObjects" : 125006,
				"nscanned" : 125006,
				"nscannedObjectsAllPlans" : 125006,
				"nscannedAllPlans" : 125006,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 976,
				"nChunkSkips" : 0,
				"millis" : 965,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"merchantId" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s487:27017",
				"filterSet" : false
			}
		],
		"offerStoreDE6/s561:27017,s562:27017,s563:27017" : [
			{
				"cursor" : "BtreeCursor shopId_1_merchantId_1",
				"isMultiKey" : false,
				"n" : 148666,
				"nscannedObjects" : 148666,
				"nscanned" : 148666,
				"nscannedObjectsAllPlans" : 148666,
				"nscannedAllPlans" : 148666,
				"scanAndOrder" : false,
				"indexOnly" : false,
				"nYields" : 1161,
				"nChunkSkips" : 0,
				"millis" : 2496,
				"indexBounds" : {
					"shopId" : [
						[
							6160,
							6160
						]
					],
					"merchantId" : [
						[
							{
								"$minElement" : 1
							},
							{
								"$maxElement" : 1
							}
						]
					]
				},
				"server" : "s563:27017",
				"filterSet" : false
			}
		]
	},
	"cursor" : "BtreeCursor shopId_1_merchantId_1",
	"n" : 516564,
	"nChunkSkips" : 3908,
	"nYields" : 4064,
	"nscanned" : 520472,
	"nscannedAllPlans" : 520472,
	"nscannedObjects" : 520472,
	"nscannedObjectsAllPlans" : 520472,
	"millisShardTotal" : 5537,
	"millisShardAvg" : 922,
	"numQueries" : 6,
	"numShards" : 6,
	"millis" : 2500
}
mongos> 

So why count counts too much documents?



 Comments   
Comment by Kay Agahd [ 29/Aug/14 ]

Thanks Scott, much appreciated.

Comment by Scott Hernandez (Inactive) [ 29/Aug/14 ]

The issue description is a not inclusive enough and we can update it. In general count is larger in the face of many conditions for sharded collections.

You can use cleanupOrphans in 2.6: http://docs.mongodb.org/manual/reference/command/cleanupOrphaned/

Comment by Kay Agahd [ 29/Aug/14 ]

Also, SERVER-3645 states out "If migrations are in progress, this will result in overcounting" but we don't have migrations in progress because the balancer is off.

Comment by Kay Agahd [ 29/Aug/14 ]

SERVER-3645 states out that "there is no filter for the count" but we are using a filter. Is it nevertheless a duplicate?

How can we clean up the database to have identical counts?

Comment by Scott Hernandez (Inactive) [ 29/Aug/14 ]

Dup of SERVER-3645

Note the nChunkSkips counts in the explain output. Those are the skipped/unowned/orphaned docs which the query (and itcount) is excluding but count isn't.

Comment by Kay Agahd [ 29/Aug/14 ]

Btw. we do NOT use slaveOk, thus we are speaking with the Primaries.
Also, there are NO migrations running, because the balancer is disabled for some weeks already.

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