How to build a perfect index to get the results fast

Working with Data
,
1

Hi Team,

Greetings, I am having a collection called txn_count_log_details, Where the collection size is around 70GB and the count of the records in the collection is 102549448, And I am using Sharding in my environment, using the txnDate field as a shardkey. I observed the following parameters in the logs

 "planSummary":"IXSCAN 
{ productBadgeId: 1, storeBadgeId: 1, pranthId: 1, txnDate: 1 }"
 ""protocol":"op_msg","durationMillis":35122, 
 keysExamined":6725351,"docsExamined":6725295,",

and Here I am using the query to get the results which I attached as follows. but the query is taking around 1.5 minutes to get the results and take the right index. Kindly give me suggestions to get the results fast either by creating the correct index or by modifying the query or adding some parameter like the $sort parameter …

Kindly help me in this matter.

txn_count_log_details_log.txt (11.8 KB)

3

Hi :wave: @MERUGUPALA_RAMES,

Welcome to the MongoDB Community forums :sparkles:

Firstly, it’s important to note that the issue is difficult to reproduce reliably since it is highly dependent on the specific data and hardware.

However, by looking into your log, I can deduce the following:

  • Based on the client’s driver info, which suggests that a Spring Boot app using MongoDB Java driver is being used, I suggest looking into the app’s code and configuration to evaluate optimization opportunities that may be the cause of the slow query.

  • The query examined 6.7 million documents, which represents approximately 6% of the total collection size of 102 million documents. While this is still a significant number of documents, it may not necessarily indicate a major performance issue on its own. However it’s possible that the query cannot be made more selective, so it’s important to focus on optimizing other aspects of the query.

Note that the shard key txnDate is not the prefix of the index used for the query.

  • Also, the query yielded 6,881 times, as indicated by the numYields field. Yields occur when a query needs to wait for a lock to be released and can impact query performance. It might be possible that hardware limitations can contribute to this issue.
  • Can you share your hardware spec, like the WT cache changes from its default size and how have you deployed this?
  • Are you using separate hardware for each node?

Furthermore, to gain a better understanding of the issue, please provide the following information:

  1. The sample documents of your collections, and the expected output that you are seeking.
  2. How many different values does the shard key contains?
  3. What MongoDB version you are using?

Best,
Kushagra

4

Hi Kesav,
Thanks for the reply,

As per the discussion, the following is the requested data. Please review it once.

  1. Can you share your hardware spec, like the WT cache changes from its default size and how have you deployed this?
    Are you using separate hardware for each node?

For WT cache changes I didn’t change anything yet, and for hardware resources, yes we are using separate hardware resources for each node as follows

Mongos : RAM - 30 GB, CPU cores- 8, HardDisk-100 GB
Config: RAM - 15 GB , CPU cores - 4 HardDisk - 100GB
3_Data_Nodes_Each: 30 GB , CPU cores - 8 and HardDisk - 500GB

2)Furthermore, to gain a better understanding of the issue, please provide the following information:

The sample documents of your collections, and the expected output that you are seeking.

{
        "_id" : ObjectId("6401cff277a245291028fd82"),
        "storeId" : NumberLong(1360255),
        "storeName" : "New Kumbharwada UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(3345885),
        "productName" : "bOPV (dose)",
        "productBadgeId" : NumberLong(2),
        "productBadgeName" : "RI Vaccines",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(40),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cff277a245291028fd81"),
        "storeId" : NumberLong(1360255),
        "storeName" : "New Kumbharwada UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(10),
        "productName" : "OPEN bOPV (vial)",
        "productBadgeId" : NumberLong(3),
        "productBadgeName" : "OPEN Vials",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(3),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cff277a245291028fd80"),
        "storeId" : NumberLong(1360255),
        "storeName" : "New Kumbharwada UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(3345907),
        "productName" : "Pentavalent (dose)",
        "productBadgeId" : NumberLong(2),
        "productBadgeName" : "RI Vaccines",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(20),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cf7a934eee407add02d8"),
        "storeId" : NumberLong(1356656),
        "storeName" : "Akhlol UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(10),
        "productName" : "OPEN bOPV (vial)",
        "productBadgeId" : NumberLong(3),
        "productBadgeName" : "OPEN Vials",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(3),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cf7a934eee407add02d7"),
        "storeId" : NumberLong(1356658),
        "storeName" : "Bharatnagar UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(3345885),
        "productName" : "bOPV (dose)",
        "productBadgeId" : NumberLong(2),
        "productBadgeName" : "RI Vaccines",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(40),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cf7a934eee407add02d6"),
        "storeId" : NumberLong(1356658),
        "storeName" : "Bharatnagar UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(3345907),
        "productName" : "Pentavalent (dose)",
        "productBadgeId" : NumberLong(2),
        "productBadgeName" : "RI Vaccines",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(20),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cf7a934eee407add02d5"),
        "storeId" : NumberLong(1356656),
        "storeName" : "Akhlol UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(3345907),
        "productName" : "Pentavalent (dose)",
        "productBadgeId" : NumberLong(2),
        "productBadgeName" : "RI Vaccines",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(20),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cf7a934eee407add02d4"),
        "storeId" : NumberLong(1356658),
        "storeName" : "Bharatnagar UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(10),
        "productName" : "OPEN bOPV (vial)",
        "productBadgeId" : NumberLong(3),
        "productBadgeName" : "OPEN Vials",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(3),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cf7a934eee407add02d3"),
        "storeId" : NumberLong(1356656),
        "storeName" : "Akhlol UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(3345885),
        "productName" : "bOPV (dose)",
        "productBadgeId" : NumberLong(2),
        "productBadgeName" : "RI Vaccines",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 4,
        "txnTypeName" : "Stock-Discards",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(40),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
{
        "_id" : ObjectId("6401cefd77a2452910289d52"),
        "storeId" : NumberLong(1356656),
        "storeName" : "Akhlol UHC",
        "storeBadgeId" : NumberLong(279),
        "storeBadgeName" : "MCCCP",
        "productId" : NumberLong(3345879),
        "productName" : "OPEN Pentavalent (vial)",
        "productBadgeId" : NumberLong(3),
        "productBadgeName" : "OPEN Vials",
        "txnStringDate" : "2023-03-03",
        "txnDate" : ISODate("2023-03-03T00:00:00Z"),
        "txnTypeId" : 2,
        "txnTypeName" : "Stock-In",
        "stateId" : NumberLong(351),
        "stateName" : "Gujarat",
        "districtId" : NumberLong(2030),
        "districtName" : "Bhavnagar",
        "isDeleted" : false,
        "stock" : NumberLong(10),
        "pranthId" : NumberLong(1344239),
        "month" : "Mar",
        "year" : "2023",
        "initialTxnDate" : ISODate("2023-03-03T00:00:00Z"),
        "initialTxnStringDate" : "2023-03-03",
        "_class" : "com.dipl.evinae.reports.mongo.entity.TxnCountLogDetails"
}
  1. How many different values does the shard key contain?

Shard Key provided as a data key, hence shard key existing values from “txnDate” :

ISODate("2021-08-02T00:00:00Z") to txnDate" : ISODate("2023-03-03T00:00:00Z")
  1. What MongoDB version you are using?

currently, it’s 4.4.16

Thanks & Regards,
Ramesh.

5

Hi @MERUGUPALA_RAMES,

Thanks for sharing the information :sparkles:

I think this index

{ productBadgeId: 1, storeBadgeId: 1, pranthId: 1, txnDate: 1 }

is not very efficient for this particular query because the shard key txnDate is not the prefix of the index.

It is important to choose a shard key that is frequently used in queries and ensure that it is the leading element of the index to leverage the full benefits of sharding. Please refer to the Shard Key Index for more details.

If you need further help, can you please share the explain db.collection.explain(‘executionStats’) output, and the output of db.collection.getIndexes()?

Best,
Kushagra