got index?
DESCRIPTION
Do all your queries use proper indexes? Really? A real life experience: In this talk I will reveal what might happen to your system if your queries do not use indexes and how to avoid this situation. I will show you different "built-in" possibilities to prevent missing indexes, and why they did not work for us! To solve the indexing issues we encountered at our company, I created a tool called "Mongo Query Inspector", which will be the main focus in this talk.TRANSCRIPT
Investigation
1
2
It is not cool when queries do not use indexes
3
1
notablescan
2
Regression tests will fail on all CI environments due to
missing index exceptions
notablescan can only be set on the mongod
process
Integration andacceptance tests
fail due to missingindexes in test DBs
3
ENABLEPROFILING
Set profiling level to 1
in order to log all queries that run longer
than 100ms
If there isno traffic,queries without index
only need 2ms
4 explain()
We could use theexplain()
command to analyzeQueries
query
A TOOL THAT:
logs queries that did not use an index
inspectsexecuted
queries formissingindexes
___is writtenin node.js
A TOOL THAT:
logs queries that did not use an index
inspectsexecuted
queries formissingindexes
___is writtenin node.js
A TOOL THAT:
Logs queries that didnot use an index
inspectsexecuted
queries formissingindexes
is writtenin node.js
Application
MongoDB
system.profile
Mongo Query
Inspector
execute queries
collect profiling data (profiling level: 2)
read profiling data
Application
MongoDB
system.profile
Mongo Query
Inspector
execute queries
collect profiling data (profiling level: 2)
read profiling data
Application
MongoDB
system.profile
Mongo Query
Inspector
execute queries
collect profiling data (profiling level: 2)
read profiling data
run explain on query
Query
Explainresult
analyzeexplain result
Keep calm andcarry on!
log queryand fields toapply index
on
Index missing?
YES NO
run explain on query
Query
Explainresult
analyzeexplain result
keepcalm andcarry on!
log queryand fields toapply index
on
Index missing?
YES NO
Integrate the Inspector in your build pipeline…
SUMMARY:
Problem approach Solution
notablescanprofilingexplain()
SUMMARY:
Problem approach Solution
notablescanprofilingexplain()
SUMMARY:
Problem approach Solution
notablescanprofilingexplain()
Index optimization
detectobsolete indexes
contributers
