Skip to content

Files search takes a long time with unified search #23835

Closed
Closed
Files search takes a long time with unified search#23835
Labels
0. Needs triagePending check for reproducibility or if it fits our roadmapbugfeature: searchhighneeds inforegression
Milestone
Nextcloud 26

Description

@jancborchardt
jancborchardt
opened on Nov 2, 2020

Depending on the amount of files, the file search component of unified search takes a long time to get results back. It’s the slowest of all – Talk, Mail and everything loads before. On our own instance, it takes roughly 40 seconds.

Also, there is no x in the input field during loading so it’s impossible to cancel the search – you have to wait for it to finish to start a different one. To fix this, we could replace the spinner with an x on hover/focus.

Gif for illustration, showing how it takes 40 seconds:
Search takes 40 seconds

(This is very related to the direct filtering of the current folder, which also doesn’t work: #23432 )

cc @nextcloud/search

Activity

  1. jancborchardt
    added
    bug
    1. to developAccepted and waiting to be taken care of
    feature: search
    on Nov 2, 2020

  2. skjnldsv commented on Nov 2, 2020

    @skjnldsv
    skjnldsv
    on Nov 2, 2020
    Member

    Also, there is no x in the input field during loading so it’s impossible to cancel the search – you have to wait for it to finish to start a different one. To fix this, we could replace the spinner with an x on hover/focus.

    Searching cancel other searches.
    You can always change your query and it should trigger the search again

  3. ChristophWurst commented on Nov 2, 2020

    @ChristophWurst
    ChristophWurst
    on Nov 2, 2020
    Member

    It's mainly slow because the files search implementation doesn't paginate. So you'd always build the full results list on the back-end before it's chopped into the individual pages. This doesn't scale, as you observed. Our instance just has a lot of files and shares, hence the slowness.

  4. andreas-p commented on Nov 5, 2020

    @andreas-p
    andreas-p
    on Nov 5, 2020

    @ChristophWurst non-pagination is NOT the problem:

    Pasting 152151 into the search term will yield a single result after ~4.7s (runtime taken from Firefox devtools).

    Executing a query directly on the PostgreSQL 12.0 server (2.6e6 rows in oc_filecache):

    • SELECT * FROM oc_filecache WHERE name LIKE '%152151%: 0.5s
    • SELECT * FROM oc_filecache WHERE name ILIKE '%152151%: 1.1s

    This makes NC file search at least 4 times slower than the database full table search.

    If the search term is not entered by copy/paste, but typed in, things get much worse, since each character will trigger a new query on the server, consuming more resources (when typing fast, the final search request will take 16s).

    @skjnldsv A new search does not cancel the running queries in the database backend.

  5. skjnldsv commented on Nov 5, 2020

    @skjnldsv
    skjnldsv
    on Nov 5, 2020
    Member

    @skjnldsv A new search does not cancel the running queries in the database backend.

    Right, it cancel the frontend request of course.

  6. andreas-p commented on Nov 5, 2020

    @andreas-p
    andreas-p
    on Nov 5, 2020

    I enabled log_min_duration_statement=800 in the PostgreSQL Server, and found that each search will execute the same query four times, which explains the x4 observed above.

  7. ChristophWurst commented on Nov 5, 2020

    @ChristophWurst
    ChristophWurst
    on Nov 5, 2020
    Member

    @ChristophWurst non-pagination is NOT the problem:

    If there was pagination the query would include more WHERE clauses to scope the range. It would therefore help.

    But yeah, ILIKE is expensive.

  8. andreas-p commented on Nov 5, 2020

    @andreas-p
    andreas-p
    on Nov 5, 2020

    Not really, if the result set is small, or not at all when using window functions, since the time consuming part is the sequential scan, not the result set transfer.

    For PostgreSQL, speed could be improved by not using name ILIKE :searchExpression (1.1s), but lower(name) ILIKE lower(:searchExpression) (0.8s) or even better lowercasedname LIKE lower(:searchExpression) (0.5s).

    But there are fruits hanging much lower:

    • eliminating those 3 exceeding queries, there's something going awfully wrong here.
    • delaying search for some 300-500ms after the last keystroke, to prevent multiple zombie queries eating database performance.

    More advanced:

    • split filesearch in two requests: First search the current directory only (my example has 4800 Files in the folder, <20ms), then the full scan. Quite often, only the current directory is of interest.

  9. andreas-p commented on Nov 10, 2020

    @andreas-p
    andreas-p
    on Nov 10, 2020

    I found the reason why the database was hammered with the same query 4x for a single search. They correspond with four shares from the same user. These result in four MountPoints, having different different roots on the same physical storage (same numericId). Unfortunately, the filtering code is buried very deep in CacheWrapper, and each wrapper retrieves the full data from its Cache instance before filtering.
    Hiding (parts of) the filtering from the View in separate Caches might be good programming practice in general, but is a real pain for big datasets. Can't see a way how to speed this up in a non-dirty way.

  10. ohthehugemanatee commented on Jan 26, 2021

    @ohthehugemanatee
    ohthehugemanatee
    on Jan 26, 2021

    Suggest re-titling this issue in line with #24029 , since this is a regression in behavior from NC19. At best, we have lost the "fast search" capability. At worst, we've lost "search" alltogether.

    My personal use case: updated to NC20 last night. Today, any search query will peg the mysql server and make nextcloud unresponsive. Having just performed a files:clean my oc_filecache table has 2057547 rows. It takes 2 seconds just to run the count query. :|

    edit: it works... just very very slowly. > 10 seconds to search testing. So far 280s for large files for youtube. In mariadb processlist I see:

    MariaDB [nextcloud]> show full processlist;
+-----+-------------+---------------------------------+-----------+---------+------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id  | User        | Host                            | db        | Command | Time | State                    | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                  | Progress |
+-----+-------------+---------------------------------+-----------+---------+------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
|   2 | system user |                                 | NULL      | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                  |    0.000 |
|   1 | system user |                                 | NULL      | Daemon  | NULL | InnoDB purge coordinator | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                  |    0.000 |
|   3 | system user |                                 | NULL      | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                  |    0.000 |
|   4 | system user |                                 | NULL      | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                  |    0.000 |
|   5 | system user |                                 | NULL      | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                  |    0.000 |
| 724 | nextcloud   | nextcloud.compose_default:52848 | nextcloud | Query   |  391 | Sending data             | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `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 (`name`  COLLATE utf8mb4_general_ci LIKE '%test%')      |    0.000 |
| 734 | nextcloud   | nextcloud.compose_default:52950 | nextcloud | Query   |  391 | Sending data             | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `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 (`name`  COLLATE utf8mb4_general_ci LIKE '%testing%')   |    0.000 |
| 746 | nextcloud   | nextcloud.compose_default:53052 | nextcloud | Query   |  390 | Sending data             | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `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 (`name`  COLLATE utf8mb4_general_ci LIKE '%testing%')   |    0.000 |
| 760 | nextcloud   | nextcloud.compose_default:53164 | nextcloud | Query   |  320 | Sending data             | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `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 (`name`  COLLATE utf8mb4_general_ci LIKE '%arge fil%')  |    0.000 |
| 769 | nextcloud   | nextcloud.compose_default:53284 | nextcloud | Query   |  320 | Sending data             | SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `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 (`name`  COLLATE utf8mb4_general_ci LIKE '%arge file%') |    0.000 |
| 771 | root        | localhost                       | nextcloud | Query   |    0 | Init                     | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                 |    0.000 |
+-----+-------------+---------------------------------+-----------+---------+------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
11 rows in set (0.001 sec)
  11. wiswedel
    added
    regression
    high
    on Jan 26, 2021

  12. icewind1991 commented on Jan 26, 2021

    @icewind1991
    icewind1991
    on Jan 26, 2021
    Member

    #25136 should help with search performance, how much of a difference it makes will depend greatly on setup specifics.

  13. 135 remaining items

  14. blizzz
    added this to the Nextcloud 26 milestone on Oct 19, 2022

  15. szaimen commented on Nov 26, 2022

    @szaimen
    szaimen
    on Nov 26, 2022
    Contributor

    Hi, please update to at least 23.0.12 and report back if it fixes the issue. Thank you!

  16. szaimen
    added
    needs info
    0. Needs triagePending check for reproducibility or if it fits our roadmap
    and removed
    1. to developAccepted and waiting to be taken care of
    on Nov 26, 2022

  17. pjft commented on Nov 26, 2022

    @pjft
    pjft
    on Nov 26, 2022
    Contributor

    I think this is good on 25 already.

  18. szaimen commented on Nov 26, 2022

    @szaimen
    szaimen
    on Nov 26, 2022
    Contributor

    Thanks for testing!

  19. szaimen
    closed this as completedon Nov 26, 2022

  20. pjft commented on Nov 26, 2022

    @pjft
    pjft
    on Nov 26, 2022
    Contributor

    For what it's worth, I think it was also good on 24, as I was running it for a few weeks before 25 was stable.

    Thanks for checking.

  21. gennaios commented on Nov 27, 2022

    @gennaios
    gennaios
    on Nov 27, 2022

    For those that have thousands of files and potentially many search results with each search, displaying five at a time is not an ideal solution. Does such merely mask the issue?

  22. pjft commented on Nov 27, 2022

    @pjft
    pjft
    on Nov 27, 2022
    Contributor

    I agree with the comment about the UI and only showing five results at a time, but it is unrelated to the performance issues or fixes. The performance issues were there when the UI was already only showing 5 results at a time, unless I'm mistaken.

    Nothing changed there. It's just the performance that improved.

    Edit: still, I'd be happy with a way to open search results in a proper, long list format. Like it used to be before it got reduced to showing results in the search panel alone.

  23. pjrobertson commented on Feb 17, 2023

    @pjrobertson
    pjrobertson
    on Feb 17, 2023
    Contributor

    Edit: still, I'd be happy with a way to open search results in a proper, long list format. Like it used to be before it got reduced to showing results in the search panel alone.

    I second this. The current small search panel is not very user friendly. It's small and doesn't show the full names/file paths of files when you search, and is clunky to scroll through. Furthermore, if you type a search then hit enter ↩, it'll automatically open the first result, which is counter-intuitive.

    It'd be great to have a full search page available. Gmail gives a good example of good search usability. Showing a popup window initially, and then another window if you hit enter.

  24. glorenzutti commented on Feb 17, 2023

    @glorenzutti
    glorenzutti
    on Feb 17, 2023

    Can I second this too ? second seconder? or a thirder?

    The UI has some room for improvement.

  25. acazenave
    added this to POC - Upstream ticket followupon Aug 30, 2024
  26. acazenave
    moved this to Todo in POC - Upstream ticket followupon Aug 30, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    0. Needs triagePending check for reproducibility or if it fits our roadmapbugfeature: searchhighneeds inforegression

    Type

    No type

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions