MongoNYC 2012: Indexing and Query Optimization

MongoNYC 2012: Indexing and Query Optimization, Richard Kreuter, 10gen. MongoDB supports a wide range of indexing options to enable fast querying of your data. In this talk we’ll cover how indexing works, the various indexing options, and cover use cases where each might be useful.

Transcript

1. explain() BTreeCursor Locality
2. Indexing for Fun and Profit Richard Kreuter MongoNYC 2012 2
3. What’s in store Indexing is important! Understanding range-based indexing Working with indexes in MongoDB • • • 3
4. Indexes are the single biggest tunable performance factor in MongoDB.
5. Absent or suboptimal indexes are the most common avoidable MongoDB performance problem.
6. So what problem do indexes solve?
8. How do you find a chicken recipe? An unindexed cookbook might be quite a page turner. Probably not what you want, though. • •
9. I know, I’ll use an index!
11. Let’s imagine a simple index ingredient page aardvark 790 ... ... beef 190, 191, 205, ... ... ... chicken 182, 199, 200, ... chorizo 497, ... ... ... zucchini 673, 986, ...
12. How do you find a quick chicken recipe?
13. Let’s imagine a compound index ingredient cooking time page ... ... ... chicken 15 min 182, 200 chicken 25 min 199 chicken 30 min 289,316,320 chicken 45 min 290, 291, 354 ... ... ...
14. Consider the ordering of index keys Chicken, 15 min Chicken, 25 min Chicken, 30 min Chicken, 45 min
15. How about a low-calorie chicken recipe?
16. Let’s imagine a 2nd compound index ingredient calories page ... ... ... chicken 250 199, 316 chicken 300 289,291 chicken 425 320 ... ... ...
17. How about a quick, low-calorie recipe?
18. Let’s imagine a last compound index calories cooking time page ... ... ... 250 25 min 199 250 30 min 316 300 25 min 289 300 45 min 291 425 30 min 320 ... ... ... How do you find dishes from 250 to 300 calories that cook from 30 to 40 minutes?
19. Consider the ordering of index keys 250 cal, 25 min 250 cal, 30 min 300 cal, 25 min 300 cal, 45 min How do you find dishes from 250 to 300 calories that cook from 30 to 40 minutes? 4 index entries will be scanned, but only 1 will match! 425 cal, 30 min
20. Range queries using an index on A, B A is a range ✔ A is constant, B is a range ✔ A is constant, order by B ✔ A is range, B is constant/range ✖ B is constant/range, A unspecified ✖✖ • • • • •
21. It’s really that straightforward.
22. On to MongoDB!
23. All this is relevant to MongoDB. MongoDB’s indexes are B-Trees, which are designed for range queries. Generally, the best index for your queries is going to be a compound index. • •
24. Key info about MongoDB’s indexes A collection may have at most 64 indexes. Almost all queries can use just 1 index ($and/$or queries are the exception). Every additional index slows down inserts & removes, and may slow updates. The maximum index key size is 1024 bytes. • • • •
25. When are indexes applicable? An index on x gets used most places you’d expect constant-value queries on x, range queries on x, $in expressions on x count, distinct, update, findAndModify that select on x, regular expressions similar to /^abc.*/ • • • • • • •
26. But indexes aren’t used sometimes Most negations: $not, $nin, $ne A few other corner cases: $mod, $where Additionally, matching most regular expressions involves scaning all index keys (cf. /a/ or /foo/i). • • •
27. Indexes do special things with arrays { title : “Chicken and Rice”, ingredients : [ “chicken”, “rice” ] } Insert ingredients page chicken 42 ... ... rice 42 ... ... [ “chicken”, “rice”] 42 “MultiKey” Index on ingredients
28. Getting a query’s plan db.rec.find({t:{$lt: 40}}).explain() { "cursor" : "BtreeCursor t_1", ... "nscanned" : 42, ... "n" : 42, "millis" : 0, ... } Pay attention to the ratio n/nscanned!
29. Operational aspects of index builds Building indexes is easy! db.collection.ensureIndex({ ingredient : 1, cookingTime : 1 }) Building indexes is hard! Read through all docs, sort all index keys, write out sorted tables... usually takes a while. You should schedule index builds carefully. • • • • •
30. Rolling out an index build for (s in secondaries) s.restartAsStandalone() s.buildIndex() s.restartAsReplSetMember() s.waitForCatchup() p.stepDown() p.restartAsStandalone() p.buildIndex() p.restartAsReplSetMember()
31. Absent or suboptimal indexes are the most common avoidable MongoDB performance problem... ...so take some time and get your indexes right!
32. To the future! MongoDB will support “index intersection”. This might make mongod’s index selection a bit less predictable, however. It will still be important to construct the right indexes! • • •
33. 10gen is hiring! http://www.10gen.com/jobs