Working with the PlanCache in MongoDB

I’ve been working a little with the PlanCache in MongoDB to troubleshoot some performance problems we’ve been experiencing. The contents of the Plan Cache are json documents (obviously) and this isn’t great to work with in the shell. Here’s a couple of javascript functions I’ve come up with to make things a little easier.

These are far from complete, are not well tested or suitable for all cases but they are a start for breaking down some of the complexity. If you have any suggestions or corrections let me know. I may add similar functionality to the mmo tool if I can get it to display nicely.

Display the cached queries by db / collection

?View Code JAVASCRIPT
use admin;
# List number of plan caches for each collection in a database
var dbs = db.runCommand({ "listDatabases": 1 }).databases;
 
dbs.forEach(function(database) {
	if(database.name != "config") {
		db = db.getSiblingDB(database.name)
		db.getCollectionNames().forEach(function(collection) {
			var plan_count = db[collection].getPlanCache().listQueryShapes().length;
			if(plan_count > 0) {
				print(db + "." + collection + " - " + plan_count.toString());
			}
		});
	}
});

Extract the critical statistics for each cached plan

Runs against each collection in the current database. This can enable you to quickly answer…

  • Number of candidate plans
  • Plan score
  • Number of documents returned
  • Number of documents examined
  • The index used
  • The number of index keys examined
?View Code JAVASCRIPT
db.getCollectionNames().forEach(function(collection) {
	db[collection].getPlanCache().listQueryShapes().forEach(function(queryShape) {
		var query = queryShape.query;
		print(db + "." + collection + "\n\n");
		printjson(query)
		var plans = db[collection].getPlanCache().getPlansByQuery(query);
		print("This query shape has " + plans.length.toString() + " plans.");
		if(plans.length > 0) {
			var plan_count = 0;
			plans.forEach(function(plan) {
				//printjson(plan);
				plan_count++;
				print("Plan " + plan_count.toString());
				print("score: " + plan.reason.score);
				print("nreturned: " + plan.reason.stats.nReturned);
				print("docsExamined: " + plan.reason.stats.docsExamined);
				print("stage: " + plan.reason.stats.inputStage.stage);
				print("indexName: " + plan.reason.stats.inputStage.indexName);
				print("keysExamined: " + plan.reason.stats.inputStage.keysExamined);
			});
		}
	});
});

Update: I added this functionality to the mm tool. It’s pretty basic and will more than likely only work with relatively simple queries.

Getting query stats can be invoked as follows…

./mm --plan_cache_query "{'restaurant_id': {'\$gt': 1.0}, 'name': {'\$gt': 'a'}}" --collection test.restaurants

The following data is displayed…

There are 2 cached plans for this query shape                                                       
{'restaurant_id': {'$gt': 1.0}, 'name': {'$gt': 'a'}}                                               
hostname           port   shard  db    collection   score   nReturned  docsExamined  stage   indexName                           keysExamined 
rhysmacbook.local  30001  rs0    test  restaurants  1.0003  0          0             IXSCAN  restaurant_id_1                     0            
rhysmacbook.local  30001  rs0    test  restaurants  1.0003  0          0             IXSCAN  name_1_borough_1_address.zipcode_1  0 

Leave a Reply