MongoDB explain() explained

There are many different considerations to be made when running queries in MongoDB. A helpful thing to use in the mongo shell when running a find() operation is to use the explain() method. In this blog post, I’ll take a look at some of the options for explain() and what the results mean.

explain()

As discussed in a previous post on indexing in MongoDB, we can use the explain() method to learn about the selected query plan. This allows for an examination of the performance of a given query. It can be used in the following manner:

db.collection.find().explain()

The information generated can be used to see what index is being used for a query, if the query is a covered query,  and which servers in a sharded collection the query is run against, to name a few.

Three different verbosity modes can be utilized to determine the amount of information provided.

Verbosity modes
  • queryPlanner – the given query provided in the find() method is put through the query optimizer to find the most efficient query. This “winning plan” is then passed to the queryPlanner and the information is returned for the evaluated query. The query is not run in this mode. As a result things like query time, e.g. executionTimeMillisEstimate are true estimates since the query has not been executed.
  • executionStats – when running in this mode, the query optimizer is run and the query is fully executed. The information returned details the results of the are what actually happened during that specific query.
  • allPlansExecution – as the name might suggest, this mode returns information about all possible query plans. While the winning plan is executed and statistics returned for it, other candidate plan information is returned as well. This is the default mode of explain().

The variety of information these different modes provides can be extremely useful. Let’s take a look at some returned results of explain() and walk through what they show.

Results

For this example, I will use a test example database of a blog. The database contains two collections, users and articles, and is running on a single, unsharded, machine. Each collection has, roughly, 550,500 documents and is not indexed beyond the index for _id.

Let’s start with looking at what gets returned from a query for a single username. And take a look at some of the bits and pieces of information provided.

db.users.find( { "username": "User_9"} ).explain()

 

explain output

The parsedQuery section is the query we are exploring. The query stage provides a description of the type of operation that occurred for the winning plan.

Operation Types

  • COLLSCAN – indicates a collection scan occurred for the query, meaning that the query looked at each document to get the results
  • IXSCAN – indicates an index was used for the query
  • FETCH – for retrieving documents
  • SHARD_MERGE – the result of merging data from shards

The stage is a tree structure and can have multiple, child, stages. The direction of the query shows whether the query was performed in a forward or reverse order. The serverInfo section displays information on the server the query was run against and includes, in the version key, the version of the MongoDB database. If the collection was in a sharded environment, each accessed shard would be listed in the serverInfo.

When the command is run using the “executionStats” verbosity mode:

db.users.find({ "username": "User_9"} ).explain("executionStats")

additional information is provided as a result of the query being run on the data.

explain with executionStats

Here we see, among other things, the time the query took to run, along with how many documents were returned, nReturned, and how many documents were examined by the database, totalDocsExamined. As mentioned in my post on indexing, ideally these two numbers should be very close to the same value.

Wrap Up

There is a lot of information available when using the explain() method. It provides some great information about how queries are actually being run and gives an indication as to where a collection can benefit from an index. It should be your first stop when examining slow queries before moving onto other MongoDB tools.

There are a lot of MongoDB specific terms in this post. I created a MongoDB Dictionary skill for the Amazon Echo line of products. Check it out and you can say “Alexa, ask MongoDB what is an index?” and get a helpful response.

Follow me on Twitter @kenwalger to get the latest updates on my postings.

Facebooktwitterredditlinkedinmail

Network Access with MicroPython on an ESP8266

In a previous post we have seen how to set up a WebREPL on a NodeMCU ESP8266 and create it’s own network. This is very handy in a lot of situations. In many other situations, however, there is already a network available for a device to join. With proper network access any machine on the network can use the NodeMCU. Let’s take a look at MicroPython networking and how we can leverage it to connect to the WebREPL interface from a different machine on the network.

Network Access

The first step is to get the NodeMCU ESP8266 connected to our network. We will need to do this from the serial connection since our WiFi settings will soon change. See my post here for how to setup a serial connection with the NodeMCU ESP8266. In taking a look at the networking documentation on the MicroPython site, we see that there is a network module available. MicroPython has made our lives easier, very nice!

With ssid as the name of the network, and password being the network password, we can get network access with the following commands:

import network
wlan = network.WLAN(network.STA_IF)
wlan.active(True)
wlan.connect('ssid', 'password')

Entering each of those commands into the REPL should allow the NodeMCU ESP8266 to connect to the network.

Network Access

 

Test the Connection

If we give the device a moment or two to connect we can get the network configuration for the device with the ifconfig function.

wlan.ifconfig()

 

NodeMCU Network Config

Great! The ESP8266 is now has network access on the 10.0.0.31 network. The other numbers there are the network mask, gateway, and the DNS address, respectively. With the board connected we can enable the WebREPL interface and start the service.

Start WebREPL

Notice the two different IP addresses there. The 192.168.4.1 is the WiFi network the ESP8266 is generating and the 10.0.0.31 address is for the external network. The addresses themselves may be different on your own device based on your network.

Network Connection to WebREPL

Open the WebREPL client, as discussed in this post and use the IP address of the local network instead of the ESP8266 default 192.168.4.1 address. In this example I’ll input ws://10.0.0.31:8266/ into the address box to connect. Enter the password for the WebREPL that you created earlier and start entering Python commands!

WebREPL connection

Over in the terminal window you should also see a notification that there is a WebREPL connection.

Showing WebREPL Connection

Wrap-Up

In this short post we have seen here how we can obtain network access for our NodeMCU ESP8266 to an existing network and access it through the WebREPL client. Now you can access it through the serial interface, it’s own network, or a device on the same network. With this variety of ways to access an ESP8266 device

Follow me on Twitter @kenwalger to get the latest updates on my postings on MicroPython and IoT.

Facebooktwitterredditlinkedinmail