Slow query with many-to-one relationship on MongoDB #20600
Description
Problem
Server environment:
OS : Ubuntu 22
Hardware : vCpu 4core, 8GB RAM
Prisma version : ^5.0.0
I have 8million documents on VideoChatting collection.
Following code always call COLLSCAN, so the cpu usage reached 100%. and the solution code has provided
export async function GET(req: Request, { params }: { params: { videoId: string }}) {
try {
const url = new URL(req.url);
const videoId = params.videoId;
const take = url.searchParams.get("take");
const page = url.searchParams.get("page");
// Use prisma aggregation to get replies
const chats = await prisma.videoChatting.findMany({
where: {
videoId: videoId as string,
},
take: Number((take) || 10),
skip: Number((take) || 10) * Math.max((Number(page) - 1), 0) ,
include:{
replies: {
select: {
id: true,
message: true,
author: true,
thumbnail: true,
timestampSeconds: true,
timestampText: true,
}
},
},
orderBy: {
timestampSeconds: "asc",
},
});
if (chats.length == 0) {
return new Response(JSON.stringify({
data: [],
metaData: {
lastCursor: null,
hasNextPage: false,
},
}), { status: 200 });
}
const lastChat = chats[chats.length - 1];
const cursor = lastChat.id;
const data = {
data: chats,
metaData: {
lastCursor: cursor,
hasNextPage: true,
},
};
return new Response(JSON.stringify(data), { status: 200 });
} catch (e: any) {
console.error(e);
return new Response(JSON.stringify({
error: e.message,
}), { status: 500 });
}
}[Logs]
{"t":{"$date":"2023-08-08T11:11:32.263+00:00"},"s":"W", "c":"COMMAND", "id":20525, "ctx":"conn166","msg":"Failed to gather storage statistics for slow operation","attr":{"opId":180698,"error":"lock acquire timeout"}}
{"t":{"$date":"2023-08-08T11:11:32.264+00:00"},"s":"I", "c":"COMMAND", "id":51803, "ctx":"conn166","msg":"Slow query","attr":{"type":"command","ns":"nadoochat.VideoChatting","command":{"aggregate":"VideoChatting","pipeline":[{"$match":{"$expr":{"$and":[{"$in":["$replyChatId",[{"$literal":{"$oid":"64bd4a9527561f44ee95d8af"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b0"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b1"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b2"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b3"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b4"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b5"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b6"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b7"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b8"}}]]},{"$ne":["$replyChatId","$$REMOVE"]}]}}},{"$project":{"_id":1,"videoId":1,"author":1,"thumbnail":1,"message":1,"timestampText":1,"timestampUsec":1,"timestampSeconds":1,"isChatonSite":1,"replyChatId":1}}],"cursor":{},"allowDiskUse":true,"$db":"nadoochat","lsid":{"id":{"$uuid":"171f49a2-c7aa-4a59-885a-19769d571c11"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1691493082,"i":1}},"signature":{"hash":{"$binary":{"base64":"1Up5iL/Jp+2CyeD4H8ScZtSXtGk=","subType":"0"}},"keyId":7259368416782319622}},"$readPreference":{"mode":"primaryPreferred"},"readConcern":{"afterClusterTime":{"$timestamp":{"t":1691493082,"i":1}}}},"planSummary":"COLLSCAN","numYields":4411,"queryHash":"74EF3DA0","planCacheKey":"74EF3DA0","queryFramework":"classic","ok":0,"errMsg":"PlanExecutor error during aggregation :: caused by :: operation was interrupted because a client disconnected","errName":"ClientDisconnect","errCode":279,"reslen":326,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":4413}},"ReplicationStateTransition":{"acquireCount":{"w":1}},"Global":{"acquireCount":{"r":4413}},"Mutex":{"acquireCount":{"r":1}}},"readConcern":{"afterClusterTime":{"$timestamp":{"t":1691493082,"i":1}},"provenance":"clientSupplied"},"writeConcern":{"w":"majority","wtimeout":0,"provenance":"implicitDefault"},"remote":"64.176.52.99:58286","protocol":"op_msg","durationMillis":3006}}
Suggested solution
Use aggregateRaw. but it maybe critical on your DX
import prisma from "@/lib/prisma";
type ObjectId = {
$oid: string;
};
type ChatData = {
_id: { $oid: string };
author: string;
message: string;
timestampText: string;
timestampSeconds: number;
thumbnail: string;
replies: any[];
id: { $oid: string };
};
type TransformedChatData = Omit<ChatData, '_id' | 'id'> & { id: string };
function transformChatData(inputData: ChatData): TransformedChatData {
console.log(inputData);
return {
...inputData,
id: inputData._id.$oid,
replies: inputData.replies // Recursive transformation for replies
};
}
type ChatArray = ChatData[];
export async function GET(req: Request, { params }: { params: { videoId: string }}) {
try {
const url = new URL(req.url);
const videoId = params.videoId;
const take = parseInt(url.searchParams.get("take") || "10");
const page = parseInt(url.searchParams.get("page") || "1");
const skip = take * (page - 1);
// Setup for MongoDB aggregation pipeline
const aggregatePipeline = [
{ $match: { videoId } },
{ $sort: { timestampSeconds: 1 } },
{
$lookup: {
from: "VideoChatting",
localField: "_id",
foreignField: "replyChatId",
as: "replies"
}
},
{
$project: {
id: "$_id",
message: 1,
author: 1,
thumbnail: 1,
replyChatId: 1,
replies: {
$map: {
input: "$replies",
as: "reply",
in: {
_id: "$$reply._id",
id: "$$reply._id",
message: "$$reply.message",
author: "$$reply.author",
thumbnail: "$$reply.thumbnail",
timestampSeconds: "$$reply.timestampSeconds",
timestampText: "$$reply.timestampText"
}
}
},
timestampSeconds: 1,
timestampText: 1
}
},
{ $skip: skip },
{ $limit: take }
];
// Actual raw aggregation query
const chats = await prisma.videoChatting.aggregateRaw({
pipeline: aggregatePipeline,
}); // Use appropriate Prisma raw query function
if (chats.length === 0) {
return new Response(JSON.stringify({
data: [],
metaData: {
lastCursor: null,
hasNextPage: false,
},
}), { status: 200 });
}
const chatArrayData = chats as unknown as ChatArray;
const finalChatArrayData = chatArrayData.map((chat:ChatData) => {
return transformChatData(chat);
});
const lastChat = finalChatArrayData[finalChatArrayData.length - 1];
const cursor = lastChat?.id;
const data = {
data: finalChatArrayData,
metaData: {
lastCursor: cursor,
hasNextPage: true,
},
};
return new Response(JSON.stringify(data), { status: 200 });
} catch (e: any) {
console.error(e);
return new Response(JSON.stringify({
error: e.message,
}), { status: 500 });
}
}