Strange "delay" on index, when using simple N1QL query

SQL++
,
1

Hi there!

First of all - Couchbase is great, I’m using it since last year in many ways. Really, really great job with this product! I learned how to effectively use views, then I tried optimize it a little bit more using some N1QL queries with primary index and even multiple secondary indexes. Then my problem appeared…
I’m using Couchbase Server, version 4.0 (community edition) in application which is saving (24/7/365) about 70 docs per second in the database. In the same time multiple processes operate on the same buckets, updating and removing this docs. Simple N1QL query (“SELECT attribute FROM bucket WHERE selector = 1”) on bucket with 40000 documents and 1 primary index, using consistency NOT_BOUNDED return only part of the data. I guess it’s all about caching on database side but this issue appear every week or two. It’s very annoying, becuase the only one way to fix it is to restart Couchbase Server. When i switch to consistency REQUEST_PLUS then it takes about minute or two to finally return empty set (or real data if you are very lucky). Server capacity is much higher (12 dedicated CPUs and 128GB RAM, dedicated SSD high-performance disk). Is there any option to fix this issue (performance of the query)? Or maybe there is any workaround? I tried fetching all the docs into application memory using view and it took a few seconds to return everyting. This solution was good but memory usage - awful. That’s why I would like to use query instead (i will be able to manipulate query conditions dynamically).
I need N1QL to work like views - it doesn’t have to be 100% accurate but when I receive only ~20% of all data exisiting in the bucket (using N1QL query) or a timeout (i guess it’s a timeout) then something is really wrong.

Best,
Maciej

2

Hi @maciej.tomaszewski,

Thank you for the kind words. We have made improvements in 4.1 and 4.5. Is it possible for you to use a newer version, or to build from source?

3

Thanks for fast reply! I’m using docker container. Are this improvements merged into community 4.0? There is no tag for community 4.1/4.5. Will you add one?

Best

4

@maciej.tomaszewski, we don’t backport the improvements in the new releases to previous versions. So 4.0 will not contain improvements in 4.1.
You have a few options; You can use enterprise edition for testing and development so 4.1 EE is available to you. 4.5 preview does not have an edition (preview releases don’t come with editions at this point) and you can use that too for your experimentations. We also release CE several months after the EE edition has been out. 4.1 CE will be out in a few weeks.

With docker, pls use the following tags for 4.0 community

docker pull couchbase:community-4.0.0

With docker, pls use the following tags for 4.5 beta

docker pull couchbaseserver/couchbase-watson

5

Thanks! So it look like I have to wait for update. I hope this error won’t occur in 4.1 and the performance of N1QL queries will be much better :slight_smile: Until then I will restart CB every week to clear it’s cache.

Best,
Maciej

6

You can use any version now. The restriction of CE vs. EE is for production use.

7

Sure, but unfortunately it’s hard to reproduce this issue on dev env (continuous high traffic for a few days required). That’s why I’m forced to wait for new CE release. I trust you that both versions (4.1&4.5) contain the fix. I’ll check it as soon as new release appear.

8

Ok. I would need to verify your exact problem to be sure it is addressed in 4.1.

9

Let’s wait till the release, I’ll let you know if it is fixed or not. By the way, it would be nice to get an update from you when the exact date of mentioned 4.1/4.5 CE release will be set.