Is there a request access limit on View and n1ql QueryEngine?

Couchbase Server
, ,
1

I`m testing with springdata(java sdk 2.2.x) and couchbase4.1.1 community.
Code and preferences will be attached below.
The code that currently does three things(key/value, view, n1ql) in a single request.
I’m running the test code with about 50 threads.

key/value has an avg latency of less than 0.5 seconds.
but, view and n1ql query has an avg latency of between 3 and 6 seconds.
At this time, the related logs accumulate continuously and appear to be waiting for the request, and the speed will be slowed down.

There is no issue when running as a single thread request.
Changing Java SDK configuration does not improve.
Please help me with what I need to solve the problem :slight_smile:

Logging

[http-nio-9001-exec-27] DEBUG o.s.d.c.r.q.ViewBasedCouchbaseQuery Executing view reduced query
[http-nio-9001-exec-36] DEBUG o.s.d.c.r.q.ViewBasedCouchbaseQuery Executing view reduced query
[http-nio-9001-exec-33] DEBUG o.s.d.c.r.q.ViewBasedCouchbaseQuery Executing view reduced query
[http-nio-9001-exec-28] DEBUG o.s.d.c.r.q.ViewBasedCouchbaseQuery Executing view reduced query
[http-nio-9001-exec-47] DEBUG o.s.d.c.r.q.AbstractN1qlBasedQuery Executing N1QL query
[http-nio-9001-exec-13] DEBUG o.s.d.c.r.q.AbstractN1qlBasedQuery Executing N1QL query
[http-nio-9001-exec-21] DEBUG o.s.d.c.r.q.ViewBasedCouchbaseQuery Executing view reduced query
[http-nio-9001-exec-2] DEBUG o.s.d.c.r.q.AbstractN1qlBasedQuery Executing N1QL query
[http-nio-9001-exec-8] DEBUG o.s.d.c.r.q.AbstractN1qlBasedQuery Executing N1QL query
…

Java Couchbase Configuration

DefaultCouchbaseEnvironment.builder()
.connectTimeout(properties.getEnv().getTimeouts().getConnect())
.queryEndpoints(10)
.viewEndpoints(10)
.build();

Java testcode

User user = userRepository.findOne(id);
// view query
int count1 = boardRepository.countByUserId(id);//view reduce=true

function (doc, meta) {
  if (doc.type == "Board" && doc.status!=false) {
    emit(doc.userId, null);
  }
}

// n1ql query
int count2 = boardRepository.countByUserIdAndStatusIsNot(id, status);//n1ql

select count(*)
from bucket
where type = 'Board'
and userId = '1'
and status != false
2

First, to rule out anything: can you run with 1 thread only and compare the timings to the same queries run through CBQ/the admin UI? Its important to compare it with 1 thread since the performance with 50 threads on the server will be less than with one thread.

Next step, on the default consistency is READ_YOUR_OWN_WRITES which is quite slow. You can tune the consistency setting via your config to EVENTUALLY_CONSISTENT or UPDATE_AFTER. This on both views and N1QL will give you much better performance, especially under load with KV ops.

Finally, for N1QL can you please share your index definition? Also I heard from @geraldss that count(*) has been improved in later versions and/or there are tricks to speed it up a bit.

1 Like
3

First , Running on a single thread will not cause any performance problems. It is also the same as what you have done in the admin UI.

Next, I have not tested it by adding the UPDATE_AFTE setting.

Finally, There were only about 3000 data that we tested. I tried to create the index normally in the field of the query condition.

I then chaged environment and to test again.
I have a better response time than a local test, but I can not be satisfied.
This is because there is a cpu problem, not the response speed result. And response time is not satisfactory.
When I went through the tests, I see that the CPU was approaching 100% And most cpu was used by cbq-engine process. I could see it in admin UI to General buckets Analytics.
Finally, It performance was not expected in the way of using n1ql frequently. I want to know if there’s a way around this, pleases.

Test info

- Test code
User user = userRepository.findOne(id);
int count1 = boardRepository.countByUserId(id);//n1ql
int count2 = boardRepository.countByUserIdAndStatusIsNot(id, status);//n1ql
int count3 = boardRepository.countByUserIdAndStatusIs(id, status);//n1ql

- API-server * 3
cpu : 2core / memory : 4GB
java application(spring data couchbase, java sdk 2.2.x)

- Couchbase node * 3
couchbase-community-4.1.1
cpu : 4core / memory : 7.5GB
- Node Configuration
bucket type : couchbase
ram : 5GB, Full Ejection
replicas : enable
disk IO : high

- Tool (Jmeter)
100 Threads, 10 period, 10 loop

- Test Data import
User About 50 row
Board About 3000 row

Test result

AVG response : 500~800ms
MAX reponse : 1500ms