Slow query analysis and optimization

Definition and Impact of Slow Queries

Slow queries refer to database query operations that exceed a predefined time threshold. In MongoDB, the slow query threshold can be set using db.setProfilingLevel(), with queries exceeding 100 milliseconds being logged by default. Slow queries directly impact system performance, leading to increased response times, reduced throughput, and in severe cases, system crashes.

// Set the slow query threshold to 50 milliseconds
db.setProfilingLevel(1, { slowms: 50 })

Slow Query Log Analysis

MongoDB provides multiple ways to identify slow queries. The most direct method is to examine the system.profile collection, which records all queries exceeding the threshold. By analyzing these logs, you can understand query patterns, execution times, and resource consumption.

// View the 10 most recent slow queries
db.system.profile.find().sort({ ts: -1 }).limit(10).pretty()

Another useful tool is the explain() method, which provides a detailed query execution plan. Focus on the following metrics:

• executionTimeMillis: Query execution time
• totalKeysExamined: Number of index keys scanned
• totalDocsExamined: Number of documents scanned
• stage: Query execution stage (COLLSCAN indicates a full collection scan)

// Analyze query execution plan
db.users.find({ age: { $gt: 30 } }).explain("executionStats")

Common Causes of Slow Queries

Missing or Inappropriate Indexes

Indexes are critical for query performance. The absence of proper indexes forces MongoDB to perform full collection scans (COLLSCAN), which can be time-consuming for large collections. Even with indexes, queries that don't leverage them effectively can cause performance issues.

// Example of creating a compound index
db.orders.createIndex({ customerId: 1, orderDate: -1 })

Inefficient Query Patterns

Certain query patterns are inherently inefficient, such as:

• Using JavaScript expressions with $where
• Regular expression queries starting with wildcards (e.g., /^abc/)
• Excessive use of $or operators
• Returning excessively large result sets

// Example of an inefficient query
db.products.find({
  $or: [
    { name: /^.*phone.*$/i },
    { description: { $exists: true } }
  ]
})

Data Model Design Issues

Poor document structures can increase query complexity. Common problems include:

• Overly nested document structures
• Excessively large array fields
• Frequent cross-collection queries
• Denormalized data models

// Example of an overly nested document structure
{
  _id: 1,
  name: "John",
  orders: [
    {
      orderId: 101,
      items: [
        {
          productId: 1001,
          details: { /* more nesting */ }
        }
      ]
    }
  ]
}

Slow Query Optimization Strategies

Index Optimization

Creating appropriate indexes is one of the most effective ways to address slow queries. Consider:

1. Query patterns: Create indexes for frequently used query conditions
2. Sorting needs: Index fields used for sorting
3. Compound index order: Follow the ESR rule (Equality-Sort-Range)
4. Index selectivity: High-selectivity fields are better suited for indexing

// Optimized compound index example
db.orders.createIndex({
  status: 1,       // Equality query field
  orderDate: -1,   // Sort field
  amount: 1        // Range query field
})

Query Rewriting

Optimizing the query itself can significantly improve performance:

• Avoid $where and JavaScript expressions
• Limit the number of returned fields (use projection)
• Add appropriate query conditions to narrow the result set
• Use $elemMatch to optimize array queries

// Optimized query example
db.products.find(
  { 
    category: "electronics",
    price: { $lt: 1000 }
  },
  { 
    name: 1,
    price: 1,
    _id: 0 
  }
).sort({ price: -1 })

Pagination Optimization

For large datasets, traditional skip/limit pagination is inefficient. Better approaches include:

• Range-based pagination (track the last seen ID or value)
• Avoid large offsets with skip()
• Consider using cursors

// Range-based pagination example
const lastSeenPrice = 500; // Price of the last record on the previous page
db.products.find({ price: { $lt: lastSeenPrice } })
           .sort({ price: -1 })
           .limit(10)

Advanced Optimization Techniques

Read-Write Separation

For read-heavy applications, consider:

• Setting read preference (readPreference) to secondary nodes
• Using replica sets to distribute read load
• Implementing application-level caching

// Setting read preference to secondary
const conn = new Mongo("replicaSetHost:port")
conn.setReadPref("secondary")

Aggregation Pipeline Optimization

MongoDB's aggregation pipeline is powerful but resource-intensive. Optimization tips:

• Use $match and $project early to reduce data volume
• Avoid unnecessary $unwind stages
• Consider indexes when using $lookup
• Leverage $facet for parallel processing

// Optimized aggregation pipeline example
db.orders.aggregate([
  { $match: { status: "completed", orderDate: { $gt: ISODate("2023-01-01") } } },
  { $project: { customerId: 1, total: 1, orderDate: 1 } },
  { $group: { _id: "$customerId", totalSpent: { $sum: "$total" } } },
  { $sort: { totalSpent: -1 } },
  { $limit: 100 }
])

Sharded Cluster Optimization

For large datasets, sharding can significantly improve query performance. Key considerations:

• Choose an appropriate shard key (high cardinality, even write distribution)
• Avoid hotspot issues
• Monitor shard balance status
• Consider hashed sharding strategies

// Enabling sharding example
sh.enableSharding("mydb")
sh.shardCollection("mydb.orders", { customerId: 1, orderDate: 1 })

Monitoring and Continuous Optimization

Slow query optimization is not a one-time task but requires ongoing monitoring and adjustment. MongoDB provides various monitoring tools:

• mongostat: Real-time database monitoring
• mongotop: Track read/write operation times
• Database commands: db.currentOp() and db.serverStatus()
• Atlas Performance Advisor (for MongoDB Atlas users)

// View currently running operations
db.currentOp({ "secs_running": { $gt: 5 } })

Establishing performance baselines and alert mechanisms is also important. Regularly run commands to collect performance metrics:

// Collect database status statistics
const stats = db.runCommand({ serverStatus: 1 })
const metrics = {
  operations: stats.opcounters,
  memory: stats.mem,
  connections: stats.connections
}

Real-World Case Studies

Case 1: E-commerce Platform Order Query Optimization

An e-commerce platform experienced slow order history queries. Analysis revealed:

1. Query conditions included user ID, date range, and order status
2. Existing indexes only covered user ID
3. Queries often scanned large numbers of documents

Solution:

// Create compound index
db.orders.createIndex({ 
  userId: 1,
  status: 1,
  orderDate: -1 
})

// Optimized query
db.orders.find({
  userId: "user123",
  status: "completed",
  orderDate: { $gt: ISODate("2023-01-01") }
}).sort({ orderDate: -1 })

Case 2: Social Media App Feed Optimization

A social media app's feed query had performance issues:

1. Needed to aggregate recent posts from all followed users
2. Used multiple $lookup operations
3. Result sets were too large and unpaginated

Optimization:

// More efficient aggregation pipeline
db.posts.aggregate([
  { $match: { 
    authorId: { $in: user.following },
    createdAt: { $gt: new Date(Date.now() - 7*24*60*60*1000) }
  }},
  { $sort: { createdAt: -1 } },
  { $limit: 50 },
  { $lookup: {
    from: "users",
    localField: "authorId",
    foreignField: "_id",
    as: "author",
    pipeline: [
      { $project: { name: 1, avatar: 1 } }
    ]
  }},
  { $unwind: "$author" }
])

Performance Testing and Benchmarking

Post-optimization, performance testing is essential to validate improvements. Methods include:

1. Using explain() to compare execution plans
2. Recording query times before and after optimization
3. Simulating production load with stress tests
4. Monitoring real-world performance in production

// Performance test example
const start = new Date()
const result = db.orders.find({ /* query conditions */ }).toArray()
const end = new Date()
const duration = end - start
print(`Query execution time: ${duration}ms`)

Related Tools and Resources

Beyond MongoDB's built-in tools, third-party tools can aid in slow query analysis:

• MongoDB Compass: GUI for viewing execution plans
• mtools: Log analysis toolkit
• Percona PMM: Monitoring and management tool
• Ops Manager: Enterprise-grade monitoring solution

For CI/CD environments, consider integrating performance tests into automated workflows:

// Simple performance test script example
function runPerformanceTest() {
  const testCases = [
    { name: "Order query", query: { status: "completed" } },
    { name: "User search", query: { name: /john/i } }
  ]
  
  testCases.forEach(test => {
    const start = Date.now()
    db.collection.find(test.query).limit(100).toArray()
    const duration = Date.now() - start
    print(`${test.name} execution time: ${duration}ms`)
  })
}