Progress on occ db:convert-filecache-bigint

Software stack:

  • Nextcloud version: 19.0.6 (docker --> on d.hub)
  • Operating system and version: Synology DS DS1813+ (DSM 6.2.3-25426 Update 3)
  • Apache or nginx version: n.a.
  • PHP version: 7.2.29
  • Docker version: 18.09
  • MariaDB version: 10.3.21-MariaDB - Source distribution

General setup:

  • MariaDB as app of the synology itself.
  • NC as docker container behind a …
  • nginx ssl proxy container.

NC facts:

  • 2,5 TiB space
  • 741.000 files
  • 122.000 folder

Is this the first time you’ve seen this error? YES

The issue I am facing:

occ db:convert-filecache-bigint seems to not come to an end.

Questions first:

  1. Can I check the progress of occ db:convert-filecache-bigint?
  2. Does someone has a rough estimation how long per 1.000 files or 100GiB it takes to perform this operation on some defined hardware (like Rpi4, Intel/AMD NAS XYZ,…)?
  3. Is there a SQL command to do the same without using occ/WebOcc?

Background:

I am in the process of upgrading my NC installation. I upgrade in the way that I pull the last minor release of the major version I am running. I add all the parameters and option to the WebUI of the docker app of the NAS an start the container.

I check than the terminal what’s going on after starting the container. I than check the webapp of NC for issues and open tasks. I fix what needs to be fixed by using the WebOCC app or do some stuff on the nginx container if it related to SSL. When all is green I move to the next major version with the highest minor version.

I add all the parameters and option to the WebUI of the docker app of the NAS and start the container. I watch the terminal and so on. Most of the times the upgrade process kicks in automatically. However, I also had to run it by hand some time.

With NC18 I got the message to run db:convert-filecache-biginton the database in offline mode. But when bringing the NC into the maintenance mode I can’t use the WebOCC. So I am using the docker terminal and run

/var/www/html/occ db:convert-filecache-bigint --no-interaction

based on the documentation, it will run some hours… but how many?

I opened another terminal and run top:

Tasks:  15 total,   2 running,  12 sleeping,   1 stopped,   0 zombie                                                                  
%Cpu(s): 34.1 us,  4.9 sy,  0.0 ni, 39.4 id, 21.3 wa,  0.0 hi,  0.3 si,  0.0 st                                                       
MiB Mem :   3947.6 total,    138.4 free,   1134.1 used,   2675.1 buff/cache                                                           
MiB Swap:   4415.9 total,   4086.5 free,    329.5 used.   2514.2 avail Mem                                                            
                                                                                                                                      
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND                                                          
   52 root      20   0  244496 116828  13420 R  99.7   2.9  32:59.18 php                                                              
    1 root      20   0  278928  22764  15744 S   0.0   0.6   0:00.61 apache2                                                          
   29 www-data  20   0  285220  28152  14936 S   0.0   0.7   0:01.05 apache2                                                          
   30 www-data  20   0  285220  28316  15148 S   0.0   0.7   0:01.06 apache2                                                          
   31 www-data  20   0  285220  26936  13732 S   0.0   0.7   0:00.93 apache2                                                          
   33 www-data  20   0  285216  27516  14308 S   0.0   0.7   0:00.93 apache2                                                          
   45 root      20   0    3852   2052   1584 S   0.0   0.1   0:00.05 bash                                                             
   50 root      20   0  161452  34564  13420 T   0.0   0.9   0:09.91 php                                                              
   53 root      20   0    8040   1780   1324 R   0.0   0.0   0:02.41 top                                                              
   54 www-data  20   0  283168  26436  15316 S   0.0   0.7   0:00.66 apache2                                                          
   56 www-data  20   0  278960   7688    652 S   0.0   0.2   0:00.00 apache2                                                          
   57 www-data  20   0  278960   7684    648 S   0.0   0.2   0:00.00 apache2                                                          
   58 www-data  20   0  278960   7688    652 S   0.0   0.2   0:00.00 apache2                                                          
   59 www-data  20   0  278960   7664    632 S   0.0   0.2   0:00.00 apache2                                                          
   60 www-data  20   0  278960   7688    652 S   0.0   0.2   0:00.00 apache2      

in this output, PHP with PID 52 is running just for a few minutes. But I had it running two times for more than 50 hours… According to the docs… It will show All tables already up to date! when done. So I am wondering if it is starting always with 0 and goes up to 100% (eventually) or if it will continue from where it was stopped when restarting the operation. Can I check the progress somehow?

some insights to my database table filecache:

  • size: it is the second largest table with 184 MiB
  • type: InnoDB
  • collation: utf8mb4_bin
  • rows: ~ 536.696

SQL table info:

# Name Type Collation Attributes Null Default Comments Extra
1 fileidPrimaryIndex bigint(20) No None AUTO_INCREMENT
2 storageIndex bigint(20) No 0
3 path varchar(4000) utf8mb4_bin Yes NULL
4 path_hashIndex varchar(32) utf8mb4_bin No
5 parentIndex bigint(20) No 0
6 nameIndex varchar(250) utf8mb4_bin Yes NULL
7 mimetypeIndex bigint(20) No 0
8 mimepartIndex bigint(20) No 0
9 sizeIndex bigint(20) No 0
10 mtimeIndex bigint(20) No 0
11 storage_mtime bigint(20) No 0
12 encrypted int(11) No 0
13 unencrypted_size bigint(20) No 0
14 etag varchar(40) utf8mb4_bin Yes NULL
15 permissions int(11) Yes 0
16 checksum varchar(255) utf8mb4_bin Yes NULL

OR am I done already? :wink:

Looking forward to your help and ideas. Let me know if I need to provide more background and info.

THX a lot for reading this and helping me.
~ Markus


update 2021-01-15:
after running it for almost 48h again (4th time or so) I got this on the console:
PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 29360256 bytes) in /var/www/html/lib/private/Log/ExceptionSerializer.php on line 137
PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 29360256 bytes) in /var/www/html/lib/private/Log.php on line 208

any idea?

I don’t remember exactly, for 200 000 entries, it was a bit of time, perhaps 30s or so on a 3.4 GHz Intel Xeon. If you have memory problems, I’d increase the memory for the php command line, if you risk of running low on memory, you can stop apache during this process).
In theory you could think of migrating the database to a more powerful host just for this procedure. But I would do that if you had 10 million files or something really large…

Can’t you check again on the web-interface in the admin section. The warning messages should disappear.