Axios GET in for loop causing "Sort exceeded memory limit" error-CodePudding

I'm trying to load entries from my mongoDB database one at a time. It works for about 400/1000 entries then breaks with the following error:

Executor error during find command :: caused by :: Sort exceeded memory limit of 33554432 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.

My Axios get request looks like:

    for (let i = 0; i < total; i  ) {
      await axios({
        method: "GET",
        url: "/api/entries/",
        params: { from: index   i, _limit: 1 },

      })
        .then((res) => {
          setPics((prev) => {
            return [...new Set([...prev, ...res.data])];
          });
          setIndex((prev) => prev  )
          setMoreEntries(res.data.length > 0)
        })

    }

and in my controller my GET function looks like:

const getEntries = asyncHandler(async (req, res) => {
  const entries = await Entry.find().allowDiskUse(true).sort({ createdAt: 'desc' }).skip(req.query.from).limit(req.query._limit)
  res.status(200).json(entries)
})

Everything works perfectly until about half the entries have loaded, then it breaks. I thought adding allowDiskUse(true) would fix it, but same result.

Edit: I should mention, if I take out .sort({ createdAt: 'desc' }) it works perfectly, but loads in the opposite order.

CodePudding user response：

The syntax itself is fine, I am not quite sure why it's not working for you.

I have two theory's that could be possible:

You are hosting your db on Atlas and are using unqualified instances for this operations as specified in their docs:

Atlas M0 free clusters and M2/M5 shared clusters don't support the allowDiskUse

It seems you're using mongoose, maybe you are using an older version that has an issue with this cursor flag or has different syntax.

Regardless of the source of the issue, I recommend you solve this by either:

Create an index on createdAt, sorting when an index exists does not scan documents into memory, which will make both the query more efficient and solve this issue.
Just use the _id index and sort with { _id: -1}, from your comment about removing the sort operation and getting documents in reverse order, it seems that your createdAt corresponds document creation date ( makes sense ), the ObjectId _id is monotonically increasing which means you can just use that field to sort.

CodePudding user response：

I ended up just doing a backwards for-loop, and using skip instead of sort, like

const getOne = asyncHandler(async (req, res) => {
  const entry = await Entry.findOne({}, {}).allowDiskUse(true).skip(req.query.from)

  res.status(200).json(entry)
})

Which worked perfectly for me, but Tom's answer is what I was looking for, thank you :)