Out of sort memory when using OrderBy (using Prisma)

Question

I am getting the following error

ResourceExhausted desc = Out of sort memory, 
consider increasing server sort buffer size (errno 1038) (sqlstate HY001)

When running a query with sorting in it, such as.

const documentId = 0x12345
await prismaClient.document.findMany({
    where: { id: documentId },
    orderBy: { version: "desc"},
    take: 1
  })

// the Raw SQL
select * from Document
where id = 0x12345
order by DocumentSnapshot.version desc limit

Answer 1

Add a matching Index

Add an index within the schema.prisma file for the database table that is throwing the error, such as:

model Document { 
  id          Bytes    @id @db.VarBinary(16)
  version     Int
  ...
  @@index(id)
  @@index([id, version(sort: Desc)]) // Add this index that matches your Prisma query
}

The index should match your where clause and your order by clause. Doing this should bypass the memory limit and instead rely on the index. For example, if selecting where id is equal to something and ordering by createdDate, then your index should be @@index([id, createdDate])

Increase your sort buffer size

Alternatively you can try increasing the sort_buffer_size in your sql engine via calling

SET sort_buffer_size = {put a number here};

Using Prisma you could do something like:

  import { PrismaClient } from "@prisma/client";

  const client = new PrismaClient();

  // Run the SQL command to set buffer size (must be before calling your query)
  await client.$executeRaw`SET sort_buffer_size = 1000000;`;

  // Optionally check the newly set buffer size
  const bufferSizeCheck = await client.$queryRaw`SELECT @@global.sort_buffer_size;`;
  console.log(`bufferSizeCheck = `, bufferSizeCheck);

  // Run your query
  const someData = await client.documentSnapshot.findMany({
    where: {
      docId,
    },
    orderBy: {
      version: "desc"
    }
  })

At some point you might reach the memory limit again, so this option might not be as solid as the index option.

According to PlanetScale support, this issue is know to happen with tables that have fields of type JSON or TEXT.