Server: Picture query doesn't provide an answer within an acceptable time
Expected behavior
If the “Photos” menu entry is chosen in the Nextcloud Android app, it initiates a server query which should show the first 60 photos within an acceptable time and load another 60 photos if requested, etc.
Current behavior
If the “Photos” menu entry is chosen in the Nextcloud Android app, the initiated server query doesn't return any photos within an acceptable time. Instead "No Photos" is displayed. One user reported that he'd waited up-to 30min within a result being returned.
@tobiasKaminsky assumes that a server problem might cause this behavior.
The problem has previously been discussed in the Nextcloud help forum
During my tests I've seen that the following message being logged on the server:
...","app":"PHP","method":"SEARCH","url":"/remote.php/dav","message":"Allowed memory size of 536870912 bytes exhausted (tried to allocate 4096 bytes) at .../nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php#158","userAgent":"Mozilla/5.0 (Android) Nextcloud-android/3.12.0 RC1","version":"18.0.4.2"}
Although the server is working like a charm and the mentioned message hasn’t been displayed in the past, I increased the available PHP memory limit from 512MB to 1GB. Surprisingly a similar message is still being logged:
...,"app":"PHP","method":"SEARCH","url":"/remote.php/dav","message":"Allowed memory size of 1073741824 bytes exhausted (tried to allocate 20480 bytes) at .../nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php#158","userAgent":"Mozilla/5.0 (Android) Nextcloud-android/3.12.0 RC1","version":"18.0.4.2"}
Now I wonder why only the Nextcloud app requests such a huge amount of memory, only to search photos and why no result is being returned. Non of the other server related functions are running into that problem, nor the Nextcloud web gui does?!
Steps to reproduce
- Open the Nextcloud Android app
- Select the Photos menu entry
- Wait for photos being displayed.
-> No photos are shown at all.
Environment
Server Configuration
OS: Linux 4.9.218
Web server: Apache2 2.4.41
Database: MariaDB 10.2.24
PHP version: 7.3.13
Nextcloud version: 18.0.4
Client Configuration
Nextcloud Android app version: 3.12.0 RC1
Phone: Samsung Galaxy S7
j-ed
All 24 comments
@tobiasKaminsky mind to share the dav request the app sends?
kesselb
on 7 May 2020
<?xml version="1.0" encoding="UTF-8"?>
<d:searchrequest xmlns:d="DAV:" xmlns:oc="http://nextcloud.com/ns">
<d:basicsearch>
<d:select>
<d:prop>
<d:displayname />
<d:getcontenttype />
<d:resourcetype />
<d:getcontentlength />
<d:getlastmodified />
<d:creationdate />
<d:getetag />
<d:quota-used-bytes />
<d:quota-available-bytes />
<oc:permissions xmlns:oc="http://owncloud.org/ns" />
<oc:id xmlns:oc="http://owncloud.org/ns" />
<oc:size xmlns:oc="http://owncloud.org/ns" />
<oc:favorite xmlns:oc="http://owncloud.org/ns" />
</d:prop>
</d:select>
<d:from>
<d:scope>
<d:href>/files/test</d:href>
<d:depth>infinity</d:depth>
</d:scope>
</d:from>
<d:where>
<d:like>
<d:prop>
<d:getcontenttype />
</d:prop>
<d:literal>image/%</d:literal>
</d:like>
</d:where>
<d:orderby>
<d:order>
<d:prop>
<d:getlastmodified />
</d:prop>
<d:descending />
</d:order>
</d:orderby>
</d:basicsearch>
</d:searchrequest>
With limit:
<?xml version="1.0" encoding="UTF-8"?>
<d:searchrequest xmlns:d="DAV:" xmlns:oc="http://nextcloud.com/ns">
<d:basicsearch>
<d:select>
<d:prop>
<d:displayname />
<d:getcontenttype />
<d:resourcetype />
<d:getcontentlength />
<d:getlastmodified />
<d:creationdate />
<d:getetag />
<d:quota-used-bytes />
<d:quota-available-bytes />
<oc:permissions xmlns:oc="http://owncloud.org/ns" />
<oc:id xmlns:oc="http://owncloud.org/ns" />
<oc:size xmlns:oc="http://owncloud.org/ns" />
<oc:favorite xmlns:oc="http://owncloud.org/ns" />
</d:prop>
</d:select>
<d:from>
<d:scope>
<d:href>/files/test</d:href>
<d:depth>infinity</d:depth>
</d:scope>
</d:from>
<d:where>
<d:like>
<d:prop>
<d:getcontenttype />
</d:prop>
<d:literal>image/%</d:literal>
</d:like>
</d:where>
<d:orderby>
<d:order>
<d:prop>
<d:getlastmodified />
</d:prop>
<d:descending />
</d:order>
</d:orderby>
</d:basicsearch>
</d:searchrequest>
Both are for user "test", therefore scope is files/test
My finding is that this is some related to amount of total files and about numbers of found files.
so if you have 100 files and it finds 10 or you have 10k files and it finds 10 files makes a huge difference.
tobiasKaminsky
on 7 May 2020
Hmm. I can reproduce it somehow on my production instance.
I'm watching the access.log and ...
- Open Nextcloud app and switch to photos and see 34 photos
- Scroll down and see 51 photos
- Scroll down again, see the loading animation
[07/May/2020:21:35:47 +0200] "SEARCH /remote.php/dav HTTP/1.1" 207 26396 "-" "Mozilla/5.0 (Android) Nextcloud-android/3.11.1"
[07/May/2020:21:35:53 +0200] "SEARCH /remote.php/dav HTTP/1.1" 207 13651 "-" "Mozilla/5.0 (Android) Nextcloud-android/3.11.1"
[07/May/2020:21:35:55 +0200] "SEARCH /remote.php/dav HTTP/1.1" 207 787 "-" "Mozilla/5.0 (Android) Nextcloud-android/3.11.1"
It seems the server sent a response already but the app still shows the loading animation. But the third response is also much smaller then the first two.
SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `file` LEFT JOIN `oc_filecache_extended` `fe` ON `file`.`fileid` = `fe`.`fileid` WHERE (`storage` = 25) AND (`mimepart` = 10) ORDER BY `mtime` desc
Is the query for the first xml. Replace storage = 25 with the actual id for your storage. It seems the query uses a index for storage and mimepart but the order by mtime desc needs some additional work.
create index search_photos_test
on oc_filecache (storage asc, mimepart asc, mtime desc);
Is there a difference if you create a index like above?
kesselb
on 7 May 2020
Any news on this?
What is a sensible expectation on reaction time, i.e. display of the first pictures?
On a Qnap or Synology with their respective app the previews show up instantly. The time for loading the pictures does also not depend on the collection size.
For 60.000 pictures Qnap takes less than a second to start displaying pictures. Nextcloud takes 45 seconds for the first 45, then again a very long time for the next 45 etc. Sometimes it even displays no pictures found, if it takes too long.
svenb1234
on 8 Aug 2020
Same issue here (Android 3.12.1, Server last official docker image, behind Traefik).
I have also this issue (infinite loading) on Activity tab. I don't know if it's related.
julientard
on 13 Aug 2020
Hi Together,
I'm experiencing the same Issue. I Just wanted to give the request send by the photos app, as this request is running without problems.
<?xml version="1.0" encoding="UTF-8"?>
<d:searchrequest xmlns:d="DAV:"
xmlns:oc="http://owncloud.org/ns"
xmlns:nc="http://nextcloud.org/ns"
xmlns:ns="https://github.com/icewind1991/SearchDAV/ns"
xmlns:ocs="http://open-collaboration-services.org/ns">
<d:basicsearch>
<d:select>
<d:prop>
<oc:fileid />
<d:getlastmodified />
<d:getetag />
<d:getcontenttype />
<d:getcontentlength />
<nc:has-preview />
<oc:favorite />
<d:resourcetype />
</d:prop>
</d:select>
<d:from>
<d:scope>
<d:href>/files/user</d:href>
<d:depth>infinity</d:depth>
</d:scope>
</d:from>
<d:where>
<d:and>
<d:or>
<d:eq>
<d:prop>
<d:getcontenttype/>
</d:prop>
<d:literal>image/jpeg</d:literal>
</d:eq>
<d:eq>
<d:prop>
<d:getcontenttype/>
</d:prop>
<d:literal>image/heic</d:literal>
</d:eq>
<d:eq>
<d:prop>
<d:getcontenttype/>
</d:prop>
<d:literal>video/mp4</d:literal>
</d:eq>
<d:eq>
<d:prop>
<d:getcontenttype/>
</d:prop>
<d:literal>video/quicktime</d:literal>
</d:eq>
</d:or>
<d:eq>
<d:prop>
<oc:owner-id/>
</d:prop>
<d:literal>user</d:literal>
</d:eq>
</d:and>
</d:where>
<d:orderby>
<d:order>
<d:prop><d:getlastmodified/></d:prop>
<d:descending/>
</d:order>
</d:orderby>
<d:limit>
<d:nresults>35</d:nresults>
<ns:firstresult>0</ns:firstresult>
</d:limit>
</d:basicsearch>
</d:searchrequest>
tacruc
on 3 Oct 2020
@tobiasKaminsky I don't see the difference between the limited and unlimted request you posted.
tacruc
on 3 Oct 2020
Ok I played arround with the queries a little:
54s:
<?xml version="1.0" encoding="UTF-8"?>
<d:searchrequest xmlns:d="DAV:" xmlns:nc="http://nextcloud.org/ns" xmlns:ns="https://github.com/icewind1991/SearchDAV/ns" xmlns:oc="http://owncloud.org/ns" xmlns:ocs="http://open-collaboration-services.org/ns">
<d:basicsearch>
<d:select>
<d:prop>
<d:displayname />
<d:getcontenttype />
<d:resourcetype />
<d:getcontentlength />
<d:getlastmodified />
<d:creationdate />
<d:getetag />
<d:quota-used-bytes />
<d:quota-available-bytes />
<oc:permissions />
<oc:id />
<oc:size />
<oc:favorite />
</d:prop>
</d:select>
<d:from>
<d:scope>
<d:href>/files/test</d:href>
<d:depth>infinity</d:depth>
</d:scope>
</d:from>
<d:where>
<d:like>
<d:prop>
<d:getcontenttype />
</d:prop>
<d:literal>image/%</d:literal>
</d:like>
</d:where>
<d:orderby>
<d:order>
<d:prop>
<d:getlastmodified />
</d:prop>
<d:descending />
</d:order>
</d:orderby>
<d:limit>
<d:nresults>35</d:nresults>
<ns:firstresult>0</ns:firstresult>
</d:limit>
</d:basicsearch>
</d:searchrequest>
457ms:
<?xml version="1.0" encoding="UTF-8"?>
<d:searchrequest xmlns:d="DAV:" xmlns:nc="http://nextcloud.org/ns" xmlns:ns="https://github.com/icewind1991/SearchDAV/ns" xmlns:oc="http://owncloud.org/ns" xmlns:ocs="http://open-collaboration-services.org/ns">
<d:basicsearch>
<d:select>
<d:prop>
<d:displayname />
<d:getcontenttype />
<d:resourcetype />
<d:getcontentlength />
<d:getlastmodified />
<d:creationdate />
<d:getetag />
<d:quota-used-bytes />
<d:quota-available-bytes />
<oc:permissions />
<oc:id />
<oc:size />
<oc:favorite />
</d:prop>
</d:select>
<d:from>
<d:scope>
<d:href>/files/test</d:href>
<d:depth>infinity</d:depth>
</d:scope>
</d:from>
<d:where>
<d:and>
<d:or>
<d:eq>
<d:prop>
<d:getcontenttype />
</d:prop>
<d:literal>image/jpeg</d:literal>
</d:eq>
<d:eq>
<d:prop>
<d:getcontenttype />
</d:prop>
<d:literal>image/heic</d:literal>
</d:eq>
<d:eq>
<d:prop>
<d:getcontenttype />
</d:prop>
<d:literal>video/mp4</d:literal>
</d:eq>
<d:eq>
<d:prop>
<d:getcontenttype />
</d:prop>
<d:literal>video/quicktime</d:literal>
</d:eq>
</d:or>
<d:eq>
<d:prop>
<oc:owner-id />
</d:prop>
<d:literal>arne</d:literal>
</d:eq>
</d:and>
</d:where>
<d:orderby>
<d:order>
<d:prop>
<d:getlastmodified />
</d:prop>
<d:descending />
</d:order>
</d:orderby>
<d:limit>
<d:nresults>35</d:nresults>
<ns:firstresult>0</ns:firstresult>
</d:limit>
</d:basicsearch>
</d:searchrequest>
So the second query is 100 times faster.
tacruc
on 3 Oct 2020
If I understand correctly the difference is that defining which mime type is requested reduces the response time. If so I would suggest to set the query to the default formats of camera apps, i.e. JPEG and MP4 for Android and whatever for iOS.
However, users with raw formats etc. should not be excluded, i.e. one should be able to enable more formats/mime types in the settings of the app.
svenb1234
on 22 Oct 2020
@nextcloud/server-triage any idea how/if we can enhance this without explicitly specifying mimetimes?
Or to phrase it the other way around: why is
tobiasKaminsky
on 23 Nov 2020
Searching in all storages you have access to is just damn slow.
Basically it explodes because of the nr of shares you have.
rullzer
on 23 Nov 2020
True, but I guess in this case it is a different problem.
(wrote it also in engineering chat).
One query against our server with "image/%" does not succeed and ends with 500.
Same query but with "image/jpeg" returns in ~10s with correct images.
tobiasKaminsky
on 23 Nov 2020
So it seems that if we have another endpoint or enhance the mimetype search, that it helps a lot.
See also above test by @tacruc: on his server it is around 100x faster.
tobiasKaminsky
on 23 Nov 2020
So it seems that if we have another endpoint or enhance the mimetype search
this doesn't seems to be a different endpoint, just different syntax on the mimetype search. I'm not surprised, searching with LIKE% and searching for exact string match doesn't really compare in termes of performances.
In photos we do the same https://github.com/nextcloud/photos/blob/master/src/services/PhotoSearch.js#L94
For the list of available mimes: https://github.com/nextcloud/photos/blob/7c718eae57b2a5a9a27ce3e0bce2d3bffff7057e/lib/AppInfo/Application.php#L36-L54
skjnldsv
on 23 Nov 2020
So another test against our server:
search for image/jpeg -> 12s
search for image/jpeg or image/png -> >35s and thus 500 error code
search for image/jpeg AND limit to own user -> 1-3s
search for image/jpeg or image/png AND limit to own user -> 1-3s
searching for any image (image/%) -> 1-3s
So it basically boils down to reducing scope to own user and then in it amazingly fast.
tobiasKaminsky
on 23 Nov 2020
So it basically boils down to reducing scope to own user and then in it amazingly fast.
Yes, that is why we don't show shares in Photos
skjnldsv
on 23 Nov 2020
So could it be a fix to limit the scope to the own user for now, and fix the shares with the metadata topic later?
tacruc
on 23 Nov 2020
While I understand that it's the easiest "fix", I hope that it is obvious that ignoring shares on a system whose main purpose is sharing without copying, is failure by design.
Just imagine how useful the Photos app in the web client and the photos section in the Android app are to a family that wants to share family photos. Should they only use the file view?
I understand that it might be useful to limit the photos view to certain shares, but the user should be able to select those.
Another aspect as to why ignoring shares in general and without the possibility to select folders does not make sense: why would the same amount of pictures be significantly slower just because they are in a shared folder - on the same host? If I have 2.000 pictures with user A and 2.000 with user B (shared) it should be as fast as having 4.000 pictures just for user A.
Of what use is ignoring shares if one does not know their size and impact? On the same host, if I have 50.000 pictures and the share only 200, I would still not be able to view them in the app although the only represent a fraction of the total number of pictures to display.
It seems there is a general performance problem with folders with many items. Actually, to be more precise, there seems to be a performance issue with displaying the first items within a folder with many items instantly. Even in file view and not a shared folder. Opening a folder with 5000 photos or videos takes a very long time >20 seconds and the app stalls afterwards for even longer. Opening the same folder with the web client takes still very long but less than half the time and the UI is responding.
So even if Nextcloud seems to be slow, why would the app be any slower than the web client?
svenb1234
on 23 Nov 2020
So even if Nextcloud seems to be slow, why would the app be any slower than the web client?
When you say web client, you mean the files app?
Because files is not searching through all your files for photos, only listing per directory.
Regarding the photos app, we all agree that this isn't really working the best we would like. But ya know, time... we can't fix everything in one day!
skjnldsv
on 24 Nov 2020
I change it now on Android, so it is in sync with photo app.
Once there is progress in tuning the search, please do not forget to mention me/ the clients đź’™
tobiasKaminsky
on 24 Nov 2020
When you say web client, you mean the files app?
Because files is not searching through all your files for photos, only listing per directory.
Regarding the photos app, we all agree that this isn't really working the best we would like. But ya know, time... we can't fix everything in one day!
There are two scenarios I compare the performance.
- Files web client Vs. files Android app view.
- Files web client app Vs. Photos web client app.
In both scenarios the compared apps do exactly the same, I.e. displaying items from just one folder.
Yes I mean the files app in the web client and the file view in the Android app. Opening the same folder is a lot slower and stalls when using the android app. Folder with 5000 pictures in it.
But there is also a performance issue with the photos web client app compared to the files web client app. If I use the "your albums" or whatever the "walk the folders" view on the photos app is called and navigate to a folder with 5000 photos it shows "no files" for a long time and then starts to display the items. It does not have to search through all photos but just the items in that folder. Using the file app on the web client is a lot faster in opening that folder and displaying the first few items. Same action, different performance.
Furthermore. Querying a database for the first 40 or so images from its index should be done in less than a second. From then on, only loading the previews takes some time.
svenb1234
on 24 Nov 2020
In both scenarios the compared apps do exactly the same, I.e. displaying items from just one folder.
Depending on what part of the Photos app you're refering to, this sentence is not true :)
The Folder/Album section have the same performances as the web Files app, as they're using the same methods (only files have pagination).
Here we're talking about the TImeline/Your photos view only, that search through your entire root filesystem for images. The performances is nothing to be compared of :)
skjnldsv
on 25 Nov 2020
@skjnldsv https://github.com/nextcloud/android/issues/7245#issuecomment-734139263
There is a query that seems to work quite fast.
What do you think?
tobiasKaminsky
on 2 Dec 2020
How does that compare to other gallery apps?
svenb1234
on 2 Dec 2020
Related issues
ThomasLeister
·
3Comments
MariusBluem
·
3Comments
rullzer
·
3Comments
mama21mama
·
3Comments
ChristophWurst
·
3Comments
Most helpful comment
I change it now on Android, so it is in sync with photo app.
Once there is progress in tuning the search, please do not forget to mention me/ the clients đź’™