[PR #153] [MERGED] Add MongoDB performance indexes to fix critical query slowdowns #152

New issue

Closed

opened 2026-10-27 21:10:04 +00:00 by bsakaguchi · 0 comments

bsakaguchi commented

2026-10-27 21:10:04 +00:00

Owner

📋 Pull Request Information

Original PR: https://github.com/Comfy-Org/Comfy-PR/pull/153
Author: @snomiao
Created: 2/3/2026
Status: ✅ Merged
Merged: 2/4/2026
Merged by: @comfy-pr-bot

Base: main ← Head: sno-db-optimize

📝 Commits (5)

376d2b4 Add MongoDB performance indexes to fix critical query slowdowns
6b2b1a8 Merge branch 'main' into sno-db-optimize
e2ddb1a Merge branch 'main' into sno-db-optimize
019759b Address Copilot review comments
d27199d Update scripts/setup-performance-indexes.ts

📊 Changes

7 files changed (+586 additions, -3 deletions)

View changed files

➕ PERFORMANCE-FIXES.md (+274 -0)
📝 bun.lock (+3 -3)
📝 lib/slack/SlackMsgs.ts (+10 -0)
📝 package.json (+1 -0)
➕ scripts/README.md (+147 -0)
➕ scripts/setup-performance-indexes.ts (+131 -0)
📝 src/CNRepos.ts (+20 -0)

📄 Description

MongoDB Performance Advisor identified two critical missing indexes causing severe performance degradation:

SlackMsgs queries scanning 80,501 docs to return 1 (637ms avg)
CNRepos queries scanning 4,998 docs to return 25 (420ms avg)

This commit adds the missing compound indexes:

SlackMsgs: { status: 1, mtime: 1 } for status filtering queries
CNRepos: { crPulls.state: 1, info.state: 1, crPulls.mtime: 1, info.mtime: 1, candidate.mtime: 1 } for complex state queries

Expected improvements:

SlackMsgs: 637ms → <50ms (92% faster), query targeting 79,841:1 → 1:1
CNRepos: 420ms → <100ms (76% faster), query targeting 2,643:1 → 2:1
Disk I/O reduction: 823.8 MB per query cycle

Also includes:

Migration script for existing deployments (scripts/setup-performance-indexes.ts)
Complete documentation in PERFORMANCE-FIXES.md
npm script for easy execution (npm run db:setup-indexes)

Indexes are created with background: true to avoid blocking operations.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/Comfy-Org/Comfy-PR/pull/153 **Author:** [@snomiao](https://github.com/snomiao) **Created:** 2/3/2026 **Status:** ✅ Merged **Merged:** 2/4/2026 **Merged by:** [@comfy-pr-bot](https://github.com/comfy-pr-bot) **Base:** `main` ← **Head:** `sno-db-optimize` --- ### 📝 Commits (5) - [`376d2b4`](https://github.com/Comfy-Org/Comfy-PR/commit/376d2b4c8e6969578da3c00e6b2c641c2ad3c9c9) Add MongoDB performance indexes to fix critical query slowdowns - [`6b2b1a8`](https://github.com/Comfy-Org/Comfy-PR/commit/6b2b1a8ff16ae74dd8f217281fc7a818f6741863) Merge branch 'main' into sno-db-optimize - [`e2ddb1a`](https://github.com/Comfy-Org/Comfy-PR/commit/e2ddb1aea60483b7c7f693dcaea2fdfa0d188763) Merge branch 'main' into sno-db-optimize - [`019759b`](https://github.com/Comfy-Org/Comfy-PR/commit/019759bdfcd4f2dfe1a4842d9058f86b03e4cc5d) Address Copilot review comments - [`d27199d`](https://github.com/Comfy-Org/Comfy-PR/commit/d27199dc80197563236ff8a7c45999ccba737234) Update scripts/setup-performance-indexes.ts ### 📊 Changes **7 files changed** (+586 additions, -3 deletions) <details> <summary>View changed files</summary> ➕ `PERFORMANCE-FIXES.md` (+274 -0) 📝 `bun.lock` (+3 -3) 📝 `lib/slack/SlackMsgs.ts` (+10 -0) 📝 `package.json` (+1 -0) ➕ `scripts/README.md` (+147 -0) ➕ `scripts/setup-performance-indexes.ts` (+131 -0) 📝 `src/CNRepos.ts` (+20 -0) </details> ### 📄 Description MongoDB Performance Advisor identified two critical missing indexes causing severe performance degradation: - SlackMsgs queries scanning 80,501 docs to return 1 (637ms avg) - CNRepos queries scanning 4,998 docs to return 25 (420ms avg) This commit adds the missing compound indexes: - SlackMsgs: { status: 1, mtime: 1 } for status filtering queries - CNRepos: { crPulls.state: 1, info.state: 1, crPulls.mtime: 1, info.mtime: 1, candidate.mtime: 1 } for complex state queries Expected improvements: - SlackMsgs: 637ms → <50ms (92% faster), query targeting 79,841:1 → 1:1 - CNRepos: 420ms → <100ms (76% faster), query targeting 2,643:1 → 2:1 - Disk I/O reduction: 823.8 MB per query cycle Also includes: - Migration script for existing deployments (scripts/setup-performance-indexes.ts) - Complete documentation in PERFORMANCE-FIXES.md - npm script for easy execution (npm run db:setup-indexes) Indexes are created with background: true to avoid blocking operations. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

bsakaguchi

2026-10-27 21:10:04 +00:00