Query: N1QL Query("access") took 49.609624065s

Sync Gateway Sync Gateway/2.7.2(2;583d2dc) CE
Couchbase server: 6.5.0 CE

sync.log.zip (28.5 KB)

I have uploaded log files.
It takes very long to do this task -> “Access: Recomputed channels for”

We have 5K+ channels, Can you please comment if this can be an issue? Is there any trade-offs related to number of channels as such?

In our application, for each event we create a channel and put all event related docs into that channel.
There is one role called event_viewers, which should be granted a view only accesss on each event creation. Whenever this (-> Access: Recomputed channels for) happens, It takes lot of time.
Could you please suggest a solution to tune this? Do I need any change in design of sync function itself?

few more errors.

2020-04-17T11:24:00.161+05:30 [INF] Query: N1QL Query("channels") took 1m15.000326969s
2020-04-17T11:24:00.161+05:30 [WRN] c:#1594 MultiChangesFeed got error reading changes feed "3b850x": Timeout performing Query -- db.(*Database).SimpleMultiChangesFeed.func1() at changes.go:548
2020-04-17T11:24:00.161+05:30 [INF] Changes: c:#1594 MultiChangesFeed done
2020-04-17T11:24:00.162+05:30 [WRN] c:#1594 WebSocket connection (#1594) closed with error write tcp> write: broken pipe -- rest.(*handler).sendContinuousChangesByWebSocket.func1.1() at changes_api.go:655
2020-04-17T11:24:00.162+05:30 [INF] HTTP: c:#1594 #1594:     --> WebSocket closed
2020-04-17T11:24:00.162+05:30 [INF] Cache:   Querying 'channels' for "3b850x" (start=#6996879, end=#7061598, limit=0)

These timeouts are coming from the Query service within Couchbase Server.

I would check the query monitor, and query stats on the server side, and make sure your node sizing is appropriate for your use-case.

Thanks for your reply
All the monitoring stats look quite normal (attaching screenshots). We only have one node however If the data size of an index itself is less than 5 MB, do you feel it should take this long even if we have one node? Number of requests also are very low at the moment.
From your experience can you please suggest whether for such issues with sync gateway, increasing the number of nodes is the only solution?

A single node shouldn’t matter too much in this case - I agree that querying using a 5MB index should not take long at all.

It’s worth noting that based on the screenshot above, the contents of the index is not resident in RAM, so the query service probably has to go down to disk to fetch the index.

What specs (CPU/RAM/Disk) are you using for your query node?

It might also be worth trying to run the access queries directly via Couchbase Server, and use EXPLAIN to narrow down any problems:

SELECT meta(`kamero`).xattrs._sync.access.`role:event_viewers` as `value` FROM `kamero` USE INDEX (sg_access_x1) WHERE any op in object_pairs(meta(`kamero`).xattrs._sync.access) satisfies op.name = "role:event_viewers" end;

8 Core, 16 GB Machine, Ubuntu 16
Something strange happened like for 1 day, Continuously all queries took lot of time.

Due to lot of errors I rebooted the systems and this issue is not present now, so when I run the query now, it just takes 500ms and so does sync gateway.

I am worried though as to what should I do next time this error pops up.

And I am sorry to repeat here, but I will really appreciate if you provide your valuable suggestion in below use case.

In our application, for each event we create a channel and put all event related docs into that channel.
There is one role called event_viewers, which should be granted a view only accesss on each event creation. Whenever this (-> Access: Recomputed channels for) happens, It takes lot of time. So I just wanted to know that is this approach really scalable in the future? For example currently there may be 5K events which can go up to 1-2M events and each event will have their own channel and that channel access we need to assign it to event_viewers.

Ok, it sounds like the query service, or the machine got into a weird state … I can’t really provide any specific advise, as I don’t work on the Index/Query services, so I’m not sure what the problem could be.
You could try posting over in the N1QL section of the forum to see if they have any ideas.

Instead of modifying the role to add additional channels (which is causing the recomputation overhead) could you instead assign your events to 2 channels? One which is an “all_events” channel, and one specific to that single event?

But I still will have to grant the guest users an access right? People should be able to sync specific event without login.

Currently there is a role to guest
"GUEST": { "disabled": false , "admin_roles" : ["event_viewers"]}

And I do call below in sync function on event creation, which is taking time.
access("role:event_viewers", [eventChannel, eventDataChannel]);

If lets say I dont want to modify the role which anyway is for guest I can give Guest an access directly like below
access("GUEST", [eventChannel, eventDataChannel]);

But would n’t above still be the problem? Or am I missing anything in your suggestion?

You can give the guest user access to the all_events channel, so any docs arriving in that channel automatically get picked up by the guest without granting additional access to the user or role.

Oh, I dint know this. I thought user must have explicit access to particular channel.
Thanks a lot! I can now think of many improvements. Let me try this.

I am afraid but this does not work. if you have a document which is in below 2 channels

Now Guest has access to all_events but when we use sync_gateway/bychannel filter and use event_id_data channel to fetch that specific event. It can not fetch that event.
However guest user can fetch the document by doc id or using all_events channel. But we do not know all document ids present in that event ( i.e. photos, albums, etc…)

And we want to sync specific event and not all events for guest. so we have to grant guest an access to that event channel.

Can we make any channel public so that channel is accessible by GUEST and all other registered users.

Ok, maybe you could have a public_events channel or similar intended to contain only events that need to be replicated to guests.

Ideally you don’t want to be updating the users/roles too often, as it’s more expensive to do than simply seeing docs appearing over a channel they already have access to.

For my usecase this wont work as well because each guest user will have at max 2-3 events followed out of thousands of events in DB and we dont want to sync lot of events which is not necessary. We sync based on event code shared to guests.

hmm noted. Thanks again for your time.

It sounds like what you actually want are individual user accounts to subscribe to individual event channels in that case - instead of trying to get this functionality through a single guest user shared between multiple people.

hmm yes. Those followers can be completly anonymous and does not need login. however I can create anonymous user with some unique id using admin API when he tries to follow a specific event, can update that user doc with particular channels access. This would scale well, Thank you again!