How to clean up database?

ℹ️ Support
1

Nextcloud version (eg, 20.0.5): 20.0.6
Operating system and version (eg, Ubuntu 20.04): Docker : nextcloud:production-apache
Apache or nginx version (eg, Apache 2.4.25): Traefik latest
PHP version (eg, 7.4): PHP of the container

The issue you are facing: The database is huge for my little installation. I have only 6 users that mainly use it for contact sync and file sync. The database is about 56Gb.

To clean the database, can I truncate the table?

Thanks
Pierre

The probleme come from oc_filecache:

MariaDB [nextcloud]> SELECT
->   TABLE_SCHEMA AS `Database`,
->   TABLE_NAME AS `Table`,
->   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
-> FROM
->   information_schema.TABLES
-> ORDER BY
->   (DATA_LENGTH + INDEX_LENGTH)
-> DESC;

±-------------------±-----------------------------------------------------±----------+
| Database | Table | Size (MB) |
±-------------------±-----------------------------------------------------±----------+
| nextcloud | oc_filecache | 50589 |
| nextcloud | oc_bruteforce_attempts | 35 |
| nextcloud | oc_activity | 7 |
| mysql | time_zone_transition | 7 |
| nextcloud | oc_mail_recipients | 4 |
| nextcloud | oc_jobs | 4 |
| nextcloud | oc_mail_messages | 3 |
| nextcloud | oc_cards | 2 |
| mysql | help_topic | 2 |
| nextcloud | oc_filecache_extended | 1 |
| mysql | time_zone_transition_type | 1 |

2 Likes
2

I would start with official internal commands like occ files:cleanup and occ files:scan --all see file operations
in my instance I have big number of files in trashbin (visible when running filescan with -v attribute) - if there is no pressure from user quota files stay in trashbin for long time which is maybe unexpected.

if this doesn’t help there try analyzing the table to find the reason for the size… there are number of reports regarding oc_filecache here in forum and on github. Depending on the initial issues there are possible solutions:

  1. sometimes the table is huge but hold mostly white space (because huge number of files disappeared from system) >> need to truncate the table
  2. often it is related to external storage where files are reference multiple times (once per user) >> no solution
1 Like
3

any idea how to execute this command in a docker environment? Doesn’t seem to exist there

4

if that is still an issue, did you try this?
https://www.thegeekstuff.com/2016/04/mysql-optimize-table/

5

as you would do with docker:

#start interactive shell
docker exec -ti nextcloud-db /bin/bash

#run occ file:scan
docker exec --user www-data nextcloud-app php occ files:scan -p 'userid/folder/subfolder'