Slow trashbin expiry

I got a system that had trashbin expiry disabled for a long time. When I enabled it in config.php and executed occ files:trashbin:expire manually, I observed it executing really slow. After a while, the cronjob jumped in and started the expiry too, and so the jobs piled up and slowed things down.

We have two issues here::

  • apparently, a cron job won’t notice that the previous job hasn’t finished.
  • Expiry is slow. I found that a query “Select … from oc_filecache where storage=$1 and name ILIKE $2” is executed. The resulting query plan is a full table scan, which seems to be executed for every file to purge. The ILIKE operator prevents a more selective query plan.

I’d like to investigate the second issue further, can someone point me to where the expiry code and this query is located?

can someone point me to where the expiry code and this query is located?

Here is the code for the trash bin app: https://github.com/nextcloud/server/tree/master/apps/files_trashbin

files:trashbin:expire

Select … from oc_filecache where storage=$1 and name ILIKE $2

I’m not 100% sure but that’s the only place where ilike name is used.

Hm, thanks for the pointers. What I see is basically
$dirContent = Helper::getTrashFiles(’/’, $user, ‘mtime’);
TrashBin::deleteExpiredFiles($dirContent, $user);
where only getTrashFiles should search for all files once, but not over and over again.
Maybe this was a non-related request that was heavily influenced by the multiple expiry runs.

Well no, just looked at the cpu usage over the last days, I can clearly see the database hammered for the 30 hours the SINGLE expire ran.