Mongodb index and query analyzer-dex

Dex introduces mongodb index and query analyzer dex. It is a MongoDB performance adjustment tool that compares MongoDB log files and index entries and provides index recommendations. Currently, you must provide a URI to connect to the database. Dex only recommends full indexes, not partial indexes. Windows platforms are not supported. Dex working principle dex is mainly used during operation

Dex introduces mongodb index and query analyzer dex. It is a MongoDB performance adjustment tool that compares MongoDB log files and index entries and provides index recommendations. Currently, you must provide a URI to connect to the database. Dex only recommends full indexes, not partial indexes. Windows platforms are not supported. Dex working principle dex is mainly used during operation

Dex introduces mongodb index and query analyzer dex. It is a MongoDB performance adjustment tool that compares MongoDB log files and index entries and provides index recommendations. Currently, you must provide a URI to connect to the database. Dex only recommends full indexes, not partial indexes. Windows platforms are not supported. Dex working principle dex performs the following three steps during Operation: 1. parse query 2 .? Use an existing index to determine the current query. 3 .? If you find that the index is incorrect, we recommend the appropriate index Step 1: parse the query

Dex parses the query into the following categories:

• EQUIV? -Common query by value, for example: {a: 1}
• SORT? -Sort operation, for example:. sort ({a: 1 })
• RANGE? -Range Query, for example: Specifically: '$ ne', '$ gt', '$ lt', '$ gte',' $ lte ',' $ in ', '$ nin',' $ all', '$ not'
• UNSUPPORTED
  • Combined Query, such as: $ and, $ or, $ nor
  • Nested queries except RANGE

Step 2: determine the current index

There are two criteria to find the index required for the query.

• Coverage (none, partial, full )?-Coverage indicates the index, which contains three values in brackets. None indicates that no index is overwritten. Full indicates that all fields in the query can be indexed. Partial indicates the situation between none and full.
• Order (ideal or not )?-Order is used to determine whether the index Order is ideal. The ideal index sequence should be: Equivalence ○ Sort ○ Range. It is worth noting that the geographical location index will only be analyzed, but no suggestions for improvement will be provided.

Step 3: recommend appropriate Indexes

Through the above two steps, we can have an understanding of the possible use of indexes in a query. Dex generates an Optimal Index for this query. If this index does not exist and the query condition does not includeUNSUPPORTEDThen, Dex will make corresponding index optimization suggestions.

Common usage of dex

Specify the log file and provide the necessary verification if verification and database are enabled

> dex -f my/mongod/data/path/mongodb.log mongodb://myUser:myPass@myHost:12345/myDb

Or enable db. setProfilingLevel (1), disable profiling after analysis, db. setProfilingLevel (0)

> dex -p mongodb://myUser:myPass@myHost:12345/myDb

Db or collection filtering dex supports filtering analysis by specifying a specific db or collection. If you want to analyze multiple databases, you must provide a connection URI to the admin database.

> dex -f my/mongod/data/path/mongodb.log -n "myFirstDb.collectionOne" mongodb://myUser:myPass@myHost:12345/myFirstDb> dex -p -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

The query time (millis) filter dex also supports filtering by specifying the query execution time. If the query time is less than the specified time, no analysis is performed. -S/-- slowms parameter to specify the time. The Unit is millis.

> dex -f my/mongod/data/path/mongodb.log -s 400> dex -p -n "*.collectionOne" mongodb://myUser:myPass@myHost:12345/admin --slowms 1000

The monitoring mode is specified? -W/-- watch parameter to obtain the current information in real time.

> dex -w -f my/mongod/data/path/mongodb.log mongodb://myUser:myPass@myHost:12345/myDb

When you use the-w/-- watch and-p/-- profile to monitor the system. profile collection, you must specify a single library.

> dex -w -p -n "myDb.*" mongodb://myUser:myPass@myHost:12345/myDb

If profiling is not enabled, dex enables profiling of level 1. Other useful options-t/-- timeout-Logfile (-f )? Part of a large log file is truncated. Unit: Minute. -- Nocheck? Ignore existing indexes and recommend indexes for all queries. Environment requirements mongod 2.0.4 or later dependent libraries include:

• Pyyaml
• Pymongo
• Dargparse

Dex Installation

# easy_install pip# pip install dex

Result output description

• RunStats-analysis log or profile Data Statistics Section
  • RunStats. linesRead-how many pieces of data (log or profile) are sent to Dex.
  • RunStats. linesAnalyzed-number of entries dex successfully extracts the query and tries to give the suggested quantity.
  • RunStats. linesWithRecommendations-The number of lines that prompted and cocould potentially benefit from an index recommendation.
  • RunStats. dexTime-The time Dex was initiated.
  • RunStats. logSource-Path to logfile processed. Null for-p/-- profile mode.
  • RunStats. timeRange-The range of times passed to Dex. Primary des all lines read.
  • RunStats. timedOut-True if the Dex operation times out per the-t/-- timeout flag.
  • RunStats. timeoutInMinutes-If timedOut is true, this contains the time. Dex provides information and statistics for each unique query in the form of a. A recommendation between des:
• Results-query report, which contains the index recommendations.

? The query report array returned by the standard output Dex is used as the result. Each query report is a unique query marked as 'querymask '.. Each report includes:

• QueryMask-query mode, with values masked ($ query for query component, $ orderby for sort component)
• Namespace-The MongoDB namespace in which to create the index, in the form "db. collection"
• Stats-specific query statistics aggregated from each query occurrence.
• Stats. count-The total number of queries that occurred.
• Stats. avgTimeMillis-The average time this query currently takes.
• Stats. totalTimeMillis-The sum amount of time consumed by all of the queries that match the queryMask.
• Recommendation-A fully-formed recommendation object.
  • Recommendation. index-The index recommended.
  • Recommendation. namespace-The recommendation namespace.
  • Recommendation. shellCommand-A helpful string for creating the index in the MongoDB shell.

The instance is as follows:

Original article address: mongodb index and query analyzer-dex. Thank you for sharing it.