search

jediswaplabs / dashboard-backend

Example Apibara project indexing a DEX and providing data through a GraphQL API.
Apache License 2.0
2 stars 6 forks source link

Optimise Mongo queries #4

Closed princearoragithub closed 1 year ago

princearoragithub commented 1 year ago

-- Check logs for slow queries -- Optimise those by indexing or any other measures

princearoragithub commented 1 year ago 
{"t":{"$date":"2023-03-16T11:35:50.956+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn58449","msg":"Slow query","attr":{"type":"command","ns":"jediswap_testnet.tokens","command":{"findAndModify":"tokens","query":{"id":{"$binary":{"base64":"Bo9camF4B2hFXeaQd+B+iXh4Ob+BZt7Pv5K2RSCcD7g=","subType":"0"}},"_chain.valid_to":null},"new":false,"update":{"$set":{"_chain.valid_to":15002}},"upsert":false,"lsid":{"id":{"$uuid":"dd4387c1-b072-4320-b398-579f0f8272b1"}},"$db":"jediswap_testnet"},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":46618,"nMatched":1,"nModified":1,"nUpserted":0,"numYields":46,"queryHash":"DE492431","planCacheKey":"DE492431","reslen":484,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":47}},"FeatureCompatibilityVersion":{"acquireCount":{"w":47}},"ReplicationStateTransition":{"acquireCount":{"w":47}},"Global":{"acquireCount":{"w":47}},"Database":{"acquireCount":{"w":47}},"Collection":{"acquireCount":{"w":47}},"Mutex":{"acquireCount":{"r":1}}},"flowControl":{"acquireCount":47,"timeAcquiringMicros":68},"storage":{},"remote":"10.8.1.137:59616","protocol":"op_msg","durationMillis":123}}
{"t":{"$date":"2023-03-16T11:35:51.325+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn58449","msg":"Slow query","attr":{"type":"command","ns":"jediswap_testnet.tokens","command":{"find":"tokens","filter":{"id":{"$binary":{"base64":"BJ02Vw1ORvSOmWdL0/zIRkTd1rlvfHQbFWK4L54ATcc=","subType":"0"}},"_chain.valid_to":null},"limit":1,"singleBatch":true,"lsid":{"id":{"$uuid":"dd4387c1-b072-4320-b398-579f0f8272b1"}},"$db":"jediswap_testnet"},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":46617,"cursorExhausted":true,"numYields":46,"nreturned":1,"queryHash":"DE492431","planCacheKey":"DE492431","queryFramework":"classic","reslen":501,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":47}},"Global":{"acquireCount":{"r":47}},"Mutex":{"acquireCount":{"r":1}}},"storage":{},"remote":"10.8.1.137:59616","protocol":"op_msg","durationMillis":109}}
{"t":{"$date":"2023-03-16T11:35:45.937+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn58449","msg":"Slow query","attr":{"type":"command","ns":"jediswap_testnet.token_day_data","command":{"find":"token_day_data","filter":{"token_id":{"$binary":{"base64":"BTyRJTvJaCwEkpygLtALPkI/ZxDS7n4NXrsG8+zzaKg=","subType":"0"}},"day_id":19332,"_chain.valid_to":null},"limit":1,"singleBatch":true,"lsid":{"id":{"$uuid":"dd4387c1-b072-4320-b398-579f0f8272b1"}},"$db":"jediswap_testnet"},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":35852,"cursorExhausted":true,"numYields":35,"nreturned":1,"queryHash":"9C94F986","planCacheKey":"9C94F986","queryFramework":"classic","reslen":524,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":36}},"Global":{"acquireCount":{"r":36}},"Mutex":{"acquireCount":{"r":1}}},"storage":{},"remote":"10.8.1.137:59616","protocol":"op_msg","durationMillis":100}}
{"t":{"$date":"2023-03-16T11:35:19.709+00:00"},"s":"I",  "c":"WRITE",    "id":51803,   "ctx":"conn58449","msg":"Slow query","attr":{"type":"update","ns":"jediswap_testnet.token_day_data","command":{"q":{"token_id":{"$binary":{"base64":"BTyRJTvJaCwEkpygLtALPkI/ZxDS7n4NXrsG8+zzaKg=","subType":"0"}},"day_id":19332,"_chain.valid_to":null},"u":{"$inc":{"transaction_count":1,"daily_volume_token":{"$numberDecimal":"106.234616"},"daily_volume_eth":{"$numberDecimal":"0.08659970165689860514843915509"},"daily_volume_usd":{"$numberDecimal":"112.6390281066004695079317603"}}},"multi":false,"upsert":false},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":35820,"nMatched":1,"nModified":1,"nUpserted":0,"numYields":35,"queryHash":"9C94F986","planCacheKey":"9C94F986","locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":36}},"FeatureCompatibilityVersion":{"acquireCount":{"w":36}},"ReplicationStateTransition":{"acquireCount":{"w":36}},"Global":{"acquireCount":{"w":36}},"Database":{"acquireCount":{"w":36}},"Collection":{"acquireCount":{"w":36}},"Mutex":{"acquireCount":{"r":1}}},"flowControl":{"acquireCount":36,"timeAcquiringMicros":57},"storage":{},"remote":"10.8.1.137:59616","durationMillis":103}}
{"t":{"$date":"2023-03-16T11:35:19.709+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn58449","msg":"Slow query","attr":{"type":"command","ns":"jediswap_testnet.$cmd","command":{"update":"token_day_data","ordered":true,"lsid":{"id":{"$uuid":"dd4387c1-b072-4320-b398-579f0f8272b1"}},"$db":"jediswap_testnet"},"numYields":35,"reslen":60,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":36}},"FeatureCompatibilityVersion":{"acquireCount":{"r":1,"w":36}},"ReplicationStateTransition":{"acquireCount":{"w":37}},"Global":{"acquireCount":{"r":1,"w":36}},"Database":{"acquireCount":{"w":36}},"Collection":{"acquireCount":{"w":36}},"Mutex":{"acquireCount":{"r":1}}},"flowControl":{"acquireCount":36,"timeAcquiringMicros":57},"storage":{},"remote":"10.8.1.137:59616","protocol":"op_msg","durationMillis":103}}
{"t":{"$date":"2023-03-16T10:13:27.968+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn361056","msg":"Slow query","attr":{"type":"command","ns":"jediswap_testnet.pairs","command":{"find":"pairs","filter":{"id":{"$binary":{"base64":"BNA5C3d7Qk5Dg5zR50R5nz3mwXbH4ywYEqQdvZwZ22o=","subType":"0"}},"$or":[{"$and":[{"_chain.valid_to":null},{"_chain.valid_from":{"$lte":19100}}]},{"$and":[{"_chain.valid_to":{"$gt":19100}},{"_chain.valid_from":{"$lte":19100}}]}]},"lsid":{"id":{"$uuid":"197fa9e0-96e4-45be-93e8-12e677f0d8f0"}},"$db":"jediswap_testnet"},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":277284,"cursorExhausted":true,"numYields":279,"nreturned":1,"queryHash":"7AD810F2","planCacheKey":"7AD810F2","queryFramework":"classic","reslen":786,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":280}},"Global":{"acquireCount":{"r":280}},"Mutex":{"acquireCount":{"r":1}}},"storage":{},"remote":"172.18.0.1:55530","protocol":"op_msg","durationMillis":533}}
{"t":{"$date":"2023-03-16T11:35:01.488+00:00"},"s":"I",  "c":"WRITE",    "id":51803,   "ctx":"conn58449","msg":"Slow query","attr":{"type":"update","ns":"jediswap_testnet.tokens","command":{"q":{"id":{"$binary":{"base64":"BTyRJTvJaCwEkpygLtALPkI/ZxDS7n4NXrsG8+zzaKg=","subType":"0"}},"_chain.valid_to":null},"u":{"$set":{"derived_eth":{"$numberDecimal":"0.0007688250077490148295043428507"}}},"multi":false,"upsert":false},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":46518,"nMatched":1,"nModified":1,"nUpserted":0,"numYields":46,"queryHash":"DE492431","planCacheKey":"DE492431","locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":47}},"FeatureCompatibilityVersion":{"acquireCount":{"w":47}},"ReplicationStateTransition":{"acquireCount":{"w":47}},"Global":{"acquireCount":{"w":47}},"Database":{"acquireCount":{"w":47}},"Collection":{"acquireCount":{"w":47}},"Mutex":{"acquireCount":{"r":1}}},"flowControl":{"acquireCount":47,"timeAcquiringMicros":59},"storage":{},"remote":"10.8.1.137:59616","durationMillis":106}}