crazy DB load from Dashboard widget

[mortee]

Describe the bug
After upgrading NC to v30, I noticed that the system load on the DB machine skyrocketed, and the DBMS keeps crashing. I started monitoring the running queries on it, and what I see is an ever inscreasing number of the query below, which keeps multiplying until either the server runs out of memory and restarts, or - if I’m lucky - docker recycles the DBMS container.

Id      User    Host    db      Command Time    State   Info    Progress
180     nextcloud       *** nextcloud       Query   629     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000

Turns out, the Collectives app causes it, only when the Dashboard is open in my browser. Disabling the app “fixes” the problem.

The weird thing is that it happens even if only a single tab whatsoever has the Dashboard open, and despite the Collectives widget is not even selected/displayed. I don’t know how to disable the widget even more, without disabling the app altogether.

In any case, even if the Dashboard is open and with the widget enabled, it shouldn’t execute a query which runs for several minutes, and especially not starting it over and over again in parallel, until it brings the DBMS down.

Let me add here that I suspect the query does a full table scan on the ~800,000 row oc_filecache, because I don't think it can use the only (maybe) related index:

+--------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table        | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| oc_filecache |          1 | fs_storage_path_prefix |            1 | storage     | A         |          36 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| oc_filecache |          1 | fs_storage_path_prefix |            2 | path        | A         |      729130 |       64 | NULL   | YES  | BTREE      |         |               | NO      |

To Reproduce
Steps to reproduce the behavior:

1. Have a quite loaded oc_filecache
2. Install and enable Collectives
3. Open the Dashboard in a browser, even with the Collectives widget not displayed

Expected behavior
Not overloading the DBMS.

Screenshots

$ echo "show full processlist;" | docker exec -i nextcloud_db sh -c "mysql -p\"\$(cat /run/secrets/root-password)\" nextcloud"
Id      User    Host    db      Command Time    State   Info    Progress
180     nextcloud       10.0.4.64:36296 nextcloud       Query   629     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
190     nextcloud       10.0.4.64:58110 nextcloud       Query   597     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
206     nextcloud       10.0.4.64:49804 nextcloud       Query   563     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
233     nextcloud       10.0.4.64:49368 nextcloud       Query   530     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
247     nextcloud       10.0.4.64:37724 nextcloud       Query   497     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
263     nextcloud       10.0.4.64:50882 nextcloud       Query   464     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
286     nextcloud       10.0.4.64:41470 nextcloud       Query   431     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
318     nextcloud       10.0.4.64:43980 nextcloud       Query   398     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
339     nextcloud       10.0.4.64:35060 nextcloud       Query   365     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
354     nextcloud       10.0.4.64:49082 nextcloud       Query   331     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
377     nextcloud       10.0.4.64:38038 nextcloud       Query   210     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
386     nextcloud       10.0.4.64:38108 nextcloud       Query   203     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
400     nextcloud       10.0.4.64:59536 nextcloud       Query   137     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
413     nextcloud       10.0.4.64:47226 nextcloud       Query   101     Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
426     nextcloud       10.0.4.64:52836 nextcloud       Query   63      Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
438     nextcloud       10.0.4.64:46384 nextcloud       Query   50      Sending data    SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p` 
ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_oc74m0tbjrc2/collectives/1/%') AND (`f`.`mimetype` = 34) ORDER BY `f`.`mtime` DESC LIMIT 7      0.000
443     root    localhost       nextcloud       Query   0       starting        show full processlist   0.000

Server details:

• Collectives app version: 2.14.4
• Nextcloud version: 30.0.0
• PHP Version: 8.2.24
• Database: MariaDB 10.11.8

Client details:

• OS: Debian
• Browser: Firefox
• Browser version: 131
• Device: desktop

[juliusknorr]

Thanks for reporting this and especially for collecting and providing all the relevant information already.

The query is located in

collectives/lib/Service/RecentPagesService.php

Lines 51 to 72 in df190af

	$qb = $this->dbc->getQueryBuilder();
	$appData = $this->getAppDataFolderName();
	$mimeTypeMd = $this->mimeTypeLoader->getId('text/markdown');
	$expressions = [];
	$collectivesMap = [];
	foreach ($collectives as $collective) {
	$value = sprintf($appData . '/collectives/%d/%%', $collective->getId());
	$expressions[] = $qb->expr()->like('f.path', $qb->createNamedParameter($value, IQueryBuilder::PARAM_STR));
	$collectivesMap[$collective->getId()] = $collective;
	}
	unset($collective);
	$qb->select('p.*', 'f.mtime as timestamp', 'f.name as filename', 'f.path as path')
	->from('filecache', 'f')
	->leftJoin('f', 'collectives_pages', 'p', $qb->expr()->eq('f.fileid', 'p.file_id'))
	->where($qb->expr()->orX(...$expressions))
	->andWhere($qb->expr()->eq('f.mimetype', $qb->createNamedParameter($mimeTypeMd, IQueryBuilder::PARAM_INT)))
	->orderBy('f.mtime', 'DESC')
	->setMaxResults($limit);
	$r = $qb->executeQuery();

I can confirm that in some cases it seems the mtime index is used which can be rather problematic:

+------+-------------+-------+--------+------------------------------+------------------------------+---------+-------------+---------+------------+----------+------------+-------------+
| id   | select_type | table | type   | possible_keys                | key                          | key_len | ref         | rows    | r_rows     | filtered | r_filtered | Extra       |
+------+-------------+-------+--------+------------------------------+------------------------------+---------+-------------+---------+------------+----------+------------+-------------+
|    1 | SIMPLE      | f     | index  | NULL                         | fs_mtime                     | 8       | NULL        | 3664891 | 3941804.00 |   100.00 |       0.00 | Using where |
|    1 | SIMPLE      | p     | eq_ref | collectives_pages_file_index | collectives_pages_file_index | 8       | oc.f.fileid | 1       | NULL       |   100.00 |       NULL |             |
+------+-------------+-------+--------+------------------------------+------------------------------+---------+-------------+---------+------------+----------+------------+-------------+

[juliusknorr]

as @max-nextcloud brought up, filtering for the storage id would help quite a bit:

(f.storage = 2) AND ...

MariaDB [oc]> ANALYZE SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p`  ON `f`.`fileid` = `p`.`file_id` WHERE (f.storage = 2) AND (`f`.`path` LIKE 'appdata_ocmk9xasdnvl/collectives/%') AND (`f`.`mimetype` = 14) ORDER
 BY `f`.`mtime` DESC LIMIT 10;
+------+-------------+-------+--------+--------------------------------------------------------------------------------------------------------------------+------------------------------+---------+-------------+---------+---------+----------+------------+-------------+
| id   | select_type | table | type   | possible_keys                                                                                                      | key                          | key_len | ref         | rows    | r_rows  | filtered | r_filtered | Extra       |
+------+-------------+-------+--------+--------------------------------------------------------------------------------------------------------------------+------------------------------+---------+-------------+---------+---------+----------+------------+-------------+
|    1 | SIMPLE      | f     | index  | fs_storage_path_hash,fs_storage_mimetype,fs_storage_mimepart,fs_storage_size,store_mimepart,fs_storage_path_prefix | fs_mtime                     | 8       | const,const | 3665175 | 1689.00 |     1.05 |       0.59 | Using where |
|    1 | SIMPLE      | p     | eq_ref | collectives_pages_file_index                                                                                       | collectives_pages_file_index | 8       | oc.f.fileid | 1       | 0.50    |   100.00 |     100.00 |             |
+------+-------------+-------+--------+--------------------------------------------------------------------------------------------------------------------+------------------------------+---------+-------------+---------+---------+----------+------------+-------------+
2 rows in set (0.005 sec)

Current query

MariaDB [oc]> ANALYZE SELECT `p`.*, `f`.`mtime` as `timestamp`, `f`.`name` as `filename`, `f`.`path` as `path` FROM `oc_filecache` `f` LEFT JOIN `oc_collectives_pages` `p`  ON `f`.`fileid` = `p`.`file_id` WHERE (`f`.`path` LIKE 'appdata_ocmk9xasdnvl/collectives/%') AND (`f`.`mimetype` = 14) ORDER BY `f`.`mtime` DESC LIMIT 10;
+------+-------------+-------+--------+------------------------------+------------------------------+---------+-------------+---------+---------+----------+------------+-------------+
| id   | select_type | table | type   | possible_keys                | key                          | key_len | ref         | rows    | r_rows  | filtered | r_filtered | Extra       |
+------+-------------+-------+--------+------------------------------+------------------------------+---------+-------------+---------+---------+----------+------------+-------------+
|    1 | SIMPLE      | f     | index  | NULL                         | fs_mtime                     | 8       | NULL        | 3665176 | 1690.00 |   100.00 |       0.59 | Using where |
|    1 | SIMPLE      | p     | eq_ref | collectives_pages_file_index | collectives_pages_file_index | 8       | oc.f.fileid | 1       | 0.50    |   100.00 |     100.00 |             |
+------+-------------+-------+--------+------------------------------+------------------------------+---------+-------------+---------+---------+----------+------------+-------------+
2 rows in set (0.006 sec)

[mortee]

May I ask why is the query executed even if the widget is not displayed? Is this a NC bug? Does it load all widgets when opening the Dashboard, regardless of their display status?

[juliusknorr]

I think the server is still collecting all widget data upfront and pass it to the frontend at page load time to have it available if a user adds the widget. However sounds like something to optimize further, could you file an issue about that in the server repo?

[mortee]

Seems to already be reported.

[max-nextcloud]

This would only require limiting the query to the right storage here:
https://github.com/nextcloud/collectives/blob/main/lib/Service/RecentPagesService.php#L64-L70
One should be able to get the storage id like this: $this->rootFolder->getStorage()->getId() .