6

Aggregation query:

 db.events.aggregate([
        {$match: { "generated_at": {
            "$gte": new Date(2017, 0, 1),
            "$lte": new Date(2017, 3, 1)
        }, game_id: ObjectId("59de213e9c43f70001c29bef"), event_type: "session"}},
        {$project: { generated_at: "$generated_at", user_device_id: "$user_device_id", 
            avg_user_session_duration: {
                $avg: "$session_duration"
            }
        }},
        {$group: { _id: {day: { $dayOfMonth: "$generated_at"}, month: {$month: "$generated_at"},  year: { $year: "$generated_at"} }, 
            avg_session_duration_per_user: {
                $avg: "$avg_user_session_duration"
            }
        }},
        {$sort: {"_id.year": 1, "_id.month": 1, "_id.day": 1}}
    ])

Indexes:

 {
    "v" : 2,
    "key" : {
        "generated_at" : 1,
        "game_id" : 1,
        "event_type" : 1,
        "impression_type" : 1,
        "source_name" : 1
    },
    "name" : "generated_at_1_game_id_1_event_type_1_impression_type_1_source_name_1",
    "ns" : "analyticsdb.events"
}

I have the aggregation query as above it works with small dataset but I have more than 10 million documents of size more than 2GB. I am using mongo atlas M10 instance. And I get this alert from mongo atlas. Also the shell just keeps on waiting for the query result but never gives any response.

Prata
  • 1,250
  • 2
  • 16
  • 31
  • How many documents are actually being selected then? What does `.count({ { "generated_at": { "$gte": new Date(2017, 0, 1), "$lte": new Date(2017, 3, 1) }, game_id: ObjectId("59de213e9c43f70001c29bef"), event_type: "session" })` tell you? – Neil Lunn Oct 18 '17 at 09:12
  • @NeilLunn yes the count was -> 3399145 – Prata Oct 18 '17 at 11:18
  • So to be clear that's 3.4 ( little rounding ) "million" results on a cursor being fed into an aggregation to group, down to approximately 90 results. Just at a quick stab, that's probably outside of the general usage recommendations for a single instance. So if you have 10 million documents and 3.4 million is covered within just three months with some additional criteria that presumably is not selecting everything between the dates, then you have a problem. You are going to need to shard if you do this as a requirement. – Neil Lunn Oct 18 '17 at 11:27
  • We try to fix this alert in this way, please refer to https://stackoverflow.com/a/69361482/3011380 – zangw Aug 24 '22 at 06:30

0 Answers0