Why run indexer in high CPU load after 500 Select queries?

Couchbase Server
1

Hi,
I have 730.000 documents in a bucket.
500.000 of this are from one type.
I run a query like this

SELECT td.*
FROM middlewareLiveCopy td
WHERE td._type='CustomersArticleRelations' AND td.articles_nr= "116614" AND td.datum_letzte_lieferung < 20210628 AND td.datum_letzte_lieferung > 0
AND td.client_id=1
AND td.customer_id=82010

This must run several 1000 times with different client_id, customer_id and datum_letzte_lieferung
But after 500 runs the server runs in heavy load caused by indexer service.
During the selects are no data changes or something else.

The high CPU usage state remains until I restart the indexer.

I have tested this with CB CE 6.6 and 7.0.2 PHP SDK 2.6 and 3.2

In the error.log I did find this:

[ns_server:error,2021-11-20T09:00:59.951Z,ns_1@127.0.0.1:service_status_keeper-index<0.609.0>:service_status_keeper:handle_cast:103]Service service_index returned incorrect status
[ns_server:error,2021-11-20T09:01:37.919Z,ns_1@127.0.0.1:service_status_keeper_worker<0.607.0>:rest_utils:get_json:57]Request to (indexer) getIndexStatus with headers [{"If-None-Match",
                                                   "aec4b07a602a49fa"}] failed: {error,
                                                                                 timeout}
[ns_server:error,2021-11-20T09:01:37.919Z,ns_1@127.0.0.1:service_status_keeper-index<0.609.0>:service_status_keeper:handle_cast:103]Service service_index returned incorrect status
[ns_server:error,2021-11-20T09:02:15.899Z,ns_1@127.0.0.1:service_status_keeper_worker<0.607.0>:rest_utils:get_json:57]Request to (indexer) getIndexStatus with headers [{"If-None-Match",
                                                   "aec4b07a602a49fa"}] failed: {error,
                                                                                 timeout}
[ns_server:error,2021-11-20T09:02:15.899Z,ns_1@127.0.0.1:service_status_keeper-index<0.609.0>:service_status_keeper:handle_cast:103]Service service_index returned incorrect status
[ns_server:error,2021-11-20T09:02:30.907Z,ns_1@127.0.0.1:service_status_keeper_worker<0.607.0>:rest_utils:get_json:57]Request to (indexer) getIndexStatus with headers [{"If-None-Match",
                                                   "aec4b07a602a49fa"}] failed: {error,
                                                                                 timeout}
[ns_server:error,2021-11-20T09:02:30.907Z,ns_1@127.0.0.1:service_status_keeper-index<0.609.0>:service_status_keeper:handle_cast:103]Service service_index returned incorrect status

Couchbase Version: CE 7.0.2 and EE 7.0.2
PHP SDK: 2.6 and 3.2
Intel(R) Core™ i7-10510U CPU @ 1.80GHz
16GB-RAM

grafik
grafik879×190 32.8 KB

indexer.zip (3.3 MB)
memcached.zip (438.1 KB)
query.zip (894.1 KB)
stats.zip (2.6 MB)

2

Why indexer memory is high cc @jeelan.poola , In mean time try the following index.

CREATE INDEX ix1 ON `middlewareLiveCopy`(`client_id`,`customer_id`,`articles_nr`,`datum_letzte_lieferung`)
WHERE _type = "CustomersArticleRelations";

https://index-advisor.couchbase.com/indexadvisor/#1

3

@markdg, Few queries to understand the scenario:

a. After the 500 query limit, for how long did you notice high CPU utilisation?
b. Are scans running during this duration?
c. What is the CPU utilisation range during the first 500 scans?
d. Did any scan parameters change causing long running scans i.e. scanning more rows - as this can lead to high CPU utilisation?

Additionally, if you can share the indexer_stats.log file, we can know how many requests are being served and how many rows are being scanned. There are no known issues we are aware of around this area. The timeouts are most likely due to indexer’s CPU saturation.

Thanks,
Varun