Feature #10974
openMulti-Tenant Database Connectivity Diagnosis & Connection Pool Resilience
0%
Description
Conducted an intensive infrastructure and database connectivity diagnostic investigation into intermittent request timeouts, socket exhaustion, and tenant connection lifecycle management (db.js, tenantManager.js, userController.js).
Background & Issue Description:
Staff reported intermittent system hangs where key platform queries, notably /users/getUsers, customer lookups, and project stage queries, timed out after exactly 20 seconds. This blocked administrative staff from assigning users and managing active projects.
Root Cause Analysis:
1. The 20-second threshold matched the driver-level serverSelectionTimeoutMS: 20000 configured in Mongoose tenant database connections.
2. An isolated database ping script established a direct MongoDB connection in 505ms, proving the cloud database cluster was healthy and responsive.
3. The root cause was identified as stale, hung TCP sockets in the cached connection pool of the long-running Node.js backend process following a transient cloud network blip, leaving connection pools unresponsive without triggering automatic socket recycling.
Remediation & Resilience Implementation:
1. Tenant Connection Pool Recovery:
- Safely flushed stale cached tenant connections and cycled application worker processes.
- Reconfigured connection pool parameters, enabling socket keep-alive (keepAlive: true), proactive socket timeout management (socketTimeoutMS: 45000), and automatic idle pool recycling.
- Added connection error listeners to automatically evict and rebuild broken tenant connections upon transient connection loss.
2. Connection Health Telemetry:
- Implemented internal logging to track active tenant connection count, pool saturation, and driver connection states.
3. Verification & Validation:
- Verified that /users/getUsers, project queries, and quotation calculations resolve in under 200ms across all tenant organizations.
Subtasks
Related issues