Timeout via Spring Boot Data Couchbase but fast (expected) response via Workbench


#1

System environment:

Couchbase Server: Enterprise 5.5.1
Spring Boot Starter Parent: 2.0.5.RELEASE
Spring Boot Data Couchbase: 3.0.10.RELEASE

I have a strange issue and can’t figure out what the reason might be here.

There is a single couchbase bucket which contains about 52M docs and I created an index for a rather long/complex query (array checks and multiple ranges). The index size is about 1.6G disk size and 1.1G data size with approx. 14M items indexed.

The execution of the exact same n1ql query runs into a timeout (set to 25s) when calling it via the Spring Boot Backend (where the n1ql is configured in the repo). But in the workbench, the execution time is about 400ms using the exact same query, which here is the expected behaviour, but not in the Spring Boot Backend. A few days ago there was no discrepancy between the two, but suddenly there is. I did nothing in between, no change in the query, no library or server updates or anything. The logs created (Logs->[tab]collecting information ) don’t reveal any obvious errors to me.

Does anyone has an idea what this behaviour could be caused by or where to look into it?

Question was also posted at stack overflow


#2

Using Spring Boot and the Java SDK, you’ll have the streaming parser and object creation involved. That takes more resources and time. The default timeout is 75s, if you leave it at the default, does it complete?

I’d recommend turning the log level up to debug/trace in a test where you bootstrap, pause, run a simple query, pause, then run your large query. I’d be looking to see if it’s in the middle of parsing/receiving items when the timeout is hit or is there anything else unusual in logs.


#3

I am not using the SDK but the Spring Data Repository implementation with the @Query annotation where I set the N1QL queries as arguments.

I already set the loglevel to debug and it shows executing the n1ql query I expected. The only difference is, that the query completes after 103 seconds instead of 500ms as it does in the workbench. I can see the expected query executing in the Query monitor while it executes.

Is there any possiblity to analyze executed queries in the console to see the created execution plan and timings, etc.?

Edit: The SDK doctor (https://github.com/couchbaselabs/sdk-doctor) shows not issues.


#4

I additionally implemented the requests with the SDK providing the same query which I also used in the Spring Boot Data repository implementation. The result is the same, slow execution time of around 103s compared to the 500ms via the workbench. I search the logs for any hints but couldn’t find any errors or problems which could point me in the right direction.

Therefore again my question: Where do I have to look (log-wise) if I want to find information about the remote query execution (execution plans, execution timings, etc.)?


#5

Hi @username98764158,

Th query profile info can be requested from the server while querying, this can be set on the N1qlParams.build().profile(N1qlProfile.TIMINGS) and the requested info should be in the N1qlQueryResult.profileInfo(). This is off by default. On spring data, this can be done by directly using CouchbaseTemplate.findByN1QL.