Why do I have slow running queries in my Amazon DocumentDB cluster?

Resolution

You might experience slow-running queries in your Amazon DocumentDB cluster for the following reasons:

• Undersized hardware
• Changes in workload
• Increased traffic
• Memory issues
• Suboptimal query plans

Monitor your workload

To determine the root cause of your database performance issues, check all server-wide resources that your instance uses. Monitor your workload to determine when the query performance was optimal and when the query began to take a long time to run.

Use CloudWatch metrics

Use the Amazon CloudWatch metrics for Amazon DocumentDB to analyze your cluster performance. To identify performance bottlenecks caused by insufficient resources, use metrics such as CPUUtilization and FreeableMemory. To understand memory optimization, use metrics such as BufferCacheHitRatio and LowMemNumOperationsThrottled.

Monitor operational-level metrics such as DocumentsInserted, DocumentsReturned, DocumentsDeleted, and TransactionsAborted to determine the workload on your instance. For more information, see Monitoring metrics and setting up alarms on your Amazon DocumentDB (with MongoDB compatibility) clusters.

Use Performance Insights

Use Performance Insights to evaluate your database workloads sliced by waits, queries, hosts, databases, and applications. Check the counter metrics and query information to determine the queries that contribute the most to database (DB) load. A database load that's higher than the Max vCPU value shows a throttled workload on the instance class. Analyze the query text to help tune your queries.

Important: Performance Insights reached its end of life on November 30, 2025. You can upgrade to the Advanced mode of Database insights. If you don't upgrade, then DB clusters that use Performance Insights will default to the Standard mode of Database Insights. Only the Advanced mode of Database Insights will support execution plans and on-demand analysis. If your clusters default to the Standard mode, then you might not be able to use these features on the console. To turn on the Advanced mode, see Turning on the Advanced mode of Database Insights for Amazon RDS and Turning on the Advanced mode of Database Insights for Amazon Aurora.

Use profiler logs

Use the Amazon DocumentDB profiler to log the run time and details of operations that are performed on your cluster. You can use profiler to monitor the slowest operations on your cluster. This helps you improve individual query performance and overall cluster performance.

Use database methods

Run the following currentOp() command on your Amazon DocumentDB instance to periodically monitor the system usage over time:

db.adminCommand({currentOp: 1, $all: 1});

To count the number of queries and operations in each namespace in the system, run the following command:

db.adminCommand({
  aggregate: 1,
  pipeline: [
    {
      $currentOp: {
        allUsers: true,
        idleConnections: true
      }
    },
    {
      $group: {
        _id: {
          desc: "$desc",
          ns: "$ns",
          WaitState: "$WaitState"
        },
        count: {
          $sum: 1
        }
      }
    }
  ],
  cursor: {}
});

Use the command's output to determine the load on the system and take the appropriate action.

Improve your queries

After you identify long-running queries, tune your queries to improve your query performance.

Use the correct query planner

To improve performance, use optimal index plans and turn on index scan support for your operators. For Amazon DocumentDB 5.0 and later, use the query planner version 2.0 to improve find, update, delete, and find-and-modify commands with advanced query planning. For queries that don't select the best index, you can use plan cache filter API to designate indexes for specific query shapes.

Analyze the statistics of a query

To improve query performance, use executionStats to analyze how your queries run. executionStats provides the number of documents that are processed at each query stage, runtime duration for each stage, and the number of attempts that are made to generate a query plan.

To view the statistics of your query, run the following command:

query.explain("executionStats");

Example operation:

db.collection.find({}).limit(2).explain("executionStats");

The preceding example operation analyzes how a query performs when you try to find two documents.

Review the returned statistics. You can add an index to reduce the full collection scans and limit the number of documents that the query must scan. It's a best practice to test new indexes and evaluate performance. Use the cloning or snapshot restore option to create a test environment.

Check your instance for inefficient indexes

Every index increases write latency, CPU usage, I/O operations, and storage consumption on your resources. Although indexes can improve query performance, it's a best practice to periodically review your indexes and remove unused indexes.

To view a breakdown of how often each index is accessed, run the following command:

db.collection.aggregate([{$indexStats:{}}]).pretty()

To view the total number of index scans, run the following command:

db.collection.stats()

Compare the count from the preceding query to the number of collection scans. Then, analyze how often operations use indexes when they access a collection.

Close inactive cursors on your instance

The instance type that you use determines the number of active cursors that you can have on your instance. For more information, see Instance limits. Use CloudWatch to monitor the DatabaseCursors metric in CloudWatch to review the count of cursors that are open.

To view more details about open cursors, run following command:

db.runCommand("listCursors")

It's a best practice to close cursors after you use them to avoid throttling. Idle cursors use CPU and memory to monitor the queue that's associated with them.

Related information

Troubleshooting performance and resource utilization