MongoDB Tuning: WiredTiger, Indexes, Query Optimization

Imagine: a server with 32 GB RAM, MongoDB using 16 GB for the WiredTiger cache, but queries still take seconds. The cause — inefficient indexes and incorrect configuration. We've seen this many times: COLLSCAN instead of IXSCAN, cache eviction from disk pages, and aggregations consuming all memory. Optimizing MongoDB is a complex task involving WiredTiger engine tuning, index design, and slow query profiling. Without a systematic approach, even powerful servers run inefficiently. Our engineers hold MongoDB certifications and have 10+ years of experience in this field. In this article, we share practical techniques that helped our clients reduce response time by 50%. We'll cover common mistakes and how to fix them. Special attention goes to the ESR rule for indexes and WiredTiger cache tuning — these two areas yield the greatest impact.

Problems We Solve

Inefficient WiredTiger Cache Configuration

By default, MongoDB allocates 50% of RAM for the cache. On a server with 32 GB, that's 16 GB. But without explicitly setting cacheSizeGB, the cache can be evicted by other processes. We always set it manually, leaving headroom for the OS and disk cache.

# /etc/mongod.conf storage: wiredTiger: engineConfig: cacheSizeGB: 12 # For a 32 GB server journalCompressor: snappy collectionConfig: blockCompressor: snappy indexConfig: prefixCompression: true 

Monitoring: db.serverStatus().wiredTiger.cache. If pages evicted by application threads > 0, the cache is under pressure — increase cacheSizeGB.

Indexes: Missing or Wrong Order

Without an index, any query becomes a COLLSCAN. The key rule is ESR (Equality, Sort, Range). Here's how to build a compound index:

// Query: find active orders for a user, sorted by date db.orders.find({ user_id: ObjectId("..."), status: "active" }).sort({ created_at: -1 }) // Correct index: equality → sort db.orders.createIndex({ user_id: 1, status: 1, created_at: -1 }) 

Slow Aggregations with $lookup

The most common mistake is placing $match after $lookup. Optimal order:

db.orders.aggregate([ { $match: { status: "completed", created_at: { $gte: ISODate("current year-01-01") } } }, { $lookup: { from: "users", localField: "user_id", foreignField: "_id", as: "user", pipeline: [{ $match: { country: "RU" } }, { $project: { name: 1, email: 1 } }] }}, { $project: { _id: 1, total: 1, "user.name": 1 } } ]) 

For parallel pipelines, use $facet.

WiredTiger Cache Tuning: Key Parameters

The key parameter is cacheSizeGB. Set it to 60–70% of available RAM, but not more than 20 GB on modern versions (according to MongoDB official documentation). Leave the rest to the OS and disk cache. For a 64 GB server, optimal cacheSizeGB is 40, but considering other processes, usually 35–40.

Indexes: Design and Maintenance

Use the ESR rule for compound indexes. Regularly check for unused indexes: db.aggregate([{ $indexStats: {} }]). Indexes with accesses.ops == 0 are dead weight — they should be dropped. This can save up to 20% of RAM.

When to Shard MongoDB?

Sharding is justified if data exceeds 200 GB or write load is above 10,000 RPS on a single server. Choose a shard key with high cardinality, e.g., a hash of user_id.

How We Do It: A Case Study

We optimized MongoDB for an e-commerce client — with a catalog of 5 million products. Query times for categories and prices were 2-3 seconds. Our solution:

• Compound indexes on (category, price, created_at).
• cacheSizeGB = 20 on a 64 GB server.
• Analytical queries routed to secondary (readPreference: secondaryPreferred).
• Replaced $lookup with post-filtering using pipeline.

Result: response time dropped to 50 ms, CPU load reduced by 30%. Our team guarantees that this indexing approach cuts response time by half compared to a typical configuration. For urgent cases, express diagnosis is available in 1 day — contact us.

Regular Check for Unused Indexes

Indexes with accesses.ops == 0 consume memory and slow down writes. Run $indexStats monthly and drop unused ones. This saves up to 20% of RAM on indexes. Correct cacheSizeGB gives a 40% speed improvement over default settings.

Process and Cost

1. Audit: profiler, explain, index analysis.
2. Design: calculate cacheSizeGB, indexes per ESR.
3. Implementation: configure, create/drop indexes, optimize queries.
4. Testing: load testing, metric comparison.
5. Deployment: apply changes, monitor.

Optimization timelines range from 3 to 10 business days. Cost is calculated individually after a free audit. Savings on server infrastructure can reach 30% due to reduced load. Our optimization pays for itself in 2-3 months.

What's Included in MongoDB Optimization

• Full audit of current configuration and performance.
• Index schema design aligned with business logic.
• WiredTiger tuning (cacheSizeGB, compression, journal).
• Slow query and aggregation optimization.
• Documentation of changes and operational recommendations.
• Team training and access handover.
• 30 days of post-deployment support.

Tuning Checklist

Comparison of Common Indexing Approaches

Example: Enabling Profiler

db.setProfilingLevel(1, { slowms: 50 }); db.getProfilingStatus(); db.system.profile.aggregate([ { $group: { _id: "$ns", avgMillis: { $avg: "$millis" }, count: { $sum: 1 } } }, { $sort: { avgMillis: -1 } }, { $limit: 10 } ]) 

Order a MongoDB performance audit — get an engineer consultation and an optimization plan.