Blog Photo

dbKoda 0.9 new features

  • Jan 15, 2018
  • Guy Harrison

The dbKoda team is very excited and very relieved to announce the latest release of dbKoda 0.9.0. As with all releases of dbKoda, this version is 100% open source, free for use in any circumstances and totally cruelty-free.

In this release we fixed some bugs and created some other bugs but hopefully did more of the former than the later. In addition, we added some significant new features:

  1. The Index Advisor suggests indexes that might help optimise selected queries. It also lets you know if there are indexes that might be safely removed.

  2. It is now possible to construct insert/update and delete statements from the database tree

  3. dbKoda now supports SSH terminal connections to a remote host and also to the local desktop

  4. We now support issuing SQL queries against a MongoDB database using Apache Drill

The index advisor

dbKoda can advise you on indexes that should be created to avoid COLLSCAN and SORT steps in execution plans. This functionality is accessed from the explain plan panel. If you have a query that you want to optimize, start by generating an explain plan using the ? toolbar button (click on the image to see it in full screen):

Query1.png

In this case, the explain generated shows both COLLSCAN and SORT steps:

Explain.png

Clicking the Index Advisor button generates suggested indexes. Because this query contains an $or condition we suggest two indexes. Note also we suggest dropping an existing index. This is because the index on {Rating:1} is now superseded by the proposed index on {Rating:1, Length:-1}:

Advisor.png

Clicking the Add Code Snippet button moves the suggested index creation statements to the code editor where you can run them. Once we create these indexes we can re-execute the explain to see the effect. As we hoped, the COLLSCAN and SORT steps are now replaced by index-based access paths.

NewPlan.png

Sometimes a COLLSCAN might actually be the most effective choice and MongoDB might decline to use the new index. The breakeven points for index scans vs collection scans were discussed in this blog post. However, in general MongoDB is strongly inclined towards indexes and most of the time it will use indexes in preference to a collection scan. Therefore, you should always benchmark a new index to make sure it is a valid improvement.

Remove unnecessary indexes

When we add indexes in the index advisor we let you know if any existing indexes are rendered unnecessary by the new indexes. You can now also check all existing indexes and remove any that are redundant. For instance if there is an index in {a:1,b:1} and another on {a:1}, we will suggest dropping the index on {a:1}.

You invoke this functionality from the database tree action Drop Unnecessary Indexes:

DropIndexes1.png

We’ll generate a list of dropIndex statements that you can execute:

DropIndexes2.png

Update/Insert/Delete support

You can create insert, update or delete statements from a fill-in the blanks form in the database tree. This works in a similar way to the simple query builder. You fill in a form and we generate the appropriate MongoDB commands in the editor. For instance, here we are generating an update operation:

update.png

Remote SSH terminal

You can do most MongoDB administration tasks from the mongo shell, but sometimes there’s no alternative to logging onto the remote host to perform operating system tasks. We now provide an SSH terminal that connects to the mongoDB host allowing you to start or stop mongo instances, examine OS level configuration, accidentally remove the operating system, and so on.

To use the SSH terminal, you need to specify SSH credentials as shown here:

SSHSetup.png

When the connection is established an SSH tab will be included in the output panel:

SSH2.png

You can execute pretty much any OS command here. For instance you could execute top to see operating system statistics and top processes:

top.png

SSH is supported natively on Linux and MacOS. Windows 10 provides an SSH server as a optional package and it’s expected that future version of Windows will support SSH natively. For information on using SSH on windows check out this FAQ entry.

You can also open a terminal window to your local desktop machine. This might be handy if you want to run mongo command line tools, invoke a local editor, etc.

Support for Drill SQL (experimental)

We now allow you to issue SQL commands against your MongoDB host using Apache Drill. This feature is a bit “experimental” - it is dependent both on the Apache Drill binaries and requires Java be installed on your local host.

To use this feature, select Query Database with Apache Drill from a database node:

Drill1.png

If this is the first time you’ve invoked Drill, there’ll be a substantial delay while the Apache Drill binary download. You’ll also have to have Java installed before Drill can run. Once Drill is downloaded, you’ll be able to open a Drill SQL editor windows and issue SQL SELECT queries against the MongoDB database:

Drill2.png

See also This blog post for some details on how Drill SQL works on embedded MongoDB arrays.

Give it a try!

We think these new features add to what is already a pretty broad set of useful (and free :-) ) dbKoda features. If you’re not aware, here’s a few of the features we’ve introduced in the last couple of releases:

  • Drag and drop aggregation pipeline Builder
  • Graphical breakdown of MongoDB storage from server/database/collection/embedded objects
  • Tabular, JSON and Chart displays for query output
  • Converter than translates MongoDB shell code into Node.js compatible code
  • And much more…

We’d love to know what you think of dbKoda, download it at dbKoda.com, and let us know what you think at Facebook, Twitter, Github or our support channel.