Hello, Community!
We have mongodb 4.2.14 serving RocketChat installation and we have a few queries that consume a lot of CPU on database server.
mongodb in a PSA mode with replica set enabled
Here is an example of that query:
command rocketchat.rocketchat_message command: count { count: "rocketchat_message", query: { _hidden: { $ne: true }, $text: { $search: "в" }, rid: "B4SsyfkyfJ289BLNxFhTApxxADGhnfF9pA", tcount: { $exists: true } }, $clusterTime: { clusterTime: Timestamp(1628746565, 31), signature: { hash: BinData(0, 3498B30E7A372385E88D915AF25FCC096E58612D), keyId: 6947176989316874242 } }, $db: "rocketchat" } planSummary: IXSCAN { _fts: "text", _ftsx: 1 }, IXSCAN { _fts: "text", _ftsx: 1 } keysExamined:2989489 docsExamined:5970248 numYields:23356 queryHash:37B2DC84 planCacheKey:C482CCF4 reslen:170 locks:{ ReplicationStateTransition: { acquireCount: { w: 23358 } }, Global: { acquireCount: { r: 23358 } }, Database: { acquireCount: { r: 23358 } }, Collection: { acquireCount: { r: 23358 } }, Mutex: { acquireCount: { r: 2 } } } storage:{ data: { bytesRead: 112669, timeReadingMicros: 172 } } protocol:op_msg 19018ms
And here is how that query load database server
My guess that keysExamined:2989489 docsExamined:5970248 is too big amount of keys to handle, so that query could be much improved by creating new index, but I don’t have proper skills in that.
Here is all created indexes for now (25 indexes)
rs0:PRIMARY> db.rocketchat_message.getIndexes()
[
{
"v" : 2,
"key" : {
"_updatedAt" : 1
},
"name" : "_updatedAt_1",
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"ts" : 1
},
"name" : "ts_1",
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"u._id" : 1
},
"name" : "u._id_1",
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"editedAt" : 1
},
"name" : "editedAt_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"editedBy._id" : 1
},
"name" : "editedBy._id_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"rid" : 1,
"t" : 1,
"u._id" : 1
},
"name" : "rid_1_t_1_u._id_1",
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"expireAt" : 1
},
"name" : "expireAt_1",
"expireAfterSeconds" : 0,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"_fts" : "text",
"_ftsx" : 1
},
"name" : "msg_text",
"weights" : {
"msg" : 1
},
"default_language" : "english",
"language_override" : "language",
"ns" : "rocketchat.rocketchat_message",
"textIndexVersion" : 3
},
{
"v" : 2,
"key" : {
"file._id" : 1
},
"name" : "file._id_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"mentions.username" : 1
},
"name" : "mentions.username_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"pinned" : 1
},
"name" : "pinned_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"snippeted" : 1
},
"name" : "snippeted_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"location" : "2dsphere"
},
"name" : "location_2dsphere",
"2dsphereIndexVersion" : 3,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"unread" : 1
},
"name" : "unread_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"drid" : 1
},
"name" : "drid_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"tmid" : 1
},
"name" : "tmid_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"tcount" : 1,
"tlm" : 1
},
"name" : "tcount_1_tlm_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"navigation.token" : 1
},
"name" : "navigation.token_1",
"sparse" : true,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"pinnedBy._id" : 1
},
"name" : "pinnedBy._id_1",
"sparse" : 1,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"starred._id" : 1
},
"name" : "starred._id_1",
"sparse" : 1,
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"rid" : 1,
"ts" : 1,
"_updatedAt" : 1
},
"name" : "rid_1_ts_1__updatedAt_1",
"ns" : "rocketchat.rocketchat_message"
},
{
"v" : 2,
"key" : {
"slackTs" : 1,
"slackBotId" : 1
},
"name" : "slackTs_1_slackBotId_1",
"ns" : "rocketchat.rocketchat_message",
"sparse" : true
},
{
"v" : 2,
"key" : {
"rid" : 1,
"tlm" : -1
},
"name" : "rid_1_tlm_-1",
"ns" : "rocketchat.rocketchat_message",
"partialFilterExpression" : {
"tcount" : {
"$exists" : true
}
}
},
{
"v" : 2,
"key" : {
"rid" : 1,
"tcount" : 1
},
"name" : "rid_1_tcount_1",
"ns" : "rocketchat.rocketchat_message"
}
]
So, please help me to improve that query (by adding proper index I guess)
Thank you!