MongoDB: Otimização e monitoramento de desempenho

// === mongoose Connection Pool Configuration ===
mongoose.connect(uri, {
  maxPoolSize: 50,          // Maximum Number of Connections(Default 100)
  minPoolSize: 5,           // Minimum Number of Connections(Default 0)
  maxIdleTimeMS: 30000,     // Connection Idle Timeout(Default: Unlimited)
  waitQueueTimeoutMS: 10000 // Connection timeout
});

// === Monitor Connection Pool ===
const db = mongoose.connection.db;
const stats = await db.admin().command({ serverStatus: 1 });
console.log('Connections:', stats.connections);

// === Force Index Usage ===
const products = await Product.find({ category: 'Electronics' })
  .hint({ category: 1 });

// === mongoose Equivalent ===
const products = await Product.find({ category: 'Electronics' })
  .hint('category_1');

// === Query only the necessary fields ===
const products = await Product.find()
  .select('sku title price')  // Don't fetch description, images, etc.
  .lean();

// === Reduce Network Traffic 90%+ ===

// === General Query: Build mongoose Document (slow) ===
const products = await Product.find();

// === lean(): Return plain object directly (fast) ===
const products = await Product.find().lean();

// Slow: $where executes JavaScript
db.products.find({ $where: 'this.price > 1000' });

// Fast: Use $gt
db.products.find({ price: { $gt: 1000 } });

// === Enable the slow query log ===
mongoose.connection.db.admin().command({
  setParameter: 1,
  slowms: 100  // Record > 100ms Queries
});

// === mongoose Enable in the middle debug ===
mongoose.set('debug', true);
// Output all queries to console

// === mongoose-debug Slow Query Log ===
mongoose.set('debug', (collectionName, method, query, doc) => {
  const start = Date.now();
  console.log(`${collectionName}.${method}(${JSON.stringify(query)})`);
});

// === Index Usage Analysis ===
db.products.aggregate([{ $indexStats: {} }]);
// Find unused indexes

// === Index Usage in Slow Queries ===
db.system.profile.find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(20)
  .forEach(profile => {
    console.log('Query:', profile.command.find);
    console.log('Plan:', profile.planSummary);
    console.log('Time:', profile.millis, 'ms');
  });

## 10. Performance Optimization Checklist
---

### (1) Database
- [ ] Dungeon Collection 3 Node(High Availability)
- [ ] Write Concern majority(No data loss)
- [ ] Read Preference primaryPreferred
- [ ] Slow Query Monitoring Enabled
- [ ] Index Usage Monitoring
- [ ] Connection Pool maxPoolSize Settings

### (2) Application Layer
- [ ] mongoose lean() For read-only queries
- [ ] projection Search only the required fields
- [ ] bulkWrite Bulk Operations
- [ ] Avoid $where / $regex Unanchored
- [ ] Error-handling middleware
- [ ] Health Check Endpoints /healthz

### (3) Operations and Maintenance
- [ ] Daily mongodump Backup
- [ ] Backup Retention 7-30 days
- [ ] Monitoring Alerts(Number of connections / Slow Queries / Disk)
- [ ] TLS/SSL Encrypted Transmission
- [ ] SCRAM + RBAC Access Control
- [ ] Atlas PITR or oplog Continuous Backup

// === Slow Queries Before Optimization ===
app.get('/api/products', async (req, res) => {
  const products = await Product.find({ isActive: true });
  // 100 Full-Table Scan of 10,000 Documents, ~3 seconds
  res.json(products);
});

// === After optimization ===
app.get('/api/products', async (req, res) => {
  const { page = 1, limit = 20, category, search } = req.query;

  // 1. Building Indexes to Optimize Queries
  const query = { isActive: true };
  if (category) query.category = category;
  if (search) query.title = new RegExp(search, 'i');

  // 2. Projection + lean + limit
  const products = await Product.find(query)
    .select('sku title price thumbnail')  // Projection
    .hint({ isActive: 1, category: 1 })  // Forced Index
    .limit(Math.min(+limit, 100))  // Maximum Limit
    .skip((+page - 1) * +limit)
    .lean();  // Performance Optimization

  // 3. Parallel count
  const total = await Product.countDocuments(query);

  res.json({ data: products, meta: { page: +page, limit: +limit, total } });
});
// After optimization:~50ms(Performance ↑60x)

// === Scenario: ShopHub product list queries are slow, Alice uses explain to diagnose ===

// 1. Diagnose the Current Query
const explain = await Product.find({ category: 'Electronics', isActive: true })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain('executionStats');

console.log('Stage:', explain.queryPlanner.winningPlan.stage);
// Output:COLLSCAN ❌ Full Table Scan!

console.log('Docs examined:', explain.executionStats.totalDocsExamined);
// Output:1000000(Scanned all of them 100 10,000 Documents)

console.log('Docs returned:', explain.executionStats.nReturned);
// Output: 20 (Returned only 20 docs)

console.log('Time:', explain.executionStats.executionTimeMillis, 'ms');
// Output: 3200ms (too slow!)

// 2. Create a composite index
db.products.createIndex({ category: 1, isActive: 1, createdAt: -1 });

// 3. Once again explain Verification
const explain2 = await Product.find({ category: 'Electronics', isActive: true })
  .sort({ createdAt: -1 })
  .limit(20)
  .hint({ category: 1, isActive: 1, createdAt: -1 })
  .explain('executionStats');

console.log('Stage:', explain2.queryPlanner.winningPlan.stage);
// Output:IXSCAN ✅ Using Indexes!

console.log('Docs examined:', explain2.executionStats.totalDocsExamined);
// Output:20(Precise Scanning)

console.log('Time:', explain2.executionStats.executionTimeMillis, 'ms');
// Output:5ms(↑640x Performance Improvements!)

// Scene: ShopHub slow product search diagnosis - is the index being used?
const mongoose = require('mongoose');
const Product = mongoose.model('Product');

async function diagnoseQuery() {
  // Step 1: Run query with explain
  const explanation = await Product.find({
    category: 'Electronics',
    price: { $gte: 100, $lte: 1000 }
  })
  .sort({ price: -1 })
  .explain('executionStats');
  
  const stats = explanation.executionStats;
  
  console.log('=== Query Performance Report ===');
  console.log(`Execution Time: ${stats.executionTimeMillis} ms`);
  console.log(`Documents Examined: ${stats.totalDocsExamined}`);
  console.log(`Documents Returned: ${stats.nReturned}`);
  console.log(`Index Used: ${stats.winningPlan.inputStage?.indexName || 'COLLSCAN'}`);
  
  // Step 2: Check for problems
  const problems = [];
  
  if (stats.executionTimeMillis > 100) {
    problems.push('Slow query (>100ms)');
  }
  
  if (stats.totalDocsExamined > stats.nReturned * 10) {
    problems.push(`High scan ratio: ${stats.totalDocsExamined}/${stats.nReturned}`);
  }
  
  if (!stats.winningPlan.inputStage?.indexName) {
    problems.push('No index used (COLLSCAN)');
  }
  
  // Step 3: Suggest fix
  if (problems.length > 0) {
    console.log('\n⚠️ Problems detected:');
    problems.forEach(p => console.log(`  - ${p}`));
    console.log('\n💡 Suggested index:');
    console.log('  ProductSchema.index({ category: 1, price: -1 })');
  } else {
    console.log('\n✅ Query performance is good');
  }
}

diagnoseQuery();

// === Scene:Product List API Performance Optimization(3s → 50ms)===

// === Before Optimization (slow) ===
app.get('/api/products', async (req, res) => {
  const products = await Product.find();  // Full Table Scan + Return all fields
  res.json(products);
});
// 100 10,000 Documents，~3000ms，~50MB Data

// === After optimization (fast) ===

// 1. Enable mongoose debug(Monitoring Queries During Development)
mongoose.set('debug', (coll, method, query) => {
  console.log(`${coll}.${method}(${JSON.stringify(query)})`);
});

// 2. Optimizing the Connection Pool
mongoose.connect(uri, {
  maxPoolSize: 50,         // Adjust Based on Concurrency
  minPoolSize: 5,
  maxIdleTimeMS: 30000,
  waitQueueTimeoutMS: 10000
});

// 3. Enable the slow query log(>100ms)
mongoose.connection.db.admin().command({
  setParameter: 1,
  slowms: 100
});

// 4. Create Appropriate Indexes
db.products.createIndex({ category: 1, isActive: 1, createdAt: -1 });
db.products.createIndex({ sku: 1 }, { unique: true });
db.products.createIndex({ title: 'text', description: 'text' });

// 5. Optimize Queries:projection + lean + hint + limit
app.get('/api/products', async (req, res) => {
  const { page = 1, limit = 20, category, search } = req.query;

  const query = { isActive: true };
  if (category) query.category = category;
  if (search) query.title = new RegExp(search, 'i');

  const [products, total] = await Promise.all([
    Product.find(query)
      .select('sku title price thumbnail rating')  // Projection:Return only 5 field
      .hint({ category: 1, isActive: 1, createdAt: -1 })  // Forced Index
      .sort({ createdAt: -1 })
      .skip((page - 1) * limit)
      .limit(Math.min(+limit, 100))
      .lean(),  // Skip mongoose hydrate
    Product.countDocuments(query)
  ]);

  res.json({
    success: true,
    data: products,
    meta: { page: +page, limit: +limit, total, pages: Math.ceil(total / limit) }
  });
});
// 100 10,000 Documents，~50ms(Performance ↑60x)，~200KB Data(Reduce 99.6%)

// === 6. explain() Verify that the index is active ===
const explain = await Product.find({ category: 'Electronics', isActive: true })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain('executionStats');

console.log('Stage:', explain.queryPlanner.winningPlan.stage);
// Output:IXSCAN(An index was used)

console.log('Docs examined:', explain.executionStats.totalDocsExamined);
console.log('Keys examined:', explain.executionStats.totalKeysExamined);
console.log('Returned:', explain.executionStats.nReturned);
console.log('Time:', explain.executionStats.executionTimeMillis, 'ms');

// === 7. Monitoring Alerts ===
// 7.1 Monitor Connection Count
const connStatus = await mongoose.connection.db.admin().command({ serverStatus: 1 });
if (connStatus.connections.current > 1000) {
  console.warn(`⚠️  Too many connections: ${connStatus.connections.current}`);
  // Send an Alert (Email/DingTalk/Slack)
}

// 7.2 Monitoring Slow Queries
const slowQueries = await mongoose.connection.db.collection('system.profile')
  .find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(10)
  .toArray();

slowQueries.forEach(q => {
  console.log(`[${q.ts}] ${q.command.find}: ${q.millis}ms`);
  console.log(`  Plan: ${q.planSummary}`);
});

// 7.3 Index Usage Rate
const indexStats = await mongoose.connection.db.collection('products')
  .aggregate([{ $indexStats: {} }])
  .toArray();

const unused = indexStats.filter(s => s.accesses.ops === 0);
unused.forEach(i => {
  console.log(`⚠️  Index not used: ${i.name}`);
  // Automatic Deletion(Caution in Production Environments)
  // await mongoose.connection.db.collection('products').dropIndex(i.name);
});

// === 8. APM Integration(Datadog/New Relic)===
const tracer = require('dd-trace').init();
tracer.use('mongoose', { service: 'shopdb' });
// All mongoose Query Auto-Tracking，Performance Data Reporting Datadog