Shulou(Shulou.com)06/01 Report--

What I want to share with you today is the performance optimization experience of MongoDB aggregate. Let's take a look.

On an Ali CVM configured with 2-core 4G, the hard disk is an ordinary cloud disk (SATA disk). In addition to mongoDB, several java applications are running. The actual available memory of single node mysql and redis,mongo is about 1.5G. When there are 2 million data in a single table, the response time of an aggregate function is about 6 seconds, and the page side requests once per second. Because the response is not timely and the page refresh is not timely, the server accumulates a large number of mongo aggregate requests, and the system is out of available memory, which directly leads to the overflow and the passive shutdown of the mongo service.

Mongod (ZN5mongo15printStackTraceERSo+0x41) [0x55bd3a2dd321]

Mongod (ZN5mongo29reportOutOfMemoryErrorAndExitEv+0x84) [0x55bd3a2dc954]

Mongod (ZN5mongo12mongoReallocEPvm+0x21) [0x55bd3a2d22b1]

Mongod (ZN5mongo11BufBuilderINS21SharedBufferAllocatorEE15growreallocateEi+0x83) [0x55bd38981833]

Mongod (ZN5mongo3rpc17OpMsgReplyBuilder22getInPlaceReplyBuilderEm+0x80) [0x55bd39d4b740]

Mongod (+ 0xAB9609) [0x55bd389be609]

Mongod (+ 0xABBA59) [0x55bd389c0a59]

Here is the aggregate script, which is simply to count the latest values of multiple status codes for a car (implemented through $first).

Db.getCollection ("vinMsgOut") .aggregate ([{"$match": {"vinCode": "LSGKR53L3HA149563"}, {"$sort": {"postTime":-1}}, {"$group": {"_ id": "$messageType", "resultValue": {"$first": "$resultValue"}}], {allowDiskUse: true})

The first reaction is to increase the filter condition and index.

Combined with business, add time condition filtering, and change $match to:

{"$match": {"vinCode": "LSGKR53L3HA149563", "createTime": {$gt: ISODate ("2020-03-01T06:30:12.038Z")}

Then create indexes for vinCode and createTime, and execute them, which is still more than 6 seconds.

Change the field of $sort to the index field createTime

{"$sort": {"createTime":-1}}

Execute again, the time is still more than 6 seconds.

Due to the limited memory available in the system, the storage engine has defaulted to the fastest wiredTiger, and the disk cannot be more powerful, so we can only start from the business. Considering that the emergence of these latest states is usually in the same period of time, with only a few hundred status codes, would it be faster to take only some of them from pipe for group after sort? With this question in mind, I added a limit.

Db.getCollection ("vinMsgOut") .aggregate ([{"$match": {"vinCode": "LSGKR53L3HA149563", "createTime": {$gt: ISODate ("2020-03-01T06:30:12.038Z")}, {"$sort": {"createTime":-1}, {"$limit": 1000}, {"$group": {"_ id": "$messageType" "resultValue": {"$first": "$resultValue"}}], {allowDiskUse: true})

The result is back in seconds!

Remove the createTime condition in $match and still go back in seconds! Does this mean that the createTime index is not working? With doubt, delete the createTime index and change the cash-back time to 5 seconds, so the createTime index is useful for $sort. To sum up, the optimization of the whole query is completed, which can be summarized as follows:

The $match condition needs to increase the index, and if there are multiple, it is best to use a combined index; the field of $sort also needs to increase the index; the _ id of $group also needs to increase the index; limit can greatly reduce the time consumption. The experience of MongoDB aggregate performance optimization is shared here, of course, not only the above and everyone's analysis methods, but the editor can ensure that its accuracy is absolutely no problem. I hope that the above content can have a certain reference value for everyone, and can be put into practice. If you like this article, you might as well share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.