S
S
Stanislav2022-01-20 10:29:33
MongoDB
Stanislav, 2022-01-20 10:29:33

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" : ""
}

There are indexes in the coll collection,
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" })
//........

I do not understand the following, is the problem in the score field that is created dynamically or in something else?
There may be somewhere else to pull out information about what exactly mongodb needs for proper operation.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question