Profiling and Monitoring in Couchbase Server 5.0 Preview (Update)

In March’s developer build, there are some more updates for N1QL query monitoring and profiling.

Go download the March 5.0.0 developer release of Couchbase Server today. Make sure to click the “Developer” tab to get the developer build (DB), and check it out. You still have time to give us some feedback before the official release.

In case you missed it, check out the post I wrote in February about the New Profiling and Monitoring in Couchbase Server 5.0 Preview, as this post very much builds on that.

As before, keep in mind that I’m writing this blog post on early builds, and some things may change in minor ways by the time you get the release.

Query Workbench

Once again, I’ll be focusing on Query Workbench for this blog post. The updates for March are mainly visual changes to the “Plan” view in Query Results.

But just to review, there are other options for running N1QL queries:

• Use the SDK of your choice.
• cbq command line tool.
• REST API N1QL endpoints

Personally, I find the Query Workbench easiest to use, as it more visually presents the profiling.

Profiling Complex Queries

Let’s look at the travel-sample data again, just like I did in last month’s post. I’m using the travel-sample bucket, but I have removed one of the indexes (DROP INDEX travel-sample.def_sourceairport;).

I then execute a N1QL query to find routes between two cities. Let’s use Columbus, Ohio and Denver, Colorado this time.

SELECT r.id, a.name, s.flight, s.utc, r.sourceairport, r.destinationairport, r.equipment
FROM `travel-sample` r
UNNEST r.schedule s
JOIN `travel-sample` a ON KEYS r.airlineid
WHERE r.sourceairport = 'CMH'
AND r.destinationairport = 'DEN'
AND s.day = 0
ORDER BY a.name;

Executing this query (on my single-node local machine) took about 8 seconds this time (as expected), which is too slow.

Visual Breakdown of Profiling

Let’s look at the plan to see what the problem might be (I broke it into two lines so the screenshots would fit in the blog post).

So, as before, the costliest parts of the query plan are the Filter and the Join. We could tell before by looking at the raw numbers and/or the percentages. But in this March release, we have a more visual way to tell: color. The parts of the plan go from gray to tan to gold based on percentages and defined thresholds.

Right now, the thresholds are based on the fraction of the total query time taken by an operation:

• Gray: less than 1% of total time
• Tan/Some gold: 1% – 5%
• Tan/More gold: 5% – 20%
• All gold: 20%

The purpose of this visual profiling is to quickly draw your eye to expensive operations. Then, if you care to know the exact numbers, you can read it in the details (in the diagram or even in the META().plan information).

I’m not going to go through the index creation part again; it’s the same as it was in the last blog post (just recreating the index that I removed for demonstration purposes).

We Still Want Your Feedback!

Stay tuned to the Couchbase Blog for information about what’s coming in the next developer build.

Interested in trying out some of these new features? Download Couchbase Server 5.0today!

We want feedback! Developer releases are coming every month, so you have a chance to make a difference in what we are building.

Do you like this new use of color to help you profile your queries? Now you can give feedback directly from within the Couchbase Web Console. Look for the feedback icon] icon at the bottom right of the screen.

Is something not working right? Please file an issue in our JIRA system at issues.couchbase.com or submit a question on the Couchbase Forums. Or, contact me with a description of the issue. I would be happy to help you or submit the bug for you (my Couchbase handlers let me have a free Kit-Kat for each good bug I submit).