previews:cleanup issues

[mmattel]

Important, files and database are on a fast NFS (NetApp) backend connected via GbE. Enterprise server running VMWare as host for ownCloud.

Steps to reproduce

1. upgrade from 10.10 to 10.11
2. run occ previews:cleanup without any options/arguments

Post successful upgrading, started occ previews:cleanup without any options/arguments. This means it should make one round with max 1000 entries (chunks) which is the default. The command started at 18h and did not finish until 23h. Rechecking in the moring, ist finished and reported 1103 orphaned previews deleted. Means it took between 4 and 11h... (remember, it is only a small instance started many years ago). Running htop in a second shell, memory is fine, mariadb takes 1-2 cores with 100%.

Following findings:

• the command took extremely long, my instance is a small one
• there is no update what is going on, though you see a lot of NFS traffic
• the default chunksize of 1000 does not get respected, it cleaned up 1103
• compared when you do a fresh install of oc 10.11, when upgrading, you do not get the entry in the backgroundjobs table OCA\Files\BackgroundJob\PreviewCleanupJob
• the command cant be stopped (ctrl c) once started

Required Solutions:

• update the documentation to relax this situation
• fix the code, especially for:
  -- improve performance
  -- respect the chunksize
  -- add the bg job to the table automatically when upgrading (migration step necessary?)
  -- improve command responsiveness (maybe to add a progress option, a timestamp how long it took)

@DeepDiver1975 @pmaier1 fyi
@jnweiger

Referencing feat: previews are physically deleted from disk and db in an async manner

Server configuration

Operating system: U22.04

Web server: nginx

Database: mariadb

PHP version: 7.4

ownCloud version: 10.11

Updated from an older ownCloud or fresh install: yes

Where did you install ownCloud from: tar

Signing status (ownCloud 9.0 and above): all good

ownCloud log (data/owncloud.log)

nothing relevant

[mmattel]

I ran the command again to check:

time sudo -u www-data ./occ previews:cleanup -- 1
[sudo] password for user_name:
645412 - username_1: deleted
645412 - username_2: deleted
2 orphaned previews deleted
 
real    663m38.376s
user    0m0.306s
sys     0m0.124s

The instance is small, it took 11h... the chunksize was set to 1, it processed 2 😱

[mmattel]

On request of @DeepDiver1975

Query:

explain
select fileid, name, user_id
from oc_filecache fc
         join oc_mounts
              on storage = storage_id
where parent in (select fileid
                 from oc_filecache
                 where storage in (select numeric_id
                                   from oc_storages
                                   where id like 'home::%'
                                      or id like 'object::user:%')
                   and path = 'thumbnails')
  and not exists(select fileid
                 from oc_filecache
                 where fc.name = CAST(oc_filecache.fileid as CHAR(24)))
  and fc.fileid
    > 0
order by user_id, fileid
limit 10;

Result:

| id   | select_type        | table        | type   | possible_keys                                                                                            | key                  | key_len | ref                           | rows   | Extra                              |
+------+--------------------+--------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------------+--------+------------------------------------+
|    1 | PRIMARY            | oc_mounts    | ALL    | mounts_storage_index                                                                                     | NULL                 | NULL    | NULL                          | 49     | Using temporary; Using filesort    |
|    1 | PRIMARY            | fc           | ref    | PRIMARY,fs_storage_path_hash,fs_parent_name_hash,fs_storage_mimetype,fs_storage_mimepart,fs_storage_size | fs_storage_path_hash | 4       | owncloud.oc_mounts.storage_id | 3212   | Using index condition; Using where |
|    1 | PRIMARY            | oc_filecache | eq_ref | PRIMARY,fs_storage_path_hash,fs_storage_mimetype,fs_storage_mimepart,fs_storage_size                     | PRIMARY              | 8       | owncloud.fc.parent            | 1      | Using where                        |
|    1 | PRIMARY            | oc_storages  | eq_ref | PRIMARY,storages_id_index                                                                                | PRIMARY              | 4       | owncloud.oc_filecache.storage | 1      | Using where                        |
|    4 | DEPENDENT SUBQUERY | oc_filecache | index  | NULL                                                                                                     | fs_storage_mimetype  | 8       | NULL                          | 372627 | Using where; Using index           |
+------+--------------------+--------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------------+--------+------------------------------------+
5 rows in set (0.001 sec)

[DeepDiver1975]

| 4 | DEPENDENT SUBQUERY | oc_filecache | index | NULL | fs_storage_mimetype | 8 | NULL | 372627 | Using where; Using index |

looks like 372k rows are processed ...... this could explain things ....

[cdamken]

It's normal behavior to remove all files will take time.

The command is to remove all previews, this is only needed if you want to turn off the previews. additional parameter is needed in the config.php

[mmattel]

It's normal behavior to remove all files will take time.

The command is to remove all previews, this is only needed if you want to turn off the previews. additional parameter is needed in the config.php

• I disagree that this is only needed when turning off previews. You get orphans e.g. atm where you rename the moint point because the internal ID changes.
• And no, it is not normal to run 11h to check 372k items in the database for orphans in the filesystem.
• There is no additional parameter known to be documented as I was in intense discussion with @DeepDiver1975 upfront

[cdamken]

This is a special command only needed to remove all previews. administrators do not need to run this command at all!

Please don't use it if you don't have a problem with previews, DB performance, and other apps that are involved in this issue., the command is not optimized and is not expected to optimize the performance.

If people want to use it then they will need assistance from oC-Support.

[DeepDiver1975]

This is a special command only needed to remove all previews. administrators do not need to run this command at all!

Please don't use it if you don't have a problem with previews, DB performance, and other apps that are involved in this issue., the command is not optimized and is not expected to optimize the performance.

this is wrong @cdamken

In regular operations NO preview will be removed during deleting files/folders.
We removed this as a performance optimization in core.
Previews will always remain on the system and need to be cleaned up.
There is a background job to do this on smaller instances.
Bigger instances need to run this in cron.

[pmaier1]

Needs assessment and improvement.

[mmattel]

@DeepDiver1975 based on the GO from Patrick above, could you have a look for improvements?

[bvdheuvel]

Hello,
I did ran into the same problem, with the query running for hours.

I think this should fix it (not tested very well, but should have the same result)

PreviewCleanup.php.diff.gz

:

select `fileid`, `name`, `user_id` from `oc_filecache` `fc`
join `oc_mounts` on `storage` = `storage_id`
where `parent` in (select `fileid` from `oc_filecache` where `storage` in (select `numeric_id` from `oc_storages` where `id` like 'home::%' or `id` like 'object::user:%') and `path` = 'thumbnails')
  and `fc`.`fileid` not in (select `fileid` from `oc_filecache` where `fc`.`name` = CAST(`oc_filecache`.`fileid` as CHAR(24)))
  and `fc`.`fileid` > '0'
  order by `user_id`, `fileid` limit 10;

Now the query runs in < 1s

[mmattel]

@DeepDiver1975 can you have a look ob the suggested change?