[SERVER-40361] Reduce memory footprint of plan cache entries Created: 27/Mar/19  Updated: 29/Oct/23  Resolved: 22/Oct/20

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 4.9.0, 4.4.3, 4.2.12, 3.6.23, 4.0.23

Type: Improvement Priority: Major - P3
Reporter: David Storch Assignee: David Storch
Resolution: Fixed Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
Documented
is documented by DOCS-13943 Investigate changes in SERVER-40361: ... Closed
Duplicate
duplicates SERVER-48400 Secondary node memory arise while bal... Closed
Related
related to SERVER-16895 Users should be able to request that ... Open
related to SERVER-40382 Add a serverStatus metric to report p... Closed
related to SERVER-51524 Alias internalQueryCacheSize to inter... Closed
is related to SERVER-34886 Plan cache size is bounded by number ... Backlog
is related to SERVER-40360 Process-global plan cache Backlog
Backwards Compatibility: Minor Change
Backport Requested:
v4.4, v4.2, v4.0, v3.6
Sprint: Query 2020-10-05, Query 2020-10-19, Query 2020-11-02
Participants:
Case:

 Description   

For some workloads, the per-collection query plan caches can consume too much memory. SERVER-34886 and SERVER-40360 together describe an architectural solution to this problem: turn the per-collection plan caches into a process-global plan cache, and bound the size of this new global cache in bytes rather than number of entries.

However, we could also reduce the memory footprint of the plan cache by auditing the debug information it holds in memory. We have observed in the field that this problem is most severe when the user runs large queries; this can result in the information held in the plan cache for introspection taking up a lot of memory. Perhaps this debug information can be truncated if it exceeds some threshold, in order to reduce the amount of debug information present in exchange for avoidance of excessive memory consumption.

The suspected worst offenders are:



 Comments   
Comment by Githook User [ 29/Jan/21 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-40361 Don't store debug info once plan cache size grows large

Introduces a new setParameter,
'internalQueryCacheMaxSizeBytesBeforeStripDebugInfo'. When
the cumulative size of a mongod's plan caches exceeds this
threshold, additional plan cache entries are stored without
any debug info. This should help to prevent problems where
the plan caches collectively consume too much memory.

The default setting of the parameter is 0.5 GB, but it can
be configured by the operator at startup or at runtime.

(cherry picked from commit eeb4b8aaffbcbb236b2d02e35dad919b4fa0aa80)
(cherry picked from commit 65ad41f1df99bbdfabeb8235351d9c21f9eea142)
(cherry picked from commit 52b11e90efa467dbe6b55977e5d2239aba3f6ec4)
(cherry picked from commit e31f945ddc59c270ba61c44ca792f4d7058c1703)
Branch: v3.6
https://github.com/mongodb/mongo/commit/d30cfe058c3df6ab66c9d734892a9321c660b5f2

Comment by David Storch [ 29/Jan/21 ]

I've just merged the backport of this work to the 4.0 branch, to be released in 4.0.23. The patch is once again a bit different from the previous version for the 4.2 branch. The main important difference that I would like to point out is that 4.0 does not have the $planCacheStats aggregation metadata source, nor does it have some of the fields associated with each plan cache entry in more recent versions, such as "queryHash" and "planCacheKey". This means that the planCacheListQueryShapes and planCacheListPlans commands are the only ways to introspect the plan cache in 4.0, and it means that the information available can be quite sparse when debug info has been stripped.

Below I have provided examples of what the output from the planCacheListQueryShapes and planCacheListPlans commands looks like in 4.0 when debug info has been stripped:

// The empty object in this output indicates that there is a plan cache entry, but its debug info has been stripped.
> db.runCommand({planCacheListQueryShapes: "c"})
{ "shapes" : [ { } ], "ok" : 1 }
 
// This output displays the details of the non-debug information in the plan cache entry which has not been stripped.
> db.runCommand({planCacheListPlans: "c", query: {a: 1, b: 1}})
{
	"plans" : [
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf a_1, pos: 0, can combine? 1\n---Leaf \n)"
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf b_1, pos: 0, can combine? 1\n)"
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf a_1, pos: 0, can combine? 1\n---Leaf b_1, pos: 0, can combine? 1\n)"
			},
			"filterSet" : false
		}
	],
	"timeOfCreation" : ISODate("2021-01-29T15:11:00.996Z"),
	"works" : NumberLong(1),
	"estimatedSizeBytes" : NumberLong(2297),
	"ok" : 1
}

Comment by Githook User [ 29/Jan/21 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-40361 Don't store debug info once plan cache size grows large

Introduces a new setParameter,
'internalQueryCacheMaxSizeBytesBeforeStripDebugInfo'. When
the cumulative size of a mongod's plan caches exceeds this
threshold, additional plan cache entries are stored without
any debug info. This should help to prevent problems where
the plan caches collectively consume too much memory.

The default setting of the parameter is 0.5 GB, but it can
be configured by the operator at startup or at runtime.

(cherry picked from commit eeb4b8aaffbcbb236b2d02e35dad919b4fa0aa80)
(cherry picked from commit 65ad41f1df99bbdfabeb8235351d9c21f9eea142)
(cherry picked from commit 52b11e90efa467dbe6b55977e5d2239aba3f6ec4)
Branch: v4.0
https://github.com/mongodb/mongo/commit/e31f945ddc59c270ba61c44ca792f4d7058c1703

Comment by Ian Whalen (Inactive) [ 07/Jan/21 ]

Author:

{'username': u'evrg-bot-webhook', 'name': u'David Storch', 'email': u'david.storch@mongodb.com'}

Message:SERVER-40361 Don't store debug info once plan cache size grows large

Introduces a new setParameter,
'internalQueryCacheMaxSizeBytesBeforeStripDebugInfo'. When
the cumulative size of a mongod's plan caches exceeds this
threshold, additional plan cache entries are stored without
any debug info. This should help to prevent problems where
the plan caches collectively consume too much memory.

The default setting of the parameter is 0.5 GB, but it can
be configured by the operator at startup or at runtime.

(cherry picked from commit eeb4b8aaffbcbb236b2d02e35dad919b4fa0aa80)
(cherry picked from commit 65ad41f1df99bbdfabeb8235351d9c21f9eea142)
Branch:v4.2
https://github.com/mongodb/mongo/commit/52b11e90efa467dbe6b55977e5d2239aba3f6ec4

Comment by David Storch [ 05/Jan/21 ]

I merged this change to the 4.2 branch earlier today, so the fix should be released as part of version 4.2.12. I would like to note an important difference between the version of this patch merged in 4.4 and the one for 4.2.

In 4.4 there is just one mechanism for viewing the contents of the plan cache: the $planCacheStats aggregation stage. When debug info is stripped from a plan cache entry, the information reported by $planCacheStats for that cache entry will be less verbose. The same is true in 4.2, but in addition to $planCacheStats the 4.2 branch supports the planCacheListQueryShapes and planCacheListPlans commands for viewing the contents of the plan cache. These commands will similarly produce less information for plan cache entries whose debug info has been stripped away. See example output below from these two commands for plan cache entries with and without debug info.

// planCacheListQueryShapes when debug info is present.
> db.runCommand({planCacheListQueryShapes: "c"})
{
	"shapes" : [
		{
			"query" : {
				"a" : 1,
				"b" : 1
			},
			"sort" : {
 
			},
			"projection" : {
 
			},
			"queryHash" : "43CAB4C5"
		}
	],
	"ok" : 1
}
 
// planCacheListQueryShapes when debug info is absent. The example query described by the
// (query, sort, projection) triple is considered debug info, so the shape is identified only by the
// 'queryHash' value.
> db.runCommand({planCacheListQueryShapes: "c"})
{ "shapes" : [ { "queryHash" : "43CAB4C5" } ], "ok" : 1 }
 
// planCacheListPlans when debug info is present.
> db.runCommand({planCacheListPlans: "c", query: {a: 1, b: 1}})
{
	"plans" : [
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf (a_1, ), pos: 0, can combine? 1\n---Leaf \n)"
			},
			"reason" : {
				"score" : 1.0002,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"b" : {
							"$eq" : 1
						}
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 1,
					"advanced" : 0,
					"needTime" : 0,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"docsExamined" : 0,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 1,
						"advanced" : 0,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"keyPattern" : {
							"a" : 1
						},
						"indexName" : "a_1",
						"isMultiKey" : false,
						"multiKeyPaths" : {
							"a" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"a" : [
								"[1.0, 1.0]"
							]
						},
						"keysExamined" : 0,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0
					}
				}
			},
			"feedback" : {
				"nfeedback" : 0,
				"scores" : [ ]
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf (b_1, ), pos: 0, can combine? 1\n)"
			},
			"reason" : {
				"score" : 1.0002,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"a" : {
							"$eq" : 1
						}
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 1,
					"advanced" : 0,
					"needTime" : 0,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"docsExamined" : 0,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 1,
						"advanced" : 0,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"keyPattern" : {
							"b" : 1
						},
						"indexName" : "b_1",
						"isMultiKey" : false,
						"multiKeyPaths" : {
							"b" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"b" : [
								"[1.0, 1.0]"
							]
						},
						"keysExamined" : 0,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0
					}
				}
			},
			"feedback" : {
 
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf (a_1, ), pos: 0, can combine? 1\n---Leaf (b_1, ), pos: 0, can combine? 1\n)"
			},
			"reason" : {
				"score" : 1.0000999999999998,
				"stats" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"a" : {
									"$eq" : 1
								}
							},
							{
								"b" : {
									"$eq" : 1
								}
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 1,
					"advanced" : 0,
					"needTime" : 0,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"docsExamined" : 0,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "AND_SORTED",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 1,
						"advanced" : 0,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"failedAnd_0" : 0,
						"failedAnd_1" : 0,
						"inputStages" : [
							{
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 1,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 1,
								"keyPattern" : {
									"a" : 1
								},
								"indexName" : "a_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"a" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"a" : [
										"[1.0, 1.0]"
									]
								},
								"keysExamined" : 0,
								"seeks" : 1,
								"dupsTested" : 0,
								"dupsDropped" : 0
							},
							{
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 0,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 0,
								"keyPattern" : {
									"b" : 1
								},
								"indexName" : "b_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"b" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"b" : [
										"[1.0, 1.0]"
									]
								},
								"keysExamined" : 0,
								"seeks" : 0,
								"dupsTested" : 0,
								"dupsDropped" : 0
							}
						]
					}
				}
			},
			"feedback" : {
 
			},
			"filterSet" : false
		}
	],
	"timeOfCreation" : ISODate("2021-01-05T22:46:04.402Z"),
	"queryHash" : "43CAB4C5",
	"planCacheKey" : "CEC1F6AF",
	"isActive" : false,
	"works" : NumberLong(1),
	"estimatedSizeBytes" : NumberLong(6043),
	"ok" : 1
}
 
// planCacheListPlans when debug info is absent. Several fields are now omitted from the 'plans' array,
// notably including the 'reason' field which contains each plan's score and a detailed description of the
// execution stats.
> db.runCommand({planCacheListPlans: "c", query: {a: 1, b: 1}})
{
	"plans" : [
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf (a_1, ), pos: 0, can combine? 1\n---Leaf \n)"
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf (b_1, ), pos: 0, can combine? 1\n)"
			},
			"filterSet" : false
		},
		{
			"details" : {
				"solution" : "(index-tagged expression tree: tree=Node\n---Leaf (a_1, ), pos: 0, can combine? 1\n---Leaf (b_1, ), pos: 0, can combine? 1\n)"
			},
			"filterSet" : false
		}
	],
	"timeOfCreation" : ISODate("2021-01-05T22:43:17.343Z"),
	"queryHash" : "43CAB4C5",
	"planCacheKey" : "CEC1F6AF",
	"isActive" : false,
	"works" : NumberLong(1),
	"estimatedSizeBytes" : NumberLong(3186),
	"ok" : 1
}

Comment by Githook User [ 20/Nov/20 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-40361 Don't store debug info once plan cache size grows large

Introduces a new setParameter,
'internalQueryCacheMaxSizeBytesBeforeStripDebugInfo'. When
the cumulative size of a mongod's plan caches exceeds this
threshold, additional plan cache entries are stored without
any debug info. This should help to prevent problems where
the plan caches collectively consume too much memory.

The default setting of the parameter is 0.5 GB, but it can
be configured by the operator at startup or at runtime.

(cherry picked from commit eeb4b8aaffbcbb236b2d02e35dad919b4fa0aa80)
Branch: v4.4
https://github.com/mongodb/mongo/commit/65ad41f1df99bbdfabeb8235351d9c21f9eea142

Comment by Githook User [ 22/Oct/20 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-40361 Don't store debug info once plan cache size grows large

Introduces a new setParameter,
'internalQueryCacheMaxSizeBytesBeforeStripDebugInfo'. When
the cumulative size of a mongod's plan caches exceeds this
threshold, additional plan cache entries are stored without
any debug info. This should help to prevent problems where
the plan caches collectively consume too much memory.

The default setting of the parameter is 0.5 GB, but it can
be configured by the operator at startup or at runtime.
Branch: master
https://github.com/mongodb/mongo/commit/eeb4b8aaffbcbb236b2d02e35dad919b4fa0aa80

Comment by David Storch [ 13/Oct/20 ]

I have developed a patch, currently in code review, which proposes adding a new setParameter called internalQueryCacheMaxSizeBytesBeforeStripDebugInfo. This new server parameter would be configurable at startup or runtime, and would default to 512 * 1024 * 1024 bytes, or 0.5 GB.

The implementation builds upon the estimate of total plan cache memory consumption added in SERVER-40382. Once this estimate of the cumulative size of all collections' plan caches exceeds internalQueryCacheMaxSizeBytesBeforeStripDebugInfo, the server will stop storing debug info alongside future cache entries. This means that once the plan cache is using 0.5 GB of server memory (in the default configuration), additional plan cache entries to be may be created, but they will be much smaller due to the absence of debug info. In most cases in the field where plan cache memory consumption has been a problem, the debug info was the biggest culprit. I'm therefore cautiously optimistic that this approach will dramatically reduce plan cache memory consumption for affected workloads. Also note that by setting internalQueryCacheMaxSizeBytesBeforeStripDebugInfo to zero, the operator can ensure that no debug info is stored in the plan cache whatsoever.

The advantage of this approach is that it is simple, and therefore likely eligible for backport at least to 4.4. Also, it does not affect the actual logic by which plans are cached and selected, so it is extremely unlikely to regress the performance of existing workloads. The disadvantage is that there is technically still no upper bound enforced in the total amount of memory consumed cumulatively by the system's plan caches. Each collection may have at most 5000 cache entries by default. With enough collections, even when debug info is not stored in these plan caches, the total amount of memory used for the caches could grow large. This problem should be solved with SERVER-40360. However, doing so would be a substantial architectural change that would not be eligible for backport.

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