Not able to see the completed query in the Query monitor tool

Couchbase Server
1

Hi,

We are trying to use the Query monitor tool to monitor our slowest queries as specified in https://docs.couchbase.com/server/current/tools/query-monitoring.html#completed-queries. However, when we click on “Completed” in the tool, we have never seen any queries showing up in this UI. We are able to see active queries in the UI. BTW, some of our queries are very slow, took more than 4 min to complete. Can you please advise what maybe the issue with the UI not displaying the completed query? Thanks.

BTW, in the document it mentioned about the following:

//////////
view the table of completed queries whose runtime exceeded a system-specified threshold (default 1 second).
/////////

How do we check this system-specified threshold using the UI? Thanks.

Jessy

2

Completed requests only keeps queries that taken more than 1sec. If query service is restarted those will be lost.

You can connect query service and issue select * from system:completed_requests;

3

@vsr1 Thanks for the reply. However, as I mentioned in my previous post, we do have queries taking more than 1 min to complete. We don’t see these queries show up in the “Completed” Query monitor UI. Do you know why those queries don’t show up in the UI? In fact, we have a lot of queries taking more than 1 sec to complete. However, nothing shows up in the “Completed” Query monitor UI.

4

Another observation from our test, if we run

//////////////
select * from system:completed_requests
/////////////

The above query takes a long time to run. We waited for 5 mins and we decided to kill this query. We then try to run

//////////
select * from system:completed_requests limit 3
/////////

the query did return with the completed queries in the response.

I suspect that select * from system:completed_requests may have too many completed queries and this may cause the Query Monitor UI NOT displaying anything at all when the “Completed” option is selected. Is this a known issue of the Query Monitor UI? If yes, is there any fixes/workaround to have the UI working?

5

Which version of couchbase are you running? There was a problem with slow queries for completed_requests. @Marco_Greco, didn’t you work on that?

6

Fixed in 5.5.3 IIRC - but it only applies if you have multiple N1QL nodes.

7

@Marco_Greco Just need to clarify. Do you mean that this problem with the Query monitor UI will only occur if there are multiple nodes for the cluster? The version of couchbase we are using is Enterprise Edition 6.0.0 build 1693. Does this version NOT contain your fixes? BTW, we do have multiple nodes defined for the cluster.

8

Yes - you do need multiple query nodes for the issue to appear, and no the fix would not be in 6.0.0.
I coded the final fix in the 6.0 family on Sep 14th, so it should be in 6.0.1.

9

Thanks, @Marco_Greco. If we can not upgrade to 6.0.1 at this point, what other options we can have to work around the issue? We are trying to do performance test on our application, we need to be able to find all the queries which has slow response time.

10

you could use the admin endpoints directly on each individual node

curl http://query node ip:8093/admin/completed_requests -u Administrator:password

11

@Marco_Greco Will the admin endpoints you refer to:

curl http:// *query node ip* :8093/admin/completed_requests -u Administrator:password

return similar output as we run query like the following in the UI:

select * from system:completed_requests where STR_TO_DURATION(serviceTime) > 1000000000
AND state=“completed”

BTW, in the system:completed_requests, what are the possible values of the state field?

12

yes - it will give you as a json document all requests that have been logged in that node, so by definition they would be requests that have lasted more than 1 billion nanosecond (unless you have changed the threshold).
Re state, it can have multiple values - I was hoping that we’d have already documented them in

https://docs.couchbase.com/server/6.0/n1ql/n1ql-rest-api/admin.html#_requests

but we only have a cursory definition
anyway, the values of interested to you are “completed” (with or without errors) “cancelled” (the request has been terminated via a delete from system:active_requests) or “closed” (the client closed the connection, so the request has been aborted because there’s nothing to give back results to).

13

@Marco_Greco thanks for the info.