Building Indexes
Hybrid Index Build(new in 4.2)
db.movies.createIndex({title: 1})
db.movies.createIndex({title: 1}, {background: true})
- One index build type
- We can now build indexes quickly and without the need to lock the database.
MongoDB now only has one index buid type available.
Query Plans
What are query plans?
MongoDB plans how to perform a query using some indexes.
db.restaurants.find({"address.zipcode": {$gt: '50000'}, cuisine: 'Sushi'}).sort({stars: -1})
// index
{"address.zipcode": 1, cuisine: 1}
Query plans are cached so that plans do not need to be generated and compared against each other every time a query is executed.
Forcing Indexes with Hint
Overriding MongoDB’s default index selection with hint()
db.people.find(
{name: "John Doe", zipcode: {$gt:"63000"}}
).hint({name: 1, zipcode: 1})
What is the method that forces MongoDB to use a particular index?
Resource Allocation for Indexes
Basic Benchmarking Part 1
- Public Test Suite
- Specific or Private Testing Environment
Tools:
- sysbench
- iibench-mongodb
Database Server Benchmarking
- YCSB
- TPC
Distributed Systems Benchmarking
- Linearization
- Serialzation
- Fault tolerance
- HiBench
- Jepsen
Basic Benchmarking Part 2
- POCDriver
What type of strategy and tools should we be using to performance benchmark a MongoDB installation?
Publicly available tools, including correct database variations
Lab 3.1: Explain Output
In this lab you’re going to determine which index was used to satisfy a query given its explain output.
The following query was ran:
> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint(REDACTED)
Which resulted in the following output:
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "m201.restaurants",
"indexFilterSet": false,
"parsedQuery": "REDACTED",
"winningPlan": {
"stage": "SORT",
"sortPattern": {
"name": 1
},
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": "REDACTED",
"indexName": "REDACTED",
"isMultiKey": false,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 1,
"direction": "forward",
"indexBounds": "REDACTED"
}
}
}
},
"rejectedPlans": [ ]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 3335,
"executionTimeMillis": 20,
"totalKeysExamined": 3335,
"totalDocsExamined": 3335,
"executionStages": "REDACTED"
},
"serverInfo": "REDACTED",
"ok": 1
}
Given the redacted explain output above, select the index that was passed to hint.
{ "address.state": 1, "stars": 1, "name": 1 }