I am working on a project, in which I have used mongoDb as database.
I am working on the cron, in which there was high mongoDb input/output operations, i.e. there was around 10k/s i/o operations on mongoDb including get, insert and update queries.
My system specifications are:-
RAM:- 384GB
OS:- ubunutu 22.04
MongoDb version:- 4.4
MongoDb storage engine:- WiredTiger
During my entire cron, there was no change into the schema of the database, there was only get and update queries are running. In which around 95% of the queries are Get query.
My problem is that, mongoDb becomes slow after a certain period of time, to find out the problem, I start analysing slow query logs, and I have found that there was high time of schemaLock into the db queries.
{"t":{"$date":"2023-04-05T12:23:26.702+00:00"},"s":"I", "c":"COMMAND", "id":51803, "ctx":"conn4409","msg":"Slow query","attr":{"type":"command","ns":"canadaloctest1867_bk_db.users","command":{"find":"users","filter":{"_id":1,"status":{"$exists":true,"$ne":9}},"limit":1,"projection":{"status":1},"singleBatch":true,"lsid":{"id":{"$uuid":"c00762d0-98b6-41ab-adac-163039f60c0f"}},"$db":"canadaloctest1867_bk_db"},"planSummary":"IXSCAN { _id: 1 }","keysExamined":1,"docsExamined":1,"cursorExhausted":true,"numYields":1,"nreturned":1,"queryHash":"D7A7C611","planCacheKey":"1FD4C0F8","reslen":147,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":2}},"ReplicationStateTransition":{"acquireCount":{"w":2}},"Global":{"acquireCount":{"r":2}},"Database":{"acquireCount":{"r":2}},"Collection":{"acquireCount":{"r":2}},"Mutex":{"acquireCount":{"r":1}}},"storage":{"data":{"bytesRead":133345,"timeReadingMicros":126},"timeWaitingMicros":{"handleLock":120764,"schemaLock":8429126}},"protocol":"op_msg","durationMillis":8654}}
According to the mongoDb documentation, schemaLock will happen if we are modifying the schema.
I have two questions:-
- Why this schemaLock happens, if there was no change into the schema in the entire transaction?
- What can be the possible solution to prevent this situation?
2
Answers
schemaLock is a lock that MongoDB acquires when it needs to modify the schema of a collection, such as creating or dropping an index. However, schemaLock can also be acquired by some read operations that need to access the collection metadata, such as explain or listIndexes. This can cause contention with other operations that need the same lock.
Some possible solutions to prevent this situation are:
You may be hitting to the WiredTiger checkpointing performance issue which is also raised in WT-5479 Checkpoint schema lock can cause long stalls in command execution on systems with many active data handles
To summarise, MongoDB relies on WiredTiger’s checkpointing mechanism to write in-memory data to disk in a consistent way to provide data durability. Checkpointing is a task that occurs periodically in the background. In the starting phase of the WiredTiger checkpoints, a schema lock is taken to prevent the handle list from being modified. Handle list consists of databases, collections, indexes available at the point of checkpoint. Sometimes starting phase takes longer which in turn causes schema lock to be hold longer. DDL tasks stall until the lock gets released.
There are enhancements in the later versions of the MongoDB which improves the performance of checkpointing task. Though can’t say it’s completely resolved as I can still observe long schema locks on 5.0 under load.
Besides upgrade, if you have large number of data handles (i.e databases, collections, indexes), you can review your schema to reduce the size of handle list. It will help ease the pressure on checkpointing as it needs to iterate over that list which also increases the overall duration of schema lock.