mongodb views HANGS when joins performed and frequent collections updates

[chiku fan]

Hi

I am new to mongodb3.4 and I am using mongodb 3.4 and java/spring-data, and was fascinated that mongodb supports views and we were in the process of migrating our whole datawarehousing data from RDBMS to mongodb3.4. The whole migration idea seems to be BREAKING with joins in views in mongodb3.4. Since 100 records are getting inserted per second in transaction collection and 2M assets is updated daily twice a day, this process TOTALLY LOCKS the views from record retrieval as mongodb.  Also I need to filter records in assets based on some criterias at run time.  Please help.

My assets collection is of 2M Records each record is of the following format :
{
            "_id" : ObjectId("58e3d9d1bd8c42d00d000029"),
            "RecordReference" : "9780002193351",
            "NotificationType" : "03",
            "ProductIdentifier" : [
                {
                    "ProductIDType" : "02",
                    "IDValue" : "0002193353"
                },
                {
                    "ProductIDType" : "15",
                    "IDValue" : "9780002193351"
                },
                {
                    "ProductIDType" : "03",
                    "IDValue" : "9780002193351"
                }
            ],
            "Barcode" : "03",
            "ProductForm" : "BC",
            "BookFormDetail" : "02",
            "DistinctiveTitle" : "Nests, Eggs, and Nestlings",
            "Contributor" : {
                "SequenceNumber" : "1",
                "ContributorRole" : "A01",
                "PersonNameInverted" : "Harrison, Colin "
            },
            "Language" : {
                "LanguageRole" : "01",
                "LanguageCode" : "eng"
            },
            "BASICMainSubject" : "NAT000000",
            "AudienceCode" : "01",
            "ImprintName" : "Penguin (Non-Classics)",
            "Imprint" : {
                "NameCodeType" : "02",
                "NameCodeValue" : "PPITHPTHPEN"
            },
            "PublisherName" : "Penguin Group USA, Inc",
            "PublicationDate" : "19870505",
            "Measure" : [
                {
                    "MeasureTypeCode" : "01",
                    "Measurement" : "5.00",
                    "MeasureUnitCode" : "in"
                },
                {
                    "MeasureTypeCode" : "02",
                    "Measurement" : "7.00",
                    "MeasureUnitCode" : "in"
                },
                {
                    "MeasureTypeCode" : "03",
                    "Measurement" : "1.00",
                    "MeasureUnitCode" : "in"
                },
                {
                    "MeasureTypeCode" : "08",
                    "Measurement" : "0.50",
                    "MeasureUnitCode" : "lb"
                }
            ],
            "OutOfPrintDate" : "20021016",
            "SupplyDetail" : {
                "SupplierSAN" : "2825074",
                "SupplierName" : "Penguin Group USA, Inc",
                "ReturnsCodeType" : "02",
                "ReturnsCode" : "N",
                "LastDateForReturns" : "20030414",
                "AvailabilityCode" : "OP",
                "Stock" : {
                    "LocationIdentifier" : {
                        "LocationIDType" : "07",
                        "IDValue" : "257-8115"
                    },
                    "LocationName" : "Pittston Township, PA",
                    "OnHand" : "0"
                },
                "PackQuantity" : "20",
                "Price" : {
                    "PriceTypeCode" : "01",
                    "ClassOfTrade" : "A11",
                    "DiscountCoded" : {
                        "DiscountCodeType" : "02",
                        "DiscountCode" : "A11"
                    },
                    "PriceAmount" : "9.95",
                    "CurrencyCode" : "USD"
                }
            }
        }
 My transaction records (30M) is of the following form:
 {
  "_id" : ObjectId("59b98c6b5d8b700d6c79de7a"),
    "type" : "Type1",
    "testing" : "Testing Nos : : : : : : : 0",
    "RecordReference" : "9780002193351"
}
The join in the View of Join of Transaction and  assets
{
    "_id" : ObjectId("59b98c6b5d8b700d6c79de7a"),
    "type" : "Type1",
    "testing" : "Testing Nos : : : : : : : 0",
    "RecordReference" : "9780002193351",
    "inventory_docs" : [
{
            "_id" : ObjectId("58e3d9d1bd8c42d00d000029"),
            "RecordReference" : "9780002193351",
            "NotificationType" : "03",
            "ProductIdentifier" : [
                {
                    "ProductIDType" : "02",
                    "IDValue" : "0002193353"
                },
                {
                    "ProductIDType" : "15",
                    "IDValue" : "9780002193351"
                },
                {
                    "ProductIDType" : "03",
                    "IDValue" : "9780002193351"
                }
            ],
            "Barcode" : "03",
            "ProductForm" : "BC",
            "BookFormDetail" : "02",
            "DistinctiveTitle" : "Nests, Eggs, and Nestlings",
            "Contributor" : {
                "SequenceNumber" : "1",
                "ContributorRole" : "A01",
                "PersonNameInverted" : "Harrison, Colin "
            },
            "Language" : {
                "LanguageRole" : "01",
                "LanguageCode" : "eng"
            },
            "BASICMainSubject" : "NAT000000",
            "AudienceCode" : "01",
            "ImprintName" : "Penguin (Non-Classics)",
            "Imprint" : {
                "NameCodeType" : "02",
                "NameCodeValue" : "PPITHPTHPEN"
            },
            "PublisherName" : "Penguin Group USA, Inc",
            "PublicationDate" : "19870505",
            "Measure" : [
                {
                    "MeasureTypeCode" : "01",
                    "Measurement" : "5.00",
                    "MeasureUnitCode" : "in"
                },
                {
                    "MeasureTypeCode" : "02",
                    "Measurement" : "7.00",
                    "MeasureUnitCode" : "in"
                },
                {
                    "MeasureTypeCode" : "03",
                    "Measurement" : "1.00",
                    "MeasureUnitCode" : "in"
                },
                {
                    "MeasureTypeCode" : "08",
                    "Measurement" : "0.50",
                    "MeasureUnitCode" : "lb"
                }
            ],
            "OutOfPrintDate" : "20021016",
            "SupplyDetail" : {
                "SupplierSAN" : "2825074",
                "SupplierName" : "Penguin Group USA, Inc",
                "ReturnsCodeType" : "02",
                "ReturnsCode" : "N",
                "LastDateForReturns" : "20030414",
                "AvailabilityCode" : "OP",
                "Stock" : {
                    "LocationIdentifier" : {
                        "LocationIDType" : "07",
                        "IDValue" : "257-8115"
                    },
                    "LocationName" : "Pittston Township, PA",
                    "OnHand" : "0"
                },
                "PackQuantity" : "20",
                "Price" : {
                    "PriceTypeCode" : "01",
                    "ClassOfTrade" : "A11",
                    "DiscountCoded" : {
                        "DiscountCodeType" : "02",
                        "DiscountCode" : "A11"
                    },
                    "PriceAmount" : "9.95",
                    "CurrencyCode" : "USD"
                }
            }
        }
 
           ]
}

Please help.

Regards
Kris

[Kevin Adistambha]

Hi Kris

I am using mongodb 3.4 and java/spring-data

I’m not overly familiar with how Spring Data interacts with MongoDB. Is it possible to determine the actual query you’re trying to optimize? The explain() output of the commonly slow queries will be very helpful.

It will also be helpful to know more about your deployment details e.g. your MongoDB version, the topology, some output from mongostat during those “hang” period, etc.

100 records are getting inserted per second in transaction collection and 2M assets is updated daily twice a day

Depending on your hardware capabilities, these may or may not be contributing to the slow queries you’re seeing. However it’s hard to tell unless you know that other parts of the equation are fully optimized (efficient queries, good use of indexing, enough RAM for most operations, etc.)

Best regards,
Kevin

​