HI Deepkaran,
I have found out that 16 cores for each of the node, does not matter if it’s index or query or data node.
As there are 32 partitions in the index, it would be good to distribute it on 2 nodes at least. And then check what kind of cpu utilization you see when the query is run.
Also, it would be good to make sure the partitions are not skewed. You can check the statistics for each partition using rest api:
https://docs.couchbase.com/server/current/rest-api/rest-index-stats.html
e.g.
curl -X GET -u Administrator:password "http://localhost:9102/api/v1/stats/travel-sample/over1000?partition=true&pretty=true"
and check the items_count for each partition.
One silly question, not sure if it has any impact on performance , as you see in my below index isEsOrdTr=False , basically index been created on “False” value , but in my data isEsOrdTr field value has value as “false” does it make any difference ?
CREATE INDEX ix2 ON Bucket_Order ( EsOrderTriggerEpoch , OrderId )
PARTITION BY hash( OrderId ) WHERE isEsOrdTr=False;
It is boolean JOSN constant . case insensitive
select false == FALSE; will return true
Hi Vsr1,
i am seeing some count mismatch issue while creating an index on isEsOrdTr=False using where clause.
When a update process runs, it’s updated the isEsOrdTr value to " true" so its should not picked up in next job run , but when the update query kick off again it’s takes the same order id again event though the value is updated to true in the doucment.
Based on my analysis looks like index is not been updated when the field value is set to true , hence it’s been picked up in next job run, can you please advise ?
If index is not updated use scan_consistency https://docs.couchbase.com/server/5.5/indexes/performance-consistency.html
i went through the document but not give me enough context about my issue.
- So my question is if i have created an index using isEsOrdTr= false , an if i have 10 documents which has isEsOrdTr= false been added to index, now for an example when my job run based on given time, it picked two document and updated the isEsOrdTr= true, so based on my understanding those two records should not be exist in the index any more since field value change “true”. Do you agree …?
so next time only 8 records been exist in the index and in next job run based on the given time frame only certain records should be picked up ?
why i am try to understand this because , my old query fetched the correct records based on the true or false value, and in my old index i did not use where and simply added that fields as cover index.
new index
CREATE INDEX ix1 ON Bucket_Order (OrderId, EsOrderTriggerEpoch) WHERE isEsOrdTr = false;
old index
CREATE INDEX ix1 ON Bucket_Order (OrderId, isEsOrdTr , EsOrderTriggerEpoch)
{
“EpochTm1”: 1590599032,
“ESOrderTriggerEpoch”: 1590591832,
“isEsOrdTr”: false,
“OrderId”: 10,
"OrderNumber: “68636”
}
Couchbase indexes are maintained asynchronously index lags behind document data. If you updated document, to reflect in the index it takes some time. If you want latest you need set scan_consistency to request_plus
Index has WHERE clause or index key it should not make any difference.
You can use cbindex and verify what index has outside query.
Here is my observations,
I have 25o k records in my bucket , now using update query i have updated 3 records and set the isEsOrdTr= true , now i have run my query using query workbench and my expection is only 24997 k records should be fetched, but it’s still pulling all 250 records.
When i have used my old index it’s retrived the 24997 k records which expected.
can you advise …? i thought after changing value to true those three records should not exist in that index in the bucket. You have mentioned that index legs take some time to be updated , then how if i ran with old index it’s giving me correct value ? 
new index -
CREATE INDEX ix1 ON Bucket_Order (OrderId, EsOrderTriggerEpoch) WHERE isEsOrdTr = false;
old index
CREATE INDEX ix1 ON Bucket_Order (OrderId, isEsOrdTr , EsOrderTriggerEpoch)
You should check your new index and see how many items are pending mutations.
It should work unless something going on your cluster. If you want you can drop and create index that should work now modify value and see if that also works
You can experiment this by simple test.
Create separate bucket
create new index on that bucket
insert one document
see if index has that entry. Change value and check index again.
Here is my anohter findings
We have 2 cluster 1 cluster have 6.5.1 and another one has 6.4.0.
When i did perform same query using cluster 1 (6.5.1) it’s retrived the correct value using new index , and if i performs same query on cluster 2 (6.4.0 ) it not fectched the records with false value.
do you or any cb team has an idea ?
yes, sorry typo. you are correct.
Did you able to repro using steps i described earlier.
Create separate bucket
create new index on that bucket
insert one document
see if index has that entry. Change value and check index again.
Yes, i tried that and it’s working that’s way.
created new bucket and inserted 5 records , i have updated 2 records among them , and ran my query with using new index and i got expected result.
But when i do in bulk i am seeing the issue on version 6.0.4. or else mutation might getting some time or index node not functionaly well.
Thank you vsr1, your input always helps us to find a better solution. But these are the key concern i have , because my application widley using CB , so if someone can help that would be great.
yes, this index has partition.
CREATE INDEX ix1 ON Bucket_Order (OrderId, EsOrderTriggerEpoch) WHERE isEsOrdTr = false ) PARTITION BY hash(OrderId`)
Can you try non-partitioned index on 6.0.4