StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is searching the needle in a haystack. We'd need some output of <code>explain()</code> for those queries that don't perform well. Unfortunately, even that would fix the problem only for that particular query, so here's a strategy on how to approach this:</p> <ol> <li>Ensure it's not because of insufficient RAM and excessive paging</li> <li>Enable the DB profiler (using <code>db.setProfilingLevel(1, timeout)</code> where <code>timeout</code> is the threshold for the number of milliseconds the query or command takes, anything slower will be logged)</li> <li>Inspect the slow queries in <code>db.system.profile</code> and run the queries manually using <code>explain()</code></li> <li>Try to identify the slow operations in the <code>explain()</code> output, such as <code>scanAndOrder</code> or large <code>nscanned</code>, etc.</li> <li>Reason about the selectivity of the query and whether it's possible to improve the query using an index <em>at all</em>. If not, consider disallowing the filter setting for the end-user or give him a warning dialog that the operation might be slow.</li> </ol> <p>A key problem is that you're apparently allowing your users to combine filters at will. Without index intersectioning, that will blow up the number of required indexes dramatically.</p> <p>Also, blindly throwing an index at every possible query is a very bad strategy. It's important to structure the queries and make sure the indexed fields have sufficient <strong>selectivity</strong>. </p> <p>Let's say you have a query for all users with <code>status</code> "active" and some other criteria. But of the 5 million users, 3 million are active and 2 million aren't, so over 5 million entries there's only two different values. Such an index doesn't usually help. It's better to search for the other criteria first, then scan the results. On average, when returning 100 documents, you'll have to scan 167 documents, which won't hurt performance too badly. But it's not that simple. If the primary criterion is the <code>joined_at</code> date of the user and the likelihood of users discontinuing use with time is high, you might end up having to scan <em>thousands</em> of documents before finding a hundred matches.</p> <p>So the optimization depends very much on the data (not only its <strong>structure</strong>, but also the <strong>data itself</strong>), its internal correlations and your <em>query patterns</em>.</p> <p>Things get worse when the data is too big for the RAM, because then, having an index is great, but scanning (or even simply returning) the results might require fetching a lot of data from disk randomly which takes a lot of time.</p> <p>The best way to control this is to limit the number of different query types, disallow queries on low selectivity information and try to prevent random access to old data.</p> <p>If all else fails and if you really need that much flexibility in filters, it might be worthwhile to consider a separate search DB that supports index intersections, fetch the mongo ids from there and then get the results from mongo using <code>$in</code>. But that is fraught with its own perils.</p> <p>-- EDIT --</p> <p>The explain you posted is a beautiful example of a the problem with scanning low selectivity fields. Apparently, there's a lot of documents for "nickey@acme.com". Now, finding those documents and sorting them descending by timestamp is pretty fast, because it's supported by high-selectivity indexes. Unfortunately, since there are only two device types, mongo needs to scan 30060 documents to find the first one that matches 'mobile'.</p> <p>I assume this is some kind of web tracking, and the user's usage pattern makes the query slow (would he switch mobile and web on a daily basis, the query would be fast).</p> <p>Making this particular query faster could be done using a compound index that contains the device type, e.g. using</p> <pre><code>a) ensureIndex({'username': 1, 'userAgent.deviceType' : 1, 'timestamp' :-1}) </code></pre> <p>or</p> <pre><code>b) ensureIndex({'userAgent.deviceType' : 1, 'username' : 1, 'timestamp' :-1}) </code></pre> <p>Unfortunately, that means that queries like <code>find({"username" : "foo"}).sort({"timestamp" : -1});</code> <a href="https://stackoverflow.com/questions/19516524/why-mongodb-cannot-use-a-compound-index-that-is-much-similarnot-exact-to-the-q/19516736#19516736">can't use the same index anymore</a>, so, as described, the number of indexes will grow very quickly.</p> <p>I'm afraid there's no very good solution for this using mongodb at this time.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload