🐛 Bug Report: Very High CPU Utilization and Slow MongoDB Queries due to missing Indexes

[L-U-C-K-Y]

📜 Description

Our application experiences extremely high CPU utilization and slow MongoDB query performance when using Novu for notifications. Even after upgrading to a high-performance server (Standard D64lds v5: 64 vCPUs, 128 GiB memory on Azure), Novu consistently utilizes 100% of the CPU. Analysis reveals that MongoDB queries on the jobs collection result in full collection scans (COLLSCAN), leading to long execution times and high resource consumption.

Here are some sample query statistics:

Query 1:

• Filter: {"_environmentId": "", "_parentId": ""}
• Execution Time: 74 seconds
• Plan Summary: COLLSCAN

Query 2:

• Filter: {"transactionId": null, "_environmentId": null}
• Execution Time: 38 seconds
• Plan Summary: COLLSCAN

👟 Reproduction steps

1. Configure Novu with a MongoDB database.
2. Run the application under normal usage with notifications being sent.
3. Monitor MongoDB queries using db.currentOp() or the MongoDB profiler.
4. Observe queries on the jobs collection with filters such as:

{"_environmentId": "<value>", "_parentId": "<value>"}
{"transactionId": null, "_environmentId": null}

5. Note the planSummary field, which reports COLLSCAN.

👍 Expected behavior

Queries on the jobs collection should use indexes to ensure efficient performance, avoiding full collection scans. CPU utilization should remain within reasonable limits even under high query loads.

1. Verify that appropriate indexes exist on the jobs collection for fields such as _environmentId, _parentId, and transactionId.
2. Optimize query structure to ensure indexes are utilized.
3. Profile queries to ensure efficient execution plans and address potential performance bottlenecks.

👎 Actual Behavior with Screenshots

• MongoDB queries use COLLSCAN, resulting in high query execution times (up to 74 seconds).
• CPU utilization reaches 100% on a powerful server (64 vCPUs, 128 GiB memory).
• Slow query performance affects the overall application responsiveness.

Novu version

v2.1.0

npm version

No response

node version

No response

📃 Provide any additional context for the Bug.

No response

👀 Have you spent some time to check if this bug has been raised before?

• I checked and didn't find a similar issue

🏢 Have you read the Contributing Guidelines?

• I have read the Contributing Guidelines

Are you willing to submit PR?

Yes I am willing to submit a PR!

[linear]

NV-5135 🐛 Bug Report: Very High CPU Utilization and Slow MongoDB Queries due to missing Indexes

[L-U-C-K-Y]

Ohhhh! I thought something might be up and I saw that in the DAL indexes are defined.

With some searching, I found the environment variable AUTO_CREATE_INDEXES and set it to true.

I'll create a PR that mentions this in the docs / docker-compose if that's fine.