MongoDB Atlas Search Indexes & Performance (as compared to a local Mongo instance)

Hello,
Few questions regarding Atlas:

  1. Is there a limit to the number of Atlas Search indexes (to be used with the $search operator) that can be created?
  2. db.profile.find seem to return different information when running the same queries on a local mongo instance vs Atlas. Is this expected?
  3. Profiler on Atlas shows a field “durationMillis” which seems to carry the time taken by the query. The results from db.profile.find (in Studio 3T) does not have this field. It does have the field “millis” which comes back as 0.0 for all Atlas queries. Any thoughts on this?
  4. The find queries on Atlas with normal (not Atlas) indexes seem to be run a COLSCAN (on Atlas). The same queries on a local Mongo instance with identical run an IXSCAN as expected.
  5. Query on Atlas executes in ~300ms (as per “durationMillis”). The same query returns in less than 50ms on the local Mongo instance. Seems like the COLSCAN is causing Atlas to be a lot slower than expected. Any thoughts on how to make Atlas use the available indexes?

Thanks!

Hi @Prasad_Kini,

  1. Is there a limit to the number of Atlas Search indexes (to be used with the $search operator) that can be created?

Currently, as per the Create an Atlas Search Index documentation, you cannot create more than:

  • 3 indexes on M0 clusters.
  • 5 indexes on M2 clusters.
  • 10 indexes on M5 clusters.

There are no limits to the number of indexes you can create on M10+ clusters.

  1. db.profile.find seem to return different information when running the same queries on a local mongo instance vs Atlas. Is this expected?

These are two separate instances, if the data is not the same, more than likely the data being returned is not the same. Do you have more information regarding this?

  1. Profiler on Atlas shows a field “durationMillis” which seems to carry the time taken by the query. The results from db.profile.find (in Studio 3T) does not have this field. It does have the field “millis” which comes back as 0.0 for all Atlas queries. Any thoughts on this?

durationMillis is the total time the query took to complete. I’m not too familiar with Studio3T, it is not an officially supported MongoDB product. Do you have more information regarding these fields or have a sample document from each that you could provide?

  1. The find queries on Atlas with normal (not Atlas) indexes seem to be run a COLSCAN (on Atlas). The same queries on a local Mongo instance with identical run an IXSCAN as expected.

Could you provide sample document(s) from each environment as well as:

  1. The output of db.collection.getIndexes() for both environments
  2. The output of db.collection.explain("executionStats").find(...) for both environments
  1. Query on Atlas executes in ~300ms (as per “durationMillis”). The same query returns in less than 50ms on the local Mongo instance. Seems like the COLSCAN is causing Atlas to be a lot slower than expected. Any thoughts on how to make Atlas use the available indexes?

This could depend on various factors in addition to the fact that one is a COLLSCAN whilst the other uses indexes. However, there is more information needed as requested above. In addition to all the above questions and information, can you provide:

  1. The MongoDB version in use for both environments
  2. The collStats for both environments for the collections you are comparing

I understand you have mentioned $search, indexes and local instance of MongoDB however please note that Atlas Search’s $search stage is only available for Atlas instances. Details on my following post response may be useful here.

Regards,
Jason

4 Likes

Hi Jason,
Thank you so much for the detailed reply.

I am on an M20 cluster with 3 nodes. I don’t always see all my queries in the profiler on the portal, even the recent ones. Few questions:

  1. Are the profilers on all 3 nodes supposed to display the same information? They don’t seem to be doing so.
  2. Is there any config that I need to change for all queries to be captured? I have tried setting the profiling level (setProfilingLevel(2)) from Studio 3T, but that doesn’t seem to have an impact on portal’s profiler.
  3. The profiling level does seem to have an impact as I do see the queries in db.profile. Would each query captured here give all info for that query? e.g. if I have an aggregation query that does a search followed by match, sort etc, would all the stats for the query be captured in one document in db.profile?
  4. db.profile captures a millis field which seems to be the time taken by the query. I have never seen durationMilllis in db.profile. This seems to suggest that the information displayed in the Atlas profiler is not the same as db.profile. Is that a correct deduction?

The overall goal of this exercise is to be able to properly understand how my queries are performing. Hence I need to know what I be looking at.

Thanks again,
Prasad

Hi @Prasad_Kini,

These are great questions!

  1. The Atlas Query Profiler will not display the same information on all 3 nodes. The Atlas Query Profiler is node-specific and is showing you slow operations specific to the node you are viewing. When you’re viewing the Query Profiler from the “cluster” view, it’s actually showing you the slow operations for the primary node.
  2. You can use the setProfilingLevel command to capture all queries, even in the Atlas Query Profiler. However, one warning worth mentioning is that this could add significant load to your cluster and could result in the Atlas Query Profiler hitting rate limits that prevent it from profiling further slow operations. This would essentially prevent Atlas Query Profiler from showing any more slow operations for the rest of the 24 hour monitoring period. With this in mind, you can use the setProfilingLevel command by setting the level to 2 and setting the slowms option to 0.
  3. Yes, stats for your aggregation would be captured in a single document. Additionally, in the Atlas Query Profiler, we provide visibility into this in the Query Details view, which allows you to see the different stages and overall statistics.
  4. That’s right, what’s captured in the Atlas Profiler is different from db.system.profile. In general, the Atlas Profiler captures a subset of the db.system.profile collection. The Atlas Profiler reads from the slow logs rather than from the system.profile collection.

Hope this helps!

Thanks,
Frank

Thanks Frank. At present, I am using the profiler just to get some performance benchmarks and am not worried about it not capturing some queries as there is not any load on the cluster.

Is there a profiler view that will show me the queries from all the nodes?

How does the cluster decide which node to send a given query to? When using a tool such as Studio 3T, once connected, will the query be always sent to one specific node?

Thanks again,
Prasad

Unfortunately, there’s no Profiler view that will show you queries across all nodes in a replica set. However, this is a feature enhancement my team is aware of that we are looking to prioritize.

I’m not too familiar with Studio 3T, as @Jason_Tran mentioned its not an officially MongoDB product. However, MongoDB provides the ability to set read preference, which can allow you to specify where you want to route read operations to. You can find more information about read preference here.

Thanks,
Frank

1 Like

Hi Frank,
Could you please share the link for the feature enhancement so that I can vote on it?

Thanks much,
Prasad

Hi @Prasad_Kini,

Here’s the link for the feature enhancement.

Thanks,
Frank

1 Like