Profiling and monitoring for N1QL are a big part of the Couchbase Server 5.0 release (now available for download).

Query Workbench

Once again, I’ll be focusing on Query Workbench for this blog post.

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

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 previous posts. 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.

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 will fit in the blog post).

Visual profiling part 1

Visual profiling part 2

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 now 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).

Thank you for your feedback!

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

Your feedback on the developer preview versions released earlier this year helped to drive improvement. Thank you!

If you have questions, the best way to contact me is either Twitter @mgroves or email me matthew.groves@couchbase.com.

Posted by Matthew Groves, Developer Advocate

Matthew is a Developer Advocate for Couchbase, and lives in the Central Ohio area. He has experience as a web developer as a consultant, in-house developer, and product developer. He has been a regular speaker at conferences and user groups all over the United States, and he has written AOP in .NET for Manning Books. He has experience in C# and .NET, but also with other web-related tools and technologies like JavaScript and PHP. You can find him on Twitter at @mgroves.

Leave a reply