N1QL Query Performance issue with Java SDK

Java SDK
1

Hi,

I have created flink (java ) to stream data from kafka topic to couchbase
In my flink’s logic, I use several N1QL query before upsert to couchbase
I have created GSI index and have tested each query only around 30ms

I have loaded 1,6 mio data to kafka to process to couchbase
It takes around 200 ops which is too long to process whole data

How can I increase the performance?

FYI I use Java SDK 2.7.13 and Couchbase 6.5.1

2

You should directly write using JAVA upsert/insert vs N1QL.

1 Like
3

i mean i need to do some query before insert to couchbase bcs i need to do the transformation

4

Hi Han,

What kind of transformation do you need to do?

What queries are you running?

Thanks,
David

5

Hi @david.nault,

For example, before insert doc to couchbase, i need to query to existing bucket with some criteria from the doc, then append to my document before insert to couchbase.

Actually I try to create something like eventing but using java.

my query is simple, select from bucket with filter that indexed, and quite fast

is there any parameter to tune the performance?
what’s the best practice for this requirement?

6

hi @vsr1,

My N1QL is used for query before upsert the data.
I have added queryEndpoint and kvEndpoint, but still only get around 300 ops/sec
I need more than 1000 ops per second
Is there any other parameter/setting to tune or maximize the performance?

7

How are you writing the documents to Couchbase? Are you upserting one document at a time using blocking methods? I wonder if you would see better performance if you could batch the documents into a tumbling window and upsert each window using async upserts.

Alternatively, I wonder if you could combine the query and the update into a single N1QL statement. I’m not a N1QL guru so I don’t know what that statement would look like… just putting the idea out there.

8

Hi @david.nault,
Sorry for the late response.
Yes, I;ve found the N1QL query using merge, so I only use 1 N1ql query to update/insert the document.

But the result that I get is only around 100 operations per secon (50 get per sec + 5- set per sec)

Please find below my sample code

String statement = “MERGE INTO myBucket AS target\r\n” +
“USING [\r\n” +
" { "client_no": " “+i+” ", "name": "test"} \r\n" +
“] AS source\r\n” +
“ON key "TEST::”+i+“"\r\n” +
“WHEN MATCHED THEN\r\n” +
" UPDATE SET target.name= source.name\r\n" +
“WHEN NOT MATCHED THEN\r\n” +
" INSERT (\r\n" +
" { \r\n" +
" "name": source.name,\r\n" +
" "client_no": source.client_no} )\r\n" +
“returning target.*”;
N1qlQuery query = N1qlQuery.simple(statement,N1qlParams.build().consistency(ScanConsistency.REQUEST_PLUS).adhoc(false));
myBucket.query(query);

  1. How many max transaction per second (TPS) usually I can get from N1QL query using SDK?
  2. How to increase my max TPS ? (add more resource, update config, etc)
  3. How’s the best practice to write that code (query using N1QL with java SDK to get best performance)

Thanks

9

As you already have key,
Get the document.
If present update with cas else insert