Indexation of oc_comments table takes ages and crash Mysql (Out of Memory)

ℹ️ Support
1

Nextcloud version (eg, 20.0.5): 24.0.0
Operating system and version (eg, Ubuntu 20.04): Debian 11
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4
PHP version (eg, 7.4): 8.0.17

We have a nextcloud instance with ~70K files, we use comments to add metadatas to the files in order to perform search quickly on them. oc_comments table has now ~150K lines.
Everytime a user open a Nextcloud session, it seems that an indexation of the oc_comments table is launched, causing de mariadb server to crash running out of memory :

MariaDB [nextcloud]> show full processlist;

| 113 | nextcloud_db_user | next.dummy.fr:55116 | nextcloud | Query | 0 | Creating sort index | SELECT * FROM oc_comments WHERE object_type = ‘files’ ORDER BY creation_timestamp DESC, id DESC LIMIT 100 OFFSET 76500 | 0.000 |
| 166 | nextcloud_db_user | next.dummy.fr:55268 | nextcloud | Query | 0 | Creating sort index | SELECT * FROM oc_comments WHERE object_type = ‘files’ ORDER BY creation_timestamp DESC, id DESC LIMIT 100 OFFSET 76400 | 0.000 |
| 198 | nextcloud_db_user | next.dummy.fr:55340 | nextcloud | Query | 0 | Creating sort index | SELECT * FROM oc_comments WHERE object_type = ‘files’ ORDER BY creation_timestamp DESC, id DESC LIMIT 100 OFFSET 76500 | 0.000 |

The forced reindexation of this table is not very scalable. Why Nextcloud want to recreate sort index at each opening session, even if no new files were added ?

Is this the first time you’ve seen this error? (Y/N): Error appears when we populate the table oc_comments. If we delete our “metadata comments”, it’s ok.

Steps to replicate it:

  1. Add thousand of comments into the oc_comments table
  2. Open a nextcloud session and browse files
  3. See the mysql/mariadb processes

no relevant errors in logs.

2

Is there another way to add metadata to files that can be used in the search form ?
For example, we need to be able to search files by their owner. Adding a comments with “OWNER: John Smith” give us the ability to search the keyword “Smith” and retrieve all his files.

3

Nextcloud 24.0.1
PHP 8.1
MariaDB

Same here!
5 Creating sort Index queries are fired and consuming all cpu. System is unresponsive everytime it happens for long time.
3 Queries at the same time (cpu cores -1) I assume would be ok.
But then again: what for is this triggered so often and how can I prevent it?

4

Disabling Recommendation app is a workaround. It’s the faulty app causing the problem. I’ve opened an issue on this app’s github