Dex: A MongoDb query performance tool

Dex is a MongoDB performance tuning tool, open-sourced under the MIT license, that compares logged queries to available indexes in the queried collection(s) and generates index suggestions based on a simple rule of thumb. (original 2012-06-19; updated 2012-07-19)

Transcript

1. Eric Sedor Index Automation and Dex July 2012
2. A complete DBaaS solution for MongoDB 1
3. 2 1.  Introduction 2.  Dex demo 3.  Index lessons 4.  Dex in detail
4. Why Dex? 3 Indexes can improve query performance by 2-3 orders of magnitude – 1000ms to 0ms! Bad queries don’t just get in their own way, they get in the way of other things too. •  Writelock •  Queued Operations •  Page Faults
5. Dex is Easy 4 > sudo pip install dex dex [options] uri > dex -f my/mongod/data/path/mongodb.log mongodb://myUser:myPass@myHost:12345/myDb > dex –p mongodb://myUser:myPass@myHost:12345/ myDb
6. Demo 5
7. Dex’s Output 6 ... { "index": "{'name': 1, 'class': 1}", "namespace": ”mongoquest.adventurers" "shellCommand": "db.adventurers.ensureIndex({'name': 1, 'class': 1}, {'background': true})" } ... Total lines read: 7 Understood query lines: 7 Unique recommendations: 5 Lines impacted by recommendations: 5
8. Index Basics 7 db.adventurers.find({'name': 'Eric', 'class': 'Wizard'}).explain() db.adventurers.getIndexKeys() db.adventurers.getIndexes() db.adventurers.ensureIndex({'name': 1, 'class': 1}, {'background': true}) db.adventurers.dropIndex({'name': 1, 'class': 1})
9. The Profile Collection 8 The system.profile is a record of operations. Be strategic about when you enable profiling. •  To start: db.setProfilingLevel(1) Level 2 is noisy but: •  Shouldn’t affect Dex •  Can give insight •  db.system.profile.find() •  .sort({millis: -1}) is helpful •  .find({ns: “mongoquest.adventurers”}) •  .find({op: {$in: [“query”, “update”, “command”]}) •  db.setProfilingLevel(0) when done •  Then db.system.profile.dropCollection()
10. Know Thy B-tree 9 •  An index is a b-tree that maps a sequence of key values to a list of document pointers. •  The index keys describe the behavior of the b-tree from left-to- right. That means index order influences: •  How quickly the scope of possible results is pruned: Here is where your data's cardinality weighs in. •  How applicable an index is to a given query: A query that doesn't include the first field(s) in the index cannot use the index. * “Eric” “Wizard” “Engineer” “Ben” “Fighter” “Noble” { name: 1, class: 1 }
11. Everything else is gotchas 10 •  Trickier with range operators like $in, $nin, $gte, $lte, etc. That just makes key order more important. •  Add a sort and you are in danger of scanAndOrder .explain will reveal {scanAndOrder: true} if MongoDB is re-ordering to satisfy a sort clause. Trick to avoid scanAndOrder: Push your sort key “further left”/”higher” in the index, above the ranges but below equivalency checks. Why?
12. Ranges, Sorts, and Index Key Order 11 carsOwned country carsOwned country document visitation order unsorted 1 2 1 2 MongoDB performs a full, in-memory scanAndOrder { name: “Bob”, country: “A”, carsOwned: 1 } { ...“Charlie”, ... “A”, ... 2 } { ...“Holger”, ... “G”, ... 1 } { ...“Dietmar”, ... “G”, ... 2 } sorted Visitation order satisfies sort; no scanAndOrder needed { ...“Charlie”, ... “A”, ... 2 } { ...“Dietmar”, ... “G”, ... 2 } A G { name: “Bob”, country: “A”, carsOwned: 1 } { ...“Holger”, ... “G”, ... 1 } 1 2 1 A G G 1 2 A optimal index: {carsOwned: 1, country: 1} document visitation order suboptimal index: {country: 1, carsOwned: 1} db.drivers.find({country: {$in: [”A”, “G”]}).sort({carsOwned: 1}) Diagram at bit.ly/mongoindex
13. Slow Hell (like normal hell only slower) 12 If everything is slow, what do we act on!? Be warned if you: •  Expose query construction to the app user •  Try to use MongoDB like an RDBMS •  Use a lot of distinct query patterns Bad Indexing → Memory Apocalypse Without warning, large portions of your working data topple out of memory and must be swapped back.
14. How Dex Works 13 1.  Dex iterates over the input (log or profile). 2.  A LogParser or ProfileParser extracts queries from each line of input. 3.  Dex passes the query to a QueryAnalyzer. 4.  The QueryAnalyzer compares the query to existing indexes, and if an index meeting Dex's criteria does not exist... 5.  Dex suggests its best index
15. The Heart of Dex 14 The order of fields in an index should be: 1) Equivalency checks {a: 1} 2) Sorts .sort({b: 1}) 3) Range checks {c: {$in: [1, 2]}}
16. One More Option (-n) 15 > dex -f my/mongod/data/path/mongodb.log -n "myFirstDb.collectionOne" mongodb:// myUser:myPass@myHost:12345/myFirstDb > dex -f my/mongod/data/path/mongodb.log -n "*.collectionOne" mongodb:// myUser:myPass@myHost:12345/admin > dex -f my/mongod/data/path/mongodb.log -n "myFirstDb.*" -n "mySecondDb.*" mongodb:// myUser:myPass@myHost:12345/admin Note that we auth to admin to run against more than one db!
17. Dex’s Guts 16 {'parsed': ..., 'namespace': db.adventurers, 'queryAnalysis': {analyzedFields': [{'fieldName': 'name', 'fieldType': 'EQUIV'}, {'fieldName': 'class', 'fieldType': 'EQUIV'}, 'fieldCount': N, 'supported': true|false}, 'indexAnalysis': {'fullIndexes': [], 'partialIndexes': [{'name': 1}] 'needsRecommendation': true|false }, 'recommendation': {'namespace': 'mongoquest.adventurers' 'index': {'name': 1, 'class': 1} 'shellCommand': 'db.ensureIndex...' } }
18. Future Plans 17 •  Command-Line support for: •  -w/--watch (monitor mode) •  Constrain by time •  Add Dex’s own “SLOW_MS” •  Geospatial Support •  Improved caching/reporting: •  Number of like queries •  Time consumed by queries •  Range of time. •  max/min nscanned/nreturned •  Improved recommendations: •  Group like recommendations •  Group like queries •  Measure cardinality for key ordering
20. 19 Questions? Thank you and good luck out there! eric@mongolab.com www.github.com/mongolab/dex http://blog.mongolab.com/2012/06/introducing-dex-the-index-bot/ http://blog.mongolab.com/2012/07/remote-dex/ http://blog.mongolab.com/2012/06/cardinal-ins/
21. 20 PS We’re hiring