Same N1QL query sometimes works and sometimes not

SQL++
1

Hello everyone,

I’m running the same query several times in a Couchbase cluster with three nodes.
The query is like this:

SELECT ARRAY x FOR x IN OBJECT_PAIRS(main.`subobject`) 
WHEN x.name = 'val1' OR 
x.name = 'val2' OR 
x.name = 'val3' OR 
x.name = 'val4' OR 
x.name = 'val5' END AS x
FROM mybucket 
use keys ["k1","k2", "k3", "k4"]

I have a primary index for the keys.

Sometimes it responds with a good response time (elapsed 286.3 ms, result size 2410), but sometimes it seems like frozen, without answering.
My question is, in what logs should I look to find out why the same query is frozen in different executions?

Thank you very much

2

The query has USE KEYS, so it will not use any Index. It directly go to Data Node and fetch the documents.
Try with CB version 4.6.3 or higher

3

Thank you for the response,

But the problem that i want to solve is not about using or not using indexes, but about why a query performs well one time but not always…

And how to know the better way to monitor or understand possible issues with logs or metrics or whatever.

Is my problem related with conf? or with RAM quota? or related with my (for sure :wink: ) bad written queries?

That’s what I want to know.

Thanks

4

The query does Fetch as cluster has 3 nodes query might went to different query service each time and one of them might have issue . Try directly connect to query service and see which one had issue and restart that Or Try 4.6.3 or higher and see if you still have issue.