Some tips for increasing query performance in MongoDB (Part 2)

Tram Ho

Introduce

In part 1, I introduced 3 ways to increase performance, help optimize query results in MongoDB

In this part 2, I will present other ways to help you more smoothly in the process of working. Lets go !!!

4. Be careful with Sort

When we want to retrieve a list of documents to display, we often have to sort by a certain rule. Example: List of users in ascending order of country codes:

Aligning can be very effective when you have an index defined. If you do not have an index defined, MongoDB must sort the results by itself and this can cause problems when analyzing a large set of documents returned. MongoDB imposes a 32 MB memory limit for sort operations and instead of returning an error, we get an empty result with no records at all.

Sorting can sometimes be problematic if you don’t pay attention to the indexed index on the collection. Back to the collection user we have indexed:

Now, we want to arrange both country and city schools in ascending order:

Although the index on the country is used, MongoDB still has to sort by city secondary politics. This makes the query speed slow and may exceed the 32 MB memory limit. Therefore, you should create a compound index:

The sort operations are now fully indexed and will run quickly. You can also sort in reverse order because MongoDB can start at the end of the index and work in reverse. For example:

However, the problem arises if you try to sort in descending country order but gradually increase the city order:

Our index cannot be used, so you must not allow secondary sort criteria not to be indexed or create another suitable index:

After that, you can freely arrange in arbitrary order:

5. Create multiple connection objects

When building an application, we use a single continuous database connection object for all queries and updates, it is still effective.

MongoDB runs all the commands in the order it receives each client connection. Although our application can make asynchronous calls to the database, every command is queued synchronously and must be completed before it can be processed further. If we have a complicated query that takes ten seconds to run, then another person cannot interact with your application at the same time on the same connection.

Performance can be improved by identifying more than one database connection object. For example:

  1. One to handle the majority of fast queries
  2. One to handle inserting and updating documents more slowly
  3. One for handling complex report creation.

Each object is treated as a separate database client and will not delay the processing of others so the application always responds to every interaction.

6. Set maximum execution time

Executing queries that take a long time can get your web application into a timeout situation and annoy the user. This can cause various problems in Node.js and we must continue to wait for asynchronous callbacks.

We can limit the execution time in milliseconds to maxTimeMS () . For example, allowing 100 milliseconds (one tenth of a second) to query documents in the User collection with a CIty field starting with the letter ‘A’:

We should set maxTimeMS to a reasonable value for any command that can take considerable time. Note that MongoDB does not allow you to define global timeout values ​​and it must be set for individual queries (although some libraries may automatically apply by default).

7. Refresh the index

If you think the structure of the query is logical but the queries are still slow, you can try rebuilding the indexes on each collections. For example, rebuilding the index in the User collection via the MongoDB command-line:

If it still doesn’t work, then we have to consider fixing the database to find and fix the problem. This method should only be used when it is out of options.

summary

Above I have presented some tips for you to increase query performance in MongoDB. Hope the article will help you. Thanks for reading.

Share the news now

Source : Viblo