Systematic MongoDB performance tuning
A major website backed by a MongoDB database is experiencing unsatisfactory performance. As an experienced MongoDB professional, you are called in to diagnose the problem. When you look at the key Operating systems performance metrics, two things stick out: both CPU and IO on the replica set primary are high. Both load average and disk IO latencies suggest that the MongoDB system needs more CPU and disk capacity.
After a quick calculation, you recommend sharding MongoDB to spread the load across 4 servers. The dollar cost is substantial as is the downtime required to redistribute data across the shard. Nevertheless, something has to be done, so management approves the expense and the downtime. Following the implementation, website performance is acceptable and you modestly take the credit.
A successful outcome? You think so, until….
- Within a few months performance is again a problem — each shard is now running out of capacity.
- Another MongoDB expert is called in and reports that a single indexing change would have fixed the original problem with no dollar cost and no down time. Furthermore, she notes that the sharding has actually harmed performance of certain queries and recommends de-sharding key collections.
- The new index is implemented, following which the database workload is reduced to one tenth of that observed during your original engagement. Management prepares to sell the now-surplus hardware on eBay and marks your consulting record with a “do not re-engage” stamp.
- Your significant other leaves you for a PHP programmer, and you end up shaving your head and becoming a monk.
After months of silent meditation, you realise that while your tuning efforts correctly focused on the activities consuming the most time within the database, they failed to differentiate between causes and effects. Consequently, you mistakenly dealt with an effect — the high CPU and IO rates– while neglecting the cause (a missing index).
The best way to avoid mistakenly focusing on a cause rather than an effect is to tune your database system in a “top down” manner. This is sometimes referred to as “Tuning by layers”, but I like to call it “Systematic database performance tuning”.
MongoDB — and indeed almost all database management systems — consist of multiple layers of code. These are illustrated in the diagram below:
The first layer of code is the application layer. Although you might think the application code is not part of the database, it is still executing database driver code and is definitely an important part of the database performance picture. The application layer includes both the data model (schema) and data access logic.
The next layer of code is the MongoDB database server. This is the code that processes JavaScript requests, maintains indexes and manages the distributed cluster.
The next layer of code is the Storage Engine. This is part of the database but is a mostly seperate code layer. In MongoDB it is usually represented by WiredTiger. The storage engine, amongst other things, is responsible for caching data in memory.
Finally we have the Storage Subsystem. This is not part of the MongoDB code base at all, but controlled by the Operating system and/or storage hardware. On a simple single server system it is represented by the filesystem and the disk device’s firmware.
The load on each of these layers is determined by the layer above. Its usually a mistake to tune a lower layer until you are certain that the layers above are optimal.
We therefore recommend tuning a MongoDB server “top down”. The general sequence for a systematic tuning process are:
- Tune the application layer to reduce server workload
- Implement an effective data model
- Take advantage of bulk operations, client-side caching and other coding best practices
- Optimise aggregation pipelines (including join and graph lookups)
- Index effectively (and use explain wisely)
2. Tune the mongoDB server to distribute workload and reduce contention
- Configure read and write concern
- Optimise cluster configuration (sharding and replica set configuration)
3. Tune Memory to avoid disk IO
- Mainly by tuning the WiredTiger cache
4. Tune IO subsystem to maximise disk throughput and minimise latency.
- Striping, mirroring, use of SSDs
I’ve written quite a few blog posts outlining the major parts of step 1 — it’s the most important step overall, and the one that developers have most control of. Expect to see some posts soon on the subsequent steps in performance tuning.
Our open source MongoDB IDE dbKoda supports the systematic tuning philosophy. In our performance dashboard, we’ve organised the performance counters to reflect the major layers. You can clearly see how the various counters reflect application, server and disk performance:
We also incorporated a lot of features to help you tune your application code — this includes a visual explain plan, index advisors and (in the next release) support for profiling and real time monitoring at the connection level. I also wrote a while back about how to use dbKoda to tune your MongoDB queries.
Understanding how database performance is affected by the various layers of code that comprise a full stack application is a critical prerequisite to achieving optimal performance. When we systematically tune our database “top down” we avoid confusing cause and effect, and avoid potentially expensive, wasteful and sometimes ultimately futile tuning efforts.
dbKoda is a totally free, open source (AGPL) product made by a small group of wanna be coding stallions in Melbourne, Australia. It contains a rich code editing environment for MongoDB, query builders, code optimisers and the cool performance dashboard you can see above. Check it out and let us know what you think at dbKoda.com!
Originally published at medium.com on May 30, 2018.
