Block Slow Queries with Operation Rejection Filters

Create the example collection

Run:

 // Create pizzaOrders collection
 db.pizzaOrders.insertMany( [
   { _id: 0, type: "pepperoni", size: "small", price: 19,
     totalNumber: 10, orderDate: ISODate( "2023-03-13T08:14:30Z" ) },
   { _id: 1, type: "pepperoni", size: "medium", price: 20,
     totalNumber: 20, orderDate: ISODate( "2023-03-13T09:13:24Z" ) },
   { _id: 2, type: "pepperoni", size: "large", price: 21,
     totalNumber: 30, orderDate: ISODate( "2023-03-17T09:22:12Z" ) },
   { _id: 3, type: "cheese", size: "small", price: 12,
     totalNumber: 15, orderDate: ISODate( "2023-03-13T11:21:39.736Z" ) },
   { _id: 4, type: "cheese", size: "medium", price: 13,
     totalNumber: 50, orderDate: ISODate( "2024-01-12T21:23:13.331Z" ) },
   { _id: 5, type: "cheese", size: "large", price: 14,
     totalNumber: 10, orderDate: ISODate( "2024-01-12T05:08:13Z" ) },
   { _id: 6, type: "vegan", size: "small", price: 17,
     totalNumber: 10, orderDate: ISODate( "2023-01-13T05:08:13Z" ) },
   { _id: 7, type: "vegan", size: "medium", price: 18,
     totalNumber: 10, orderDate: ISODate( "2023-01-13T05:10:13Z" ) }
] )

Add an operation rejection filter

Run the following setQuerySettings command to add an operation rejection filter with these fields:

• find with a filter and a sort, which defines the query shape.
• $db with the database for the query settings.
• settings that reject the query shape.

db.adminCommand( {
   setQuerySettings: {
      find: "pizzaOrders",
      filter: {
         orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) }
      },
      sort: {
         totalNumber: 1
      },
      $db: "test"
   },
   settings: {
      reject: true
   }
} )

The following truncated output shows the queryShapeHash field value and the settings reject field is true:

{
   queryShapeHash: 'AB8ECADEE8F0EB0F447A30744EB4813AE7E0BFEF523B0870CA10FCBC87F5D8F1',
   settings: { reject: true },
   representativeQuery: {
      find: 'pizzaOrders',
      filter: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
      sort: { totalNumber: 1 },
      '$db': 'test'
   },
   ok: 1,
   ...
}

(Optional) Use the explain command to confirm settings

Run explain:

db.pizzaOrders.explain().find( { orderDate: { $gt: ISODate(
"2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 } )

The following truncated output shows the querySettings reject field is true:

{
   queryPlanner: {
     winningPlan: {
       stage: 'SINGLE_SHARD',
       shards: [
         {
           explainVersion: '1',
           ...
           namespace: 'test.pizzaOrders',
           parsedQuery: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
           querySettings: { reject: true },
           ...
         }
       ]
    }
}

(Optional) Verify the query is rejected

Attempt to run this query:

db.pizzaOrders.find( { orderDate: {
$gt: ISODate("2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 } )

The following output verifies the query is rejected:

MongoServerError: Query rejected by admin query settings

(Optional) List all operation rejection filters

To list all of the operation rejection filters, use the $querySettings and $match stages in an aggregation. In the following aggregation example:

• The showDebugQueryShape field is true in the $querySettings stage, which returns a debug version of the query shape. For details, see query shape statistics.
• The $match stage filters the query settings to keep those where settings.reject is set to true, which corresponds to the operation rejection filters.

Aggregation example:

db.aggregate( [
  { $querySettings: { showDebugQueryShape: true } },
  { $match: { "settings.reject": true } }
] )

Output:

[
   {
     queryShapeHash: 'AB8ECADEE8F0EB0F447A30744EB4813AE7E0BFEF523B0870CA10FCBC87F5D8F1',
     settings: { reject: true },
     representativeQuery: {
       find: 'pizzaOrders',
       filter: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
       sort: { totalNumber: 1 },
       '$db': 'test'
     },
     debugQueryShape: {
       cmdNs: { db: 'test', coll: 'pizzaOrders' },
       command: 'find',
       filter: { orderDate: { '$gt': '?date' } },
       sort: { totalNumber: 1 }
     }
   }
]

(Optional) Remove the operation rejection filter

The following example uses removeQuerySettings to remove the operation rejection filter, which are identified using the queryShapeHash value from the output shown in step 2 earlier:

db.adminCommand( {
   removeQuerySettings: "AB8ECADEE8F0EB0F447A30744EB4813AE7E0BFEF523B0870CA10FCBC87F5D8F1"
} )

You can also get the queryShapeHash value from:

• Atlas Query Profiler
• Log Messages

You can also remove an operation rejection filter using a query shape. For example:

db.adminCommand( {
   removeQuerySettings: {
      find: "pizzaOrders",
      filter: {
         orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) }
      },
      sort: {
         totalNumber: 1
      },
      $db: "test"
   }
} )

(Optional) Verify the query runs

Because the operation rejection filter is removed, the following query is unblocked and now runs:

db.pizzaOrders.find( { orderDate: {
$gt: ISODate("2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 } )