Performance issues with multiple live queries

[sleith]

Hi, i got a database issue while loading the live query view.
i read about this issue in other threads, but still no luck on fixing this.

My app flow:

1. app pull all data from server. more than 140.000 documents
2. after all data pulled, then i start 10 live query
3. this caused database busy. only 3 live query able to be completed (being called in observeValueForKeyPath). Others are not able to complete, even after waiting for a long time.
4. I stop the app, and relaunch it. Because no more data pulled, it simply goes to start 10 live queries. And it able complete them all.
   Even though sometimes i see some 'database busy' log, but still works good, can complete the query.

So perhaps if app pull large amount of document and after that do live queries, somehow database locked and live queries can't be completed.

Is there a way for app to detect if database busy? so i can set the app to restart it like step #4.

Thanks

[snej]

If you have the LiveQueries running while the replication is pulling in documents, they're going to re-run the query every time new documents are added. This is a lot more work than just running the query once at the end.

When you say "after all data pulled, then i start 10 live query", how are you waiting for the replication to finish? It sounds like that isn't working and you're starting the live queries while there replication is still running.

I would really like to see logs with the "Sync" and "Query" logging keywords enabled (see here for details on turning on logging.) Please don't paste huge logs here; use a Gist or Pastebin or something like that. Thanks.

[sleith]

Hi, thanks for your respond :)
I pull detect the status for kCBLReplicationIdle and checking for pull.changesCount. if it still has > 0, i keep retry to pull.
After no more data, then i stop the sync before starting the live queries:

- (void)forgetSync {
    if(pull!=nil){

            [pull removeObserver:self forKeyPath:@"running"];
            [pull removeObserver:self forKeyPath:@"completedChangesCount"];
            [pull removeObserver:self forKeyPath:@"status"];
            [pull removeObserver:self forKeyPath:@"error"];
            [pull stop];
    }
    pull = nil;

    if(push!=nil){
            [push removeObserver:self forKeyPath:@"completedChangesCount"];
            [push removeObserver:self forKeyPath:@"error"];
            [push stop];
    }
    push = nil;
}

I'll check about the logging and get back asap.
Thank you ;)

[sleith]

Hi,
here please find the log:
https://gist.github.com/sleith/412a2e458a714e000cbe

I tried run 2 times, the database busy issue seems works fine. App can get all the live queries completed even though got some database busy log.

Not sure what was happened before, perhaps i just need to wait more time..
Anyway, if you see the log at the bottom lines, about async query finished with very little rows.
but somehow it takes 2 minutes. Any idea what cause it?

And my other question, how to know about the CBLView construction progress? because if it took time for large data, i want to show the progress.

Thanks

[snej]

if you see the log at the bottom lines, about async query finished with very little rows.
but somehow it takes 2 minutes. Any idea what cause it?

I'm not sure what's going on there. For example CBLLiveQuery: 0x17758c80 takes about 90 seconds. The replicators have stopped. If you can reproduce this, can you pause the app in Xcode during this time, then enter bt all in the debugger console to dump all the thread stacks, and upload that?

Now, the logs show that two of your queries return a huge number of rows:

13:36:46.540| Query: <CBLLiveQuery: 0x176efb20>: Async query user-dd778164267840f988bf70337aad46c6/attachment...
13:36:46.545| Query: <CBLLiveQuery: 0x176b56e0>: Async query user-dd778164267840f988bf70337aad46c6/activity...
...
13:39:31.221| Query: <CBLLiveQuery: 0x176efb20>: ...async query finished (55966 rows)
13:39:31.279| Query: <CBLLiveQuery: 0x176efb20>: ...Rows changed! (now 55966)
13:40:12.776| Query: <CBLLiveQuery: 0x176b56e0>: ...async query finished (56204 rows)
13:40:12.834| Query: <CBLLiveQuery: 0x176b56e0>: ...Rows changed! (now 56204)

That's going to be pretty slow and use a lot of RAM. You should be able to make the queries smaller, either by limiting the number of rows, or using paging to get the rows incrementally, or by using grouping or a reduce function to aggregate data.

Also: Make sure you're not emitting doc as the value of an emit call. This is almost never necessary, and tends to slow down indexing and querying by bloating the size of the index. I mention this because yesterday I profiled another developer's app, who's also having trouble with database-busy warnings, and that was a big part of the problem.

[sleith]

Hi,
thanks for your suggestions :)
Btw what i mean is not that rows, but after that. you can see there are only 0 and 1 rows, but somehow on first time app create view, it's quite slow.

13:40:42.194| Query: <CBLLiveQuery: 0x17758dd0>: ...Rows changed! (now 0)
13:41:11.775| Query: <CBLLiveQuery: 0x17758f70>: ...async query finished (1 rows)
13:41:11.776| Query: <CBLLiveQuery: 0x17758f70>: ...Rows changed! (now 1)
13:41:40.595| Query: <CBLLiveQuery: 0x1965de60>: ...async query finished (0 rows)
13:41:40.595| Query: <CBLLiveQuery: 0x1965de60>: ...Rows changed! (now 0)
13:42:10.682| Query: <CBLLiveQuery: 0x1889edf0>: ...async query finished (3 rows)
13:42:10.682| Query: <CBLLiveQuery: 0x1889edf0>: ...Rows changed! (now 3)

After next loading view, it's fast.
I already emit null value. i use mapping for the keys, and load the documents when needed only.

thanks

[snej]

you can see there are only 0 and 1 rows, but somehow on first time app create view, it's quite slow

There are only o or 1 rows returned from the query, but that has nothing to do with how much work it took to update the index. It's usually the index updates that are slow.

Can you add the "View" logging keyword too? That will show how much time is spent indexing.

Also, please update to the latest master branch if you can, since it has some optimizations in it. In particular, if you have views that will be used at around the same time, give them a common prefix ending in a slash. Something like "prefix/view1", "prefix/view2", etc. Then they will be indexed at the same time, which is faster.

[jessliu]

@lichenyang2 can you take a look and add a performance test to check what the current benchmark is and if with fix what the improvement would be?

[pasin]

@sleith , I couldn't reproduce the issue. Indexing 150k along with 10 views at once could take some time. Please also ensure that the live query objects get retained properly while waiting for the index and query result. I will close the issue and we can reopen if needed.

@lichenyang2 , I have a initial result that I can share with you and we could create a new enhancement issue if needed.