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.

Author

Posted by Matthew Groves

Matthew D. Groves is a guy who loves to code. It doesn't matter if it's C#, jQuery, or PHP: he'll submit pull requests for anything. He has been coding professionally ever since he wrote a QuickBASIC point-of-sale app for his parent's pizza shop back in the 90s. He currently works as a Senior Product Marketing Manager for Couchbase. His free time is spent with his family, watching the Reds, and getting involved in the developer community. He is the author of AOP in .NET, Pro Microservices in .NET, a Pluralsight author, and a Microsoft MVP.

Leave a reply