I need to run occ db:add-missing-indices but it takes few days to be completed. During this time service is unavailable, so it doesn’t suit me. Any hints to run command without downtime?
Usually that should not run for days, what size of an instance are we talking about here?
mysql 90Gb.
Hundreds of users?
Or some table that grew too large due to some problem? Can you check that e.g. oc_sessions or similar tables did not get abnormally large? Or some app-related table?
many users, yes.
filecache takes most of size.
| file | oc_filecache | 92702.34 |
| file | oc_files_metadata | 1156.91 |
| file | oc_activity | 115.74 |
| file | oc_onlyoffice_filekey | 66.11 |
| file | oc_properties | 46.40 |
| file | oc_cards | 29.88 |
| file | oc_filecache_extended | 24.83 |
| file | oc_files_versions | 17.03 |
You can execute. Maybe it helps.
occ files:cleanup
You can read this thread for more infos.
How much files do you have in your Nextcloud user and group data directorys?
have no idea. a lot.
You can execute occ db:add-missing-indices in the background. You can use tools like screen or better tmux on your Nextcloud server. Read something about tmux.
- install
tmuxon your Nextcloud server (apt-get install tmux) - use only one session ever, use on every use this command:
tmux attach || tmux newor shortertmux attach || tmux - in tmux execute your command e.g.
occ - detach from tmux session and your Nextcloud server (read documentaton or just kill ssh-session)
- connect again to your server and reattach you old tmux with the command above, maybe your
occ-command is executed to the end.
First test it with another command e.g. top or a vim. 
yeah i know about tmux. Problem that NC fall into 504 during command execution.
Can you explain it? You must not interrupt your occ-command. Or can you not start the command again?
sorry misspell) execution ofcourse.
Then you probably have another problem. After how long or how many hours does the error occur? Are there any logs?
Maybe a timeout problem. Search e.g. max_execution_time
Maybe test:
php -d max_execution_time=7200 occ db:add-missing-indices
Use a high value.
That about DB table locking during operation.
We wait the whole night, NC was unavailable.
Sorry i am not an expert. But maybe you can use:
'filelocking.enabled' => false,
while executing the command. But it maybe a risk for you.
Do you have as many files as you have entries in the filecache table? It should be of the same order of magnitude.
Not sure how you configured your database, but now with a very large table, it might be an option to give the database a bit more resources so it can work more efficiently. When you clean up the database, and you want to avoid conflicts with other access, I’d put NC in maintenance mode.
we have really many files, so table size is fine.
DB host never run out of resources. Basic mysql optimisation also done.
cleanup will take a lot time too, so i can do it only during night.
that about files multiple access, not database.
for now we testing Database configuration — Nextcloud latest Administration Manual latest documentation
this part looks like solution /etc/php7/conf.d/mysql.ini.
It’s the part in the documentation. But if your table is that big, these values are likely not valid for you. You can try mysqltuner or tuning-primer scripts that analyze your runtime information and give tips what values might be interesting to change.
in this case, it’s not sure if the clean procedure helps a lot, and it will probably verify that all the entries are valid.
after a bit more optimisations, command works as intended without downtime.
1 Like