How to shrink oc_filecache (440GB, vs 360GB files hosted)

ℹ️ Support
,
1

Hi NC Community,

Thanks for providing a huge backlog on all kinds of things, it has helped me over and over again before I made an account, because this time I can’t figure out a solution. Sorry for the long read!

TL;DR: oc_filecache is larger than the amount of files hosted (440GB vs 360GB). I try to delete records from it. I can read/write SQL, but PHP not so well. I have a hard time.

Nextcloud version (eg, 20.0.5): 18
Operating system and version (eg, Ubuntu 20.04): Debian 10
Apache or nginx version (eg, Apache 2.4.25): nxinx
PHP version (eg, 7.4):

The issue you are facing:
Nextcloud 18 is running fine. I installed it via the excellent Yunohost platform. The Yunohost bit is not relevant for my problem, I include it for completenes.

Now an upgrade from NC 18 to NC 20 is available.

Yunohost was not able to perform the upgrade. It makes a backup of NC18 (duplicating the database), then upgrades NC18 to NC20 (creating a new instance of the database), finally dropping the not needed NC18 copies in case of succes or rolling back changes when a problem is encountered.

The process was not quite clear to me, so I ran the upgrade while the system was using about 90% disk space, ie, 0.9TB out of about 1TB. 100GB seemed enough headroom for the upgrade.

The upgrade took quite long, and I found that one table in particular was larger than I expected, oc_filecache, at some 440GB. The upgrade failed, clearly due to lack of space.

I can increase the diskspace, but I really want to shrink the space used by oc_filecache.

I found I should use, and did run,

sudo -u nextcloud php /var/www/nextcloud/occ files:cleanup

for a week.

I also found that occ files:cleanup deletes records in oc_filecache that are related to non-existant storage ID’s. Some external storage has been configured, so I removed all of those.

Besides running occ files:cleanup, I ran manual deletes on the table (at higher fileid ranges) for records with storage ID not in the storage table. After a week I was down from 321 million to 318 million records; in MySQL show processlist; told me the cleanup was at fileid 80 million of 420 million, so about 20% tablespace in one week.

I ran a delete on the database with a fixed storage ID, instead of a subselect on the storage table. That ran 5-10 times faster than the subselect variant.

I quit occ files:cleanup. Unexpectedly, it still caused a rollback. The processlist showed discrete deletes per record, so I expected them to be separate transactions. It seems that my manual deletes got caught in the same transaction, because after quitting occ files:cleanup, I was back at 321 million records in oc_filecache.

The loss of deletes would be temporary though, because now I could run manual deletes much faster. A batch of 10000 deletes would take just over 2 minutes, and a batch of 100000 scaled nicely to 18 minutes.

I took my chances with a batch of 1000000. That turned out too much at once:

MariaDB [nextcloud]> delete from oc_filecache  where storage = 22 and fileid > 100000000  limit 1000000 ;
Query OK, 1000000 rows affected (10 hours 26 min 0.573 sec)

Not only did it run for 10 hours, somewhere after completion a rollback was triggered. This morning I was back at 321 million records, once again.

The data file does not use freed up space either, or is growing faster than I can pump deletes into it:

 ls -hs oc_filecache.ibd 
441G oc_filecache.ibd

How can I remove records from oc_filecache, and preferably return the freed space to the OS?

Is this the first time you’ve seen this error? (Y/N):
Not sure whether it’s an error, or the result of not expected but explainable behaviour.

Steps to replicate it:
I think this is the culprit:

  1. Configure external storage
  2. Browse external storage
  3. Files are copied from external storage to the database

The output of your Nextcloud log in Admin > Logging:
It is rather largish; the downloaded log is 28M (it doesn’t take the log level ticks in regard) . Mostly it’s Error and Fatal, Doctrine\DBAL\Exception\LockWaitTimeoutException, I expect while running large batches of manual deletes. There are no errors after 20210203.

[core] Error: Doctrine\DBAL\Exception\LockWaitTimeoutException: An exception occurred while executing 'DELETE FROM `oc_share` WHERE `item_type` in ('file', 'folder') AND NOT EXISTS (SELECT `fileid` FROM `oc_filecache` WHERE `file_source` = `fileid`)':

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction at <<closure>>

0. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
   Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... n", Doctrine\DBAL\Dr ... ]})
1. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 145
   Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... n")
2. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1063
   Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "DELETE FROM `oc ... )", [])
3. /var/www/nextcloud/lib/private/DB/Connection.php line 220
   Doctrine\DBAL\Connection->executeUpdate("DELETE FROM `oc ... )", [], [])
4. /var/www/nextcloud/apps/files_sharing/lib/DeleteOrphanedSharesJob.php line 62
   OC\DB\Connection->executeUpdate("DELETE FROM `oc ... )")
5. /var/www/nextcloud/lib/private/BackgroundJob/Job.php line 61
   OCA\Files_Sharing\DeleteOrphanedSharesJob->run(null)
6. /var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php line 55
   OC\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})
7. /var/www/nextcloud/cron.php line 125
   OC\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

at 2021-02-03T14:41:01+00:00

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

# cat /var/www/nextcloud/config/config.php                                                                                              
<?php
$CONFIG = array (
  'passwordsalt' => '',
  'secret' => '',
  'trusted_domains' =>
  array (
    0 => '',
    1 => '',
  ),
  'datadirectory' => '/home/yunohost.app/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '18.0.4.2',
  'overwrite.cli.url' => 'https://online.osba.nl',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => '',
  'dbpassword' => '',
  'installed' => true,
  'instanceid' => '',
  'ldapIgnoreNamingRules' => false,
  'ldapProviderFactory' => 'OCA\\User_LDAP\\LDAPProviderFactory',
  'updatechecker' => false,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'integrity.check.disabled' => true,
  'filelocking.enabled' => true,
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' =>
  array (
    'host' => 'localhost',
    'port' => '6379',
    'timeout' => '0.0',
    'password' => '',
  ),
  'logout_url' => 'https://online.osba.nl/yunohost/sso/?action=logout',
  'maintenance' => false,
  'loglevel' => 2,
  'config_is_read_only' => true,
  'hashing_default_password' => true,
  'app_install_overwrite' =>
  array (
    0 => 'calendar',
    1 => 'social',
    2 => 'spreed',
    3 => 'folderplayer',
  ),
);

The output of your Apache/nginx/system log in /var/log/____:
It does not seem there’s much Nextcloud-related. I will paste more on request

2 Likes
2

The first question for me would be how you ended up with so many entries. Is it just external storage, this must be enourmeous folder structures, or some clean up routine is not working at all (cronjobs?). We are talking about 300 million files/folders!

How much files and folder is your setup supposed to use? It makes a difference, if you use 100 million, 10 million or perhaps just 10 000.

What is triggering this rollback? I’d perhaps try to backup things (not easy regarding space), so you can go back, then stop all the rollback and Nextcloud itself and try to clean the database.

3

Hi tflidd,

Thanks for your reply!

Initially it was my first question as well, not knowing the internals of Nextcloud I postponed looking into that. The answer to that is not most urgent :wink:
I must say that the server is busy all the time (disk activity-led on). A server should, of course, and not only Nextcloud is on it. The processlist does not show often/long running queries when looking into it at random moments.

The setup is mostly syncing our family’s laptops home directories and it is on the receiving end of the Android Nextcloud client. I would expect hundreds of thousands files as a rough upper limit. Not a million, let alone upwards of three hundred million. (Seeing max fileid is 413697015, some files have already been deleted over the years).

rollback?

I am not sure a rollback has occured, but while the cleanup job or a delete batch is running, the count of rows is running down steadily (using phpmyadmin’s data browser, which says on top Showing rows 0 - 99 (321723492 total, Query took 0.0008 seconds.)
This morning I deleted 100000 records,

MariaDB [nextcloud]> delete from oc_filecache  where storage = 22  limit 100000 ;                                                                                     
Query OK, 100000 rows affected (5 hours 28 min 38.173 sec)

Took longer than I expected, based on previous runs. Just before refreshing phpmyadmin the count was at 3216xxyyy, after refreshing back at 321723492. Quite disappointing :frowning:

Making a backup is feasible, space-wise. I don’t understand what you mean by ‘go back and stop all the rollback’, would you elaborate?

I find it striking that the reversion of the total number of records is instantaneous. I had expected a rollback to be visible in the process list, but there is no indication of activity there.

4

I would put Nextcloud in maintenance mode, so it doesn’t use the database during this time. So you can focus on deleting and the deleting procedure does not interfere with other queries.

Perhaps you can later run the cleanup-commands with occ as well with the maintenance mode on (if that is not working, it should tell you).

Did you check which storage ids have most files?

SELECT storage, COUNT(*) FROM oc_filecache GROUP BY storage;

5

Hi, thanks again!

I started with maintenance mode turned on last week, occ files:cleanup does not run in maintenance mode. Even if speeding up the deletes by a factor two by not having me or my wife uploading a photo from our phone, it would mean two weeks downtime.
I can still grow the file system by enough to let the upgrade succeed, and let the cleanup run afterwards.

Before I found out that phpmyadmin shows a count on screen, I tried getting a count(*) directly on oc_filecache, and cancelled the query after an hour without getting a result. I could run the query per storage overnight. While knowing how many records there are per storage is interesting, it doesn’t make the deletions run faster.

I tried finding out whether MySQL/MariaDB has any indicator as to whether a rollback is active/pending. I had no luck, would you know?

6

Sorry, I think my system cannot give an answer to that. The query has been running on the CLI since last night, there is no reply yet.
Before starting the query, I commented out the cron job that calls cron.php, to minimize interference.

7

There are a tuning scripts for database server to adjust the cache settings for best performance. tuning-primer.sh and mysqltuner.pl. If your Nextcloud setup is running on a small hardware, a way could be to make a database dump directly to a remote system over network (400 GB will take some time) if you then have a more suited system to carry out the cleaning up…

8

All our hardware is energy efficient as much as possible, the machine running Nextcloud is one of the most powerful at home :stuck_out_tongue:

I cancelled the select query.

I am still trying to find out how to check whether a rollback is being performed in the background.

I’d hoped show status could shed a light. From …

MariaDB [(none)]> show status like '%roll%';
MariaDB [(none)]> show status like '%comm%';

… only Flush_commands = 1 , everything like commit or rollback is zero.

Maybe all of this is even a rabbit hole I can leave unexplored. If I give the server 1TB of extra space, temporarily, I can perform the upgrade.

It’s not the most satisfying of workarounds. After reading search engine results of other people trying to clean up their oc_filecache, it seems there is no workable solution yet.

The filecache might grow, but slower after disabling all remote storage. I can still connect clients to external storage over SSHFS, it’s only the Android clients missing out.

In the mean time, I’ll check out the tuning-primer and mysqltuner. Thank you for your suggestions and reading my story :slight_smile:

9

No, forget it. They change the database structure, add indeces, for your database that will take ages.

RAM is most important for databases. Sometimes you already have enough of it but the configuration doesn’t use all of it, therefore the tuning scripts.

You have hit a bug somewhere. You have to fix this. I’d clean out the database to a certain degree and then try to upgrade to a current version. If you can run without the external storage, you can see if the crazy entries come back and then slowly add them.

10

Ah, bummer :frowning:

I’d supposed 16GB to be enough for a small instance, but Nextcloud has grown in unexpected ways :wink: As you say: tuning; not all of it seems to be used.

I’ll give it a go. Seeing the speed at which things crawl, it might take a while, but I’ll come back for an update (or more advice).

Thanks!

11

Not sure about your current database, but in principle for your setup it looks pretty decent. Just if you want to backup the tables, you can compress the dumps directly, so it can use considerably less memory. However, the whole process might take a long time.

There are a few more topics about problems with the filecache table and external storage:

12

I’m starting to believe there’s a limitation in the automatic row count feature of phpMyAdmin.

Every time while deleting, the count of rows on top of the screen in phpMyAdmin is decreasing. Once I stop deleting files, and wait a while, the counter jumps back to where it was. The last few days that number has been 321734100

There are two arguments for that belief:

  • The count takes a fraction of a second. When I manually do select count (*) from oc_filecache, there is no result after an hour.
  • The count does descent while deleting records, but settles back at the original value.

If it displayed a statistic value and discounted current deletes, I could explain the behaviour; still strange the cached value does not seem to be the correct value.

The highest fileid is 413701561, so about 90 million records must have been created and deleted to get to a total of 321734100 records.

I worried my deletes got undone, either by rollback or by a trigger on oc_filecache, but it seems there are not any triggers on the nextcloud database (show triggers does not show any). An explicit rollback is never shown in the mysql-CLI.
Another reason to believe nothing is in fact being rolled back, is that I now delete all records for storage-id 22 starting at the lowest fileid’s and working up from there.
When I restart at fileid = 1, the first millions of records are checked with no deletes, where there were earlier.

After deleting a a few hundred thousand records, the server keeps crunching for quite a while. I thought it might be rolling back transactions, now I surmise it is actually just running the journal against the actual table.

It takes this server about 2 hours to delete 350k rows, initially. If I quit pushing more deletes at it, it keeps busy writing for hours afterwards to the tune of 8MB/s and reading a third of that.

I’ll post the procedure I use for deleting, it may help another, or perhaps someone has a suggestion for improvements.

13

It is perhaps not the most elegant solution, but it seems to work after a fashion.

Overview:

  • a table that contains batch numbers as well as logging
  • a procedure that takes a batch size and a number of batches to run deletes rows for a specific storage id.

I don’t know how to query MySQL statistics, and direct querying of the table takes too long, so my nice-to-have informative logging columns are empty.

First, create the table …

MariaDB [nextcloud]> create table oc_filecache_delete_log ( batch_nr int
                                     , log_comment varchar (150)
                                     , storage_id int 
                                     , deleted_rows int 
                                     , current_count int 
                                     , current_max int 
                                     );

… and populate it with the necessary number of batches for my goal (33000 batches of max 10000 deletes)

MariaDB [nextcloud]> delimiter $$ 
drop procedure oc_filecache_delete_log_init $$
create procedure oc_filecache_delete_log_init (in v_batch_nr_insert int)
begin 
  
  while v_batch_nr_insert > 0
  do    
    set v_batch_nr_insert = v_batch_nr_insert - 1 ;    
    insert into oc_filecache_delete_log(batch_nr) values(v_batch_nr_insert) ;  
  end while;  
end$$ 
delimiter ;

/*--------------------------------------------------------------------*\
MariaDB [nextcloud]> call oc_filecache_delete_log_init (33000);
Query OK, 33000 rows affected (27 min 12.248 sec)
\*--------------------------------------------------------------------*/

Now create the procedure that does the heavy lifting

delimiter $$ 
drop procedure oc_filecache_cleanup $$
create procedure oc_filecache_cleanup (v_nr_of_batches int, v_batch_size int, v_storage_to_clean int, hours_to_run int)
begin 
  -- nr_of_batches: the number of batches to work through
  -- storage_to_clean: the storage id to purge from oc_filecache
  declare v_start_batch_id int;
  declare v_end_batch_id int;
  declare v_current_batch_id int;
  declare v_range_start int;
  declare v_range_end int;
  declare v_end_date date;
  declare v_end_time time;
  
  -- find out where to start  
  set v_start_batch_id = coalesce ( (select max(batch_nr) -- if storage_id is known from previous run, select last run batch)
                                   from oc_filecache_delete_log 
                                  where storage_id = v_storage_to_clean
                                )
                              , 1 -- if storage_id is not yet known, start at first batch
                              );
  
  -- find out where to stop 
  set v_end_batch_id = coalesce(v_start_batch_id + v_nr_of_batches
                                , (select max(batch_nr) 
                                     from oc_filecache_delete_log)
                                );
                                

  -- set a default batch size if none is given    
  if v_batch_size is null 
  then 
    set v_batch_size = 10000;
  end if;
  
  set v_current_batch_id = v_start_batch_id;
  
  while v_current_batch_id <= v_end_batch_id 
  do
    -- delimit the range to seek for deletes; start at batch_id*batch_size
    set v_range_start = v_current_batch_id * v_batch_size; 
    -- the end of the range is as many as the size of the batch further
    set v_range_end   = v_range_start + v_batch_size;
    
    -- actual deleting
    delete from oc_filecache  
     where fileid >= v_range_start 
       and fileid < v_range_end 
       and  storage = v_storage_to_clean 
     limit v_batch_size ;
    
    -- log or it didn't happen
    update oc_filecache_delete_log set log_comment  = ''
                                     , storage_id   = v_storage_to_clean
                                     , deleted_rows = null 
                                     , current_count= null 
                                     , current_max  = null -- (select max(fileid from oc_filecache_cleanup))
     where batch_nr = v_current_batch_id
    ;
    -- increase counter 
    set v_current_batch_id = v_current_batch_id + 1 ;
    commit; 
  end while;   
end$$

delimiter ;

Run it like this :

MariaDB [nextcloud]> call oc_filecache_cleanup (30,10000,22,1);
--Query OK, 217467 rows affected (58 min 15.839 sec)

Each subsequent batch will pick up where the other left. As long as batch sizes remain equal, I can calculate from …

SELECT * FROM `oc_filecache_delete_log` where storage_id is not null having max(batch_nr)
272

… that 272*10000 rows have been scanned, so the first 2720000 rows have been purged of storage id 22.
I have 32 rows in oc_storages, with max storage id being 43, so after this one, 10 more storages have to be purged from oc_filecache.

In the mean time housekeeping has dropped to zero, as far as disk activity is concerned. Some actual logging in my table would have been nice, now I guess the deleting of 350k rows has taken 2 hours foreground-activity, followed by about the same amount of background-activity. I think it would be nice to pause the batch job after each batch, until background-activity has dropped, but I don’t know how to query that so I’ll run smaller batches and let the system settle in between.

I should see if I can move the table to an SSD, or at least see if I can enable SSD caching for this HDD. Those are exactly the workloads that HDD’s hate.

I ran the optimizer scripts, and most things are in green, but still hardly any of 16GB RAM is being used. That is another area of improvement to research.

14

Not sure if you can disable transaction logs (or at least chose good paramters). In this case, stop the webserver that there is no interaction with any Nextcloud queries.

InnoDB can roll back changes, that’s perhaps happening in your case, so you might want to find out why.

15

Hi TFLIDD,

Thanks for the suggestions!

By chance my solution is comparable to the solution offered in the blog post. I did increase my innodb_log_file_size (/etc/mysql/mariadb.cnf) from 5M to 50M (48 being the default for 10.4, with 96 for 10.5), and innodb_buffer_pool_size up from 16M to 128M (the latter being the default in the docs).

I tried figuring out which range actually has been purged of storage id 22, at first with

MariaDB [nextcloud]> select min(fileid) from oc_filecache where storage = 22;

and killing the query after 10 minutes, then trying to cheat by asking for

select fileid from oc_filecache where storage = 22 order by fileid asc;

The query finally returned after 20+ minutes, having disk reads averaging 100MB/s, telling me 3634223 was the first fileid belonging to storageid 22. So, the good news: no deletes have been rolled back :slight_smile:

After reading the blogpost, I learned at least:

  • do sleep(5) , for example, to wait 5 seconds.
  • deletes do not reclaim disk space, except when populating a new table with the result set and dropping the original table. That was the original goal of my exercise.
  • do not kill processes on a distributed database (does not apply to me, but good to keep in mind)