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.