Identify top N queries in couchbase

How to identify top N queries with respect to the following Is there any custom queries available or any other options available like reports that are apart from the query workbench option

  • Top CPU
  • Top I/O
  • Top Elapsed
  • Top Read
  • Top without indexes

N1QL has a rather comprehensive monitoring infrastructure, made up of two parts

  • monitoring keyspaces
  • profiling

By combining the two, you can determine all of what you ask above.
In terms of keyspaces, the two of interest are system:active_requests (all of the requests running or queued right now) and system:completed_requests (requests that have completed but are of interest because of some reason, eg they run beyond a certain threshold).

In terms of monitoring, you can see phase count and times, pr the timings of the whole execution tree.
You can see those in the metrics, but, more importantly you can access them, sort them and aggregate them from the system keyspaces.

If you want to examine only the current queries - all you have to do is query system:active requests.
If you want to examine all the load for a specific amount of time - you turn on saving all requests in completed requests, wait for the specific amount of time, eg 10 minutes, and the select from system:completed_requests.
The other thing that you have to do is turn on profiling.
So as a quick example

curl -u Administrator:password -d '{"completed-threshold": 0, "profile": "phases"}'

This turns on saving phase times and counts and saving requests
Do some work.

select statement, remoteAddr, requestAddr, phaseCounts.fetch from system:completed_requests order by phaseCounts.fetch desc limit 10

would give you the top 10 requests having the top reads from the requests that have completed recently

select statement, serviceTime from system:completed_requests where phaseTimes.scan is missing order by serviceTime desc limit 10

would give you the 10 most costly requests that haven’t done and index scan (or you could order by primaryScan)
You can work out the rest from these examples.

The N1QL monitoring and profiling documentation:

n1ql monitoring

A couple of oldies but goldies to get you going:

query perormance monitoring
optimize N1QL performance
New profiling and monitoring in N1QL V5.0

1 Like

Thanks for providing the detailed information