Slow Query Analysis

Built-in Profiler

MongoDB provides a built-in query profiler, enabled via db.setProfilingLevel(n,m) command.

Profiler Level Settings

  1. Level 0 (profiling off): db.setProfilingLevel(0)
  2. Level 1 (record slow queries): db.setProfilingLevel(1, m) - m is millisecond threshold
  3. Level 2 (record all operations): db.setProfilingLevel(2)

Usage Example

// Set to record queries slower than 50ms
db.setProfilingLevel(1, 50)

// View profiling data
db.system.profile.find().sort({ts:-1}).limit(10)

Database Performance Problem Analysis

  1. Unreasonable application design

    • Frequent short connections
    • Unoptimized SQL queries
    • N+1 query problem

  2. Incorrect data types

    • Field type doesn’t match actual data

  3. Hardware configuration issues

    • CPU, memory, disk I/O insufficient

  4. Missing indexes

    • Frequently executing full table scans

EXPLAIN Basic Concepts

Output Fields

  1. id: Sequence number of SELECT operation in query
  2. type: Table access method (best to worst: system > const > eq_ref > ref > range > index > ALL)
  3. possible_keys: Possible indexes to use
  4. key: Actually used index
  5. rows: Estimated number of rows to examine

Common Optimization Scenarios

  1. Full table scan (type=ALL): Add appropriate indexes
  2. File sort (Using filesort): Add indexes for ORDER BY columns
  3. Temporary table (Using temporary): Optimize GROUP BY queries
  4. Index cover (Using index): Good situation