<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Kay Agahd on Medium]]></title>
        <description><![CDATA[Stories by Kay Agahd on Medium]]></description>
        <link>https://medium.com/@kay.agahd?source=rss-a31335b1254e------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*03BnfobXtCXVA-QpcUR9tQ.jpeg</url>
            <title>Stories by Kay Agahd on Medium</title>
            <link>https://medium.com/@kay.agahd?source=rss-a31335b1254e------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 05:09:46 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@kay.agahd/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Practical Guide To Analyze Slow MongoDB Operations]]></title>
            <link>https://medium.com/idealo-tech-blog/practical-guide-to-analyze-slow-mongodb-operations-9363035b01fb?source=rss-a31335b1254e------2</link>
            <guid isPermaLink="false">https://medium.com/p/9363035b01fb</guid>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[query]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[performance]]></category>
            <dc:creator><![CDATA[Kay Agahd]]></dc:creator>
            <pubDate>Tue, 09 Mar 2021 08:29:14 GMT</pubDate>
            <atom:updated>2021-03-09T08:29:14.903Z</atom:updated>
            <content:encoded><![CDATA[<h3>Practical Guide To Analyzing Slow MongoDB Queries</h3><h4>How to quickly identify and mitigate performance bottlenecks of MongoDB operations</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*da33l07hnZbEtC7eiGo0cA.jpeg" /></figure><p>In this blog post, I want to share how we use the open-sourced <a href="https://github.com/idealo/mongodb-slow-operations-profiler">mongodb-slow-operations-profiler</a> to quickly identify the reasons for performance issues and how to mitigate or even avoid them.</p><h3>Preparation</h3><p>After you have installed, configured, and started the <a href="https://github.com/idealo/mongodb-slow-operations-profiler">mongodb-slow-operations-profiler</a>, open its application status page in a browser: http://&lt;YOUR_WEBSERVER&gt;:&lt;YOUR_PORT&gt;/mongodb-slow-operations-profiler/app?adminToken=&lt;YOUR_TOKEN&gt;</p><p>In the search box above the table, enter a part or the whole name of the database that you want to analyze, e.g. backend.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6Sd34ChzOp6VcfxCJbsmJA.png" /><figcaption>the table on the application status page</figcaption></figure><p>Each line in the table corresponds to a database running on a mongoD instance. In this example, searching backend shows all mongoD’s where the database offerListServiceBackend is running, which I want to analyze. <br>Tick the checkbox in the column header to select all lines.</p><p>Before you can analyze slow queries, enable profiling by setting a threshold, e.g. 100 milliseconds. This means that all operations taking longer than 100 ms will be written into a capped collection of the database being profiled.<br>Keep in mind that a low threshold may add stress to the whole database system because the threshold is valid for the <strong>whole mongoD</strong> instance (see <a href="https://docs.mongodb.com/manual/tutorial/manage-the-database-profiler/#database-profiling-overhead">MongoDB’s documentation</a> for more details). This means also that one can’t set different thresholds for different databases running on the same mongoD instance at the same time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/190/1*DHy7tVrDEXjPchSSSsMGNA.png" /><figcaption>start profiling with a threshold of 100 ms</figcaption></figure><p>To collect the profiled operations from the capped collection to the collector database for later analysis, start collecting:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/110/1*XuGto7QsfutY1UjnLlgvZA.png" /><figcaption>start collecting</figcaption></figure><p>Click the link refresh to update the metrics in the table. The column #SlowOps shows how many slow operations have been collected so far from each database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/193/1*xxuh5xvmiUPwMsRWfk0NwA.png" /></figure><p>After having collected a bunch of slow operations, click the link analyse to open the analysis page.</p><h3>Analysis</h3><p>The analysis page opens with default parameters, showing all collected slow operations that occurred within the last hour on the selected databases with a resolution of one minute. You can change these parameters in the form.</p><p>Scroll down to the table and click the link Clean just above the table on the right-hand side in order to show only the most relevant columns. You can show/hide more of these columns by clicking on these links.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uX_VYdFXPtab4Jdmtte9xA.png" /><figcaption>the table on the analysis page</figcaption></figure><p>In the column Group, you see what makes up the slow operations that have been grouped together. In the screenshot above, for example, only the queried fields differ (see fields) between the two shown slow operation types.<br>Just below fields, click the example link to see an example of a concrete slow-operations document on a new page:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/403/1*f6-MwjyTgJPXhdwY2UeBuQ.png" /><figcaption>the filter expression of an example slow-operations document</figcaption></figure><p>Such concrete examples help to interpret the syntax of fields because you can exactly see, how one of the queries of the clicked slow-operations type looked like.</p><p>Close the example page to return to the analysis page. <br>Now, let’s dive into the analysis by sorting some columns of the table!</p><h4>SumMs = most expensive operations</h4><p>Sort descending by column SumMs (click twice on the column header) to see the most expensive operations first. These operations may be worth being optimized first because they needed altogether the most operation time, either because they were slow or were executed very often, or both.<br>Continue reading to learn how to optimize them.</p><h4>MaxMs = slowest operations</h4><p>Sort descending by column MaxMs to see the slowest operations first. Since they are slow, they may use a lot of resources which may slow down the database even if they are not executed very often. <br>A new or better-suited index may speed them up significantly.</p><h4>SumBytes = heaviest data streamers</h4><p>Sort descending by column SumBytes to see the operations first that transferred the most data. Those operations can’t be really quick because they need to fetch and return a lot of data. The question is, if they are really needed or if such a use case can be better solved by using a streaming platform such as <a href="https://kafka.apache.org/">Apache Kafka</a> instead of a database.</p><h4>SumRet / rKeys / rDocs = query performance</h4><p>Sort ascending by column rKeys to see the operations first that did not use any index which is the case where rKeys is 0 because no index key was read from the index. <br>rDocs is the number of documents read from the disk. If it’s greater than 0 and no index was used (rKeys is 0), it indicates a collection scan which is bad. <br>If rKeys for query and getmore operations is much higher than the number of returned documents (column Sum ret), the database is scanning many index keys to find the result documents. Consider creating or adjusting indexes to improve <strong>index efficiency</strong>.</p><p>Generally spoken for query and getmore operations, the larger rKeys divided by rDocs <strong>and</strong> rKeys equal or similar to Sum ret, the better the query performance.</p><p>The <strong>optimum</strong> is when rDocs is 0 and rKeys is greater than 0 because then the index <strong>covered</strong> the query, which means that all data requested by the client was found in the index without needing to fetch any document from disk. <br><strong>However</strong>, keep your indexes small enough so that they always fit in RAM. You shouldn’t cover your query by an index if the index would be too big to fit in RAM. Also, fields that change very frequently are not a good choice to put into indexes.</p><h4>wDocs = data restructuring effort</h4><p>Sort descending by column wDocs to see the operations first that wrote a lot of documents. Writing documents implies also updating data structures such as indexes. When the BTree index structure becomes unbalanced, it requires rebuilding this part of the index which may be resource-intensive.<br>The question is whether you are able to reduce the quantity of such operations or better use a database that is better suited for heavy writes than for reads. MongoDB is more optimized for read-intensive rather than write-intensive use cases.</p><h4>memSort = sorting costs</h4><p>Sort descending by column memSort to see the operations first that could not use an index to sort the result which is the case where memSort is true. If you spotted such operations you may adapt the index so that the result is already in the right order instead of doing an in-memory sort.<br><a href="https://blog.mlab.com/2012/06/cardinal-ins/">Here</a> is an old, but still valid, and very good article that covers some edge-cases for building such indexes.</p><h3>More insights</h3><p>Go back to the application status page, tick the checkboxes of the lines of the database(s) you are interested in, and execute the following commands against their mongoD instances.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/249/1*AOPCF_WUivhCwKrEQnDRWg.png" /></figure><p>I describe the commands in the order of their importance in regards to spot performance issues, not in the order of the above screenshot.</p><h4>host info</h4><p>Click on the link host info. You see on a new page a table with some info about the hosts of the selected database(s). <br>If the same database is located on multiple mongoD’s (e.g. replicaSet and/or sharded cluster), check whether the values in all columns are identical among all mongoD’s, because you want to have identical hardware, identical software versions, and identical configurations among all database servers. If not, you may have just found the reason for sporadic performance issues.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*naUJ9V3jX_BqD-ZES6oS7Q.png" /><figcaption>host info of a replica set having a different number of CPU cores</figcaption></figure><h4>index access stats</h4><p>Click on the link index access stats. You see on a new page a table with the index access statistics of all collections of the selected database(s).<br>Sort in ascending order by column #access in order to see indexes first that have been accessed less often.</p><p>If it’s 0, the index has never been accessed since server restart or index creation (see column since). If it’s furthermore an _id index, which is the primary key, the collection has never been used and may be dropped.</p><p>If the number of accesses is quite low, maybe the index is not used anymore.<br>In such case, the index wastes resources and is a candidate to be dropped as long as it’s <strong>not a TTL index</strong> (see column TTL) because when time-to-live (TTL) indexes are accessed by the internal remove job, the access counter is <strong>not</strong> incremented.</p><h4>db stats</h4><p>Click on the link db stats. You see on a new page a table with some statistics about the selected database(s). Evaluate the column indexSize to know if the indexes fit still in RAM.<br>Also, if the same database is located on multiple mongoD’s (e.g. replicaSet and/or sharded cluster), check whether the numbers in all columns are similar among all mongoD’s. If not, it indicates that the database is not well balanced among the mongoD’s. The reason may be different configurations, unsuited shard keys, or unsharded collections. Unsharded collections are located on only one shard, hence putting load on only this one shard instead to balance the load to all shards.</p><h4>collection stats</h4><p>Click on the link collection stats. You see on a new page a table with some statistics about all collections of the selected databases.<br>Here you can sort by columns such as size, count, storageSize, #indexes, totalIndexSize to get an idea which of the collections is the most “expensive” in terms of resource utilization. <br>You may use the search box to show only collections you’re interested in.</p><h4>current ops</h4><p>If you have not yet enabled profiling and collecting and encounter a sporadic performance issue, e.g. requests are slow or even stuck, you can inspect all currently running operations of the selected database system, respectively the selected databases, by clicking on the command current ops.<br>On the newly opened web page, sort either by column microsecs running or secs running in descending order to see the longest-running operations first.<br>You may use the search box to show only what you’re interested in, in this example the collection productOffer.<br>The opid might be used to kill an operation by the command db.killOp(opid) in case it got stuck or slows down everything else (see MongoDB’s <a href="https://docs.mongodb.com/manual/reference/method/db.killOp/index.html">documentation</a>). <br>The other columns of the table should be self-explanatory.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Zb3ZPYrqIyQhndsI-36hnQ.png" /><figcaption>excerpt of the current operations filtered by productOffer</figcaption></figure><p>And that’s it! I hope you got some interesting insights! <br>If you found this article useful, give me some high fives 👏🏻 and share it with your friends so others can find it too. Follow me here on Medium (<a href="https://medium.com/u/a31335b1254e">Kay Agahd</a>) to stay up-to-date with my work. Thanks for reading!<br>Btw. idealo is hiring: Check out our vacancies <a href="https://jobs.idealo.com/l/en">here</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9363035b01fb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/idealo-tech-blog/practical-guide-to-analyze-slow-mongodb-operations-9363035b01fb">Practical Guide To Analyze Slow MongoDB Operations</a> was originally published in <a href="https://medium.com/idealo-tech-blog">idealo Tech Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What Slowed Down the Queries? …MongoDB Chose the Wrong Index!]]></title>
            <link>https://medium.com/idealo-tech-blog/what-slowed-down-the-queries-mongodb-chose-the-wrong-index-b673a41af2cd?source=rss-a31335b1254e------2</link>
            <guid isPermaLink="false">https://medium.com/p/b673a41af2cd</guid>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[indexing]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[query]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Kay Agahd]]></dc:creator>
            <pubDate>Fri, 02 Oct 2020 08:22:03 GMT</pubDate>
            <atom:updated>2020-11-06T14:52:30.912Z</atom:updated>
            <content:encoded><![CDATA[<h3>What Slowed Down the Queries? … <strong>MongoDB Chose the Wrong Index!</strong></h3><h4>Why queries might become slower even though more hardware had been added to speed them up</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*njNzxF3KyuRFSJS2mkdbTw.png" /></figure><p><strong>TL;DR</strong> We suddenly experienced a much longer query execution time even though we had added a new shard to our cluster to gain more performance. Here’s how we found the reason and solved it.</p><h3>The Problem</h3><p>One of our MongoDB queries took almost twice the time than usual. The execution plan revealed that 1 of the 5 shards picked another index. Was this the reason that the query response time went up? Why did the 5th shard choose another index than shards 1 to 4?</p><p>The primary key _id is also our <a href="https://docs.mongodb.com/manual/core/sharding-shard-key/index.html">shard key</a>. Its content is a kind of md5 hash. However, our query did not use the shard key. So could it be that the documents on shard 5 were better suited to use another index than the other shards? Let’s find out!</p><h3>Show Me Your Query and Indexes!</h3><p>Our query is as follows:</p><p>db.offer.find({shopId:9701, offerStatus:1})</p><p>Only 2 fields are queried without any <a href="https://docs.mongodb.com/manual/reference/method/db.collection.find/index.html#projection">projection</a>, <a href="https://docs.mongodb.com/manual/reference/method/db.collection.find/index.html#order-documents-in-the-result-set">sort</a>, or <a href="https://docs.mongodb.com/manual/reference/method/cursor.hint/#cursor.hint">hint</a>. The query planner evaluated these 3 indexes to find the best-suited one:</p><pre>(A) {shopId:1, missingSince:1, merchantId:1}</pre><pre>(B) {shopId:1, offerStatus:1, merchantId:1, _id:1}</pre><pre>(C) {offerStatus:1, missingSince:1, offerId:1, _id:1}</pre><p>Shard 1 to 4 was using index A while shard 5 decided to take index B.</p><h3>Recap: Query Plans</h3><p>As written in MongoDB’s v4.0 (we are using v4.0) <a href="https://docs.mongodb.com/v4.0/core/query-plans/index.html">documentation</a>:</p><blockquote>For each query, the query planner searches the query plan cache for an entry that fits the <a href="https://docs.mongodb.com/v4.0/reference/glossary/#term-query-shape">query shape</a>. If there are no matching entries, the query planner generates candidate plans for evaluation over a trial period. The query planner chooses a winning plan, creates a cache entry containing the winning plan, and uses it to generate the result documents.</blockquote><p>But what are the selection criteria to determine <strong>the winning plan?</strong> I found this in MongoDB’s v4.4 (current version as of Sept. 2020) <a href="https://docs.mongodb.com/manual/core/query-plans/">documentation</a>:</p><blockquote>The evaluation of the most efficient query plan is based on the number of “work units” (<a href="https://docs.mongodb.com/manual/reference/explain-results/#explain.executionStats.executionStages.works"><em>works</em></a>) performed by the query execution plan when the query planner evaluates candidate plans.</blockquote><p>This is only half of the truth because besides work units, other criteria such as <a href="https://github.com/mongodb/mongo/blob/e54d83a72d47c17241196618d70db35c05e8243a/src/mongo/db/query/plan_ranker.cpp#L108">productivity</a> and <a href="https://github.com/mongodb/mongo/blob/e54d83a72d47c17241196618d70db35c05e8243a/src/mongo/db/query/plan_ranker.h#L121">bonus points</a> e.g. noFetchBonus, noSortBonus, and noIxisectBonus, are taken into account to calculate the query plan score. The bonus points are used to solve tie breaks in case query plans have the same number of work units and the same productivity as I found in MongoDB’s <a href="https://github.com/mongodb/mongo/blob/e54d83a72d47c17241196618d70db35c05e8243a/src/mongo/db/query/plan_ranker.h#L83">source code</a>. This knowledge becomes relevant later in this article.</p><h3>Execution Statistics</h3><p>We compared the execution statistics of the candidate plans for both indexes A and B on shard 3. I removed some insignificant lines to make it better fit here. These are the stats for candidate A (using index A):</p><pre>&quot;nReturned&quot; : 101,<br>&quot;executionTimeMillisEstimate&quot; : 23,<br>&quot;totalKeysExamined&quot; : 112,<br>&quot;totalDocsExamined&quot; : 112,<br>&quot;executionStages&quot; : {<br>    &quot;stage&quot; : &quot;SHARDING_FILTER&quot;,<br>    &quot;nReturned&quot; : 101,<br>    &quot;executionTimeMillisEstimate&quot; : 23,<br>    &quot;works&quot; : 112,<br>    &quot;advanced&quot; : 101,<br>    &quot;needTime&quot; : 11,<br>...<br>    &quot;inputStage&quot; : {<br>        &quot;stage&quot; : &quot;FETCH&quot;,<br>        &quot;filter&quot; : {<br>            &quot;offerStatus&quot; : {<br>                &quot;$eq&quot; : 1<br>            }<br>        },<br>        &quot;nReturned&quot; : 112,<br>        &quot;executionTimeMillisEstimate&quot; : 23,<br>        &quot;works&quot; : 112,<br>        &quot;advanced&quot; : 112,<br>        &quot;needTime&quot; : 0,<br>...<br>        &quot;docsExamined&quot; : 112,<br>        &quot;inputStage&quot; : {<br>            &quot;stage&quot; : &quot;IXSCAN&quot;,<br>            &quot;nReturned&quot; : 112,<br>            &quot;executionTimeMillisEstimate&quot; : 0,<br>            &quot;works&quot; : 112,<br>            &quot;advanced&quot; : 112,<br>            &quot;needTime&quot; : 0,<br>...<br>            &quot;indexName&quot; : &quot;shopId_1_missingSince_1_merchantId_1&quot;,</pre><p>Versus candidate B (using index B):</p><pre>&quot;nReturned&quot; : 42,<br>&quot;executionTimeMillisEstimate&quot; : 6,<br>&quot;totalKeysExamined&quot; : 112,<br>&quot;totalDocsExamined&quot; : 42,<br>&quot;executionStages&quot; : {<br>    &quot;stage&quot; : &quot;FETCH&quot;,<br>    &quot;nReturned&quot; : 42,<br>    &quot;executionTimeMillisEstimate&quot; : 6,<br>    &quot;works&quot; : 112,<br>    &quot;advanced&quot; : 42,<br>    &quot;needTime&quot; : 70,<br>...<br>    &quot;inputStage&quot; : {<br>        &quot;stage&quot; : &quot;SHARDING_FILTER&quot;,<br>        &quot;nReturned&quot; : 42,<br>        &quot;executionTimeMillisEstimate&quot; : 3,<br>        &quot;works&quot; : 112,<br>        &quot;advanced&quot; : 42,<br>        &quot;needTime&quot; : 70,<br>...<br>        &quot;inputStage&quot; : {<br>            &quot;stage&quot; : &quot;IXSCAN&quot;,<br>            &quot;nReturned&quot; : 112,<br>            &quot;executionTimeMillisEstimate&quot; : 1,<br>            &quot;works&quot; : 112,<br>            &quot;advanced&quot; : 112,<br>...<br>            &quot;indexName&quot; : &quot;shopId_1_offerStatus_1_merchantId_1__id_1&quot;,</pre><p>On the first sight, that looks odd because even though plan B was nearly 3 times <strong>faster</strong> than plan A (6 ms versus 23 ms), plan A has been chosen. However, execution time is <strong>not</strong> relevant for MongoDB to choose its index. As I wrote earlier, the number ofwork units lets the query planner decide which index is more suitable. However, in this case, we have a draw since the number of work units is identical (112) for both plans A and B.</p><h3>Which Plan To Choose?</h3><p>Unfortunately, it’s not documented which query plan wins, if the number of work units is identical. However, MongoDB’s <a href="https://github.com/mongodb/mongo/blob/e54d83a72d47c17241196618d70db35c05e8243a/src/mongo/db/query/plan_ranker.cpp#L108">source code</a> reveals that productivity, which is the number of <a href="https://docs.mongodb.com/manual/reference/explain-results/#explain.executionStats.executionStages.advanced">advanced</a> divided by the number of <a href="https://docs.mongodb.com/manual/reference/explain-results/#explain.executionStats.executionStages.works">works</a>, is also taken into account to calculate the query plan score. Since works is identical for both plans, the higher number of advanced, here plan A, gets a higher score and wins.</p><h3>Why “advanced” Is Different in Both Plans?</h3><p>As you can see, the number of advanced is only decremented in stage SHARDING_FILTER. It decreased from 112 to 101 in plan A and from 112 to 42 in plan B. As MongoDB’s <a href="https://docs.mongodb.com/manual/reference/explain-results/index.html#explain-results">documentation</a> states out, SHARDING_FILTER is for filtering out orphan documents from shards. <br>Orphaned documents are documents that live on a shard they don’t belong to. This can happen due to unclean shutdowns of mongod or if the <a href="https://docs.mongodb.com/manual/tutorial/manage-sharded-cluster-balancer/index.html">balancer</a> was not able to delete chunks from the source shard that had already been copied from the source to the destination shard.<br>So shard 3 had orphaned documents that had to be removed from the query result by the SHARDING_FILTER stage. Obviously, the first 101 returned documents matching the shopId in index A <strong>contained fewer orphans</strong> than the first 101 documents matching both fields shopId <strong>and</strong> offerStatus in index B.</p><h3>Why I Think MongoDB Is Wrong</h3><p>Not only index B filters out documents that match both fields shopId <strong>and</strong> offerStatus in one go, but index B is also used in the next stage to remove orphans because the shard key _id is part of the compound index. <br>The fetch stage is needed in plan B only to return the matched documents because no projection was used.</p><p>However, plan A can use its index only to match the shopId field. The next stage has to fetch already all matched documents from the disk to remove those that don’t match offerStatus. <br>Orphans are removed in plan A only at the last stage before the already fetched documents are returned.</p><p>Knowing that fetching documents (most probably from disk) is much more resource-intensive and as such is much slower than to read the information from the index (most probably from RAM), it’s surprising that MongoDB does not honor plan B more. <br>Instead, Plan B was penalized only for removing more orphans than Plan A, even though plan B could use the index for that, instead to fetch the documents from the disk, like plan B.</p><p>In other words, MongoDB’s query planner honors an index that can minimize skipping index entries to match documents. Each skip <strong>decreases</strong> the advanced counter and as such decreases the score of the query plan that uses this index.</p><p>This is kind of “selectivity of an index” that Charlie Swanson, Software Engineer at MongoDB, defined in his presentation “Reading the .explain() Output”, see <a href="https://www.mongodb.com/presentations/reading-the-explain-output">video recording</a> at 24:55, as follows:</p><blockquote>“The ratio of number of advanced or returned to number of examined”</blockquote><p>The best query plan for Charlie Swanson is (see <a href="https://www.mongodb.com/presentations/reading-the-explain-output">video recording</a> at 26:50) :</p><blockquote>“It’ll take the one with the highest number of advances as the most efficient plan”</blockquote><p>For most use cases this approach works fine but in cases where orphaned documents have to be removed, even by the aid of an index, it may fail to choose the best-suited index.</p><h3>Why Shard 5 Got It Right?</h3><p>Yes, you guessed it right already: because shard 5 doesn’t have orphaned documents!<br>We added shard 5 a few days ago and it seems that the balancer was not able to remove any of the copied chunks from the donor shards 1 to 4!<br>We even executed the <a href="https://docs.mongodb.com/v4.0/reference/command/cleanupOrphaned/">cleanupOrphaned</a> command several times. It returned after a few hours without any failure message but we still could find orphaned documents (this issue might be worth another story).<br>We could see in our Grafana dashboard that the number of documents was quite different between shard 5 and shards 1 to 4, even though the number of chunks was almost identical on all shards.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lJvUD_F8dc3rGtidzNDEfw.png" /><figcaption>number of documents per shard</figcaption></figure><p>We added shard 5 on the 8th of September. As you can see, the balancer copied documents to shard 5 until the 13th of September but the number of documents did not decrease on the donor shards 1 to 4 until the 14th of September. The difference in the number of documents didn’t start to decrease until September 22nd when we started our own written script to remove orphaned documents. 5 days later, the number of documents was almost identical on all shards (we throttled our script to avoid performance issues while deleting orphaned documents).<br>Btw. the dashboard is based on <a href="https://docs.mongodb.com/v4.0/reference/method/db.collection.estimatedDocumentCount/index.html">estimatedDocumentCount()</a> aka <a href="https://docs.mongodb.com/v4.0/reference/method/db.collection.count/">count()</a> and not on <a href="https://docs.mongodb.com/v4.0/reference/method/db.collection.countDocuments/">countDocuments()</a>. That’s why we also see orphaned documents in the graph. <br><strong>Hint 1:</strong> Never call <a href="https://docs.mongodb.com/v4.0/reference/method/db.collection.countDocuments/">countDocuments()</a> frequently since it’s a very resource-intensive command. Every single document has to be read from the hard disk and written to the RAM in order to sort out orphaned documents to determine a correct count result. Better clean up orphaned documents and use <a href="https://docs.mongodb.com/v4.0/reference/method/db.collection.estimatedDocumentCount/index.html">estimatedDocumentCount()</a> which returns the number of documents very quickly and efficiently.<br><strong>Hint 2:</strong> If you want to have a quick visual overview of slow queries, index usage statistics, and more, check out our open-sourced <a href="https://github.com/idealo/mongodb-slow-operations-profiler">mongodb-slow-operations-profiler</a>. Here’s a two-part article (<a href="https://medium.com/idealo-tech-blog/how-to-visually-spot-and-analyze-slow-mongodb-operations-d91ac819e0de">one</a>, <a href="https://medium.com/idealo-tech-blog/mongodb-slow-operations-analyzer-2-0-24da414fad13">two</a>) I have written about.</p><h3>Tiebreaker</h3><p>It’s very interesting to see the candidate plans on shard 5 (which never had orphaned documents) because the above-mentioned bonus points come into play to find the best-suited query plan.<br>As for the other shards 1 to 4, the query planner had to choose one of the above-mentioned plans A, B, or C. <br>Plan C had the lowest score and lost the race for the best plan. <br>However, plan A and B had the <strong>same number</strong> of both works and advanced. The execution statistics of the candidate plans are not obvious why plan B won but the bonus points that I mentioned earlier in this post must have been used to solve the draw.<br>I’m rather surprised that both metrics works and advanced, which are largely responsible to score the plan, are identical. As for me, Index B is much better suited for the above-given reasons than index A for this type of query.</p><p>This is candidate A (using index A), executed on a shard without orphans:</p><pre>&quot;nReturned&quot; : 101,<br>&quot;executionTimeMillisEstimate&quot; : 106,<br>&quot;totalKeysExamined&quot; : 101,<br>&quot;totalDocsExamined&quot; : 101,<br>&quot;executionStages&quot; : {<br>    &quot;stage&quot; : &quot;SHARDING_FILTER&quot;,<br>    &quot;nReturned&quot; : 101,<br>    &quot;executionTimeMillisEstimate&quot; : 106,<br>    &quot;works&quot; : 101,<br>    &quot;advanced&quot; : 101,<br>    &quot;needTime&quot; : 0,<br>...<br>    &quot;inputStage&quot; : {<br>        &quot;stage&quot; : &quot;FETCH&quot;,<br>        &quot;filter&quot; : {<br>            &quot;offerStatus&quot; : {<br>                &quot;$eq&quot; : 1<br>            }<br>        },<br>        &quot;nReturned&quot; : 101,<br>        &quot;executionTimeMillisEstimate&quot; : 105,<br>        &quot;works&quot; : 101,<br>        &quot;advanced&quot; : 101,<br>        &quot;needTime&quot; : 0,<br>...<br>        &quot;docsExamined&quot; : 101,<br>        &quot;inputStage&quot; : {<br>            &quot;stage&quot; : &quot;IXSCAN&quot;,<br>            &quot;nReturned&quot; : 101,<br>            &quot;executionTimeMillisEstimate&quot; : 9,<br>            &quot;works&quot; : 101,<br>            &quot;advanced&quot; : 101,<br>            &quot;needTime&quot; : 0,<br>...<br>            &quot;indexName&quot; : &quot;shopId_1_missingSince_1_merchantId_1&quot;,</pre><p>Versus candidate B (using index B), executed on a shard without orphans:</p><pre>&quot;nReturned&quot; : 101,<br>&quot;executionTimeMillisEstimate&quot; : 93,<br>&quot;totalKeysExamined&quot; : 101,<br>&quot;totalDocsExamined&quot; : 101,<br>&quot;executionStages&quot; : {<br>    &quot;stage&quot; : &quot;FETCH&quot;,<br>    &quot;nReturned&quot; : 101,<br>    &quot;executionTimeMillisEstimate&quot; : 93,<br>    &quot;works&quot; : 101,<br>    &quot;advanced&quot; : 101,<br>    &quot;needTime&quot; : 0,<br>...<br>    &quot;docsExamined&quot; : 101,<br>    &quot;inputStage&quot; : {<br>        &quot;stage&quot; : &quot;SHARDING_FILTER&quot;,<br>        &quot;nReturned&quot; : 101,<br>        &quot;executionTimeMillisEstimate&quot; : 6,<br>        &quot;works&quot; : 101,<br>        &quot;advanced&quot; : 101,<br>        &quot;needTime&quot; : 0,<br>...<br>        &quot;inputStage&quot; : {<br>            &quot;stage&quot; : &quot;IXSCAN&quot;,<br>            &quot;nReturned&quot; : 101,<br>            &quot;executionTimeMillisEstimate&quot; : 5,<br>            &quot;works&quot; : 101,<br>            &quot;advanced&quot; : 101,<br>            &quot;needTime&quot; : 0,<br>...<br>            &quot;indexName&quot; : &quot;shopId_1_offerStatus_1_merchantId_1__id_1&quot;,</pre><p>As you can see, they differ from the candidate plan of shard 3 (having orphans) only in not removing orphaned documents so that advanced has not been decremented.</p><p>As soon as shards 1 to 4 were cleaned from the orphaned documents, the query planner chose the right query plan B for all shards. From then on, the query execution time was better than before we added shard 5 to the cluster.</p><h3>Conclusion</h3><p>Even if you add a new shard to increase the performance of your MongoDB cluster, you may encounter slower queries because of orphaned documents that make the query planner choose not the best query plan.<br>Decent monitoring helps to detect and eventually solve the issue.</p><p>And that’s it! I hope you enjoyed reading and gained some new helpful insights!<br>If you found this article useful, give me some high fives 👏🏻 and share it with your friends so others can find it too. Follow me here on Medium (<a href="https://medium.com/u/a31335b1254e?source=post_page-----ec8aada2223f--------------------------------">Kay Agahd</a>) to stay up-to-date with my work. Thanks for reading!<br>Btw. <a href="https://www.idealo.de/">idealo</a> is hiring: Check out our vacancies <a href="https://jobs.idealo.com/l/en">here</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b673a41af2cd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/idealo-tech-blog/what-slowed-down-the-queries-mongodb-chose-the-wrong-index-b673a41af2cd">What Slowed Down the Queries? …MongoDB Chose the Wrong Index!</a> was originally published in <a href="https://medium.com/idealo-tech-blog">idealo Tech Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mission possible: Resize MongoDB capped collections without downtime]]></title>
            <link>https://medium.com/idealo-tech-blog/mission-possible-resize-mongodb-capped-collections-without-downtime-ec8aada2223f?source=rss-a31335b1254e------2</link>
            <guid isPermaLink="false">https://medium.com/p/ec8aada2223f</guid>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[devops]]></category>
            <dc:creator><![CDATA[Kay Agahd]]></dc:creator>
            <pubDate>Thu, 06 Aug 2020 07:19:24 GMT</pubDate>
            <atom:updated>2020-10-06T22:00:21.193Z</atom:updated>
            <content:encoded><![CDATA[<h3>Mission Possible: Resize MongoDB Capped Collections Without Downtime</h3><h4>Why our MongoDB had almost no available space on HD and how we could avert the imminent disaster without downtime.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a2TCxpOEr4EukMcFhDvdXw.png" /></figure><p><strong>TL;DR</strong> MongoDB supports capped collections with a specified maximal size but once created, you can’t resize it without downtime. Here’s how we got around it.</p><h3>Recap: Capped Collections</h3><p>As written in MongoDB’s <a href="https://docs.mongodb.com/v4.0/core/capped-collections/index.html">documentation</a>:</p><blockquote>Capped collections are fixed-size collections that support high-throughput operations that insert and retrieve documents based on insertion order. Capped collections work in a way similar to circular buffers: once a collection fills its allocated space, it makes room for new documents by overwriting the oldest documents in the collection.</blockquote><p>Capped collections are (nearly) perfect to store documents for a certain period or a well-defined quantity. Neither you nor MongoDB needs to actively delete the oldest documents. They will just be overwritten by the newest ones, once the capped collection reaches its max size.<br>In this regard, they are superior to <a href="https://docs.mongodb.com/manual/tutorial/expire-data/">TimeToLive (TTL) indexes</a> because capped collections don’t require an index in order to find and eventually delete the oldest documents. Capped collections also don’t require to scan the whole collection for “outdated” documents as TTL indexes do once per minute. Thus, the database needs less RAM and fewer CPU cycles which increases the general database performance.</p><p>Speaking of performance, if you need to <a href="https://medium.com/idealo-tech-blog/how-to-visually-spot-and-analyze-slow-mongodb-operations-d91ac819e0de">visually spot and analyze slow MongoDB operations</a>, I suggest you to checkout idealo’s MongoDB <a href="https://github.com/idealo/mongodb-slow-operations-profiler">slow-operations-profiler</a>, that has been open-sourced on <a href="https://github.com/idealo/mongodb-slow-operations-profiler">github.com</a>. I’ve also written about it in another <a href="https://medium.com/idealo-tech-blog/how-to-visually-spot-and-analyze-slow-mongodb-operations-d91ac819e0de">story</a>.</p><h3>Our Capped Collection</h3><p>We need to store log data for at least 90 days, the longer the retention time the better. We are using three dedicated bare-metal servers in a <a href="https://docs.mongodb.com/master/replication/">replica set</a> for this purpose. Each server has 376 GB RAM, 56 CPU cores, and 5.8 TB SSD’s in Raid 10.</p><h4>The Max Size Problem</h4><p>Since we wanted to store the maximum quantity of log data, we wanted to limit the capped collection at 80% of our HD. Naively one would use the following command to create the capped collection, given that 5101733952992 Bytes are 80% of 5.8 TB.</p><p>db.createCollection(&quot;offerMapping&quot;, {capped:true, size:5101733952992})</p><p>The problem is that size defines the maximum size of <strong>uncompressed</strong> data but MongoDB stores data <strong>compressed</strong> on disk for many years already.</p><h4>Historically Grown</h4><p>MongoDB’s first and only storage engine <a href="https://docs.mongodb.com/v3.0/core/mmapv1/"><strong>MMAP</strong></a> stores data <strong>uncompressed</strong> on disk, so the size parameter made sense at that time. <br>However, the current storage engine <a href="https://docs.mongodb.com/manual/core/wiredtiger/index.html"><strong>WiredTiger</strong></a>, stores data <strong>compressed</strong> on disk. WiredTiger has been introduced to MongoDB in <a href="https://docs.mongodb.com/manual/release-notes/3.0/">version 3.0</a> already and became the default storage engine since <a href="https://docs.mongodb.com/manual/release-notes/3.2/">version 3.2</a>, published in 2015. <br>The MMAP storage engine has been deprecated since <a href="https://docs.mongodb.com/manual/release-notes/4.0/">version 4.0</a> which was published in 2018.</p><h4>Today’s Problem</h4><p>In order to create the capped collection with the correct size, you need to <strong>estimate</strong> how good WiredTiger can compress your data.<br>Around one year ago, we made such tests and calculated that MongoDB could compress up to 26 TB of uncompressed data to fill 80% of our 5.8 TB hard disk. Naively one would use the following command to create the capped collection, given that 28587302322800 Bytes are 26 TB.</p><p>db.createCollection(&quot;offerMapping&quot;,{capped:true, size:28587302322800})</p><p><strong>What’s wrong with this? </strong><br>Well, even if this capped collection is the only collection on the server, you still have to add the sizes of the <a href="https://docs.mongodb.com/master/core/replica-set-oplog/index.html">oplog</a> and indexes. Both are stored <strong>compressed</strong> on disk by WiredTiger. <br>Even though we calculated all these parameters (almost) correctly, our server ran almost out of available disk space.</p><p><strong>How could this happen?</strong><br>This could happen because the content of the documents stored in the collection became less compressible within time. For the same reason, the index sizes increased. <br>Both led to the fact that the HD would have had almost no space left.</p><p>That’s the reason why <strong>it makes no sense to indicate </strong>the size of <strong>uncompressed</strong> data for a capped collection <strong>while</strong> the default MongoDB storage engine WiredTiger <strong>stores data compressed</strong> on disk.</p><p>I’ve created a <a href="https://jira.mongodb.org/browse/SERVER-49916">feature request</a> at mongodb.org with a high priority. A short time later it has been downgraded to only Minor priority by MongoDB staff which means that it will probably <strong>never</strong> be implemented regarding all the “minor” feature requests that are older than 10 years already. <br>I’d be glad if you could <a href="https://jira.mongodb.org/browse/SERVER-49916">vote for it</a>!</p><h3>It Could Be So Easy</h3><p>A workaround would be to simply resize the capped collection. That’s already possible since MongoDB version 3.6 - but <strong>only</strong> for the <strong>oplog</strong>. The oplog is also just a capped collection, so I wonder, why MongoDB engineers did not implement this feature in general for <strong>all</strong> capped collections. Do they think that’s good enough <strong>to guess</strong> the max size of compressed data, to know how well WiredTiger may compress the documents now and in the future?</p><p>As of today, there is no documented way to resize capped collections without downtime even though such <strong>unresolved </strong>feature requests exist for 10 years already, (e.g. <a href="https://jira.mongodb.org/browse/SERVER-1864">SERVER-1864</a>, which has also a priority of only Minor).<br>The standard way to resize a capped collection is by creating a new capped collection with the right max size, stop all writing database clients, <strong>copy all documents</strong> from the old to the new collection, restart database clients and finally delete the old collection.</p><p>In our case, we would have to copy more than 25 TB of uncompressed data! Do the engineers at MongoDB really want us to be offline for that long?</p><h3>Our Workaround</h3><p>Initially, we naively tried to create a smaller capped collection with the same name on one of the replica set members, which we restarted in <a href="https://docs.mongodb.com/master/tutorial/perform-maintence-on-replica-set-members/index.html">maintenance mode</a> as a standalone server. Then we wanted to <a href="https://docs.mongodb.com/database-tools/mongodump/">mongodump</a> the last 90 days of log data from the replica set and pipe it to <a href="https://docs.mongodb.com/database-tools/mongorestore/">mongorestore</a> to the standalone server. The last step would have been to add the standalone server back to the replica set. <br>This did not work because MongoDB uses since version 3.6 <a href="https://docs.mongodb.com/manual/core/databases-and-collections/#unique-identifiers">immutable UUID’s</a> to identify its collections internally. The collection UUID remains the same across all members of a replica set. <br>Unfortunately, the <a href="https://docs.mongodb.com/master/reference/method/db.createCollection/index.html">createCollection</a> command does not support passing a UUID, so the collection will be created with a random UUID which differs from the collection UUID of the other replica set members. Once the standalone member comes back to join the replica set, MongoDB refuses to replicate due to the wrong collection UUID and shuts down the newly added server. <br>Thus, mongodump and mongorestore to the rescue:</p><p>When mongodump writes data to disk, it creates for each collection a collectionName.metadata.json file that belongs to a collection named collectionName. Such a file looks like this:</p><pre>{&quot;options&quot;:{&quot;capped&quot;:true,&quot;size&quot;:{&quot;$numberLong&quot;:&quot;28587302322800&quot;}},&quot;indexes&quot;:[{&quot;v&quot;:2,&quot;key&quot;:{&quot;_id&quot;:1},&quot;name&quot;:&quot;_id_&quot;,&quot;ns&quot;:&quot;changelog.offerMapping&quot;},{&quot;v&quot;:2,&quot;key&quot;:{&quot;offerId&quot;:1.0},&quot;name&quot;:&quot;offerId_1&quot;,&quot;ns&quot;:&quot;changelog.offerMapping&quot;}],&quot;uuid&quot;:&quot;fbb83cda241d45779dc88983351d5447&quot;}</pre><p>As you can see, besides the indexes that will be recreated after having restored the collection, there is also the <strong>size and the UUID</strong> of the collection. We can modify the size to our new size.</p><p>Since I was just interested to get this metadata.json file, I added the -q parameter to mongodump to match no documents:</p><pre>mongodump -h ${HOST}:${PORT} -d ${DB} -c ${COLLECTION} -u ${USER} -p ${PASS} -q &#39;{ &quot;_id&quot; : &quot;foo&quot; }&#39; --out /data/backup/</pre><p>Once modified the size within the metadata.json file, I could restore the (empty) collection with mongorestore and its parameter --preserveUUID:</p><pre>mongorestore -h ${HOST2}:${PORT2} -d ${DB} -c ${COLLECTION} -u ${USER} -p ${PASS} --drop --preserveUUID /data/backup/${DB}/${COLLECTION}.bson</pre><p>Now, the new capped collection having the same UUID but having another size has been created on the standalone server. We can now dump our last 90 days of log data and restore it on the standalone server on-the-fly:</p><pre>mongodump -h ${HOST}:${PORT} -d ${DB} -c ${COLLECTION} -u ${USER} -p ${PASS} --archive -q &#39;{_id: {$gt: new ObjectId(&quot;5eaab2e00e55cebb702911e1&quot;)}}&#39; | mongorestore -h ${HOST2}:${PORT2} -u ${USER} -p ${PASS} --maintainInsertionOrder --archive</pre><p>In order to pipe mongodump to mongorestore we have to use the --archive flag, which in turn makes obsolete the -d and -c parameters of the mongorestore command.<br>Very important is the parameter --maintainInsertionOrder because if you omit it, mongorestore will insert documents in an arbitrary order which is dangerous for a capped collection where the first inserted documents will be the first overwritten ones when the capped collection gets full (FIFO). If the insertion order is not kept during restore, then documents to be overwritten will not be the oldest ones.</p><p>Once mongodump and mongorestore have finished, you end the maintenance period and put back the standalone server into the replica set. It will then automatically replay the oplog and comes in sync eventually.</p><p>As for us, we have reduced the capped collection to 50% of its previous size, which on the one hand is large enough to store at least the log data of the last 90 days, and on the other hand, allows enough space for a growing amount of log data per day or an even lower compression factor.<br>As you can see in the screenshot, only slightly more than 30% of disk space was occupied when the log data of the last 90 days was restored. Until then the capped collection continues to grow slowly until it reaches its maximum size, which, given the current compression factor, should be about 50% of the total disk capacity.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JpaLZj3WLFGMe7zCGf5rSQ.png" /><figcaption>as the Disk Usage looks today</figcaption></figure><h4>Final Steps</h4><p>For the rest of the replica set members you can, of course, proceed as you did with the first one but here is an even easier way: Since one replica set member (here ${HOST2}) has already the correctly sized capped collection, you can simply use it as a donor and restore the data on the next standalone member (here ${HOST3}):</p><pre>mongodump -h ${HOST2}:${PORT2} -d ${DB} -c ${COLLECTION} -u ${USER} -p ${PASS} --archive | mongorestore -h ${HOST3}:${PORT3} -u ${USER} -p ${PASS} --maintainInsertionOrder --archive --drop --preserveUUID</pre><p>If you do it this way, it’s important that mongorestore uses the --drop flag to drop the still existing old capped collection. This time you need also to add the flag --preserveUUID to preserve the collection UUID when mongorestore creates the new capped collection on the standalone server.</p><h4>Did You Say ObjectId?</h4><p>You may have wondered that I used ObjectId to retrieve the last 90 days of documents. Our documents have a timestamp field but it’s not indexed so it would take very long to query it. <br>However, our primary key _id of the capped collection is an <a href="https://docs.mongodb.com/master/reference/method/ObjectId/index.html">ObjectId</a> which contains the timestamp in its first 4 Bytes. Knowing this, we can calculate in the mongoshell an ObjectId that corresponds to a date 90 days ago:</p><pre>&gt; var now = new Date()<br>&gt; var d = new Date(now.getTime()-(90*24*60*60*1000));<br>&gt; var dhex = (Math.round(d.getTime()/1000)).toString(16)<br>&gt; var oid = new ObjectId(dhex + &quot;0000000000000000&quot;)</pre><p>Then we need a second ObjectId that differs a bit, say one hour, because it’s unlikely that the first calculatedObjectId exists due to the added padding of zeros.</p><pre>&gt; var d2 = new Date(d.getTime()+(60*60*1000));<br>&gt; var d2hex = (Math.round(d2.getTime()/1000)).toString(16)<br>&gt; var oid2 = new ObjectId(d2hex + &quot;0000000000000000&quot;)</pre><p>With these two ObjectId&#39;s we can get a document which has been created within the time span of both of them:</p><pre>&gt; db.offerMapping.find({_id:{$gt:oid, $lt:oid2}}, {_id:1}).limit(1)<br>{&quot;_id&quot; : ObjectId(&quot;5eaab2e00e55cebb702911e1&quot;)}</pre><p>And that’s the _id I used in my query to get the last 90 days of documents.</p><p>And that’s it! I hope you enjoyed reading and gained some new helpful insights! <br>If you found this article useful, give me some high fives 👏🏻 and share it with your friends so others can find it too. Follow me here on Medium (<a href="https://medium.com/u/a31335b1254e">Kay Agahd</a>) to stay up-to-date with my work. Thanks for reading!<br>Btw. <a href="https://www.idealo.de/">idealo</a> is hiring: Check out our vacancies <a href="https://jobs.idealo.com/l/en">here</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ec8aada2223f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/idealo-tech-blog/mission-possible-resize-mongodb-capped-collections-without-downtime-ec8aada2223f">Mission possible: Resize MongoDB capped collections without downtime</a> was originally published in <a href="https://medium.com/idealo-tech-blog">idealo Tech Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MongoDB Slow Operations Analyzer 2.0]]></title>
            <link>https://medium.com/idealo-tech-blog/mongodb-slow-operations-analyzer-2-0-24da414fad13?source=rss-a31335b1254e------2</link>
            <guid isPermaLink="false">https://medium.com/p/24da414fad13</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[technology]]></category>
            <dc:creator><![CDATA[Kay Agahd]]></dc:creator>
            <pubDate>Tue, 11 Sep 2018 12:16:54 GMT</pubDate>
            <atom:updated>2021-03-26T08:19:41.073Z</atom:updated>
            <content:encoded><![CDATA[<h4>From a pure visual analysis tool to a potential administration tool</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uechvyxEOpoSSC-SMShVTg.gif" /></figure><p><strong>TL;DR</strong> If you are just interested in the result of our work or want to have a quick visual overview of your slow queries you might be interested in our <a href="https://github.com/idealo/mongodb-slow-operations-profiler">open source project</a> which is hosted on our Github repo. I recently wrote also a <a href="https://medium.com/idealo-tech-blog/practical-guide-to-analyze-slow-mongodb-operations-9363035b01fb">practical guide to analyze slow MongoDB operations</a> by using this tool.<br>Keep on reading if you want to know more insights about this project.</p><p>In my <a href="https://medium.com/idealo-tech-blog/how-to-visually-spot-and-analyze-slow-mongodb-operations-d91ac819e0de">first blog post</a> I wrote about our way to visually spot and analyze slow MongoDB operations. The resulting web application has been developed in 2013 already. Some time later it turned out that our web application missed an overview of the current configured database profilers. Since each mongod instance and database has its own database profiler, we ended up to have over 400 profilers. Not all are running at the same time. Only a few, 66 to say a number, are running constantly. The rest will be enabled on demand when we experience decreasing database performance. Also, we didn’t want to feed a new configuration to the application just to enable some database profilers. We just wanted to click them to activate or deactivate them. This is why the need for an application status page came in.</p><h4>Application status page</h4><p>Besides showing the status of the collector, means where and how many slow operations have been collected (read and written) since application restart, the application page should also show every registered database profiler in a table. Since profiling works per database, each database to be profiled is presented by one row. In regard of the potentially high number of rows, the table has to be filterable. Columns should be sortable and selectable to be hidden or shown in order to increase visibility. The sum of the values, where it makes sense, should be shown at the bottom of the columns. To implement such a flexible data table client side, we used <a href="https://datatables.net/">datatables</a> which we were using with great ease on the analysis page already. Here is a mockup of such a table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hs-t14IIVbzm0wjx-04qng.png" /></figure><p>There are the following columns:</p><ul><li>Label of the database system</li><li>Replica set name</li><li>Status (primary, secondary, arbiter, recovering, down, etc.)</li><li>Host server address</li><li>Database to be profiled</li><li>Collections to be profiled</li><li>SlowMS threshold</li><li>Status whether profiling is running or stopped</li><li>Status whether collecting is running or stopped</li><li>Timestamp of the last written slow operation</li><li>Quantity of the last written slow operations within different time periods (last 10 seconds, 1, 10, 30 and 60 minutes, 12 and 24 hours)</li></ul><p>The table should be sorted by default by the columns Label, ReplSet and Status which gives a very good overview over a whole bunch of clusters.</p><p>By the last columns which show the quantity of the last collected slow operations, we are able to see instantly which databases may behave abnormally. In such case, we may either analyse those databases or switching off collecting or lower their slowMS threshold in order to profile less slow operations.</p><p>Interesting to note is that loading the application page quickly, especially if many MongoDB systems with many databases are to be profiled, is not trivial. It requires that all profiling readers get their status update (e.g. if the database profiler is running or stopped) from the database server concurrently. However, since the webserver is limited in CPU cores, it won’t be able to handle all threads in parallel within the given max. response timeout. In such cases the user should be informed that not all threads could terminate in time, and hence the application status page might be incomplete. Adding more CPU cores to the webserver, incrementing the max. response timeout or profiling quicker responding or fewer MongoDB servers will alleviate or even avoid this issue.</p><h4>Action panel</h4><p>Now that we have a summery of all database profilers, we can use this view to execute more than just commands like profiling on/off. The user just needs to tick the database profiler(s) and then chose the command to execute. The user can choose whether the command has to run against the corresponding database system (i.e. mongos-router) or against the individually selected nodes (i.e. mongod). The difference is that the command will run either against the entry point of the database system (i.e. router or primary) or against all selected nodes which may be secondaries as well. Current implemented commands are:</p><ul><li>list databases and their collections</li><li>show currently running operations (requires MongoDB v3.2 or newer)</li><li>show index access statistics of all databases and their collections (requires MongoDB v3.2 or newer)</li></ul><p>Implementing new commands is quite easy: one just need to create a new java class which implements the interface de.idealo.mongodb.slowops.command.ICommand. The interface has only 2 methods in order to execute the database command and to transform the result to a corresponding table structure.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/732f4d8fc150a69156042ffbb97e698c/href">https://medium.com/media/732f4d8fc150a69156042ffbb97e698c/href</a></iframe><p>The command result is shown in a new page in a table built with the already above mentioned javascript library <a href="https://datatables.net/">datatables</a> which makes the table filterable and sortable by one or more columns at once.</p><p>This is especially useful when we want to see instantly whether a current running operation might block or slow down the database, which is by our experience one of the most frequently asked questions of the database users, aka software developers. If you have used until now MongoDB’s command currentOp to achieve the same, you’ll know how cumbersome it is to get a meaningful picture of the current operations. If you did not, have a look at the <a href="https://docs.mongodb.com/manual/reference/method/db.currentOp/#output-example">output example</a> of the currentOp command and ask yourself how you would query this beast.</p><p>Here is a screenshot of the data table showing result of the currentOp command filtered by “offerStore.offer”:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QDdnu7M8aySDrHaRA4H-fA.png" /></figure><p>The data table is much easier to filter, to sort and finally to spot the slow operation entry than using MongoDB’s currentOp command. With our action panel, almost everybody in our company may tick its database and click on the currentOpcommand, wait the result page is loaded and finally sort by column secs running to see instantly the longest running operations. We might consider to add a killcommand to avoid copying &amp; pasting the opId and executing MongoDB&#39;s killOpcommand manually in case we want to kill such long running operations.</p><p>Last but not least, the application status page simplifies also the launch of an analysis of collected slow operations because users just need to tick the database and click the analyse action. This opens the analysis page, preset with the database label(s), server address(es), replica set(s), database(s) and a time span of the last 24 hours, and with default grouping by preset fields and a default resolution by hour. Users may adapt these preset values of course but they do not need initially to enter such values by themselves.</p><h4>Further benefit</h4><p>Another benefit of the globally collected slow operations is that we can easily keep an eye on the evolution of slow operations within any time span.<br>We are doing this by periodically executing an aggregation query against our collector database:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/39fdee7e392f39a615ef84309160d41d/href">https://medium.com/media/39fdee7e392f39a615ef84309160d41d/href</a></iframe><p>The query counts for each database system the number of all collected slow operations within the last 60 seconds, finds out the maximum duration and calculates the sum of all durations, returned document counts and response lengths. These results are sent to our graphite server.</p><p>The aggregation query execution time is only 100 to 200 ms because it uses the index {ts:-1, label:1}. Since only documents that were inserted within the last minute have to match, only the very beginning of the index needs to be traversed, which makes the query fast although the collector database contains already more than 250 million documents!</p><p>With these metrics we have built a Grafana dashboard:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uechvyxEOpoSSC-SMShVTg.gif" /></figure><p>Users can select their database system, here for example offerstore-it, through the select box above the diagram. Then, by clicking on one or multiple metrics below the diagram, they can bring out the corresponding graph(s). In this screenshot for example, the orange graph shows that the maximum duration per query is 1.24 min, the average of all maximum durations is 7.4 sec, hence a strong variation, visible also by the peaks in the diagram. The yellow graph in this example shows the number of slow queries on the right y-axis. This varies from max 79 to averaged 15 slow operations per minute. The light blue graph sums up the execution times of all collected slow operations per minute. As you can see, the database system in this example is characterized by many heavy peaks which are nearly perfectly recurrent. This is because the database is nearly exclusively used by backend jobs that run periodically. If the jobs could be distributed more evenly, the general database performance would be better.</p><p>If we zoom out, back to begin of 2018 until now, we can see that the number and averaged duration times of slow operations, besides a few heavy spikes, have generally not increased. That’s a good sign that nothing is running out of the rudder so far.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mEkj6CpuZny5qak2t_2ytg.png" /></figure><p>Even if averaged execution times of 4.8 seconds may seem very slow, we don’t have to forget that this is the average of only those queries, exceeding our defined threshold slowMS of 3 seconds for this database system. It would not make much sense to lower the threshold because one should first fix the slowest queries before collecting still more not so slow queries to optimize them further.</p><p>The following screenshot shows the end-to-end response times and number of operations monitored throughout the year 2018 by the main user of this database system:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DmlXRdTYDXOrjC0vOp7rpw.png" /></figure><p>As you can see, the average response time for findAndModify operations is only 11 ms although some spike went up to 1.3 seconds maximum. However, such heavy spikes were extremely rare for this database client. Without taking into account these 3–4 spikes throughout the year, the average response time for findAndModify is generally below 5 ms. In the background, you see the number of read and write operations which reached a maximum of 40M ops/minute (66K ops/sec) each. The average of read and write counts both together is more than 20M ops/minute (33K ops/second). That’s a quite good overall performance for a database cluster consisting of only 3 shards, storing nearly 300 million of documents. Our <a href="https://github.com/idealo/mongodb-slow-operations-profiler">mongodb-slow-operations-profiler</a> helped us to achieve this good performance.</p><p>I’m curious to know your feedback, suggestions or contributions! Hopefully, you liked both of my posts of this blog post series. If you found this article useful, give me some high fives 👏🏻 and share it with your friends so others can find it too. Follow me here on Medium (<a href="https://medium.com/u/a31335b1254e">Kay Agahd</a>) to stay up-to-date with my work. Thanks for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=24da414fad13" width="1" height="1" alt=""><hr><p><a href="https://medium.com/idealo-tech-blog/mongodb-slow-operations-analyzer-2-0-24da414fad13">MongoDB Slow Operations Analyzer 2.0</a> was originally published in <a href="https://medium.com/idealo-tech-blog">idealo Tech Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to visually spot and analyze slow MongoDB operations]]></title>
            <link>https://medium.com/idealo-tech-blog/how-to-visually-spot-and-analyze-slow-mongodb-operations-d91ac819e0de?source=rss-a31335b1254e------2</link>
            <guid isPermaLink="false">https://medium.com/p/d91ac819e0de</guid>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[software-development]]></category>
            <dc:creator><![CDATA[Kay Agahd]]></dc:creator>
            <pubDate>Wed, 05 Sep 2018 14:17:41 GMT</pubDate>
            <atom:updated>2021-03-26T08:17:29.152Z</atom:updated>
            <content:encoded><![CDATA[<h4>We improved the performance of our MongoDB databases by using our own developed operation profiler and visualizer</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yGTt-yX_6F3uq8JAFxGaBw.png" /></figure><p><strong>TL;DR</strong> If you are just interested in the result of our work or want to have a quick visual overview of your slow queries you might be interested in our <a href="https://github.com/idealo/mongodb-slow-operations-profiler">open source project</a> which is hosted on our Github repo. I recently wrote also a <a href="https://medium.com/idealo-tech-blog/practical-guide-to-analyze-slow-mongodb-operations-9363035b01fb">practical guide to analyze slow MongoDB operations</a> by using this tool. <br>Keep on reading if you want to know more insights about this project.</p><p>Did you ever wonder why your MongoDB database seemed to be much slower than expected? Did you experience periodical hot spots and wanted to know the culprit(s)? Did you ever want to know which database operations were the most expensive, probably the most improvable?</p><p>Well, in these cases you certainly got in touch with MongoDB’s built-in <a href="https://docs.mongodb.com/manual/tutorial/manage-the-database-profiler/">database profiler</a> because it allows to collect slow operations per database on a running “mongod” process. Although MongoDB’s database profiler is a valuable source to spot slow operations, it has some inconveniences which may make hunting the culprit time and resource eating operations hard:</p><ol><li><strong>each mongod (and database) needs to be handled separately</strong> <br>This means, that you have to start the database profiler on every mongod that constitutes your database(s). Since one of MongoDB most appreciated feature is horizontal scaling, which means that you simply add mongod running servers to the database cluster in order to scale out, you may have tens, hundreds or more running mongod’s. Multiply this number with the number of databases that are running on this cluster because each database has its own database profiler. Lucky you if you can exclude some unsuspicious databases on that cluster to be profiled, else you may have to start hundreds or thousands of database profilers and, worse, to analyze thereafter the results of each of them.</li><li><strong>it gives only a view over a limited time span due to capped collection</strong> MongoDB’s database profiler writes the collected slow queries in a capped collection, which is limited per default to 1 MB. When the collection is full, new incoming data overwrite the oldest (FIFO). This makes it impossible to have a historical view of the slow operations, especially, if the collected query is many Bytes big. Of course, you could delete the default capped collections and create bigger ones for each mongod and database but that would add still more work to do profiling.</li><li><strong>querying the slow-operations-collection may add more stress to the profiled database<br></strong>Of course, if the capped collection is very small (e.g. the default 1 MB) this wouldn’t be an issue but if you had increased its size in order to cover a bigger time span then the database performance could even more decrease with every query run against that collection, especially if no or only suboptimal indexes are used.</li><li><strong>different formats of </strong><strong>query field make querying more difficult</strong> <br>The collected operations may have different formats/schemata. Here are 3 of it to give you an idea:</li></ol><ul><li>query as document:</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f32b5ad643610dcc4e4a565baa67e45f/href">https://medium.com/media/f32b5ad643610dcc4e4a565baa67e45f/href</a></iframe><ul><li>query as an embedded document:</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b8caedc1d7b076b99230574275d0cefc/href">https://medium.com/media/b8caedc1d7b076b99230574275d0cefc/href</a></iframe><ul><li>query embedded as $query:</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9df9b00b5f4fb54a5bb680c1a604dbf4/href">https://medium.com/media/9df9b00b5f4fb54a5bb680c1a604dbf4/href</a></iframe><p>You would need to execute multiple queries or to execute one quite complex query in order to respect all these different formats/schemata.</p><p>5.<strong> operations sent through mongos router omit the user (jira ticket: </strong><a href="https://jira.mongodb.org/browse/SERVER-7538"><strong>SERVER-7538</strong></a><strong>)</strong> <br>Although this bug has been created already in November 2012 and its priority is Major -P3, it&#39;s still unresolved, which makes it difficult to know the user of the collected slow query.</p><h3>Our requirements</h3><h4>Why we use MongoDB</h4><p>We are using MongoDB since version 1.6 (around 2011) in production, mainly because of heavy data growth. MongoDB’s ability to scale out horizontally, automatic failover, and its powerful query language made it an optimal fit. We use it mainly for backend processing where we don’t need to rely on transactions. As a price comparison company, we need to store all offers of products we get constantly from our commercial clients (aka shops) in order to show the best prices of products to our end customers. In simple words, if our databases are too slow then prices won’t be updated as fast as we, our commercial clients, and end customers expect to.</p><p>Right now, our biggest MongoDB database is constituted of 66 data nodes (mongod’s). Each data node is a bare metal server consisting of generally 48 CPU’s, 128 GB RAM, and 750 GB SSD’s in Raid 1. This database stores nearly 2 Billion documents and executes up to 1 Billion write operations each day. There are slightly more read than write operations. Besides that, we have other sharded clusters and sole replica sets, the latter both as bare metal servers and VM’s.</p><h4>What we wanted more</h4><p>In order to optimize data processing, we wanted to have a quick overview of slow operation types and their quantity within a time period. “Types” means whether the slow operation was an insert, update, delete or read, the server where the query was executed and the fingerprint of the query, means, the fields that were queried, projected, and sorted on.</p><p>We also wanted a historical view to see how slow operations evolved. This allows us to correlate events like software deployments, database or hardware updates, unusual fast data growth, etc. with performance degradations. Furthermore, recurrent peaks become clearly visible and can be eventually investigated.</p><p>Even if we were using MongoDB primarily for huge datasets, we are seeing an increasing number also of smaller MongoDB databases, often only replica sets and sometimes only running on KVM hosts. Having an overview of all database systems in one central place, issuing administrative commands to some or all of them would be big time saver.</p><p>Last but not least, related to our main goal to optimize database operations, we wanted to filter by slow operation types and time range to drill down to a minimal point in time to spot the culprit queries. These tasks should not be reserved to database administrators or MongoDB experts. All software developers who are using MongoDB should be empowered to inspect the databases their software is talking to in order to spot performance bottlenecks as fast, easy, and efficient as possible. The desired results are:</p><ul><li>faster queries</li><li>better adapted indexes</li><li>better adapted data schemata</li><li>less required resources (hardware)</li><li>smarter workflow by avoiding unnecessary data flows</li><li>higher relevant data throughput</li></ul><h4>Achieving our objectives</h4><p>Two global steps are required:</p><ul><li>collect and aggregate slow operations from all concerned mongod’s and databases into <strong>one</strong> global collection</li><li>provide a company-wide accessible graphical user interface to query this global collection, show filterable results in a concise manner</li></ul><p>Both requirements can be achieved by a web application.</p><h4>Step 1 of 2</h4><p><strong>Global collection</strong></p><p>The usage of one global collection allows easy and fast querying of all slow queries of a whole MongoDB sharded system and can even correlate multiple MongoDB systems one with another. Since it’s located on another replica set, it avoids interfering with the profiled mongod’s while the collected slow operations are being analyzed. As long as no capped collection is used to store slow operations, it keeps historical data for all the time needed for investigation.</p><p><strong>Collector</strong></p><p>We will use tailable cursors to collect constantly slow operations from the profiled mongod’s database(s). We have to guarantee that only one collector per mongod and database is running at once in order to avoid doubled entries. In case of failure, the collector must quickly enough reconnect to the capped collection before data gets overwritten but it has to avoid DoS.</p><p><strong>Collected entries</strong></p><p>The slow operation documents collected by MongoDB’s database profiler are quite verbose. Here is an example:</p><pre>{<br>   &quot;op&quot;:&quot;getmore&quot;,<br>   &quot;ns&quot;:&quot;offerStore.offer&quot;,<br>   &quot;query&quot;:{<br>     “query”:{<br>		 &quot;shopId&quot;:123,<br>		 &quot;onlineProductIds&quot;:{<br>		    &quot;$ne&quot;:null<br>		 },<br>		 &quot;smallPicture&quot;:{<br>		    &quot;$ne&quot;:null<br>		 },<br>		 &quot;_id&quot;:{<br>		    &quot;$gt&quot;:1555008076<br>		 },<br>		 &quot;lastChange&quot;:{<br>		    &quot;$gt&quot;:ISODate(&quot;2013­04­02T22:00:00Z&quot;)<br>		 }<br>      },<br>      “orderby”:{<br>	 	“_id”:1<br>      }<br>   },<br>   &quot;cursorid&quot;:NumberLong(&quot;5773493375904448215&quot;),<br>   &quot;ntoreturn&quot;:500,<br>   &quot;keyUpdates&quot;:0,<br>   &quot;numYield&quot;:2350,<br>   &quot;lockStats&quot;:{<br>      &quot;timeLockedMicros&quot;:{<br>	 &quot;r&quot;:NumberLong(8724165),<br>	 &quot;w&quot;:NumberLong(0)<br>      },<br>      &quot;timeAcquiringMicros&quot;:{<br>	 &quot;r&quot;:NumberLong(5321722),<br>	 &quot;w&quot;:NumberLong(7)<br>      }<br>   },<br>   &quot;nreturned&quot;:500,<br>   &quot;responseLength&quot;:94656,<br>   &quot;millis&quot;:5322,<br>   &quot;ts&quot;:   ISODate(&quot;2013­04­05T01:41:31.710Z&quot;),<br>   &quot;client&quot;:&quot;172.16.65.202&quot;,<br>   &quot;user&quot;:&quot;pl_parser&quot;<br>}</pre><p>We reduce its size by keeping only interesting fields. Such reduced document would look like:</p><pre>{<br>   &quot;_id&quot;:ObjectId(&quot;512e43099bbcf52b9aff3602&quot;),<br>   &quot;ts&quot;:ISODate(&quot;2013­04­05T01:41:31.710Z&quot;),<br>   &quot;adr&quot;:&quot;s233.ipx&quot;,<br>   &quot;op&quot;:&quot;getmore&quot;,<br>   &quot;fields&quot;:[<br>      &quot;shopId&quot;,<br>      &quot;onlineProductIds.$ne&quot;,<br>      &quot;smallPicture.$ne&quot;,<br>      &quot;_id.$gt&quot;,<br>      “lastChange.$gt“<br>   ],<br>   &quot;sort&quot;:[<br>      &quot;_id”<br>   ],<br>   &quot;nret&quot;:500,<br>   &quot;reslen&quot;:94656,<br>   &quot;millis&quot;:5322,<br>   &quot;user&quot;:&quot;pl_parser&quot;,<br>   &quot;lbl&quot;:&quot;myCluster&quot;<br>}</pre><p>We chose short field names to reduce the size even more. Key-value pairs inside query and orderby are transformed to arrays of only key names (aka field names) appended with their operator (or none for equality comparisons) because it doesn&#39;t matter to know the exact parameter values nor the sort order of a slow query in order to be able to check whether such query may be optimized. Appending the operator such as $in, $exists, $gt, $lt etc. to the field, e.g. _id.$gtas shown above, is helpful to get the right picture because it makes a big difference in terms of performance to query for example {_id : 123}or {_id : {$in : [1,2,3...thousands of values...n]}}. The latter query will take a multiple of time compared to the first, so it&#39;s essential to see also the operator to get the analysis right.</p><p>We added one field lbl to associate the entry with a user-friendly label of the cluster or database system to which the server belongs to. With such documents, we are well equipped to query efficiently our global slow operations collection.</p><p>Today, our collector database, respectively its collection where slow operations are stored, contains already more than 250 million documents! The average document size is only 238 Bytes, data size ca. 55 GB, index size less than 9 GB, and storage size, thanks to wired tigers storage engine using compression, only less than 12 GB. That’s great in regard to more than 250 million stored documents!</p><h4>Step 2 of 2</h4><p><strong>Graphical user interface, aka analysis page</strong></p><p>The graphical user interface is to filter and analyze slow operations. It consists of three parts:</p><ul><li>a search form</li><li>a diagram</li><li>a data table</li></ul><p>These three components need to be implemented in an analysis page.</p><p><strong>Search form</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/588/1*Malf3VGrujCipLMyksK22A.jpeg" /></figure><p>The search form contains several input fields in order to limit the search. The most important are from- and to-date to define a time span as well as an input field to enter the name of the database system. The following input fields may contain one ore more values. If the user enters multiple values, they will be logically combined by OR:</p><ul><li>Server address</li><li>Replica set name</li><li>Database name</li><li>Collection name</li><li>User name</li><li>Operation type</li></ul><p>The same applies to the following input fields except multiple values will be logically combined by AND:</p><ul><li>Queried fields</li><li>Sorted field</li></ul><p>Last but not least there are two input fields to define the min and max time in milliseconds because it may be helpful to analyze only such queries which took more than x but less than y milliseconds.</p><p>Besides these input fields, there are the following checkboxes in order to group by one or multiple criteria:</p><ul><li>Label of the database system</li><li>Server address</li><li>Replica set name</li><li>Database name</li><li>Collection name</li><li>User name</li><li>Operation type</li><li>Queried fields</li><li>Sorted fields</li></ul><p>As you can see, the checkboxes reflect almost all above-mentioned input fields. The more boxes are checked, the finer-grained is the result. For example, if you check Queried fieldsbut let Sorted fieldsunchecked, all slow queries queried on the same fields will be in the same group, no matter if they were sorted by the same fields or not. &quot;Same group&quot; means, that you will see one entry in the diagram and data table with its timestamp and duration of operation. The more checkboxes you set, the more entries you will obtain, allowing you to drill in deeper, for example, to know which of the N database servers constituting the whole analyzed database system were exactly involved, or which collection or which user, etc.</p><p>Finally, the search form offers also the following radio buttons in order to define a suitable resolution for the diagram (it’s not applicable to the data table because it does not have a time dimension like the diagram):</p><ul><li>Year</li><li>Month</li><li>Week</li><li>Day</li><li>Hour</li><li>Minute</li></ul><p>For example, if you choose Hour, all slow operations of the same type (remember the above described Group-by checkboxes) occurred between a precise hour, say between 08:00,00 and 08:59,59 o&#39;clock, are grouped together. You won&#39;t be able to know the exact minute when they occurred. These will be shown as they occurred all at 08:00,00 o&#39;clock. However, you will still know the exact quantity of them.</p><p><strong>Diagram</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/424/1*lI_RDBBl6QAWU6EYNsojwQ.png" /></figure><p>If we want a chronological overview of the collected slow queries, a diagram in which the x-axis is the timestamp of when the slow operation happened, is practical. We wanted also instantly to get an adequate picture of both the duration and of the quantity of the filtered slow operation types. We came up with an idea where the y-axis corresponds to the duration time. If multiple slow operations of the same type happened at the same time, a circle of a surface that corresponds to the number of these slow operations is drawn in place of a single point onto the diagram. This idea allows us to immediately see when many or just a few slow operations occurred while seeing at the same time their duration time.</p><p>The higher a point or circle on the y-axis, the slower was the execution time of this operation. The greater the diameter of the circle, the more slow operations of this type were executed at this time. The diagram has to be zoomable in and out. While hovering with the mouse over the shown points, respectively circles, details of them has to be displayed in a legend next to the diagram. To implement such a diagram client side, we used <a href="http://dygraphs.com/">dygraphs</a> which is a fast, flexible open-source JavaScript charting library.</p><p><strong>Data table</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*abOyAAMIymIeX5SDxAzGQA.png" /></figure><p>The presentation of slow operations in a data table is advantageous when data needs to be filtered more (drilled down) or sorted by a certain criterion. For example, you could sort by a column which sums up the duration times of all operations of the corresponding type in order to see instantly which of these queries altogether were the most expensive ones.</p><p>Regarding the data we collect with each slow operation, we may build the following table columns:</p><ul><li>Slow operation type</li><li>Count</li><li>Durations:</li></ul><pre>- min<br>- max<br>- average<br>- sum</pre><ul><li>Number of returned documents:</li></ul><pre>- min<br>- max<br>- average<br>- sum</pre><ul><li>Number of returned documents per millisecond</li><li>Milliseconds per returned document</li></ul><p>The table has to be filterable. Columns should be sortable and selectable to be hidden or shown. The sum of the values, where it makes sense, is shown at the bottom of the columns.</p><p>To implement such a flexible data table client-side, we used <a href="https://datatables.net/">datatables</a> which is a plug-in for the jQuery javascript library.</p><h4>Implementation insights</h4><p><strong>How to query collected slow operations?</strong></p><p>Regarding our schema of the collected slow operations, we can group by time component to apply a resolution of year, month, week, etc. Grouping by server address, user, operation, queried, or sorted fields allows to define different slow operations types. Fields like ts, adr, op,fields,sort, millis, user and lblmay be used to filter specific slow operations respectively to focus on a specific time period.</p><p>Here is an example of such an aggregation query to illustrate what’s being said:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/955/1*4kwTRwVrY1OAkTV-BsOSdg.png" /></figure><p>Since we always query the timestamp field to focus on a specific time period and often indicate the label of the profiled database system, we built a combined index {ts:-1, label:1}. The order of tsis descending because we query generally recently collected slow operations.</p><p>Furthermore, we added an index {adr:1, db:1, ts:-1} which is essential for starting up quickly all profiling readers because they need to know the last stored slow operation for their mongod host address and database else they could re-insert already written entries which may be still present in the system.profile capped collection.</p><p>It turned out that both indexes perform very well. Like I wrote above, our collector database today stores already more than 250 million documents. It’s running on a single replica set (no sharding), shared with other databases. However, queries to get the latest profiling entry for a database label are executed within 10 ms, even if they are executed concurrently for all our registered databases which is the case when the web application starts up.<br>Aggregation queries, executed for analyzing slow operations are slower because they are much more complex but they have finished generally within a few seconds dependent on the search and group criteria.</p><p><strong>Configuration</strong></p><p>We need to configure which databases have to be profiled and also which database has to be used to store collected slow operation entries, called collector. The latter could be expressed like this JSON formatted object:</p><pre>{	&quot;collector&quot;:{<br>	    &quot;hosts&quot;:[<br>	      &quot;myCollectorHost_member1:27017&quot;,<br>	      &quot;myCollectorHost_member2:27017&quot;,<br>	      &quot;myCollectorHost_member3:27017&quot;],<br>	    &quot;db&quot;:&quot;profiling&quot;,<br>	    &quot;collection&quot;:&quot;slowops&quot;,<br>	    &quot;adminUser&quot;:&quot;&quot;,<br>	    &quot;adminPw&quot;:&quot;&quot;<br>	  }<br>}</pre><p>The collector has some access points, defined by array variable hosts. The database and collection where the slow operation entries have to be written are defined by fields dbrespectively collection. If authentication is used, we need to enter the credentials of an user having an administrative role. An administrative role is required to be able to create the database and collection in case they do not yet exist.</p><p>The database systems to be profiled could be configured this way:</p><pre>{	&quot;profiled&quot;:[<br>	    { &quot;enabled&quot;: false,<br>	      &quot;label&quot;:&quot;dbs foo&quot;,<br>	      &quot;hosts&quot;:[<br>		&quot;someHost1:27017&quot;,<br>		&quot;someHost2:27017&quot;,<br>		&quot;someHost3:27017&quot;],<br>	      &quot;ns&quot;:[<br>		&quot;someDatabase.someCollection&quot;,<br>		&quot;anotherDatabase.anotherCollection&quot;],<br>	      &quot;adminUser&quot;:&quot;&quot;,<br>	      &quot;adminPw&quot;:&quot;&quot;,<br>	      &quot;slowMS&quot;:250,<br>	      &quot;responseTimeoutInMs&quot;: 1500<br>	    },<br>	    { &quot;enabled&quot;: false,<br>	      &quot;label&quot;:&quot;dbs bar&quot;,<br>	      &quot;hosts&quot;:[&quot;someMongoRouter:27017&quot;],<br>	      &quot;ns&quot;:[<br>		&quot;someDatabase.someCollection&quot;,<br>		&quot;anotherDatabase.*&quot;,<br>		&quot;*.myCollection&quot;],<br>	      &quot;adminUser&quot;:&quot;&quot;,<br>	      &quot;adminPw&quot;:&quot;&quot;,<br>	      &quot;slowMS&quot;:250,<br>	      &quot;responseTimeoutInMs&quot;: 3000<br>	    },<br>	    { &quot;enabled&quot;: false,<br>	      &quot;label&quot;:&quot;dbs with many databases to be profiled&quot;,<br>	      &quot;hosts&quot;:[&quot;someOtherMongoRouter:27017&quot;],<br>	      &quot;ns&quot;:[&quot;*.*&quot;],<br>	      &quot;adminUser&quot;:&quot;&quot;,<br>	      &quot;adminPw&quot;:&quot;&quot;,<br>	      &quot;slowMS&quot;:250,<br>	      &quot;responseTimeoutInMs&quot;: 3000<br>	    }<br>	]    <br>}</pre><p>Fields of profiled entries explained:</p><ul><li>enabled = whether collecting has to be started automatically upon (re)start of the application</li><li>label = a label of the database system in order to be able to filter, sort, and group on it</li><li>hosts = an array of members of the same replica set, or just a single host, or one or more mongo router of the same cluster. The application will <strong>resolve all members</strong> of a replica set (even if only 1 member has been defined) respectively all shards and its replica set members of a whole MongoDB cluster.</li><li>ns = an array of the namespaces to be collected in the format of databaseName.collectionName. The placeholder * may be used instead of databaseName and/or collectionName to collect from all databases and/or all collections. Examples:</li><li>databaseName.* collects from all collections from database databaseName</li><li>*.collectionName collects from all databases from collection collectionName</li><li>*.* collects from all collections from all databases</li><li>adminUser= if authentication is enabled, name of the user for database &quot;admin&quot; having role &quot;root&quot;</li><li>adminPw= if authentication is enabled, the password of the user</li><li>slowMS= threshold of slow operations in milliseconds This threshold can&#39;t be set per database. It&#39;s set per mongod instance. This is a MongoDB limitation.</li><li>responseTimeoutInMs= response timeout in ms</li></ul><p>To make the configuration less verbose, recurrent fields like enabled, adminUser, adminPw, slowMs and responseTimeoutInMscould be set to default values that apply where these fields have not been specified.</p><h4>The result</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f-BL5wzoYBf_neaoRTxJMg.png" /></figure><p>The result is a web application that all software developers within the company can access to visually spot and analyze slow MongoDB operations. They may share a view of their identified slow operations by means of the URL of the web application so other colleagues will have an identical view and may investigate further by themselves. That was a basic step to enable software development teams to be more independent, being less contingent on the database administrator team.</p><p>Also, this project was the first project that my company open-sourced.<br>Please have a look and play with it if you’re interested. It’s hosted on our <a href="https://github.com/idealo/mongodb-slow-operations-profiler">Github repository</a>. You will see there, that the web application has evolved from a pure slow operation analysis tool to a potential administration tool. I will explain the latter in the <a href="https://medium.com/idealo-tech-blog/mongodb-slow-operations-analyzer-2-0-24da414fad13">following post</a>. Stay tuned!</p><p>I’m curious to know your feedback, suggestions, or contributions! If you found this article useful, give me some high fives 👏🏻 and share it with your friends so others can find it too. Follow me here on Medium (<a href="https://medium.com/u/a31335b1254e">Kay Agahd</a>) to stay up-to-date with my work. Thanks for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d91ac819e0de" width="1" height="1" alt=""><hr><p><a href="https://medium.com/idealo-tech-blog/how-to-visually-spot-and-analyze-slow-mongodb-operations-d91ac819e0de">How to visually spot and analyze slow MongoDB operations</a> was originally published in <a href="https://medium.com/idealo-tech-blog">idealo Tech Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>