HUGE (really) SQL activity on oc_properties table!

Because an image illustrates it better:

One hundred users, 4Tb of files, 400Gb database size (95% are oc_activity table) are causing this.

MariaDB server 10.3 is optimized against best practices regarding the available RAM (16GB) and the CPU cores (8). oc_properties table records 300M query access per day (!!) with only…160 records inside it. The second table with more access is oc_filecache with 120M per day.

Galera cluster is configured for Read and Write split,screenshot is one read nodes (of two)

This causes very long delays on WebGUI to only open a page.

Anybody has any idea ?

How to reduce OC_Activitiy table

120M per day = 43 GB in a year :wink:

380GB (95%) / 120M = 3116 = 8 years?

I think 120M a day or 95% is not correct.
Or it was higher in the last months and/or years.

I do not know what happens if you reduce the days.
Perhaps it is better to temporary deactivate CRON and execute it manually one time.
Also i do not know if cron delete old entries.

Thanks but the problem here seems oc_properties not oc_activity (despite of its size).
We see very little activity on that table, while 95% of whole server activity comes from oc_properties table…

Our activity retention is set to 45 days since several months, and cron runs correctly.
Checked the activity table, oldest timestamp is 45 days old.

What version of nextcloud are you running? They added indexing to oc_properties (pull #20716) which I believe never got backported so it’s only available in version 19.

NC18, but we created them too on NC18 (manually).