Hi Team,
We are facing a query performance issue in stage and production environment.The same query is performing well in
development,QA,performance environment. Data volume is same adn no of indexes are same.
In production/Stage mongo cluster the same query is scanning all the documents( Full collection scan) before update/insert.
Could you please let us know why is the query behaviour is different between production & Performance environment.
Please find the following query details:
Production environment:
Mongo DB version : 3.2.4
Server type : Physical servers
Configuration : 3 replicasets 32 CPU cores,190GB RAM, (All the 3 replicas with same configuration)
Query or Job details:
Job Type: Data loading
Query type: Upsert
Total no of records in the collection : 2000000
Application Query Type : Upsert
Avg time to execute the query : 7Secs
Expected Average time to execute : 2-5 milliseconds
Applications are running in docker machine.All 3 fields in where condition have indexes: CUSIP,EXCHANGES.SYMBOL,ORIGIN_DATA_SOURCE
Query from mongod.log in production environment:
2020-05-15T21:19:51.119-0700 I WRITE [conn3085104] update prodsecmaster.falcon_unverified_securities query: { $and: [ { $or: [ { CUSIP: “VLY1200417C00025000” }, { EXCHANGES.SYMBOL: { $in: [ “VLY1200417C00025000” ] } } ] }, { ORIGIN_DATA_SOURCE: “RWBAIRD” } ] } update: { $setOnInsert: { CUSIP: “VLY1200417C00025000”, ISIN: “”, SEDOL: “”, VALOREN: “”, SECURITY_DESCRIPTION: “VLY1 100.00 APR 20 25.000 CALL”, SECURITY_DESCRIPTION_CAP: “VLY1 100.00 APR 20 25.000 CALL”, LAST_UPDATED: new Date(1589600199027), CREATED: new Date(1589600199027), IS_DUMMY_SECURITY: 0, ENV_SECURITY_TYPE: “”, issue_date: “”, maturity_date: “”, ISO_CFI_CODE: “”, ORIGIN_DATA_SOURCE: “RWBAIRD”, ISSUE_STATUS: “”, VENDOR_CUSIP: “VLY1200417C00025000”, VENDOR_ISIN: “”, VENDOR_SEDOL: “”, VENDOR_VALOREN: “”, VENDOR_SYMBOL: “”, moody_rating: “”, frequency: “”, call_date: “”, fund_family: “”, interest_rate: “”, federal_taxable: “”, s_and_p_rating: “”, first_coupon_date: “”, sector: “”, cdsc_fund_flag: “”, issue_type_multiplier: “”, agency_factor: “”, minimum_purchase: “”, last_coupon_date: “”, closed_flag: “”, accrual_method: “”, envestnet_dummy_security_flag: “”, state_taxable: “”, call_price: “”, income_currency: “”, firm_eligible: “”, gics_sector: “”, share_class: “”, fund_year: “”, is_synthetic_security: false, falcon_security_style: “Other”, security_style: “Other”, SECURITY_STYLE_RULE: “FALCON_SM:VENDOR_SECURITY_STYLE->Other”, SECURITY_TYPE: “Call Option”, SECURITY_TYPE_RULE: “FALCON_SM:Security_Type_Module->Call Option”, IS_DELETED: 1, SECURITY_DESCRIPTIONS: [ { SECURITY_DESCRIPTION: “VLY1 100.00 APR 20 25.000 CALL” } ], EXCHANGES: [ { COUNTRY_CODE: “”, EXCHANGE_CODE: “XNYS”, CURRENCY_CODE: “”, SYMBOL: “VLY1200417C00025000” } ], CUSIPS: [ { CUSIP: “VLY1200417C00025000” } ], DATA_SOURCES: [ { DATA_SOURCE: “RWBAIRD”, VENDOR_SECURITY_ID: “0” } ], VENDOR_SECURITY_TYPES: [ { VENDOR: “YODLEE_FALCON”, VENDOR_SECURITY_TYPE: “Call Option” } ], VERSION: 3412, securities_id: 400033948 } } keysExamined:1820087 docsExamined:1820087 nMatched:1 nModified:0 keyUpdates:0 writeConflicts:0 numYields:14219 locks:{ Global: { acquireCount: { r: 5332506, w: 5332506 }, acquireWaitCount: { w: 1 }, timeAcquiringMicros: { w: 63195 } }, Database: { acquireCount: { w: 5332506 } }, Collection: { acquireCount: { w: 5332506 } } } 7505ms
Keys Examined for each record:
CUSIP: “ZEN200417C00065000”: keysExamined:1820087 docsExamined:1820087 nMatched:1 nModified:0 keyUpdates:0
CUSIP: “VLY1200417C00025000” keysExamined:1820087 docsExamined:1820087 nMatched:1 nModified:0 keyUpdates:0
CUSIP: “XPP200417P00055000” keysExamined:1820087 docsExamined:1820087 nMatched:1 nModified:0 keyUpdates:0
Query from mongod.log in other(development,QA,Perf) environments:
2020-05-22T00:16:37.414+0530 I WRITE [conn18] update security_master.falcon_unverified_securities appName: “MongoDB Shell” command: { q: { $and: [ { $or: [ { CUSIP: “XLK200424P00086500” }, { EXCHANGES.SYMBOL: { $in: [ “XLK200424P00086500” ] } } ] }, { ORIGIN_DATA_SOURCE: “RWBAIRD” } ] }, u: { $setOnInsert: { CUSIP: “XLK200424P00086500”, ISIN: “”, SEDOL: “”, VALOREN: “”, SECURITY_DESCRIPTION: “XLK 100.00 APR 20 86.500 PUT”, SECURITY_DESCRIPTION_CAP: “XLK 100.00 APR 20 86.500 PUT”, LAST_UPDATED: new Date(1588853159614), CREATED: new Date(1588853159614), IS_DUMMY_SECURITY: 0.0, ENV_SECURITY_TYPE: “”, issue_date: “”, maturity_date: “”, ISO_CFI_CODE: “”, ORIGIN_DATA_SOURCE: “RWBAIRD”, ISSUE_STATUS: “”, VENDOR_CUSIP: “XLK200424P00086500”, VENDOR_ISIN: “”, VENDOR_SEDOL: “”, VENDOR_VALOREN: “”, VENDOR_SYMBOL: “”, moody_rating: “”, frequency: “”, call_date: “”, fund_family: “”, interest_rate: “”, federal_taxable: “”, s_and_p_rating: “”, first_coupon_date: “”, sector: “”, cdsc_fund_flag: “”, issue_type_multiplier: “”, agency_factor: “”, minimum_purchase: “”, last_coupon_date: “”, closed_flag: “”, accrual_method: “”, envestnet_dummy_security_flag: “”, state_taxable: “”, call_price: “”, income_currency: “”, firm_eligible: “”, gics_sector: “”, share_class: “”, fund_year: “”, is_synthetic_security: false, falcon_security_style: “Other”, security_style: “Other”, SECURITY_STYLE_RULE: “FALCON_SM:VENDOR_SECURITY_STYLE->Other”, SECURITY_TYPE: “Put Option”, SECURITY_TYPE_RULE: “FALCON_SM:Security_Type_Module->Put Option”, IS_DELETED: 0.0, SECURITY_DESCRIPTIONS: [ { SECURITY_DESCRIPTION: “XLK 100.00 APR 20 86.500 PUT” } ], EXCHANGES: [ { COUNTRY_CODE: “”, EXCHANGE_CODE: “XNYS”, CURRENCY_CODE: “”, SYMBOL: “XLK200424P00086500” } ], CUSIPS: [ { CUSIP: “XLK200424P00086500” } ], DATA_SOURCES: [ { DATA_SOURCE: “RWBAIRD”, VENDOR_SECURITY_ID: “0” } ], VENDOR_SECURITY_TYPES: [ { VENDOR: “YODLEE_FALCON”, VENDOR_SECURITY_TYPE: “Put Option” } ], VERSION: 1462.0, securities_id: 400325388 } }, multi: false, upsert: true } planSummary: IXSCAN { EXCHANGES.SYMBOL: 1 }, IXSCAN { CUSIP: 1 } keysExamined:0 docsExamined:0 nMatched:0 nModified:0 upsert:1 keysInserted:14 numYields:0 locks:{ Global: { acquireCount: { r: 1, w: 1 } }, Database: { acquireCount: { w: 1 } }, Collection: { acquireCount: { w: 1 } } } 2ms
Keys Examined for each record:
CUSIP: “XLK200424P00086500” keysExamined:0 docsExamined:0 nMatched:0 nModified:0 upsert:1 keysInserted:14
Thanks,
Balaji