p24-infra Docs

mongodb-slow-queries-index

8 min read

Playbook: MongoDB Slow Queries on regRecords — Investigation & Index Management

Issue: #1198 Scope: w3_db.regRecords (RESO EUROPA) and w4_db.regRecords (Keller) on rs0 Companion playbook: mongodb-compound-wildcard-indexes.md — the application record of the indexes that were actually created for this issue.

This playbook is the operational / diagnostic guide: how to recognise the slow-query pattern, how to verify the current index state, and how to safely add an index on these (multi-million-document) collections without locking production. The companion playbook above is the change record of what was built on 2026-06-24.

TL;DR — current state (verified 2026-06-28 against rs0 PRIMARY bms-2)

The search-index fix this issue called for is already deployed. The remaining work is application-side (Pinbox24 repo) and monitoring (p24-infra), not a new database index.

CollectionDocsSearch index presentVerified
w3_db.regRecords~7.46Mp24_search_compound_wildcard {officeId:1, regId:1, $**:1} (+ 37-field wildcardProjection)✅ 2026-06-28
w4_db.regRecords~2.52Mp24_search_recorddata_wildcard {officeId:1, regId:1, deleted:1, recordData.$**:1}✅ 2026-06-28

Slow-query volume has collapsed from 1,271/day (2026-06-10) to 1–10/day (June 2026), consistent with these indexes being in place. The historical 223-second incidents were March 2026, before the indexes existed.

Do NOT add a MongoDB $text index to “fix” this. See Why not a $text index — it is redundant with the deployed wildcard indexes, provides zero benefit without a Pinbox24 application change (different repo), is expensive on these collection sizes (w4_db.regRecords already carries ~11 GB of indexes), and risks OOM on bms-3 (MongoDB already uses ~21.7 GB of 32 GB).

Trigger — how to recognise this problem

  • Grafana / mongod log shows find/aggregate on *_db.regRecords taking tens of seconds to minutes.
  • The query is the Pinbox24 “search records” feature: a $match with officeId + regId + a large $or of non-anchored regex conditions across 7–100+ fields, then $sort + $limit + $facet.
  • Affected offices to date: RESO EUROPA (officeId 590c6d334704d811efc9fd5a, w3_db) and Keller (officeId 5d36ccf9903f5002cca1e474, w4_db).

Root cause

Non-anchored regex (/term/i, no ^) cannot be served by a normal B-tree index. After the {officeId, regId} prefix narrows the candidate set, MongoDB must evaluate every regex against every candidate document in memory. With a large register (~23,800 docs for RESO EUROPA × 22+ regexes) this reaches 223 s. The wildcard indexes let the planner union per-field index scans instead of loading all documents; see the companion playbook’s “Expected improvement” section.

Step 1 — Confirm the current index state (read-only, safe)

Credentials: MONGODB_RS0_ADMIN_USER / MONGODB_RS0_ADMIN_PASSWORD from secrets/administration.env.sops (or secrets/n8n-bms4.env.sops). Never print the values — load into a variable and reference it only as a command argument.

# bms-4 (arbiter) and the rs0 peers are all in the port-27017 ufw allowlist, so you can connect
# directly with a replica-set URI from any p24-infra server — no SSH hop required.
export SOPS_AGE_KEY_FILE="$HOME/.age/p24-infra-keys.txt"
MUSER=$(sops -d --input-type dotenv --output-type dotenv secrets/administration.env.sops | grep '^MONGODB_RS0_ADMIN_USER=' | cut -d= -f2- | tr -d '"')
MPASS=$(sops -d --input-type dotenv --output-type dotenv secrets/administration.env.sops | grep '^MONGODB_RS0_ADMIN_PASSWORD=' | cut -d= -f2- | tr -d '"')
export MURI="mongodb://${MUSER}:${MPASS}@145.239.133.104:27017,51.68.155.224:27017/admin?replicaSet=rs0&authSource=admin"
unset MPASS   # value now only inside $MURI; do not echo $MURI
 
mongosh "$MURI" --quiet --eval '
["w3_db","w4_db"].forEach(function(dbn){
  var idx = db.getSiblingDB(dbn).regRecords.getIndexes();
  var search = idx.filter(function(i){ return /p24_search/.test(i.name); });
  print(dbn + ".regRecords: " + idx.length + " indexes; search index(es): " +
        (search.length ? search.map(function(i){return i.name;}).join(", ") : "MISSING"));
});'
unset MURI

Expected: each line lists a p24_search_* index. If one shows MISSING, the index was dropped or the collection was restored from a backup predating 2026-06-24 — re-create it per the companion playbook.

Avoid explain() on the PRIMARY for this query shape. MongoDB’s multi-plan trial briefly executes candidate plans even in queryPlanner mode; for a large $or regex over a 7M-document collection this can itself run for tens of seconds and add load. If you must profile, run it against the SECONDARY (bms-3) with readPreference=secondary and during low traffic.

Step 2 — Decide whether a new index is actually needed

Most of the time the answer is no — the wildcard indexes already cover the search path. Add a new index only for a specific, profiled slow query that the existing indexes do not cover (e.g. a query filtering on a date field with no supporting index, like the 2026-01-28 incident that fell back to the _id index = full scan).

Decision checklist before creating any index:

  • You have an actual slow query from the log/profiler (not a hypothesis).
  • getIndexes() confirms no existing index already covers it.
  • The new index is a normal B-tree compound index (e.g. {officeId:1, regId:1, registeredData:1}) — not a second wildcard or a $text index.
  • You have checked RAM headroom on bms-3 (free -g; MongoDB already ~21.7 GB / 32 GB) and disk on bms-2 (df -h).
  • The build is scheduled off-peak and uses the rolling procedure below.

Why not a $text index

  1. Redundant — the deployed wildcard indexes already serve the multi-field search.
  2. No benefit without an app change — a $text index is only used by $text: {$search: ...} queries. Pinbox24 currently sends $or regex; until the Pinbox24 application (separate repo, running on bms-1) is rewritten to use $text, a text index sits unused. That app change is tracked as remaining work below, not in this repo.
  3. One per collection — MongoDB allows a single $text index; adding one constrains future schema.
  4. Cost — text indexes tokenise every word of every indexed field. On 7.46M / 2.52M documents this is large and slow to build; w4_db.regRecords already carries ~11 GB of indexes.

Step 3 — Safe index build on a large rs0 collection (only if Step 2 says yes)

MongoDB 7.0 builds indexes with the hybrid method (brief intent locks at start/end, the build itself does not hold a collection-exclusive lock; the deprecated {background:true} option is ignored). Even so, on a 7M-document collection the build consumes CPU/RAM. Prefer a rolling build to keep the PRIMARY unaffected:

  1. Build on the SECONDARY first. Connect directly to bms-3 (the current SECONDARY), drop it from the set is not required for a rolling build in modern MongoDB — instead, build on the PRIMARY with a maintenance window, or use the documented rolling procedure (stop secondary → standalone → build → rejoin). For p24-infra’s small two-data-bearing-node set, the simplest safe path is:
    • Schedule an off-peak window.
    • Create the index on the PRIMARY (bms-2). It replicates to bms-3 automatically.
    • Watch db.currentOp({ "command.createIndexes": { $exists: true } }) and free -g on both nodes.
  2. Verify with getIndexes() on both bms-2 and bms-3.
  3. Confirm it’s used by profiling the specific query on the SECONDARY (readPreference=secondary).

Index creation command pattern (substitute the real fields; this is an example for a date-field gap):

mongosh "$MURI" --quiet --eval '
db.getSiblingDB("w3_db").regRecords.createIndex(
  { officeId: 1, regId: 1, registeredData: 1 },
  { name: "p24_regrecords_registereddata" }
);'

dropIndex(...) is the instant, safe rollback (metadata-only; files removed in the background).

Remaining work (NOT in this repo / not a database index)

These are the real follow-ups; the database index portion of #1198 is complete.

#ItemWhereNotes
1Reduce $limit: 10000 → 500 before $facetPinbox24 app repo (bms-1)The pipeline paginates to 10 results; loading 10k before faceting is wasted work. Biggest remaining win, app-side only.
2(Optional) migrate $or regex → $text: {$search}Pinbox24 app repoOnly worthwhile with a text index; weigh against the already-effective wildcard indexes.
3Deploy Mezmo agent on bms-2 + bms-3p24-infra#1196 — per-query slow-log detail + Grafana alert on slow-query spikes.
4Investigate external NoSQLBooster access 103.89.59.203p24-infraSee below.

Security follow-up — 103.89.59.203

IP 103.89.59.203 connected directly via NoSQLBooster v9.1.6 (authenticated — it passed MongoDB auth) and ran a slow find on w3_db.regRecords on 2026-01-28; last session 2026-02-05. No sessions after ufw was enabled on bms-2/bms-3 on 2026-06-23 — the firewall now blocks it. Actions:

  1. Confirm the IP is not on the rs0 ufw allowlist (it should not be).
  2. Identify the credential owner. If the connecting MongoDB user cannot be accounted for, rotate the relevant MongoDB password following mongodb-credential-rotation.md and log it in docs/secrets-rotation-log.md.

Escalation / error notification

If an index build fails, OOMs a node, or causes replication lag that affects Pinbox24, follow the project error-notification standard: post to P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URL (red) and open a GitHub issue in radieu/p24-infra with the failing command, node, and db.currentOp() output.

Admin credentials

MONGODB_RS0_ADMIN_USER / MONGODB_RS0_ADMIN_PASSWORD — secrets/administration.env.sops (also in secrets/n8n-bms4.env.sops). Decrypt with the safe single-key extraction pattern; never echo the value.

Audit Log — Log to infra_operations

After this operation completes, log it to the infra_operations audit table.

Python (Linux server — bms-4, vps-i1, vps-h1, or similar):

import sys
sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.log_op import log_op
 
log_op(
    actor="claude",  # "radieu" for manual human ops, "claude" for agent
    op_type="config_change",
    resource="mongodb-rs0",
    result="success",  # "success" | "failed" | "skipped"
    detail="MongoDB slow query resolved — index created on rs0",
    env="bms-2",
    gh_issue=2730,
)

PowerShell (Windows dev machine):

$env:SUPABASE_URL = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_URL=").ToString().Split("=",2)[1].Trim()
$env:SUPABASE_SERVICE_KEY = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_SERVICE_KEY=").ToString().Split("=",2)[1].Trim()
python -c "
import os, sys
sys.path.insert(0, 'C:/code_2026/p24-infra')
from scripts.lib.log_op import log_op
log_op('claude', 'config_change', 'mongodb-rs0', 'success', 'MongoDB slow query resolved — index created on rs0', 'bms-2')
"
$env:SUPABASE_URL = ''; $env:SUPABASE_SERVICE_KEY = ''

Graph View

Backlinks