Mongo $sort and $count getting worse with new releases

user_6625635 0
Viewed 87

After upgrading my mongo server from 3.4 to 4.2, some aggregations using $sort and $count are now very slow to execute. To be sure it was related to the upgrade I installed several versions of Mongo locally and tried the same aggregation on the same amount of data and here's the results :

db.getCollection('devices').aggregate([
{ $lookup: { from: "sites", localField: "_id", foreignField: "devices", as: "site" } }, 
{ $unwind: { path: "$site", preserveNullAndEmptyArrays: true } },
{ $lookup: { from: "users", localField: "site._id", foreignField: "sites", as: "users" } },
{ $count: "count"}
])

Mongo version     Time
3.4            => 0.47s
3.6            => 0.632s
4.0            => 0.641s
4.2            => 2.98s
4.3.5          => 3.1s


db.getCollection('devices').aggregate([
{ $lookup: { from: "sites", localField: "_id", foreignField: "devices", as: "site" } },
{ $unwind: { path: "$site", preserveNullAndEmptyArrays: true } },
{ $lookup: { from: "users", localField: "site._id", foreignField: "sites", as: "users" } },
{ $sort: { _id: 1 } },
{ $skip: 0 },
{ $limit: 20 }
])

Mongo version     Time
3.4            => 0.476s
3.6            => 0.581s
4.0            => 0.669s
4.2            => 2.89s
4.3.5          => 3.24s

I have indexes on every fields used by the $lookup I know that the $sort should be put at the first stages of the pipeline to use the index but as this query is written dynamically, the sorted field is not always on _id and can be one of the field coming from the $lookup, also $match may be added dynamically between the stages, eg. $match: {"users.type": "customer"}

My real questions are "How can the performances getting so worse by upgrading mongo ? Am I missing something obvious ?"

Thanks for your help.

aggregation-framework mongodb
1 Answers

Without a lot more information about what exactly you benchmarked it is impossible to say what is responsible for the difference in elapsed time. Does the elapsed time include the time for the driver to connect to the database?

How can the performances getting so worse by upgrading mongo ?

Generally speaking, new features are constantly being added to all software that is under active development, hence it is reasonable to expect that an individual operation may get slower over time either because it itself does more work to accommodate the growth in features, or because there is overhead around it due to feature growth elsewhere in the system.

user_95409250
Related