Mongodb aggregate $group stage takes a long time

Question

I'm practicing how to use MongoDB aggregation, but they seem to take a really long time (running time).

The problem seems to happen whenever I use $group. All other queries run just fine.

I have some 1.3 million dummy documents that need to perform two basic operations: get a count of the IP addresses and unique IP addresses.

My schema looks something like this:

{
    "_id":"5da51af103eb566faee6b8b4",
    "ip_address":"...",
    "country":"CL",
    "browser":{
        "user_agent":...",
    }
}

Running a basic $group query takes about 12s on average, which is much too slow.

I did a little research, and someone suggested creating an index on ip_addresses. That seems to have slowed it down because queries now take 13-15s.

I use MongoDB and the query I'm running looks like this:

    visitorsModel.aggregate([
        {
            '$group': {
                '_id': '$ip_address',
                'count': {
                    '$sum': 1
                }
            }
        }
    ]).allowDiskUse(true)
        .exec(function (err, docs) {
            if (err) throw err;

            return res.send({
                uniqueCount: docs.length
            })
        })

Any help is appreciated.

Edit: I forgot to mention, someone suggested it might be a hardware issue? I'm running the query on a core i5, 8GB RAM laptop if it helps.

Edit 2: The query plan:

{
    "stages" : [
        {
            "$cursor" : {
                "query" : {

                },
                "fields" : {
                    "ip_address" : 1,
                    "_id" : 0
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "metrics.visitors",
                    "indexFilterSet" : false,
                    "parsedQuery" : {

                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "direction" : "forward"
                    },
                    "rejectedPlans" : [ ]
                },
                "executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 1387324,
                    "executionTimeMillis" : 7671,
                    "totalKeysExamined" : 0,
                    "totalDocsExamined" : 1387324,
                    "executionStages" : {
                        "stage" : "COLLSCAN",
                        "nReturned" : 1387324,
                        "executionTimeMillisEstimate" : 9,
                        "works" : 1387326,
                        "advanced" : 1387324,
                        "needTime" : 1,
                        "needYield" : 0,
                        "saveState" : 10930,
                        "restoreState" : 10930,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "direction" : "forward",
                        "docsExamined" : 1387324
                    }
                }
            }
        },
        {
            "$group" : {
                "_id" : "$ip_address",
                "count" : {
                    "$sum" : {
                        "$const" : 1
                    }
                }
            }
        }
    ],
    "ok" : 1
}

prasad_ · Accepted Answer

This is some info about using $group aggregation stage, if it uses indexes, and its limitations and what can be tried to overcome these.

1. The $group Stage Doesn't Use Index: Mongodb Aggregation: Does $group use index?

2. $group Operator and Memory:

The $group stage has a limit of 100 megabytes of RAM. By default, if the stage exceeds this limit, $group returns an error. To allow for the handling of large datasets, set the allowDiskUse option to true. This flag enables $group operations to write to temporary files.

See MongoDb docs on $group Operator and Memory

3. An Example Using $group and Count:

A collection called as cities:

{ "_id" : 1, "city" : "Bangalore", "country" : "India" }
{ "_id" : 2, "city" : "New York", "country" : "United States" }
{ "_id" : 3, "city" : "Canberra", "country" : "Australia" }
{ "_id" : 4, "city" : "Hyderabad", "country" : "India" }
{ "_id" : 5, "city" : "Chicago", "country" : "United States" }
{ "_id" : 6, "city" : "Amritsar", "country" : "India" }
{ "_id" : 7, "city" : "Ankara", "country" : "Turkey" }
{ "_id" : 8, "city" : "Sydney", "country" : "Australia" }
{ "_id" : 9, "city" : "Srinagar", "country" : "India" }
{ "_id" : 10, "city" : "San Francisco", "country" : "United States" }

Query the collection to count the cities by each country:

db.cities.aggregate( [
    { $group: { _id: "$country", cityCount: { $sum: 1 } } },
    { $project: { country: "$_id", _id: 0, cityCount: 1 } }
] )

The Result:

{ "cityCount" : 3, "country" : "United States" }
{ "cityCount" : 1, "country" : "Turkey" }
{ "cityCount" : 2, "country" : "Australia" }
{ "cityCount" : 4, "country" : "India" }

4. Using allowDiskUse Option:

db.cities.aggregate( [
    { $group: { _id: "$country", cityCount: { $sum: 1 } } },
    { $project: { country: "$_id", _id: 0, cityCount: 1 } }
],  { allowDiskUse : true } )

Note, in this case it makes no difference in query performance or output. This is to show the usage only.

5. Some Options to Try (suggestions):

You can try a few things to get some result (for trial purposes only):

Use $limit stage and restrict the number of documents processed and see what is the result. For example, you can try { $limit: 1000 }. Note this stage needs to come before the $group stage.
You can also use the $match, $project stages before the $group stage to control the shape and size of the input. This may return a result (instead of an error).

[EDIT ADD]

Notes on Distinct and Count:

Using the same cities collection - to get unique countries and a count of them you can try using the aggregate stage $count along with $group as in the following two queries.

Distinct:

db.cities.aggregate( [
   { $match: { country: { $exists: true } } },
   { $group: { _id: "$country" } },
   { $project: { country: "$_id", _id: 0 } }
] )

The Result:

{ "country" : "United States" }
{ "country" : "Turkey" }
{ "country" : "India" }
{ "country" : "Australia" }

To get the above result as a single document with an array of unique values, use the $addToSetoperator:

db.cities.aggregate( [
   { $match: { country: { $exists: true } } },
   { $group: { _id: null, uniqueCountries: { $addToSet:  "$country" } } },
   { $project: { _id: 0 } },
] )

The Result: { "uniqueCountries" : [ "United States", "Turkey", "India", "Australia" ] }

Count:

db.cities.aggregate( [
   { $match: { country: { $exists: true } } },
   { $group: { _id: "$country" } },
   { $project: { country: "$_id", _id: 0 } },
   { $count: "uniqueCountryCount" }
] )

The Result: { "uniqueCountryCount" : 4 }

In the above queries the $match stage is used to filter any documents with non-existing or null countryfield. The $project stage reshapes the result document(s).

MongoDB Query Language:

Note the two queries get similar results when using the MongoDB query language commands: db.collection.distinct("country") and db.cities.distinct("country").length (note the distinct returns an array).

Mongodb aggregate $group stage takes a long time

Answers (2)

Related Questions