Why do queries run slowly in my Amazon DocumentDB cluster?

Short Description

The performance of your Amazon DocumentDB Cluster might be affected for any of the following reasons:

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

Use the steps in this article to improve Amazon DocumentDB cluster performance.

Resolution

Monitor your workload

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

Use Amazon CloudWatch metrics

Use the 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 like BufferCacheHitRatio and LowMemNumOperationsThrottled.

Monitor operational level metrics to understand the workload on your instance. Useful metrics include DocumentsInserted, DocumentsReturned, DocumentsDeleted and TransactionsAborted. For more information, see Amazon DocumentDB metrics and Monitor metrics and set up alarms on your Amazon DocumentDB clusters.

Use Performance Insights

Use Performance Insights to evaluate your database workloads sliced by waits, queries, hosts, databases, and applications. Capture counter and detailed query information, and evaluate which queries contribute most to database load. A database load (measured as average active sessions) that's higher than the Max vCPU value indicates throttled workload on the instance class. Analyze the query text to help tune your queries.

Use Profiler logs

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

Use database methods

Run the following currentOp() command on your Amazon DocumentDB instance periodically. Monitor the system usage over time.

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

The following query counts the number of queries and operations in each namespace in the system. After you run the query, analyze the system usage results to understand the load on the system and take appropriate action.

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

Optimize your queries

After you identify any long running queries, consider ways that can improve your query performances. Use the following approaches to tune a query.

Analyze the statistics of a query

Get the executionStats for the query as shown in the following example. This metric provides the number of documents returned from a particular stage. It also details the amount of run time spent at each stage, and how many tries it takes to generate a query plan.

db.runCommand({explain: {<query document>}});
or
query.explain("executionStats");

See the following example operation:

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

Based on the statistics, you can add an index. This helps to reduce the full collection scans and limits the number of documents it must scan. It's a best practice to test any 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 incurs additional write latency, CPU usage, I/O operations and the overall utilized storage on your resource. So although indexes can improve query performance, it's a best practice to review your indexes periodically and remove unused indexes.

Run a command similar to the following to view a breakdown of how often each index is accessed:

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

Run the following command to view the total number of scans that were performed with indexes (index scans). This count is compared to the number of scans performed without an index (collection scans). You can then analyze how often indexes are used during the operations performed on a collection.

db.collection.stats()

Close inactive cursors on your instance

There is a limit to the number of active cursors that are open on an instance at a given time. This limit depends on the type of Amazon DocumentDB instance you use. For more information, see Instance limits. Monitor the DatabaseCursors metric in CloudWatch to review the count of cursors that are open. Run a command similar to the following to view more details about open cursors.

db.runCommand("listCursors")

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

Related information

Performance and resource utilization