Share
Embed
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)
Ben Wen
July 17, 2012
Transcript
21 slides
-
2 1. Introduction 2. Dex demo 3. Index lessons 4.
Dex in detail -
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 -
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 -
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 -
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}) -
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() -
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 } -
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? -
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 -
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. -
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 -
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]}} -
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! -
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...' } } -
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 -
19 Questions? Thank you and good luck out there! [email protected]
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/