Mongodb indexing

Working with Data
#1

Hi team ,

Need help on indexing part in mongodb. as per developer requirement , due to poor performance, I created single key and compound indexes on certain columns of a query. But when checked I found , indexes does not getting used while query run and in Mongo Compass , there is 0 value in index execution Usage.

How can i make use of those indexes to performance improvements ?

Kind regards
Gaurav

#2

Hi Gaurav,

There are many reasons why a query didn’t use indexes. But before that, could you post more details:

  • What are the indexes defined in the collection?
  • What is your query?
  • How did you check that no indexes are used? Could you post the output of db.collection.explain('executionStats').find(...) from the mongo shell?
  • Please post some example documents
  • What is your MongoDB version

Best regards,
Kevin

#3

Hi Kevin

please find below

Please post some example documents == >>
_id : 45021
_class : “com.ctl.bmp.service.pricing_service.ds.dto.PriceBook”
priceBookName : “SOUTH TACOMA WA TACMWAFA ESHOP-Customer Care Individual Regular”
priceBookDescription : “SOUTH TACOMA WA TACMWAFA ESHOP-Customer Care Individual Regular”
effectiveFromDate : 2019-05-21T00:00:00.000+00:00
currency : “USD”
catalogId : “EC02875”
catalogName : “Catalog WA SOUTH TACOMA TACMWAFA ESHOP-Customer Care Individual Regula…”
catalogSpecName : “Catalog Spec WA SOUTH TACOMA TACMWAFA ESHOP-Customer Care Individual R…”
catalogSpecId : “ES02875”
offers : Array

What are the indexes defined in the collection? == >>
1 ) catalogName_catalogSpecId_
2) id

How did you check that no indexes are used? == >> Please find attach screenshot from Mongo Compass
( here it Usage columns showing 0)

Index Usage
Index Usage1072×417 27.4 KB

What is your MongoDB version == > 4.0.14-8

Please also not there are two collections ( one with document which example above) which linked with DBreff - and size of both collections are 8.5 and 60.7 GB respectively. does size of collections also impacts performance ? i read on web that DBReff does impacts performance but not sure about Collection size.

#4

Hi Gaurav

What is your MongoDB version == > 4.0.14-8

This seems to be a version of the Percona fork of MongoDB server. Is this correct?

If yes, you might want to contact Percona support regarding this behaviour, since it involves modified server code that may work differently from official MongoDB servers.

What I did notice from your screenshot is, if your collections are so large, the index sizes are suspiciously small. This could be either 1) the large collections are not indexed, or 2) the Percona fork of the server is behaving differently.

does size of collections also impacts performance

In most cases, yes. If improperly indexed, larger collections would require more resources to process. For example, a query with a collection scan would then require the server to load all 8.5/60.7 GB of data from disk into memory to answer the query. This will be a massive workload if the server was provisioned with a smaller amount of RAM and slower disks.

Having said that, if your working set (data that’s frequently accessed + indexes) can comfortably fit in RAM, performance impact could be minimal even with large collections.

Best regards,
Kevin

1 Like
#5

Hi Kevin ,

Thanks lot for helping out in this , can you please help to know why index usage showing 0 ? in last screenshot. How can we make them in use for queries ?

  1. If the collection size impacts query performance what should we do about it ? do we need any old document purging scripts for this , what is the recommended size for Document and Collection in MongoDB ?

Kind regards
Gaurav

#6

Hi Gaurav,

As your server is not an official MongoDB release but a fork from Percona, I’m afraid I can’t really help why those indexes are not used in this case since I have no idea what changes Percona did to the server. I would suggest contacting Percona Support for this.

Having said that, in an official MongoDB server, you would need to create indexes to support your queries.

Regarding collection size, query performance typically depends on:

  • Your provisioned hardware
  • Whether queries are indexed
  • Whether your working set fits in your provisioned RAM
  • How complex are your schema design

I’m afraid the answer to those questions are very use-case based and there’s no one general answer to answer them all. There is no “recommended” collection size, as a result.

Thus, this is a very deep topic with very personal answers, as what’s considered as acceptable performance to other people may not be acceptable to you, and vice versa.

However, if I may offer suggestions, I would recommend you to take a look at MongoDB University free courses, especially:

The courses listed above would hopefully help you answer the questions you have.

Best regards,
Kevin

3 Likes
#7

Hi Kevin

Sorry for delay in responding.Thanks so much for kind help, please allow me to work on those points you suggested and revert you for further queries.

Thanks once again
Gaurav

#8

Hi Kevin

just add on - since our collection size are 60+ GB…is there any utility in mongo which can reduced the collection size ? or should we go for purging old documents from the collection? to make it reduce.

Kind regards
Gaurav