Frequent query timeout

SQL++
, , ,
1

I have a 2 node cluster environment (community edition 6.0.0 build 1693) where both nodes are running data, index, search and query services. One of the buckets have 70000 items and other two have fewer than 200 items. The larger bucket has used only 10% of its allotted memory quota. Overall the master node has consumed 75% of the RAM and CPU utilization on both instances is around 8%. So, I don’t see any constraints from the number of documents and system resource perspective. These documents are relatively small with sub objects of 10-12 short string fields.

Each document has a _id field that keeps Meta().Id of the document. I have created index on Meta().Id and bucket(_id)

Problem is with frequent timeout on searches. With such low number of documents, I would expect reasonably god speed even with just #primary indexes.

A search like "SELECT * from bucket where Meta().id LIKE “12345567.%” ORDER BY bucket.field2 " times out very often and many similar searches where indexes are used correctly. Query monitor shows that these searches have taken 6+ minutes in most cases. Sometimes (when I run on the query console), search completes in 3-4 secs.

I also see a lot of java.util.concurrent.TimeoutException in my java SDK side.

I have set connectTimeout: 20s, kvTimeout: 5s, autoreleaseAfter: 5s, queryTimeout: 150sec, keepAliveInterval: 50s, keepAliveTimeout: 5s
Im a little lost and not finding a very good way to track these issues. The TimeoutException manifests as improper working of our application. Numerous logs in the log folder are not very helpful to isolate the problem. One of the common ones is:

java.util.concurrent.TimeoutException
        at rx.internal.operators.OnSubscribeTimeoutTimedWithFallback$TimeoutMainSubscriber.onTimeout(OnSubscribeTimeoutTimedWithFallback.java:166)
        at rx.internal.operators.OnSubscribeTimeoutTimedWithFallback$TimeoutMainSubscriber$TimeoutTask.call(OnSubscribeTimeoutTimedWithFallback.java:191)
        at rx.internal.schedulers.EventLoopsScheduler$EventLoopWorker$2.call(EventLoopsScheduler.java:189)
        at rx.internal.schedulers.ScheduledAction.run(ScheduledAction.java:55)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)

How do I track these performance issues? Why is couchbase taking such a time for query even for such a small dataset?
(sidenote: I migrated this from mongodb where it just used to run on a single instance without any additional tuning)

2

A covering index might be helpful here. As is, your query is doing a complete lookup of every document (since you are using SELECT *). If you don’t really need every field, a covering index alone is going to give you a boost.

3

Id must be lower case .

If query qualifies too many documents you can get document keys and use SDK fetch the documents.

CREATE INDEX ix1 ON bucket(_id,field2);
SELECT META(d).id 
FROM bucket AS d
WHERE d._id LIKE  "12345567.%"
 ORDER BY d.field2 ;
4

Thank you all for the suggestions. Covering index alone didn’t help. In fact I have an index on id and and another one on (id, field2). I didn’t see the query using second index though the query is expected to order by field2.

@vsr1, your suggestion of getting individual documents and fetching them works, though it involved multiple queries. BTW, why do I see frequent TimeoutExceptions? What parameters should I use to resolve that?