Two similar mongodb servers, one has slow queries, other does not

Ops and Admin
1

I have two setups of mongodb server (developer env, production env.)
While dev env shows no slow query even below 100ms. The production env shows too many slow queries (4000ms-7000ms).
Both have mongodb@4.4, same systems (ubuntu@18), prodution has more resources available than dev (memory, storage, etc.)

This is the slow query log in production env

{"t":{"$date":"2022-11-30T18:20:21.470+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn5","msg":"Slow query","attr":{"type":"command","ns":"mydb.sessionactivity","command":{"find":"sessionactivity","filter":{"$and":[{"deviceSessionId":"62d1"},{"deleted":{"$ne":true}}]},"sort":{"startTime":1,"endTime":-1},"limit":9007199254740991.0,"returnKey":false,"showRecordId":false,"lsid":{"id":{"$uuid":"9c3"}},"$db":"mydb"},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":2109349,"hasSortStage":true,"cursorExhausted":true,"numYields":2164,"nreturned":3,"queryHash":"1AA21857","planCacheKey":"D35640A3","reslen":1587,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":2165}},"ReplicationStateTransition":{"acquireCount":{"w":2165}},"Global":{"acquireCount":{"r":2165}},"Database":{"acquireCount":{"r":2165}},"Collection":{"acquireCount":{"r":2165}},"Mutex":{"acquireCount":{"r":1}}},"storage":{},"protocol":"op_msg","durationMillis":4331}}
{"t":{"$date":"2022-11-30T18:20:29.046+00:00"},"s":"I",  "c":"COMMAND",  "id":48742,   "ctx":"conn13","msg":"Profiler settings changed","attr":{"from":{"level":0,"slowms":4000,"sampleRate":0.42},"to":{"level":0,"slowms":5000,"sampleRate":0.42}}}
2

Your collection has no supporting Index for this query. The whole collection (2.1M documents) is being scanned, reading from disk where necessary(slow). Based only on this query an optimal index might look like:

{"deviceSessionID":1, "deleted":1,"startTime":1,"endTime":-1}

An index like {"deviceSessionID":1, "deleted":1} or even {"deviceSessionID":1} might be less optimal but could support more use cases.

2 Likes