activity
activity copied to clipboard
nextcloud
[Bug]: oc_activity design easily leads to bloat, see current usage by "recognize" app
⚠️ This issue respects the following points: ⚠️
- [X] This is a bug, not a question or a configuration/webserver/proxy issue.
- [X] This issue is not already reported on Github (I've searched it).
- [X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
- [X] Nextcloud Server is running on 64bit capable CPU, PHP and OS.
- [X] I agree to follow Nextcloud's Code of Conduct.
Bug description
The current design of the oc_activity table makes it easy or even necessary for apps that modify "systemtags" automatically to waste enormous recources in a short period of time - without noticeable benefit to the users. I am not deep into the uses and requirments of oc_activity but logging number_of_files * number_of_feature_catalog_entries * number_of_users entries of seemingly useless systemtag changes seems not useful. So, could apps be enabled to disable oc_activity logging for their changes?
We have gathered over 7 GB of oc_activity data mainly caused by recognize over only 90 days with about 30,000 files and 100 users.
See https://github.com/nextcloud/recognize/issues/844 for the corresponding issue in nextcloud/recognize.
Steps to reproduce
- Install and enable recognize on a medium sized NextCloud server.
- Wait a few weeks.
- Do:
mysql -t <<SQL
SELECT table_schema, table_name, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "MB Size"
FROM information_schema.TABLES
ORDER BY (data_length + index_length) ASC;
SQL
- Be puzzled why
oc_activityis so huge.
Expected behavior
No app can by simple metadata manipulations cause such table bloat on oc_activity.
Installation method
Community Manual installation with Archive
Nextcloud Server version
26
Operating system
Debian/Ubuntu
PHP engine version
PHP 8.1
Web server
Nginx
Database engine version
MariaDB
Is this bug present after an update or on a fresh install?
None
Are you using the Nextcloud Server Encryption module?
None
What user-backends are you using?
- [X] Default user-backend (database)
- [ ] LDAP/ Active Directory
- [ ] SSO - SAML
- [ ] Other
Configuration report
# sudo -u www-data php occ config:list system {
"system": {
"instanceid": "***REMOVED SENSITIVE VALUE***",
"passwordsalt": "***REMOVED SENSITIVE VALUE***",
"datadirectory": "***REMOVED SENSITIVE VALUE***",
"cron.lockfile.location": "<redacted>",
"dbtype": "mysql",
"version": "26.0.1.1",
"dbname": "***REMOVED SENSITIVE VALUE***",
"dbhost": "***REMOVED SENSITIVE VALUE***",
"default_phone_region": "DE",
"dbtableprefix": "oc_",
"dbuser": "***REMOVED SENSITIVE VALUE***",
"default_locale": "de_DE",
"activity_expire_days": 90,
"dbpassword": "***REMOVED SENSITIVE VALUE***",
"installed": true,
"forcessl": true,
"maxZipInputSize": 838860800,
"defaultapp": "apporder",
"allowZipDownload": true,
"mail_smtpmode": "smtp",
"mail_smtphost": "***REMOVED SENSITIVE VALUE***",
"mail_smtpport": "587",
"mail_smtptimeout": 10,
"mail_smtpauthtype": "PLAIN",
"trusted_domains": [
"<redacted>",
"<redacted>",
"<redacted>",
"<redacted>",
"<redacted>",
"<redacted>"
],
"memcache.local": "\\OC\\Memcache\\Redis",
"redis": {
"host": "***REMOVED SENSITIVE VALUE***",
"port": 6379
},
"activity_use_cached_mountpoints": "true",
"loglevel": 0,
"secret": "***REMOVED SENSITIVE VALUE***",
"forceSSLforSubdomains": false,
"appstore.experimental.enabled": true,
"trashbin_retention_obligation": "auto",
"updater.release.channel": "stable",
"overwrite.cli.url": "https:\/\/<redacted>",
"default_language": "de",
"mail_domain": "***REMOVED SENSITIVE VALUE***",
"mail_from_address": "***REMOVED SENSITIVE VALUE***",
"mail_smtpauth": 1,
"mail_smtpname": "***REMOVED SENSITIVE VALUE***",
"mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
"mail_smtpsecure": "tls",
"mysql.utf8mb4": true,
"has_rebuilt_cache": true,
"twofactor_enforced": "true",
"twofactor_enforced_groups": [
"admin"
],
"twofactor_enforced_excluded_groups": [],
"theme": "",
"mail_sendmailmode": "smtp",
"trusted_proxies": "***REMOVED SENSITIVE VALUE***",
"maintenance": false,
"app_install_overwrite": [
"emlviewer",
"apporder"
]
}
}
List of activated Apps
After having removed "recognize":
# sudo -u www-data php occ app:list Enabled:
- activity: 2.18.0
- admin_audit: 1.16.0
- announcementcenter: 6.6.0
- apporder: 0.15.0
- bruteforcesettings: 2.6.0
- calendar: 4.3.3
- circles: 26.0.0
- cloud_federation_api: 1.9.0
- comments: 1.16.0
- contacts: 5.2.0
- contactsinteraction: 1.7.0
- dashboard: 7.6.0
- dav: 1.25.0
- deck: 1.9.2
- emlviewer: 1.0.7
- federatedfilesharing: 1.16.0
- federation: 1.16.0
- files: 1.21.1
- files_external: 1.18.0
- files_fulltextsearch: 26.0.0
- files_lock: 27.0.0
- files_markdown: 2.4.0
- files_pdfviewer: 2.7.0
- files_rightclick: 1.5.0
- files_sharing: 1.18.0
- files_trashbin: 1.16.0
- files_versions: 1.19.1
- firstrunwizard: 2.15.0
- forms: 3.2.0
- fulltextsearch: 26.0.0
- fulltextsearch_elasticsearch: 26.0.0
- groupfolders: 14.0.2
- holiday_calendars: 0.3.0
- impersonate: 1.13.0
- integration_twitter: 1.0.6
- keeweb: 0.6.12
- logreader: 2.11.0
- lookup_server_connector: 1.14.0
- mail: 3.1.1
- maps: 1.0.2
- music: 1.8.3
- news: 21.2.0
- nextcloud_announcements: 1.15.0
- notes: 4.7.2
- notifications: 2.14.0
- notify_push: 0.6.3
- oauth2: 1.14.0
- password_policy: 1.16.0
- photos: 2.2.0
- polls: 5.0.5
- privacy: 1.10.0
- provisioning_api: 1.16.0
- qownnotesapi: 23.3.0
- quota_warning: 1.17.0
- recommendations: 1.5.0
- related_resources: 1.1.0-alpha1
- richdocuments: 8.0.1
- serverinfo: 1.16.0
- settings: 1.8.0
- sharebymail: 1.16.0
- spreed: 16.0.3
- support: 1.9.0
- survey_client: 1.14.0
- suspicious_login: 4.4.0
- systemtags: 1.16.0
- tasks: 0.14.5
- text: 3.7.2
- theming: 2.1.1
- twofactor_backupcodes: 1.15.0
- twofactor_email: 2.7.2
- twofactor_nextcloud_notification: 3.7.0
- twofactor_totp: 8.0.0-alpha.0
- updatenotification: 1.16.0
- user_retention: 1.10.0
- user_status: 1.6.0
- user_usage_report: 1.10.0
- viewer: 1.10.0
- weather_status: 1.6.0
- workflowengine: 2.8.0
Disabled:
- calendar_resource_management: 0.1.1-alpha.1 (installed 0.1.1-alpha.1)
- encryption: 2.14.0 (installed 1.0.0)
- integration_whiteboard: 0.0.14 (installed 0.0.14)
- sharelisting: 1.1.1 (installed 1.1.1)
- twofactor_gateway: 0.20.0 (installed 0.20.0)
- user_ldap: 1.16.0
Nextcloud Signing status
No errors have been found.
Nextcloud Logs
No response
Additional info
No response
Hi @tvogel - if I understand correctly, the recognise app is the one that is writing massive amounts of rows to the table, yes? Lemme see what I can do on this, it might take a bit of time though.
Related discussion https://github.com/nextcloud/recognize/discussions/955