Skip to content

mongodb-guide.md

Latest commit

 

History

History
738 lines (554 loc) · 21 KB

mongodb-guide.md

File metadata and controls

738 lines (554 loc) · 21 KB

MongoDB Slow Query Detection Guide

This guide provides comprehensive information on using IQToolkit Analyzer for MongoDB performance analysis and optimization.

Table of Contents

  1. Overview
  2. Quick Start
  3. Configuration
  4. Usage Examples
  5. Analysis Features
  6. Deep Profiler Analysis
  7. Real-Time Operation Monitoring
  8. Report Formats
  9. Best Practices
  10. Troubleshooting

Overview

IQToolkit Analyzer provides comprehensive analysis of MongoDB query performance by:

  • Profiler Integration: Connects to MongoDB's built-in profiler to collect query execution data
  • Pattern Recognition: Groups similar queries and identifies common performance issues
  • Impact Analysis: Calculates performance impact scores based on execution time, frequency, and resource usage
  • Optimization Suggestions: Provides specific recommendations for improving query performance
  • Collection Analysis: Analyzes collection-level performance characteristics and index usage
  • Deep Profiler Analysis: Advanced analysis using mongo-toolkit for execution efficiency, index usage, and pipeline optimization (v0.3.0rc3+)
  • Real-Time Operations Monitoring: Monitors currently running operations for long-running and blocking queries (v0.3.0rc3+)
  • Real-Time Metrics Snapshot: Captures serverStatus, dbStats, and collStats for live health and capacity signals (v0.3.0rc3+; introduced in v0.3.0a6)
  • Comprehensive Reporting: Generates detailed reports in multiple formats (JSON, HTML, Markdown)

Key Features

  • ✅ Real-time Monitoring: Continuous monitoring of slow queries
  • ✅ Operation Monitoring: Real-time tracking of currently running operations (v0.3.0rc3+)
  • ✅ Real-Time Metrics: serverStatus/dbStats/collStats snapshot in reports (v0.3.0rc3+; introduced in v0.3.0a6)
  • ✅ Intelligent Grouping: Groups similar query patterns for focused analysis
  • ✅ Index Optimization: Detects missing indexes and collection scans
  • ✅ Aggregation Analysis: Specialized analysis for aggregation pipelines
  • ✅ Deep Profiler Analysis: Advanced insights using mongo-toolkit integration (v0.3.0rc3+)
  • ✅ Lock Contention Detection: Identifies lock waiting and contention patterns (v0.3.0rc3+)
  • ✅ Multi-format Reports: JSON, HTML, and Markdown report generation
  • ✅ Threshold Configuration: Customizable performance thresholds
  • ✅ Collection Insights: Per-collection performance analysis

Quick Start

1. Install Dependencies

# Install required MongoDB dependencies
pip install pymongo

# Install optional visualization dependencies
pip install matplotlib  # For chart generation

2. Create Configuration

# Create a sample configuration file
iqtoolkit-analyzer mongo config create --output mongodb_config.yml

3. Configure MongoDB Connection

Edit mongodb_config.yml:

connection:
  connection_string: "mongodb://localhost:27017"
  # Add authentication if needed
  # username: "your_username"
  # password: "your_password"

databases_to_monitor:
  - "your_database_name"

thresholds:
  slow_threshold_ms: 100.0
  critical_threshold_ms: 5000.0

4. Test Connection

iqtoolkit-analyzer mongo test-connection -c "mongodb://localhost:27017"

5. Run Analysis

# Single analysis
iqtoolkit-analyzer mongo analyze -c "mongodb://localhost:27017" -d myapp

# Save results to a file
iqtoolkit-analyzer mongo analyze -c "mongodb://localhost:27017" -d myapp -o ./reports/results.json

# Continuous monitoring (Ctrl+C to stop)
iqtoolkit-analyzer mongo monitor -c "mongodb://localhost:27017" -d myapp --interval 5

Configuration

Connection Settings

connection:
  connection_string: "mongodb://localhost:27017"
  connection_timeout_ms: 5000
  username: "monitoring_user"  # Optional
  password: "password"         # Optional
  auth_source: "admin"         # Optional
  use_ssl: true               # Optional

Performance Thresholds

thresholds:
  slow_threshold_ms: 100.0          # Queries slower than 100ms
  very_slow_threshold_ms: 1000.0    # Very slow threshold  
  critical_threshold_ms: 5000.0     # Critical threshold
  max_examined_ratio: 10.0          # Max examined/returned ratio
  min_frequency_for_analysis: 5     # Minimum frequency for analysis

Profiling Configuration

profiling:
  profiling_level: 1              # 0=off, 1=slow ops, 2=all ops
  enable_on_startup: true
  sample_rate: 1.0               # 1.0 = 100%, 0.1 = 10%
  profile_data_retention_hours: 24

Analysis Options

analysis:
  normalize_queries: true
  group_similar_queries: true
  analyze_collections: true
  analyze_index_usage: true
  suggest_new_indexes: true
  include_query_examples: true

Usage Examples

Command Line Interface

Tip: add --examples to any command to print example usage and exit.

Basic Analysis

# Analyze specific database
iqtoolkit-analyzer mongo analyze -c "mongodb://localhost:27017" -d myapp

# Save results to file
iqtoolkit-analyzer mongo analyze -c "mongodb://localhost:27017" -d myapp -o ./reports/results.json

# Verbose output
iqtoolkit-analyzer mongo analyze -c "mongodb://localhost:27017" -d myapp -v

Report Generation

# Save JSON results
iqtoolkit-analyzer mongo analyze -c "mongodb://localhost:27017" -d myapp -o ./reports/results.json

Continuous Monitoring

# Monitor with 5-minute intervals (300 seconds)
iqtoolkit-analyzer mongo monitor -c "mongodb://localhost:27017" -d myapp --interval 300

# Monitor with 10-second intervals
iqtoolkit-analyzer mongo monitor -c "mongodb://localhost:27017" -d myapp --interval 10

Programmatic Usage

Basic Analysis

from iqtoolkit_analyzer.mongodb_analyzer import MongoDBSlowQueryDetector
from iqtoolkit_analyzer.mongodb_config import MongoDBConfig

# Load configuration
config = MongoDBConfig.from_yaml_file('mongodb_config.yml')

# Create detector
detector = MongoDBSlowQueryDetector(
    config.get_effective_connection_string(),
    config.thresholds
)

# Initialize and analyze
if detector.initialize():
    slow_queries = detector.detect_slow_queries('myapp', time_window_minutes=60)
    
    for query in slow_queries:
        print(f"Slow query in {query.collection}:")
        print(f"  Duration: {query.avg_duration_ms:.1f}ms")
        print(f"  Frequency: {query.frequency}")
        print(f"  Impact Score: {query.impact_score:.1f}/100")

Report Generation

from iqtoolkit_analyzer.mongodb_report_generator import MongoDBReportGenerator

# Generate comprehensive report
report = detector.generate_comprehensive_report('myapp')

# Create report generator
generator = MongoDBReportGenerator(config)

# Generate HTML report
generator.generate_html_report(report, 'analysis_report.html')

# Generate charts
chart_files = generator.generate_charts(report, './charts')
print(f"Generated charts: {chart_files}")

Analysis Features

Real-Time Metrics Snapshot

The analyzer captures a lightweight, read-only snapshot of live database metrics:

  • serverStatus: connections, network, memory, lock activity, WiredTiger cache
  • dbStats: database size, object counts, index sizing
  • collStats: per-collection size, counts, and index sizes

These metrics are included in the report under realtime_metrics and can be used to identify immediate capacity or resource pressure signals.

Query Pattern Recognition

The system automatically identifies and groups similar queries:

// These queries are grouped as the same pattern:
db.users.find({name: "John", age: 25})
db.users.find({name: "Jane", age: 30}) 
db.users.find({name: "Bob", age: 45})

// Normalized pattern: {find: "?", filter: {name: "?", age: "?"}}

Performance Metrics

Each query pattern includes comprehensive metrics:

  • Duration Metrics: Average, minimum, maximum execution time
  • Frequency: Number of executions in the analysis window
  • Efficiency Score: Based on examined/returned document ratio
  • Impact Score: Weighted score considering duration, frequency, and resource usage
  • Index Usage: Execution plan analysis and index utilization

Collection-Level Analysis

Per-collection insights include:

  • Document count and storage size
  • Index count and utilization
  • Query patterns and performance
  • Optimization recommendations

Optimization Suggestions

Automated suggestions for:

  • Missing Indexes: Identifies fields that would benefit from indexing
  • Collection Scans: Detects queries scanning entire collections
  • Inefficient Queries: High examined/returned document ratios
  • Aggregation Optimization: Pipeline stage ordering and optimization
  • Query Restructuring: Suggestions for query improvements

Deep Profiler Analysis

New in v0.3.0rc3 - Advanced analysis using mongo-toolkit integration

The deep profiler analysis module provides sophisticated query optimization insights:

Analysis Categories

Execution Efficiency

Evaluates query selectivity and document scanning patterns:

  • Detects high examined-to-returned document ratios
  • Identifies collection scans on large datasets
  • Calculates scan efficiency metrics

Example:

⚠️  HIGH: Low query efficiency: scanned 50,000 docs but only returned 10
    Recommendation: Add index on status, created_at
    Impact: Could reduce docs scanned by 5000x

Index Usage

Assesses index effectiveness and identifies optimization opportunities:

  • Evaluates index scan efficiency
  • Detects missing indexes on filter fields
  • Recommends composite indexes

Example:

❌ HIGH: No index used for query filter on ['customer_id', 'status']
    Recommendation: Create index on customer_id, status
    Impact: Index creation could provide 10-100x performance improvement

Aggregation Pipeline Optimization

Optimizes aggregation pipeline structure:

  • Ensures early $match stages for filtering
  • Detects blocking stages without filtering
  • Identifies unnecessary $lookup operations
  • Recommends pipeline restructuring

Example:

⚠️  MEDIUM: $match stage should be first to filter early
    Recommendation: Move $match to the beginning of the pipeline
    Impact: Could reduce documents processed by subsequent stages

Using Deep Analysis

Deep insights are automatically included in analysis results:

from iqtoolkit_analyzer.mongodb_analyzer import MongoDBProfilerIntegration

profiler = MongoDBProfilerIntegration(connection_string, thresholds)
if profiler.connect():
    records = profiler.collect_profile_data("mydb")
    for record in records:
        slow_query = profiler.analyze_profile_record(record)
        
        # Access deep insights
        for insight in slow_query.deep_insights:
            print(f"[{insight.severity}] {insight.category}")
            print(f"  {insight.message}")
            if insight.recommendation:
                print(f"  → {insight.recommendation}")

For detailed information, see MongoDB Profiler Deep Analysis Guide.

Real-Time Operation Monitoring

New in v0.3.0rc3 - Monitor currently running operations in real-time

The real-time operation monitoring module uses MongoDB's currentOp command to provide live insights into database activity:

Monitoring Capabilities

Long-Running Operations

Detects operations running longer than the configurable threshold:

  • Default threshold: 30 seconds
  • Customizable per use case
  • Identifies optimization opportunities

Example:

⚠️  HIGH: Found 3 long-running operations (avg duration: 45000ms)
    Recommendation: Review long-running queries; consider optimizing or adding indexes
    Impact: Optimizing 3 operations could free up resources and improve throughput

Lock Contention Detection

Identifies operations waiting for locks:

  • Detects which locks are contested
  • Shows count of waiting operations
  • Provides lock contention insights

Example:

🔒 HIGH: Lock contention on Global lock (4 operations waiting)
    Recommendation: Review long-running operations and optimize transaction scope
    Impact: Could improve throughput by reducing lock wait times for 4 operations

Operation Breakdown

Analyze workload composition by operation type and database:

Operation Distribution:
  find: 12 operations
  update: 5 operations
  insert: 2 operations
  delete: 1 operation

Database Breakdown:
  testdb: 15 operations
  otherdb: 3 operations

Blocking Operation Detection

Identifies operations unable to proceed due to locks:

  • Shows which operations are blocked
  • Displays blocking chain (if available)
  • Recommends concurrency improvements

Using Real-Time Monitoring

Get a snapshot of current operations:

from iqtoolkit_analyzer.mongodb_analyzer import MongoDBProfilerIntegration

profiler = MongoDBProfilerIntegration(connection_string, thresholds)
if profiler.connect():
    # Get current operations snapshot
    snapshot = profiler.get_current_operations_snapshot()
    
    if snapshot:
        print(f"Total operations: {snapshot.total_operations}")
        print(f"Long-running: {len(snapshot.long_running_ops)}")
        print(f"Blocking: {len(snapshot.blocking_ops)}")
        
        # Analyze insights
        for insight in snapshot.insights:
            print(f"[{insight.severity}] {insight.category}")
            print(f"  {insight.message}")
            if insight.recommendation:
                print(f"  → {insight.recommendation}")

Continuous Monitoring

Monitor for problems in real-time:

import time

profiler = MongoDBProfilerIntegration(connection_string, thresholds)
if profiler.connect():
    while True:
        snapshot = profiler.get_current_operations_snapshot()
        
        if snapshot and (snapshot.long_running_ops or snapshot.blocking_ops):
            print(f"Alert at {snapshot.timestamp}!")
            print(f"  Long-running: {len(snapshot.long_running_ops)}")
            print(f"  Blocking: {len(snapshot.blocking_ops)}")
            # Take action (alert, log, etc.)
        
        time.sleep(10)  # Check every 10 seconds

Filtered Monitoring

Monitor specific operation types or databases:

# Monitor only write operations
filter_query = {
    "op": {"$in": ["update", "insert", "delete"]}
}
snapshot = profiler.get_current_operations_snapshot(filter_query)

# Monitor specific database
filter_query = {
    "ns": {"$regex": "^mydb\\."}
}
snapshot = profiler.get_current_operations_snapshot(filter_query)

# Monitor long-running operations in real-time
filter_query = {
    "secs_running": {"$gte": 30}
}
snapshot = profiler.get_current_operations_snapshot(filter_query)

For detailed information, see MongoDB CurrentOp Analysis Guide.

Report Formats

JSON Report

Structured data format ideal for programmatic processing:

{
  "metadata": {
    "generated_at": "2025-11-15T10:30:00Z",
    "database_name": "myapp",
    "tool_version": "1.0.0"
  },
  "executive_summary": {
    "overview": {
      "total_slow_query_patterns": 15,
      "total_executions_analyzed": 1250,
      "average_query_duration_ms": 450.2,
      "collections_affected": 8
    },
    "severity_breakdown": {
      "critical_queries": 3,
      "high_impact_queries": 7,
      "medium_impact_queries": 5
    }
  },
  "slow_queries": [...],
  "recommendations": [...],
  "contract_results": [
    {
      "severity": "high",
      "findings": ["Collection scan"],
      "recommendations": ["Add an index"]
    }
  ]
}

contract_results is optional and only included when contract analysis is enabled.

Legacy non-contract outputs are deprecated and will be removed in v0.4.0. Use contract_results for forward-compatible automation.

HTML Report

Interactive web-based report with:

  • Executive summary dashboard
  • Sortable query analysis table
  • Severity indicators and color coding
  • Detailed optimization suggestions
  • Collection performance overview

Markdown Report

Human-readable format perfect for documentation:

# MongoDB Slow Query Analysis Report

## Executive Summary

- **Total Slow Query Patterns:** 15
- **Average Query Duration:** 450.2ms
- **Collections Affected:** 8

### Key Findings

- High percentage of collection scans detected
- Several queries with low efficiency scores
- Aggregation operations dominate slow queries

Best Practices

Configuration

  1. Set Appropriate Thresholds

    thresholds:
  # Development
  slow_threshold_ms: 50.0
  
  # Production  
  slow_threshold_ms: 200.0

  2. Use Sampling in High-Traffic Environments

    profiling:
  sample_rate: 0.1  # Sample 10% of operations

  3. Configure Retention Periods

    profiling:
  profile_data_retention_hours: 24
  auto_cleanup_enabled: true

Monitoring Strategy

  1. Regular Analysis: Run weekly comprehensive analysis
  2. Continuous Monitoring: Use monitoring mode for critical systems
  3. Threshold Tuning: Adjust thresholds based on application requirements
  4. Index Monitoring: Regular review of index recommendations

Performance Optimization Workflow

  1. Identify Critical Queries: Focus on high-impact queries first
  2. Analyze Execution Plans: Review planSummary for optimization opportunities
  3. Implement Indexes: Add recommended indexes
  4. Validate Improvements: Re-run analysis to measure improvements
  5. Monitor Changes: Continuous monitoring to catch regressions

Troubleshooting

Common Issues

Connection Problems

# Test connection
iqtoolkit-analyzer mongo test-connection -c "mongodb://localhost:27017"

# Test connection with database profiling check
iqtoolkit-analyzer mongo test-connection -c "mongodb://localhost:27017" -d myapp

Profiling Issues

  1. Insufficient Permissions

    // Grant profiling permissions
db.grantRolesToUser("monitoring_user", ["dbAdmin"])

  2. Profiling Not Enabled

    // Check profiling status
db.getProfilingStatus()

// Enable profiling
db.setProfilingLevel(1, { slowms: 100 })

No Slow Queries Detected

  1. Check if profiling is enabled and collecting data
  2. Verify threshold settings are appropriate
  3. Ensure sufficient analysis time window
  4. Check if queries meet minimum frequency requirements

Memory Issues

For large datasets:

analysis:
  max_collections_to_analyze: 20  # Limit collection analysis
  
profiling:
  sample_rate: 0.1  # Reduce sampling rate
  profile_collection_size_mb: 50  # Smaller profile collection

Debug Mode

Enable verbose logging for troubleshooting:

iqtoolkit-analyzer mongo analyze -c "mongodb://localhost:27017" -d myapp --verbose

Or in configuration:

log_level: "DEBUG"
log_file: "/tmp/mongodb-slow-query-debug.log"

Performance Tuning

For optimal performance:

  1. Limit Analysis Scope

    databases_to_monitor: ["critical_db"]  # Focus on critical databases
analysis:
  max_collections_to_analyze: 10

  2. Optimize Thresholds

    thresholds:
  min_frequency_for_analysis: 10  # Higher frequency threshold
  time_window_minutes: 30         # Shorter time window

  3. Use Appropriate Sampling

    profiling:
  sample_rate: 0.05  # 5% sampling for high-volume systems

Advanced Usage

Custom Analysis Scripts

from iqtoolkit_analyzer.mongodb_analyzer import MongoDBSlowQueryDetector
from iqtoolkit_analyzer.mongodb_config import MongoDBThresholdConfig

# Custom thresholds
thresholds = MongoDBThresholdConfig(
    slow_threshold_ms=50.0,
    critical_threshold_ms=1000.0,
    min_frequency_for_analysis=3
)

# Custom analysis
detector = MongoDBSlowQueryDetector("mongodb://localhost:27017", thresholds)
if detector.initialize():
    # Analyze last 2 hours
    queries = detector.detect_slow_queries("myapp", time_window_minutes=120)
    
    # Filter for specific collections
    user_queries = [q for q in queries if q.collection == "users"]
    
    # Custom reporting
    for query in user_queries:
        if query.impact_score > 50:
            print(f"High-impact query: {query.operation_type}")
            print(f"Suggestions: {', '.join(query.optimization_suggestions)}")

Integration with Monitoring Systems

# Integration example for alerting systems
def check_critical_queries(database_name: str) -> bool:
    detector = MongoDBSlowQueryDetector(connection_string, thresholds)
    if detector.initialize():
        queries = detector.detect_slow_queries(database_name, 15)  # Last 15 minutes
        critical_queries = [q for q in queries if q.impact_score > 80]
        
        if critical_queries:
            # Send alert
            send_alert(f"Critical slow queries detected in {database_name}")
            return False
    return True

For more examples and advanced usage patterns, see the examples documentation.