Couchbase N1Q1 query always return empty and dropping-recreating primary index fails

SQL++
1

This is the second time it happens, it was solved by re-installing couchbase last time, but I think I need to figure out the reason.

I only use one small bucket for testing (less than 500 documents), so I don’t think this is related to ram/disk usage. It worked fine until a point when all queries fail to return anything(all queries return empty)

Queries like

select MYBUCKET.* from MYBUCKET where someBool == false order by date desc

and

delete from MYBUCKET

does not do anything at all, even the delete query won’t delete anything. Weirdly, no error returned.

I tried to drop the primary index, it shows timeout but actually succeed.
Then I tried to create primary index again, this is the error message i got.

GSI CreatePrimaryIndex() - cause: Encounter errors during create index. Error=Request timed out. Index server may still be processing this request. Please check the status after sometime or retry. .

Is there any log or anything I should provide to help with debugging?

PS

  • The queries are executed in admin page(http://localhost:8091/ui/index.html#!/query/workbench)

  • Using C# SDK to execute the query also result in the same.

  • Restarting does not solve the problem.

2

@meowzz, please share the indexer.log file from /opt/couchbase/var/lib/couchbase/logs. Which couchbase-server version are you using?

3

Hi sorry for the late reply, the problem comes again.
I’m using CouchBase (Community Edition 5.0.1 build 5003 )
Here’s the google drive link for my indexer.log

https://drive.google.com/file/d/1sK1rCNqc9XwrCm31B5sVjrdozwXjOsyu/view?usp=sharing

This time, the problem has a different behaviour…
When I do a select all query to the bucket, it only return a same set of result(count 261) but when I check the total document count in the bucket admin page, it shows a 270+. One more thing, I don’t know if it is related, the CPU usage for indexer.exe is constantly high(around 50%).

Thanks for helping!

4

@deepkaran.salooja
Hi sorry for the late reply, the problem comes again.
I’m using CouchBase (Community Edition 5.0.1 build 5003 )
Here’s the google drive link for my indexer.log

https://drive.google.com/file/d/1sK1rCNqc9XwrCm31B5sVjrdozwXjOsyu/view?usp=sharing

This time, the problem has a different behaviour…
When I do a select all query to the bucket, it only return a same set of result(count 261) but when I check the total document count in the bucket admin page, it shows a 270+. One more thing, I don’t know if it is related, the CPU usage for indexer.exe is constantly high(around 50%).

Thanks for helping!

5

@meowzz, from the logs I see the count of 261 for primary index on POS_Bucket and there is nothing pending to be indexed. I do see this bucket had some deletions. If you have already refreshed the UI and the total count is 270+, we would need the full set of logs to investigate further. You can collect it from UI->Logs.

Regarding the high cpu usage, a couple of issues related to that were fixed in 5.1.1 release MB-29197 and MB-29194. What is the total number of buckets on your system?

6

@deepkaran.salooja
Thanks for your help, Just collected the logs but yesterday while I was poking around, I deleted the primary index and created a index on the field I need to query, then the query works fine again, I do not know if this will affect the logs. Anyway here it is.

Google drive link.
https://drive.google.com/open?id=1GXPkKdKemrtSAGluVNykAur_HsZmyP5k

BTW the 5.1.1 version you mentioned, it hasn’t been available as a community version right? I only see 5.0.1 on the website.

Thanks

7

@deepkaran.salooja
Another weird thing:

select count(*) from BUCKET where FIELD=VALUE
return result 19

select * from BUCKET where FIELD=VALUE
return result [], empty array

If there’s no data returned in the second query, shouldn’t it be count=0 in the first query?

the log related to this should be in the same collected zip in the above post.

Thanks a lot …

8

select META().id from BUCKET where FIELD=VALUE;

Also post EXPLAIN

9

@vsr1
Sorry I’ve dropped the primary index and everything works fine again with the field index… I am not able to reproduce the problem at this point…

10

@deepkaran.salooja
Hi, just something that might be related.
After I drop the two primary index on two(actually that’s all I have) buckets and create only field index I need, the CPU usage drops to very low. So the CPU usage problem may be related to primary index?

11

Index on filed can pass the predicates to indexer apply predicates, on primary index predicates are applied post index scan/fetch. Query service has to do lot of work and cpu usage will be more.

Please read the articles in http://blog.couchbase.com/wp-content/uploads/2017/03/N1QL-A-Practical-Guide-v2.pdf

A Deep Dive Into Couchbase N1QL Query Optimization
Understanding Index Scans in Couchbase N1QL Query
Designing Index for Query in Couchbase N1QL

12

@vsr1
Thanks for the info, but does it explain why the query returns out-dated result and the constantly high CPU usage? I don’t think a primary indexed bucket with less than 300 documents should have performance issue…

13

@meowzz, as you had already dropped the primary index before collecting the logs, we don’t get to see what we were looking for. I’ll try to reproduce this in-house.

14

@deepkaran.salooja
Thanks a lot, please let me know what is going on if possible.

15

@deepkaran.salooja
Hi, Unfortunately the problem comes up again, this time I don’t have any primary index, only 4 field indexes.
Here is the link to my full log. Really appreciate the help.

https://drive.google.com/file/d/1Gm5sz4k0iQBxYqeqvuPZGMsFqs5tNzP8/view?usp=sharing

16

From the logs, I see that compaction is failing and there are a few outstanding mutations, which is odd for such a small setup.

You could try:

  1. Increase memory quota to 1GB.
  2. Change compaction mode to append only:
    https://developer.couchbase.com/documentation/server/5.1/settings/configure-compact-settings.html
  3. Drop and recreate the indexes after above steps.
17

@deepkaran.salooja
Ok I am applying these changes, dropping index is very slow, the following error shows in the query result:

[ { "code": 5000, "msg": "GSI Drop() - cause: Fail to drop index on some indexer nodes. Error=Request timed out. Index server may still be processing this request. Please check the status after sometime or retry.\n. If cluster or indexer is currently unavailable, the operation will automaticaly retry after cluster is back to normal.", "query_from_user": "drop index POS_Bucket.isSyncedSqlIndex" } ]

The first drop succeed even the above error is returned, but not the second drop… Now I will try reboot and drop again.

18

@deepkaran.salooja
I’ve done the above changes. It works again. Any idea on why default setting is causing the problem? Or what can I do to help?

19

@meowzz, there is nothing obvious from the logs. If the problem happens again, may be you can try to install on a new instance or use a certified OS:
https://developer.couchbase.com/documentation/server/current/install/install-platforms.html

20

@deepkaran.salooja i’m having this problems. I’m using this
Community Edition 5.1.1 build 5723 for Window OS. Please help me. I don’t drop or clear anything yet.