High, permantent CPU load on two MySQL connections

[pensan]

We recently updated the host which runs Nextcloud to Ubuntu 22.04 and have some minor issues with the fulltextsearch since then. Not sure if it is only fulltextsearch related or more of a general Nextcloud problem, but you may have more insights here.

After starting/restarting the mysqld service, the CPU load increases after a couple of minutes and then is stuck.

Inspecting the mysqld process with pidstat -t -p PID 1 shows two connections that are responsible for the high load. One at 80%+ and one on 45%+.

15:48:32      UID      TGID       TID    %usr %system  %guest   %wait    %CPU   CPU  Command
[...]
Average:      111         -   2215442   48.10    0.50    0.00    0.00   48.60     -  |__connection
[...]
Average:      111         -   2854935   82.35    0.09    0.00    0.00   82.43     -  |__connection
[...]

Digging into those two threads via mysql> select * from performance_schema.threads where THREAD_OS_ID = 2215442 \G shows two these 2 queries in the PROCESSLIST_INFO field:

TID 2215442
SELECT li.owner_id, li.provider_id, li.document_id, li.collection, li.source, li.status, li.options, li.err, li.message, li.indexed FROM oc_fulltextsearch_index li WHERE (li.status <> '1') AND (err = '0') AND (li.collection = 'local')

and TID 2854935
SELECT size, unencrypted_size FROM oc_filecache WHERE parent = 1149533

Looking into mysql> show processlist; reveals the following:

+-------+-----------------+-----------+-----------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
| Id    | User            | Host      | db        | Command | Time   | State                  | Info                                                                                                 |
+-------+-----------------+-----------+-----------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
|     5 | event_scheduler | localhost | NULL      | Daemon  | 106103 | Waiting on empty queue | NULL                                                                                                 |
| 20664 | nextcloud       | localhost | nextcloud | Query   |      1 | executing              | SELECT `li`.`owner_id`, `li`.`provider_id`, `li`.`document_id`, `li`.`collection`, `li`.`source`, `l |
| 80561 | nextcloud       | localhost | nextcloud | Query   |      1 | executing              | SELECT `size`, `unencrypted_size` FROM `oc_filecache` WHERE `parent` = 1149533                       |
| 90103 | root            | localhost | NULL      | Query   |      0 | init                   | show processlist                                                                                     |
+-------+-----------------+-----------+-----------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+

The "Time" field varies between 0 and 3.

While 2215442 sometimes is shortly reduced to a lower CPU usage (prob. because of 'other queries' that are run in between) 2854935 will vary only slightly.

Occasionally another thread opens with this query:
SELECT a.name FROM oc_filecache a LEFT JOIN oc_filecache b ON a.name = b.fileid WHERE (a.storage = '1') AND (b.fileid IS NULL) AND (a.path LIKE 'appdata\\_oc36nkjj747h/preview/_/_/_/_/_/_/_/%') AND (a.mimetype = '2')

After around 30 seconds this query is resolved and the thread reduced to normal/idle.

Running the two 'problematic' queries by hand will return results in less then 3 seconds.

Do you have ideas where the problem could come from and how to fix it?
I can provide more information if needed.

Any help is greatly appreciated.