Indexer not working after a period of time

Hi,
I have experienced some problems with the indexer in the last months.
From time to time, the queries do not return all the data. It seems to be related to the indexes because if I drop and create the indexes again, the problem is solved and querying works again.
This issue can manifest in different ways: some times some documents are received, but other times when this issue occurs, nothing is received anymore and no query is working.
This happened multiple times, on two of our servers were we host a couchbase database, so it is not an isolated issue.
Version of couchbase used: Community Edition 5.0.1 build 5003.
I can provide the indexer-logs file if that can help.
Did anyone experience issues like this? And how can I prevent them from happening?
Thank you.

It would be good to check from the UI stats if index has pending mutations to be indexed when you experience this issue. If you capture the logs when the issue is happening, it can provide us more insights.

In general, it is good to make sure that you provide sufficient RAM quota to Indexer and also use “Circular Write Mode” for the index compaction settings.

indexer-logs.zip (1.1 MB)
I uploaded the indexer logs from one of the days when we experienced this problem.
I didn’t observe anything unusual in the UI. And it lasted for a few hours, until we recreated the index.
We have RAM allocated like this:
Data service: 4096 mb
Index service: 2048 mb
Search service: 512 mb
We also have the ‘Circular write mode with day + time interval trigger’ option checked for all days.

Thank you.

Hi @sebastian.dumitrascu

From indexer logs, I can see in the last set of indexer stats, the build progress and items count of indexes are:

“daan:#primary:build_progress”: 100, “daan:#primary:items_count”: 8092,
“daan:daan-result-index:build_progress”: 0, “daan:daan-result-index:items_count”: 0,
“daan:employment-index:build_progress”: 0, “daan:employment-index:items_count”: 0,
“daan:reminder-index:build_progress”: 0, “daan:reminder-index:items_count”: 0,
“daan:respondent-index:build_progress”: 0, “daan:respondent-index:items_count”: 0,
“daan:type-index:build_progress”: 100, “daan:type-index:items_count”: 0,

The indexes that have 0 build_progress also high number of pending documents:

“daan:daan-result-index:num_docs_pending”: 2571348,
“daan:employment-index:num_docs_pending”: 2571348,
“daan:reminder-index:num_docs_pending”: 2571348,
“daan:respondent-index:num_docs_pending”: 2571348,

Other two indexes type-index and #primary have num_pending_docs as 0.

Throughout the logs, the only indexes I see that are in ACTIVE state are #primary and type-index. Can you please double check if these indexes are built? Also, please share cbcollect logs of the cluster to debug further.

Thanks,
Prathibha

Hi @prathibha
I will upload the collect logs as soon as possible.
The only active indexes are #primary and type-index, and they are built 100%. The others were deleted in the meantime.
Thanks,
Sebastian.

Here are the collect logs: https://we.tl/t-9ql89lQKZs

Hi @sebastian.dumitrascu,

You mentioned some queries were not working? Can you share what queries they are and which index(es) those queries use? If you can post explain plan of those queries, that would be good. Since only two indexes are active and built 100%, do you expect these two indexes to be sufficient for all your queries? Both these indexes have num_docs_pending stats as 0. daan:#primary has 8092 items and daan:type-index has 0 items. A type-index can have 0 item if leading key (ie type) is missing. Can you please check if that is the case in your documents?

Thanks,
Prathibha

Hi @prathibha,
So the queries that I had problems with where the select queries. (e.g.: select * where “type” = );
Now that problem doesn’t occur anymore so I cannot provide the “Explain”.
Most of the queries we do are based on the “type” property or on the id (using getById). So I am not sure what would be the appropriate number of indexes needed.
What does “num_docs_pending” mean? could that be a problem?
I am not sure why the “type-index” has 0 items, because the key is not missing (the “type” prop).

Thanks,
Sebastian

Checkout Indexing and Optimization sections of https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf

https://blog.couchbase.com/create-right-index-get-right-performance/

If queries has only type predicate you can create single index
CREATE INDEX ix1 ON bucket(type);

Hi @sebastian.dumitrascu
Could you please share a sample document and queries which do not work? Given the indexer stats, the index is fully built, yet has 0 items. It would help if you share the above requested.
Thanks,
Prathibha