Mongodb – How to reduce time spent acquiring the mongodb schema lock

lockingmongodbmongodb-4.0

We have a mongodb 4.0.10 cluster, backed by WiredTiger, in production, with a 3 node replica set consisting of a master and two slaves. One of the slaves has another service co-located that queries the slave extensively. In addressing some slowness in the co-located service I'm seeing a lot of surprisingly slow queries. This one took 3.3 seconds:

  find: "myColl",
  filter: { myField: "myValue" },
  projection: { name: 1 },
  $db: "myDb",
  $clusterTime: { clusterTime: Timestamp(1568198047, 3), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } },
  lsid: { id: UUID("2ed823aa-e6af-4898-a4c1-c039d28a32ab") },
  $readPreference: { mode: "secondary" } }
  planSummary: IXSCAN { myField: 1 } keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:232
  locks:{ Global: { acquireCount: { r: 1 } },
          Database: { acquireCount: { r: 1 } },
          Collection: { acquireCount: { r: 1 } } }
  storage:{ data: { bytesRead: 355, timeReadingMicros: 4 }, timeWaitingMicros: { schemaLock: 3284692 }

The line that stands out to me here, is the last one, indicating that it spends 99.9% of its time waiting to acquire something called a schema lock.

I checked this particular database and collection and it turns out the collection had 50 items at query time and the database itself was tiny (less than 1k documents in total). Furthermore, there's also an index on myField.

Here's some other data about our particular usage of mongodb that might be relevant:

  • Multi tenancy by having a database per customer
  • Most documents are small
  • Most documents will have a similar size throughout their life cycle (I read some others here had performance issues related to padding and documents getting moved around as they grew in size)
  • Customer data grows through document count, not document size

I've been monitoring these slows queries for a while now and I can't see any pattern. It's like mongodb is doing some maintenance task every so often and whatever query runs at that time is forced to wait.

Why is a read query waiting to acquire a schema lock? What can I do to eliminate this long wait?

Best Answer

This is a general architectural problem with the WiredTiger storage engine. It is now being discussed here: https://jira.mongodb.org/browse/WT-5479

Long story short, your number of open files is too high. If you can, consider removing unnecessary indexes, e.g. indexes on small collections. You could also explore the new wildcard indexes introduced in MongoDB 4.2

Related Question