Couchbase query taking more than 10 minutes and then timeout

SQL++
,
1

Hi Team,

I am running the below query and it is taking more than 10 minutes and no result.

select count(id) from SS-TEST data where meta().id like ‘%Table16_1’

Bucket Data : 400 GB & Bucket Memory : 16 GB
Index Memory : 8 GB

Can someone help me in rectifying the below.

I have created primary index for the bucket.

Thanks and Regards,
Suresh

2 
CREATE INDEX ix1  `SS-TEST` (REVERSE(META().id);
select count(id) from `SS-TEST` data where REVERSE(META().id ) like  REVERSE("%Table16_\\1");
3

Hello,

Thanks for your respeonse …

Created index as suggested and tried…

It worked for initial load which already exists in server.
Pushed few more GB’s of Data to couchbase and after that query results timeout for the latest Data inserted.

Error:

query.log

2021-02-11T18:36:19.205+05:30 [Error] PickRandom: Fail to find indexer for all index partitions. Num partition 1. Partition with instances 0
2021-02-11T18:36:19.215+05:30 [Warn] Fail to find indexers to satisfy query request. Trying scan again for index 18421880698216471738, reqId:f215f0a8-12ef-4b72-a5ed-221a740bae9c : read tcp 127.0.0.1:41124->127.0.0.1:9101: i/o timeout from [127.0.0.1:9101] …
2021-02-11T18:36:22.269+05:30 [Info] serviceChangeNotifier: received PoolChangeNotification

indexer.log

2021-02-11T18:41:39.475+05:30 [Info] ServiceMgr::rebalanceJanitor Running Periodic Cleanup
2021-02-11T18:41:29.849+05:30 [Info] [Queryport “:9101”] connection 127.0.0.1:41230 closed
2021-02-11T18:41:29.849+05:30 [Error] transport error between 127.0.0.1:9101->127.0.0.1:41230: write tcp 127.0.0.1:9101->127.0.0.1:41230: write: broken pipe
2021-02-11T18:41:27.253+05:30 [Info] ServiceMgr::GetCurrentTopology [0 0 0 0 0 0 0 1]

Am i missing something here …

Regards,
Suresh

4

@suresh2112 What is the version of Couchbase Server you are running? Are you using community or enterprise edition? Can you give the exact create index statement? Can you do a cbcollect and share?

5

HI Raju,

Version : 6.5 community Edition.

Index created using query

CREATE INDEX tekclan_id ON tekclan((meta().id))

Select query using to fetch Data …

select count(*) from tekclan data where meta().id like “pc_tgb_hrapps_work10_018::%”

6

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/comparisonops.html
You have wildcard character in the middle. You need to Escape that.

select count(*) 
from default data where meta().id like "pc\\_tgb\\_hrapps\\_work10\\_018::%" ;

Do EXPLAIN on query and check what passed to indexer in the spans section of IndexScan3 operator.