Large oc_filecache

ℹ️ Support
,
1

Nextcloud version: 17.0.1
Operating system and version: Ubuntu 18.04 LTS
Apache or nginx version: Apache 2.4.29
PHP version: 7.2.24
PostgreSQL: 10.10

In the past I have mounted a filesystem via External Storage (SMB/CIFS) with a large amount of small backup files. This has led to a large oc_filecache table.

The 10 largest table in my otherwise very small NC instance:

select schemaname as table_schema,
    relname as table_name,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_relation_size(relid)) as data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
      as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
         pg_relation_size(relid) desc
limit 10;

afbeelding

I have unmounted this filesystem. There are approx 27 million entries in oc_filecache and 95% of them are releated to his particular filesystem (select count(*) from oc_filecache where storage=13). After unmounting the filesystem, the related entry in the oc_storages table remained (numeric_id=13). Not sure if this is deliberate, but currently the case.

afbeelding

I have searched the forum on ways how to get rid of these now obsolete entries in oc_filecache, to make it smaller again. Have tried occ files:cleanup. But to my information this only deletes entries in oc_filecache for there is no entry in oc_storages. As since there is still an entry for this filesystem, nothing gets cleaned up.

Have tried copying the table oc_filecache to oc_filecache_temp (without the entries for storage=13). After that I rename oc_filecache to oc_filecache_orig and oc_filecache_temp to oc_filecache.

This seemed to have worked ok, but a day later I noticed issues in syncing and uploading data, so I reverted back to a snapshot from before the oc_filecache manipulation.

Since this table is so out of proportion related to the rest of the tables, I do would like to get this “solved”.

Does anyone have a idea what else I could try?

Thanks a lot in advance!

1 Like
2

Had some time on my hands at home … and decided to have another go on this. Ultimately I did the following:

  • complete VM backup :wink:
  • stop NC service
  • “truncate table oc_filecache” in PostgreSQL
  • vacuum full with analyze of oc_filecache (to schrink the table and return the storage to OS)
  • sudo -u www-data php occ files:scan --all -vvv
  • start NC service

This did the trick for me.

1 Like
3

Did you loose all your shares, too?

4

No, the only thing that was done, was deleting the table contents of oc_filecache. Afterwards a new scan was done, so all files on the shares where I wanted the scanning to be done were found.

5

apology for my question, affects this also the table oc_filecache_extended or only oc_filecache? finally, the name is similar.

As you can see here: https://docs.nextcloud.com/server/19/admin_manual/configuration_server/occ_command.html#file-operations-label

occ files:cleanup allready makes the cleanup. And after that:
occ files:scan --all makes the rescan for all users.

I think the direct operation on the database layer is risky, and under the circumstance that there is a cleanup command at least strange. Possible there is a bug and have to be reported?

bye.

1 Like
6

This has been a while for me, so I can’t remember fully. However, I think I tried the cleanup command, which did not remove the entries from the cache tables and thus the table remained way too large.

I also do not want to fiddle around too much on the database level, but getting this too large table back to normal size was too important for me (the large table caused also the backups to be large, which resulted in space issues). Ofcourse, made a backup before tinkering with the table.

7

Update: after reading back my original post, the answer to the cleanup job is there:

“Have tried occ files:cleanup. But to my information this only deletes entries in oc_filecache for there is no entry in oc_storages. As since there is still an entry for this filesystem, nothing gets cleaned up.”

1 Like
8

I tried this on a large installation. It took over 3 hours to complete and when it finished, all share’s were lost. The files were all there, but the information about who has shared with files with whom was gone.

9

We are having the same issue. Sounds like a bug to me. We’re stuck with a 20 GB database and no proper way out.

What if we manually delete the storage (DELETE FROM oc_storages WHERE...) then run a occ cleanup. Would that work ? Would it be a cleaner workaround ?

Otherwise, a fix is much needed…

10

please review this thread. there are lot of references and troubleshooting hints.

from my experience the problem could result from (external) storages not connected to the system anymore. try this command to identify the most used storage:

select storage,count(storage) from oc_filecache group by storage;

you can correlate storage id using this query:

select * from oc_storages

if you find unused storages (like disconnected/old) it’s safe to kill referenced storage IDs:

delete from oc_storages where id like ‘smb:%’;
delete from oc_storages where numeric_id like ‘%choose a number%’;

subsequent cron jobs or occ files:scan --all should fix the oc_filecache table

1 Like
11

can someone explain, why there are thematically different types of entrys in the oc_storages table?
i have made a select on table oc_storages and found an old local:nc_data record from nextcloud version 18!?
Also the columns “available” and “last_checked” seems not be used. Table “last_checked” in example was allways null. Should Nextcloud figure out the not extant directory?
Thematically are the real storages directory “local:” and the virtual users “home:” directorys extant. All “home:” directorys are also in the “local:” directory. Because here in my installation there is only one nc_data directory and nothing else.
An:
select count(oc_filecache.name)
from oc_filecache
where oc_filecache.storage = 4;
counts 206695 entrys versus 2280 folder and 93551 files :thinking:
oc_filecache.storage 4 is the local “nc_data” directory.

Edit:
I’m also had made files count in nc_data with:
find /var/nc_data/ -type f | wc -l
that gives me: 129493 files back.

After I made an update in Table oc_storage - relating the old nextcloud 18 record - column available from 1 to 0, I had made an occ files:cleanup, but nothing has been deleted.