Answer the question
In order to leave comments, you need to log in
MongoDB how to overcome slow queries with sorting?
Hello.
I have a very heavy load on the server from MongoDB, I'm looking for roots, and now I'm only thinking about indexes. Monga eats not RAM, but a processor, an 8-core processor is loaded a little less than completely.
To begin with, I turned on the database profiler for slow queries exceeding 400ms, I see the following picture.
{
"op" : "command",
"ns" : "site.coll",
"command" : {
"aggregate" : "coll",
"pipeline" : [
{
"$match" : {
"$text" : {
"$search" : "поисковой запрос какой то текст"
},
"type" : "card",
"$and" : [
{
"catalog" : {
"$nin" : [
ObjectId("54369100d8f80e0f969850da")
]
}
},
{
"catalog" : ObjectId("54367806d8f80e0f969850c8")
}
],
"public" : {
"$gte" : 4
}
}
},
{
"$project" : {
"_id" : 1,
"score" : {
"$meta" : "textScore"
}
}
},
{
"$sort" : {
"score" : {
"$meta" : "textScore"
}
}
},
{
"$skip" : 12
},
{
"$limit" : 12
},
{
"$group" : {
"_id" : null,
"uid" : {
"$push" : "$_id"
}
}
}
],
"allowDiskUse" : true,
"cursor" : {
},
"lsid" : {
"id" : UUID("e68a1b13-f355-40b5-bf64-5aebc9b8d4f7")
},
"$db" : "site"
},
"keysExamined" : 415972,
"docsExamined" : 324304,
"hasSortStage" : true,
"cursorExhausted" : true,
"numYield" : 4516,
"nreturned" : 1,
"queryHash" : "66974B51",
"planCacheKey" : "DB2DAB9C",
"locks" : {
"ReplicationStateTransition" : {
"acquireCount" : {
"w" : NumberLong(4520)
}
},
"Global" : {
"acquireCount" : {
"r" : NumberLong(4520)
}
},
"Database" : {
"acquireCount" : {
"r" : NumberLong(4519)
}
},
"Collection" : {
"acquireCount" : {
"r" : NumberLong(4519)
}
},
"Mutex" : {
"acquireCount" : {
"r" : NumberLong(3)
}
}
},
"flowControl" : {
},
"storage" : {
"data" : {
"bytesRead" : NumberLong(711558),
"timeReadingMicros" : NumberLong(16191)
}
},
"responseLength" : 309,
"protocol" : "op_msg",
"millis" : 1566,
"planSummary" : "IXSCAN { _fts: \"text\", _ftsx: 1 }, IXSCAN { _fts: \"text\", _ftsx: 1 }, IXSCAN { _fts: \"text\", _ftsx: 1 }",
"ts" : ISODate("2022-01-20T07:09:56.199Z"),
"client" : "127.0.0.1",
"allUsers" : [ ],
"user" : ""
}
schema.index({ indexAt: 1 })
schema.index({ type: 1, catalog: 1, public: 1 })
schema.index({ type: 1, catalog: 1, public: 1, indexAt: -1 })
schema.index({ subscribe: 'text', title: 'text' }, { default_language: "russian" })
//........
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question