search

pulsejet / memories

Fast, modern and advanced photo management suite. Runs as a Nextcloud app.
https://memories.gallery
GNU Affero General Public License v3.0
2.89k stars 77 forks source link

503 on /memories/api/faces #114

Closed illnesse closed 1 year ago

illnesse commented 1 year ago

Used to work fine & super fast last night, all i did in the meantime was updating memories and recognize, then did a memories:index. Now it stays at pending, then times out, no errors/warning in the log, latest version. Everything else loads instantly.

Once i open the people tab i see 2 mariadb threads stuck at 94% cpu, otherwise system load is low, lots of free resources

Photos/people works, although uses extreme amounts of cpu and memory clientside, even on a high end system, due to its bad implementation. It just freezes after a few hundred faces and then the browser dies a painful death

MariaDB [nextcloud]> select count(*) from oc_recognize_face_detections;
+----------+
| count(*) |
+----------+
|    36632 |
+----------+
1 row in set (0.005 sec)

MariaDB [nextcloud]> select count(*) from oc_recognize_face_clusters;
+----------+
| count(*) |
+----------+
|     4475 |
+----------+
1 row in set (0.001 sec)
illnesse commented 1 year ago

I did some mysql config adjustments and it now sometimes loads the clusters again. Although the clusters with the most faces in them still stay grey and there are a couple mariadb processes stuck at 100% now

image

illnesse commented 1 year ago

clicking on one of the timed out faces gives me another timeout for apps/memories/api/days?face=root%2FPerson+Name

pulsejet commented 1 year ago

Hmm. Does the timeline and folders view work fine or that has this issue too?

illnesse commented 1 year ago

It's just the faces

illnesse commented 1 year ago 
# Time: 221026 11:22:23
# User@Host: nextcloud[nextcloud] @ localhost []
# Thread_id: 82  Schema: nextcloud  QC_hit: No
# Query_time: 137.486448  Lock_time: 0.000098  Rows_sent: 1227  Rows_examined: 449157
# Rows_affected: 0  Bytes_sent: 23094
SET timestamp=1666783343;
SELECT `rfc`.`id`, `rfc`.`user_id`, `rfc`.`title`, COUNT(DISTINCT m.fileid) AS `count` FROM `oc_recognize_face_clusters` `rfc` INNER JOIN `oc_recognize_face_detections` `rfd` ON `rfc`.`id` = `rfd`.`cluster_id` INNER JOIN `oc_memories` `m` ON `m`.`fileid` = `rfd`.`file_id` INNER JOIN `oc_filecache` `f` ON (`f`.`fileid` = `m`.`fileid`) AND (`f`.`parent` IN (WITH RECURSIVE cte_folders(fileid) AS (
                    SELECT
                        f.fileid
                    FROM
                        oc_filecache f
                    WHERE
                        f.fileid = 4473335
                    UNION ALL
                    SELECT
                        f.fileid
                    FROM
                        oc_filecache f
                    INNER JOIN cte_folders c
                        ON (f.parent = c.fileid
                            AND f.mimetype = 2
                            AND f.fileid NOT IN (-1)
                        )
                )
                SELECT
                    fileid
                FROM
                    cte_folders
                )) GROUP BY `rfc`.`id` ORDER BY rfc.title <> '' DESC, `count` DESC, `rfc`.`id` ASC;
illnesse commented 1 year ago 
MariaDB [nextcloud]> select count(*) from oc_filecache;
+----------+
| count(*) |
+----------+
|  1247761 |
+----------+

wonder why filecache has so many rows too, i only have like 120k files in storage, any ideas?

apparently i have smb connections in my oc_storages that aren't in use at all and are not showing up in the ui, with 100k rows in oc_filecache referencing them.. ?

pulsejet commented 1 year ago 
MariaDB [nextcloud]> select count(*) from oc_filecache;
+----------+
| count(*) |
+----------+
|  1247761 |
+----------+

wonder why filecache has so many rows too, i only have like 120k files in storage, any ideas?

apparently i have smb connections in my oc_storages that aren't in use at all and are not showing up in the ui, with 100k rows in oc_filecache referencing them.. ?

Maybe you need occ files:cleanup? There are also other files in there, e.g. previews.

illnesse commented 1 year ago

Oh alright, should be the previews then, doing a container backup right now so i can mess around properly

pulsejet commented 1 year ago

@illnesse this is hard to test for me since I don't run recognize in production myself (yet). Basically the potential fix is adding an index on file_id on oc_recognize_face_detections. Could try this and see if it fixes the issue? Just need to run the SQL:

CREATE INDEX memories_fileid_idx ON oc_recognize_face_detections (file_id);
pulsejet commented 1 year ago

Also I'd like to know how many rows this table has oc_recognize_face_detections

illnesse commented 1 year ago

Also I'd like to know how many rows this table has oc_recognize_face_detections

see 1st post

adding an index fixed the issue, that was the next thing i was going to check, thanks!

pulsejet commented 1 year ago

Great, thanks! Please leave this open till it's actually fixed. I could repro a similar issue with tags. This is deeper than it looks.

illnesse commented 1 year ago

getting a 7 sec wait time for /apps/memories/tags too btw, even though response is [ ]

pulsejet commented 1 year ago

@illnesse can you delete the index you added and test with v4.3.8?

pulsejet commented 1 year ago

Out of curiosity, how does the People view of the Photos 2.0 app work with so many faces?

illnesse commented 1 year ago

@illnesse can you delete the index you added and test with v4.3.8?

Slightly faster than before i believe but still pretty bad, index is kinda needed. It loaded instantly after a reload but probably due to sql cache, if i wait a couple minutes it invalidates the cache and things get slow again (you can even tell which clusters haven't been in view for a longer time, they take longer to load).

See sql log for the slowest queries below

# Query_time: 14.579012  Lock_time: 0.008200  Rows_sent: 15  Rows_examined: 475
# Query_time: 21.674801  Lock_time: 0.008040  Rows_sent: 15  Rows_examined: 1435
# Query_time: 23.547120  Lock_time: 0.013047  Rows_sent: 15  Rows_examined: 419
# Query_time: 24.900378  Lock_time: 0.011393  Rows_sent: 15  Rows_examined: 6131
# Query_time: 12.418584  Lock_time: 0.012614  Rows_sent: 15  Rows_examined: 269
# Query_time: 27.636710  Lock_time: 0.011348  Rows_sent: 15  Rows_examined: 14165
# Query_time: 5.678734  Lock_time: 0.008149  Rows_sent: 15  Rows_examined: 191
# Query_time: 26.428310  Lock_time: 0.009210  Rows_sent: 15  Rows_examined: 355
# Query_time: 28.937850  Lock_time: 0.007806  Rows_sent: 15  Rows_examined: 15065
# Query_time: 29.287116  Lock_time: 0.007049  Rows_sent: 15  Rows_examined: 6887
# Query_time: 29.604388  Lock_time: 0.009020  Rows_sent: 15  Rows_examined: 1919
# Query_time: 29.650191  Lock_time: 0.009424  Rows_sent: 15  Rows_examined: 5379
# Query_time: 29.832278  Lock_time: 0.009896  Rows_sent: 15  Rows_examined: 701
# Query_time: 29.966336  Lock_time: 0.009037  Rows_sent: 15  Rows_examined: 915
# Query_time: 27.540527  Lock_time: 0.013663  Rows_sent: 15  Rows_examined: 311
# Query_time: 30.750450  Lock_time: 0.011090  Rows_sent: 15  Rows_examined: 3687
# Query_time: 29.200923  Lock_time: 0.013985  Rows_sent: 15  Rows_examined: 357
# Query_time: 31.209311  Lock_time: 0.008668  Rows_sent: 15  Rows_examined: 489
# Query_time: 20.038356  Lock_time: 0.013795  Rows_sent: 15  Rows_examined: 99
# Query_time: 20.674474  Lock_time: 0.005975  Rows_sent: 15  Rows_examined: 95
# Query_time: 22.703237  Lock_time: 0.005704  Rows_sent: 15  Rows_examined: 115
# Query_time: 22.875748  Lock_time: 0.008519  Rows_sent: 15  Rows_examined: 111
# Query_time: 22.983000  Lock_time: 0.007801  Rows_sent: 15  Rows_examined: 111
# Query_time: 23.365393  Lock_time: 0.005947  Rows_sent: 15  Rows_examined: 99
# Query_time: 23.555461  Lock_time: 0.013267  Rows_sent: 15  Rows_examined: 87
# Query_time: 24.686692  Lock_time: 0.008230  Rows_sent: 15  Rows_examined: 107
# Query_time: 24.748149  Lock_time: 0.006089  Rows_sent: 15  Rows_examined: 95
# Query_time: 25.609602  Lock_time: 0.006310  Rows_sent: 15  Rows_examined: 107
# Query_time: 25.116815  Lock_time: 0.013458  Rows_sent: 15  Rows_examined: 95
# Query_time: 26.128691  Lock_time: 0.006087  Rows_sent: 15  Rows_examined: 99
# Query_time: 26.224378  Lock_time: 0.006274  Rows_sent: 15  Rows_examined: 105
# Query_time: 25.742336  Lock_time: 0.013850  Rows_sent: 15  Rows_examined: 97
# Query_time: 25.934329  Lock_time: 0.013529  Rows_sent: 15  Rows_examined: 91
# Query_time: 16.463199  Lock_time: 0.010630  Rows_sent: 15  Rows_examined: 399
# Query_time: 16.473760  Lock_time: 0.008959  Rows_sent: 15  Rows_examined: 181
# Query_time: 6.053356  Lock_time: 0.012929  Rows_sent: 15  Rows_examined: 141
# Query_time: 24.350923  Lock_time: 0.005510  Rows_sent: 15  Rows_examined: 239
# Query_time: 24.789033  Lock_time: 0.005637  Rows_sent: 15  Rows_examined: 241
# Query_time: 29.404318  Lock_time: 0.010294  Rows_sent: 15  Rows_examined: 483
# Query_time: 27.801490  Lock_time: 0.013560  Rows_sent: 15  Rows_examined: 127

image image

Frontend load times once cached: 👍🏻 

1217 requests
7.8 MB transferred
58.8 MB resources
Finish: 6.38 s
DOMContentLoaded: 1.40 s
Load: 2.00 s

Out of curiosity, how does the People view of the Photos 2.0 app work with so many faces?

Simple answer: It doesn't.

image

server is a 16 core 16gb lxc container with redis and php opcache etc, barely any load (0.3)

The tab thread in my chrome based browser however is stuck at 86% cpu now (this is a 32 core machine with 128gb ram) It's still trying to load faces but it's so awfully slow and spitting errors, i'm almost unable to scroll through

service-worker.js:1 Uncaught (in promise) DOMException: Cache.put() encountered a network error
xhr.js:220          PROPFIND https://nextcloud.int.root.fail/remote.php/dav/recognize/root/faces/9106/ 404
ConsoleLogger.js:64 [ERROR] photos: Error fetching face files {level: 2, app: 'photos', uid: 'root', status: 404, response: {…}}

I put it out of its misery after 10 minutes of loading time, only a tiny fraction of clusters displayed amount of faces, no images at all.

illnesse commented 1 year ago

Would it maybe help speed up the queries if we delete all those empty cluster leftovers from merging/moving faces? i have 4475, only maybe 1200 of those actually have faces.

pulsejet commented 1 year ago

It should have created the index, so it shouldn't be slow anymore. Did you run the migrations after upgrade? Maybe try occ memories:index and it should create the index on the recognize table (otherwise something is broken).

illnesse commented 1 year ago

Oh i see, i installed the update first, then removed the index ;)

pulsejet commented 1 year ago

Ahh I see. Running index should still re-add it.

illnesse commented 1 year ago

image

Checked 103488 files in 122.483 sec
2405 not valid media items
101083 skipped because unmodified
0 (re-)processed

looking good

pulsejet commented 1 year ago

So to confirm, is it usable after running this (is the index being created right?)

illnesse commented 1 year ago

Actually yes.. seems like it never actually removed the index, since memories added a new one with a different name ( memories_fileid_idx vs. memories_fileid_id )

it looks like this now: image

Same behaviour like above (slow until cached)

pulsejet commented 1 year ago

Something is off here. It really shouldn't take 12s to examine 25 rows 👀

illnesse commented 1 year ago

I disabled slow query logging again and rebooted to rule out any other issues, getting consistent times now (the weird slow until cached behaviour is gone):

people list:

149 requests
6.6 MB transferred
29.9 MB resources
Finish: 7.49 s
DOMContentLoaded: 1.28 s
Load: 1.35 s

single people page still seems a little slow:

123 requests
1.3 MB transferred
30.3 MB resources
Finish: 13.41 s
DOMContentLoaded: 1.11 s
Load: 1.16 s

i'll keep an eye on it while i clean up more clusters and keep you posted

illnesse commented 1 year ago

image

normal timeline became a bit slower too

pulsejet commented 1 year ago

Couple of questions:

  1. What kind of ballpark loading time did you have before 3.3.7?
  2. Can you confirm the memories index exists on oc_filecache?
illnesse commented 1 year ago

reboot

i rebooted the whole server now (not just the container) - behaviour hasn't changed, timeline performance still bad

  1. What kind of ballpark loading time did you have before 3.3.7?

it wasn't noticeable, < 3s, same filecache size but less face detections

  1. Can you confirm the memories index exists on oc_filecache?

image

After the reboot, people tab isn't loading any more (although clusters and face detections seem fine in the db) probably because of this More than 1000 expressions in a list are not allowed on Oracle

illnesse commented 1 year ago 
{
   "reqId":"Ps2E2kA5DLstnKo3un54",
   "level":3,
   "time":"2022-10-27T13:13:27+00:00",
   "remoteAddr":"192.168.2.204",
   "user":"root",
   "app":"core",
   "method":"GET",
   "url":"/apps/memories/api/faces",
   "message":"More than 1000 expressions in a list are not allowed on Oracle.",
   "userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36",
   "version":"25.0.0.18",
   "exception":{
      "Exception":"Doctrine\\DBAL\\Query\\QueryException",
      "Message":"More than 1000 expressions in a list are not allowed on Oracle.",
      "Code":0,
      "Trace":[
         {
            "file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
            "line":294,
            "function":"execute",
            "class":"OC\\DB\\QueryBuilder\\QueryBuilder",
            "type":"->"
         },
         {
            "file":"/var/www/nextcloud/apps/memories/lib/Db/TimelineQueryFaces.php",
            "line":76,
            "function":"executeQuery",
            "class":"OC\\DB\\QueryBuilder\\QueryBuilder",
            "type":"->"
         },
         {
            "file":"/var/www/nextcloud/apps/memories/lib/Controller/ApiController.php",
            "line":281,
            "function":"getFaces",
            "class":"OCA\\Memories\\Db\\TimelineQuery",
            "type":"->"
         },
         {
            "file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
            "line":225,
            "function":"faces",
            "class":"OCA\\Memories\\Controller\\ApiController",
            "type":"->"
         },
         {
            "file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
            "line":133,
            "function":"executeController",
            "class":"OC\\AppFramework\\Http\\Dispatcher",
            "type":"->"
         },
         {
            "file":"/var/www/nextcloud/lib/private/AppFramework/App.php",
            "line":172,
            "function":"dispatch",
            "class":"OC\\AppFramework\\Http\\Dispatcher",
            "type":"->"
         },
         {
            "file":"/var/www/nextcloud/lib/private/Route/Router.php",
            "line":298,
            "function":"main",
            "class":"OC\\AppFramework\\App",
            "type":"::"
         },
         {
            "file":"/var/www/nextcloud/lib/base.php",
            "line":1047,
            "function":"match",
            "class":"OC\\Route\\Router",
            "type":"->"
         },
         {
            "file":"/var/www/nextcloud/index.php",
            "line":36,
            "function":"handleRequest",
            "class":"OC",
            "type":"::"
         }
      ],
      "File":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
      "Line":264,
      "message":"More than 1000 expressions in a list are not allowed on Oracle.",
      "query":"SELECT `rfc`.`id`, `rfc`.`user_id`, `rfc`.`title`, COUNT(DISTINCT m.fileid) AS `count` FROM `*PREFIX*recognize_face_clusters` `rfc` INNER JOIN `*PREFIX*recognize_face_detections` `rfd` ON `rfc`.`id` = `rfd`.`cluster_id` INNER JOIN `*PREFIX*memories` `m` ON `m`.`fileid` = `rfd`.`file_id` INNER JOIN `*PREFIX*filecache` `f` ON (`f`.`fileid` = `m`.`fileid`) AND (`f`.`parent` IN (:dcValue1)) GROUP BY `rfc`.`id` ORDER BY rfc.title <> '' DESC, `count` DESC, `rfc`.`id` ASC",
      "exception":{

      },
      "CustomMessage":"More than 1000 expressions in a list are not allowed on Oracle."
   }
}

for 

/apps/memories/api/days/123
/apps/memories/api/tags
/apps/memories/api/faces

etc

illnesse commented 1 year ago

https://stackoverflow.com/a/29096874/20207735

pulsejet commented 1 year ago

That is similar to what it's doing now with v4.4+. Btw for more background, we had a single index on the actual file path earlier (before v4.3.8). This was super fast, but mysql doesn't like it (#106), and the index tends to get very bulky because the column is VARCHAR(4000). So the newer design uses a CTE to query the folders and then directly look for files in these. Before 4.4, this query is done in PHP; now everything is done in SQL again, by joining the CTE to the base query directly. This should hopefully fix this particular issue of 1000s of operators.

Btw my dev setup now has ~40k images and faces/timeline loads within 100ms for me (the query part). Maybe your queries were slow because Oracle doesn't like so many operators in the first place.

illnesse commented 1 year ago

Sounds tricky, i have to admit i haven't even really looked at the source yet but i'll try to find some free time i can dedicate. I just built master, people page still empty, it loads instantly but with an empty [ ] response for /faces, Mysql 1000 operator errors are gone though. Tags page works now, very fast too. Some sidebar vue errors but i'll file those elsewhere i guess

pulsejet commented 1 year ago

Huh. Is the timeline fine? (also performance-wise). Also I'm assuming Photos (the app) still shows some faces.

illnesse commented 1 year ago

Yup and performance is fine too, photos/people works, no errors in log or whatsoever for memories/people page, just []

pulsejet commented 1 year ago

Any query log? Also I assume the status code is 200 for /apps/memories/api/faces.

illnesse commented 1 year ago

200 yes

221028 17:09:56    232 Connect  nextcloud@localhost on nextcloud using Socket
                   232 Query    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
                   232 Query    SET SESSION AUTOCOMMIT=1
                   232 Query    SELECT * FROM `oc_appconfig`
                   232 Query    SELECT `uid`, `displayname`, `password` FROM `oc_users` WHERE `uid_lower` = 'root'
                   232 Query    SELECT * FROM `oc_authtoken` WHERE (`token` = 'asdsadsadasdasd') AND (`version` = 2)
                   232 Query    SELECT `appid`, `configkey`, `configvalue` FROM `oc_preferences` WHERE `userid` = 'root'
                   232 Query    SELECT `class`, `entity`, CAST(`events` AS CHAR) AS `events` FROM `oc_flow_operations` WHERE `events` <> '[]' GROUP BY `class`, `entity`, CAST(`events` AS CHAR)
                   232 Query    SELECT `gu`.`gid`, `g`.`displayname` FROM `oc_group_user` `gu` LEFT JOIN `oc_groups` `g` ON `gu`.`gid` = `g`.`gid` WHERE `uid` = 'root'
                   232 Query    SELECT `provider_id`, `enabled` FROM `oc_twofactor_providers` WHERE `uid` = 'root'
                   232 Query    SELECT `storage_id`, `root_id`, `user_id`, `mount_point`, `mount_id`, `f`.`path`, `mount_provider_class` FROM `oc_mounts` `m` INNER JOIN `oc_filecache` `f` ON `m`.`root_id` = `f`.`fileid` WHERE `user_id` = 'root'
                   232 Query    SELECT `id`, `numeric_id`, `available`, `last_checked` FROM `oc_storages` WHERE `id` = 'home::root'
                   232 Query    SELECT `fileid` FROM `oc_filecache` WHERE (`storage` = 2) AND (`path_hash` = 'd41d8cd98f00b204e9800998ecf8427e')
                   232 Query    SELECT `m`.`mount_id`, `mount_point`, `storage_backend`, `auth_backend`, `priority`, `m`.`type` FROM `oc_external_mounts` `m` INNER JOIN `oc_external_applicable` `a` ON `m`.`mount_id` = `a`.`mount_id` WHERE ((`a`.`type` = 1) AND (`a`.`value` IS NULL)) OR ((`a`.`type` = 3) AND (`a`.`value` = 'root')) OR ((`a`.`type` = 2) AND (`a`.`value` IN ('admin')))
                   232 Query    SELECT `type`, `value`, `mount_id` FROM `oc_external_applicable` WHERE `mount_id` IN (5, 6)
                   232 Query    SELECT `key`, `value`, `mount_id` FROM `oc_external_config` WHERE `mount_id` IN (5, 6)
                   232 Query    SELECT `key`, `value`, `mount_id` FROM `oc_external_options` WHERE `mount_id` IN (5, 6)
                   232 Query    SELECT `id`, `numeric_id`, `available`, `last_checked` FROM `oc_storages` WHERE `id` IN ('local::/dir.1/', 'local::/dir.2/')
                   232 Query    SELECT `fileid` FROM `oc_filecache` WHERE (`storage` = 9) AND (`path_hash` = 'd41d8cd98f00b204e9800998ecf8427e')
                   232 Query    SELECT `fileid` FROM `oc_filecache` WHERE (`storage` = 11) AND (`path_hash` = 'd41d8cd98f00b204e9800998ecf8427e')
                   232 Query    SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `unencrypted_size`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `filecache` LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid` WHERE (`storage` = 9) AND (`path_hash` = 'd41d8cd98f00b204e9800998ecf8427e')
                   232 Query    SELECT `id`, `mimetype` FROM `oc_mimetypes`
                   232 Query    SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `unencrypted_size`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `filecache` LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid` WHERE (`storage` = 9) AND (`path_hash` = 'd41d8cd98f00b204e9800998ecf8427e')
                   232 Query    SELECT `fileid` FROM `oc_filecache` WHERE (`storage` = 9) AND (`path_hash` = 'd41d8cd98f00b204e9800998ecf8427e')
                   232 Query    SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `unencrypted_size`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `filecache` LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid` WHERE `filecache`.`parent` = 5628943 ORDER BY `name` ASC
                   232 Query    START TRANSACTION
                   232 Query    COMMIT
                   232 Query    SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `unencrypted_size`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `filecache` LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid` WHERE (`storage` = 9) AND (`path_hash` = 'd41d8cd98f00b204e9800998ecf8427e')
                   232 Query    SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `filecache`.`name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `unencrypted_size`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `filecache` LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid` WHERE (`storage` = 9) AND (`path_hash` = '8e62557cf794a1098462faf10a839379')
                   232 Query    WITH RECURSIVE oc_cte_folders(fileid) AS (
        SELECT
            f.fileid
        FROM
            oc_filecache f
        WHERE
            f.fileid = 5628943
        UNION ALL
        SELECT
            f.fileid
        FROM
            oc_filecache f
        INNER JOIN oc_cte_folders c
            ON (f.parent = c.fileid
                AND f.mimetype = 2
                AND f.fileid <> 5628968
            )
    ) SELECT `rfc`.`id`, `rfc`.`user_id`, `rfc`.`title`, COUNT(DISTINCT m.fileid) AS `count` FROM `oc_recognize_face_clusters` `rfc` INNER JOIN `oc_recognize_face_detections` `rfd` ON `rfc`.`id` = `rfd`.`cluster_id` INNER JOIN `oc_memories` `m` ON `m`.`fileid` = `rfd`.`file_id` INNER JOIN `oc_filecache` `f` ON `f`.`fileid` = `m`.`fileid` INNER JOIN `oc_cte_folders` `cte_f` ON `f`.`parent` = `cte_f`.`fileid` GROUP BY `rfc`.`id` ORDER BY rfc.title <> '' DESC, `count` DESC, `rfc`.`id` ASC
                   232 Quit
pulsejet commented 1 year ago

That looks totally fine. Can you try changing your timeline path to / to see if that changes anything?

illnesse commented 1 year ago

i could only change it to " " but played around a bit and the only thing i managed was that timeline disappeared as well

pulsejet commented 1 year ago

Wow 😆 I'm stumped now. Maybe you need to try some query variations. Or perhaps the memories index is messed up and needs clearing?

illnesse commented 1 year ago

I tried the statement in mysql as well, empty set (this is the right one, correct?)

image

pulsejet commented 1 year ago

Yup. Try this maybe. If this works then your memories index is broken.

WITH RECURSIVE oc_cte_folders(fileid) AS (
  SELECT 
    f.fileid 
  FROM 
    oc_filecache f 
  WHERE 
    f.fileid = 5628943 
  UNION ALL 
  SELECT 
    f.fileid 
  FROM 
    oc_filecache f 
    INNER JOIN oc_cte_folders c ON (
      f.parent = c.fileid 
      AND f.mimetype = 2 
      AND f.fileid <> 5628968
    )
) 
SELECT 
  `rfc`.`id`, 
  `rfc`.`user_id`, 
  `rfc`.`title`, 
  COUNT(DISTINCT f.fileid) AS `count` 
FROM 
  `oc_recognize_face_clusters` `rfc` 
  INNER JOIN `oc_recognize_face_detections` `rfd` ON `rfc`.`id` = `rfd`.`cluster_id` 
  INNER JOIN `oc_filecache` `f` ON `f`.`fileid` = `rfd`.`file_id` 
  INNER JOIN `oc_cte_folders` `cte_f` ON `f`.`parent` = `cte_f`.`fileid` 
GROUP BY 
  `rfc`.`id` 
ORDER BY 
  rfc.title <> '' DESC, 
  `count` DESC, 
  `rfc`.`id` ASC
illnesse commented 1 year ago

Yup. Try this maybe. If this works then your memories index is broken.

Empty set (0.366 sec)

emptied query cache, dropped index on oc_filecache and did memories:index, still nada

pulsejet commented 1 year ago

This query isn't using memories anymore, so that's even weirder. How about this:

SELECT 
  `rfc`.`id`, 
  `rfc`.`user_id`, 
  `rfc`.`title`, 
  COUNT(DISTINCT f.fileid) AS `count` 
FROM 
  `oc_recognize_face_clusters` `rfc` 
  INNER JOIN `oc_recognize_face_detections` `rfd` ON `rfc`.`id` = `rfd`.`cluster_id` 
  INNER JOIN `oc_filecache` `f` ON `f`.`fileid` = `rfd`.`file_id` 
GROUP BY 
  `rfc`.`id`
illnesse commented 1 year ago

363 rows in set (0.039 sec)

pulsejet commented 1 year ago

@illnesse could you join an IM chat at gitter? https://gitter.im/pulsejet/memories Faster to debug that way.

363 rows in set (0.039 sec)

Interesting. I thought you had thousands of faces? (let's continue in gitter hopefully)

pulsejet commented 1 year ago

I believe the original issue is now fixed, so let's close this now.