mongoDB Atlas optimisation - Part 1

If you are on mongoDB Atlas you might have familiar with alert like this(if alerts are on for this category)

Official documentation says,

“Scanned objects/returned” is similar, except it’s about the number of documents scanned versus the number returned. A large number is a sign that you may need an index on the fields you are querying on. This metric is available for MongoDB 2.6 and newer.

How to over come this?

Within this alert, you can find a link to the Profiler and if you are lucky there is a high chance you can identify the query which triggers above alert here itself. But as you can see in the screenshot below of the Profiler ui,

By default, only operations that took longer than 100ms will be shown.

So if you have a query triggering above alert but not taking 100ms to complete that operation will not be available in the Profile UI. Let's see how we can overcome this.

Since this Profiler tab actually analyzes the MongoDB log rather than the actual database profiler we can lower the threshold(default 100ms) via mongo shell. This will leave the database profiler disabled but lower the threshold of what's logged to the MongoDB log from 100 milliseconds.

1. Let's create a new Database user, which we can use to connect via mongo shell (Can reuse if you have an admin user, but recommended approach would be to create a temporary user considering security)

2. Whitelist your IP Address (if you have enabled access from any IP Address no need to follow this step)

3. Then using the mongo shell you can connect.

mongo "mongodb+srv://<URL>/<DB>" --username <USER>

Note: Connection command can be easily locate if you goto CONNECT>Connect with the mongo shell for your DB cluster

After connecting,

1. db.getProfilingStatus() should give you the current profiling status.
2. To lower the threshold you can execute,

  db.setProfilingLevel(0, { slowms: 50 }) 

This will lower the threshold from 100ms to 50ms but will keep profiling disabled as denoted by 0.
You can keep this setting for 5-6 hours during the peak usage, so profiler UI should be able to gather as much as information. With this information you can identify indexing updates you can do to get rid of above alert.

Note: we can run this multiple rounds lowering the slowms eg:- 25, 15, 10 to further analyse

Once you are done with all improvements make sure to set it back at default 100ms as this can add more load to the system.