CB 6.0.0 community with 30+ Million objects - SELECT takes too long to execute

SQL++
, , , ,
1

Hi,

We run CB 6.0.0 on a strong VM (m5.2xl) with 8 CPUs and 32GB RAM.
The server holds at the moment 30+ million short documents.

When running a simple

SELECT meta().id
FROM Bucket_Name
WHERE meta().id LIKE “PREFIX_NUMBER%”
LIMIT 3;

the server responds after 50+ seconds at best. In many cases it doesn’t respond at all and exits with a time out.

This is by far extremely long execution time and is completely useless for production environments.

We’d appreciate help from indexing / querying specialists here to tune the server so that it will be useful and not simply eating our budgets.

Thank you all in advance.

2

Hi @arik_bar

Check EXPLAIN and check what index used.
The query might be using primary index. Query uses LIKE with prefix. The prefix has underscore(_) which is wild card character. You must escape the underscore as follows so that it can pass more info to indexer (Page 151 of https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf).

SELECT  RAW meta().id 
FROM Bucket_Name 
WHERE meta().id LIKE "PREFIX\\_NUMBER%" 
LIMIT 3;

WHERE meta().id LIKE “PREFIX_NUMBER%”

                   "spans": [
                        {
                            "range": [
                                {
                                    "high": "\"PREFIY\"",
                                    "inclusion": 1,
                                    "low": "\"PREFIX\""
                                }
                            ]
                        }
                    ]

vs

WHERE meta().id LIKE "PREFIX\\_NUMBER%"

           "spans": [
                        {
                            "range": [
                                {
                                    "high": "\"PREFIX_NUMBES\"",
                                    "inclusion": 1,
                                    "low": "\"PREFIX_NUMBER\""
                                }
                            ]

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/comparisonops.html

Also checkout https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/