Query performance monitoring with Couchbase?

Couchbase Server
1

Hi,

We will be using .NET but I didn’t want to post it there since I want to see if there is anything we can see from the Couchbase Database server side.

Is there a way for Couchbase to be able to monitor what queries are running slow/long/takes a lot of CPU/doing a lot of reads/etc? Say an application is connected to the Couchbase Database and running a query slowly. Without looking on the application logs, is there a way that Couchbase will show this information – either via command line or the web console? I believe cbstats is looking at server/node/bucket-level metrics but not necessarily at the query level.

Coming from Oracle, there are a lot of tools available that will show query-level information. Does Couchbase have anything similar?

I believe I read that with 4.5DP, there is a Query Monitor process that might provide some details, but I’m not sure to what extend. I also read that with the Java SDK, there is a QueryMetrics object that has the info() method that might have some details. Is that only available in Java and not .NET? With that said, that relies on the application to put the information into a log.

Thank you.
Regards, Steve

2

I assume you have already seen these new additions in 4.5 (developer preview at the moment)
http://developer.couchbase.com/documentation/server/4.5-dp/query-monitoring.html

There is also an existing UI capability that shows the high level state of the query executions for the cluster or per node.

Untitled.jpg2880×1800 436 KB

We are working on expanding the list as well. For your case, are you looking for expensive queries running right now? or are there others that you find useful in Oracle that you’d like to see here?
thanks
-cihan

3

Hi Cihan,

Will it show me which query is slow (for example)? Also, is this for both map reduce and N1QL?

Oracle has AWR, ASH, and Statspack (outside of the internal tables/views). At glance, it’ll show me what’s the top 10 queries (elapsed time, CPU time, reads, writes, etc). With Oracle, there is a SQL_ID, which would allow me to drill down to the individual query’s execution plan. Then I can see if there is something wrong (e.g., no index, etc).

Thank you.
Regards, Steve

4

Hi Steve,
Detecting which query is slow is an application specific issue as we don’t yet know what is unusual for a given query. You can get things like request start time and total execution time as of now to see if a query is pushing over a threshold using active_requests. For the index usage, you can look at the explain output in our case “explain select …” will give you the index usage.

Your asks are all great.The level of detail you’d like isn’t here but it is all in our plans.
thanks
-cihan

5

Thanks Cihan.
That’s understandable. Maybe in the future. However, it’ something that we’ll have to plan out for in design. Much appreciated for your time in responding to my questions! :slight_smile:

Regards, Steve