Continuous heavy per-mount SELECT COUNT(*) FROM oc_filecache queries on large instance (457 group folders) #279

Title

Continuous heavy per-mount SELECT COUNT(*) FROM oc_filecache queries on large instance (457 group folders)

Which version of Nextcloud are you using?

33.0.9

Which version of PHP context_chat are you using?

5.4.0

Which version of backend context_chat are you using?

5.4.1

Nextcloud deployment method?

Bare metal, Ubuntu 22.04, PHP-FPM (PHP ), MariaDB 10.6.23
AppAPI 33.0.0, Group folders 21.0.15
Context Chat Backend deployed via AppAPI + HaRP on a remote GPU host. Upgraded from Context Chat 4.x.

Describe the Bug

The database is continuously busy with long-running SELECT COUNT(*) FROM oc_filecache ... queries,
one per mount. Several of them run in parallel at any time, and they keep re-appearing (also for low
group folder IDs such as 2 and 6, so the counting seems to start over again and again).

The queries match StorageService::countFilesInMount()
(https://github.com/nextcloud/context_chat/blob/v5.4.0/lib/Service/StorageService.php#L68).

They are not caused by a manually started occ context_chat:stats: while observing, either no stats
process was running or only a single one (which cannot explain several concurrent counts for different
group folders). The Context Chat admin settings page was not open.

Example from SHOW FULL PROCESSLIST (several of these concurrently, each ~1–7 s):

SELECT COUNT(*) FROM `oc_filecache` `filecache`
WHERE ((SELECT `p`.`encrypted` FROM `oc_filecache` `p` WHERE `p`.`fileid` = `filecache`.`parent`) = 0)
  AND (`filecache`.`storage` = 2)
  AND (`filecache`.`path` LIKE '__groupfolders/28/%')
  AND (`filecache`.`path` NOT LIKE 'files_versions/%')
  AND (`filecache`.`path` NOT LIKE 'files_trashbin/%')
  AND (`filecache`.`storage` = '2')
  AND (`filecache`.`mimetype` IN (29, 13, 20, 4, 27, 113, 14, 45, 34, 33, 8, 44, 9, 37, 118, 52, 67, 68, 135))
  AND (`filecache`.`size` <= 104857600) AND (`filecache`.`size` > 0)
-- Time: 7 s, State: Sending data

Numbers from our instance

• oc_filecache: ~1.9 M rows
• Group folders: 457, all in the same storage (storage = 2, paths __groupfolders/<id>/...)
• Each per-mount query: ~1–7 s → group folders alone ≈ 8–50 min per full count, plus home storages
• All indices from occ db:add-missing-indices are present (incl. fs_storage_path_prefix)
• occ context_chat:stats takes more than one hour for the same reason

What we checked

• Only the backend (ExApp/context_chat_backend/5.4.1) is calling Context Chat's OCS endpoints
  (GET / DELETE /ocs/v2.php/apps/context_chat/queues/documents).
• Queues are small: oc_context_chat_queue ~550 rows, oc_context_chat_action_queue ~20 rows,
  oc_context_chat_content_queue 0 rows, oc_context_chat_fs_events ~5 rows.
• last_indexed_time is set:

  - app: context_chat
  - key: last_indexed_time
  - value: 1789557889
  - type: integer
  - lazy: true

• PHP-FPM was restarted after setting last_indexed_time. The COUNT queries still re-appear afterwards.

Possibly related code observations

• QueueController::deleteDocumentsQueueItems() calls setInitialIndexCompletion() after every batch, which can
  fall back to StorageService::countFiles():
  https://github.com/nextcloud/context_chat/blob/v5.4.0/lib/Controller/QueueController.php#L184
  https://github.com/nextcloud/context_chat/blob/v5.4.0/lib/Controller/QueueController.php#L376
• withinThreshold() looks inverted compared to its comment ("if the new files in the queue are less than 2% of
  the total eligible files, we consider the initial indexing complete"):

  return ((float)($total - $current) / (float)$total) < $threshold;

It is called with $current = queuedNewFilesCount and $total = eligibleFilesCount. With e.g. 557 queued of
100 000 eligible files this evaluates to 0.994 < 0.02 → false, so completion would never be detected via this
path. The code is unchanged on main.

Since the queries continue even with last_indexed_time set and after a PHP-FPM restart, there seems to be
another caller of countFiles() / countFilesInMount() that we could not identify yet.

Possibly related: nextcloud/context_chat_backend#310 ("The initial indexing is still running" never completes).

Stack trace of the triggering request

<optional: excerpt from PHP-FPM slowlog containing countFiles / countFilesInMount>

To Reproduce

1. Large instance (~2 M oc_filecache rows) with several hundred group folders
2. Upgrade Context Chat from 4.x to 5.4.0, deploy Context Chat Backend 5.4.1 via AppAPI/HaRP
3. Let the backend process the queues
4. Observe SHOW FULL PROCESSLIST → repeated per-mount SELECT COUNT(*) FROM oc_filecache ...

Expected behavior

Counting all eligible files should not run continuously in the background and should not put permanent
load on the database. occ context_chat:stats should finish in reasonable time on large instances.