Unusably slow server, timeouts - seems related to mounts

meonkeys · January 8, 2023, 7:54pm

I love Nextcloud and use my home server constantly. I’m running into a brutal intermittent performance issue and I’m hoping someone might have ideas on how to work around the problem until I can properly debug it or a fix is released.

The main symptoms are very slow HTTP response times from the web UI, slow WebDAV responses, and timeouts from desktop and mobile clients (they show the server as “offline”). This slowness seems to last for somewhere around 24 hours then seems to resolve on its own. It happens every one to three months or so.

I don’t see anything useful in the logs, but when I run show processlist in MariaDB during the slowdown I see many INSERT and DELETE statements running against the oc_mounts table.

I’ve tried restarting the server (destroying and re-creating Docker containers), disabling apps like External storages support, Talk, Activity, and Full text search, killing the cron job, and killing my elasticsearch server. None of these attempts have helped.

It is in the issue tracker here:

github.com/nextcloud/server

high rate of churn for oc_mounts table

opened 06:40PM - 21 Nov 22 UTC

meonkeys

bug 0. Needs triage performance 🚀 24-feedback 25-feedback

### How to use GitHub * Please use the 👍 [reaction](https://blog.github.com/2…016-03-10-add-reactions-to-pull-requests-issues-and-comments/) to show that you are affected by the same issue. * Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue. * Subscribe to receive notifications on status change and new comments. --- ## Steps to reproduce ```sql SELECT MAX(id) FROM oc_mounts ``` ## Issue description I'm seeing what appears to be a massive amount of what I'll call churn against the `oc_mounts` table, and I think the root cause might be Nextcloud media/files shared in Talk. I've been using Talk for a while and have shared lots of media/files in chats. This is either a bug report or performance enhancement idea. Recently I experienced https://github.com/nextcloud/server/issues/33289 . Things improved when I upgraded to 24.0.7, but I'm still seeing lots of DML (mostly DELETEs and INSERTs) against `oc_mounts`. I looked for slow queries by enabling the MariaDB slow query log with a threshold of 10 seconds. I have none. I took row counts for all tables in my Nextcloud database. None are over 500k and most are much smaller. I looked at `MAX(id)` for every table, and found that `oc_mounts` had only 7k rows, but the latest inserted rows had IDs greater than 6 million. When I cleared all messages from a Talk chat with lots of attachments, I noticed the number of rows in `oc_mounts` decreased. I don't have a stand-alone repro. The churn could be all due to https://github.com/nextcloud/server/issues/33289 , I don't know. I'm hoping this leads to (a) nothing, (b) a known design choice we can improve or (c) a bug we can fix. If nextcloud/spreed isn't the correct project, maybe nextcloud/server is? ## Talk app **Talk app version:** 14.0.6 **Custom Signaling server configured:** no **Custom TURN server configured:** yes, (private / self-hosted coturn / seems to work OK) **Custom STUN server configured:** yes, `stun.nextcloud.com:443` ## Server configuration  **Operating system**: Ubuntu 20.04 LTS 64-bit server **Web server:** Apache **Database:** MariaDB **PHP version:** 8.0.25 **Nextcloud Version:** 24.0.7 (using Docker image) **List of activated apps:** <details> ``` - accessibility: 1.10.0 - activity: 2.16.0 - admin_audit: 1.14.0 - bruteforcesettings: 2.4.0 - calendar: 3.5.2 - circles: 24.0.1 - cloud_federation_api: 1.7.0 - comments: 1.14.0 - contacts: 4.2.2 - contactsinteraction: 1.5.0 - cookbook: 0.9.18 - dav: 1.22.0 - deck: 1.7.2 - event_update_notification: 2.0.0 - federatedfilesharing: 1.14.0 - files: 1.19.0 - files_automatedtagging: 1.14.0 - files_external: 1.16.1 - files_fulltextsearch: 24.0.1 - files_pdfviewer: 2.5.0 - files_retention: 1.13.2 - files_rightclick: 1.3.0 - files_sharing: 1.16.2 - files_trashbin: 1.14.0 - files_versions: 1.17.0 - files_videoplayer: 1.13.0 - firstrunwizard: 2.13.0 - forms: 2.5.1 - fulltextsearch: 24.0.0 - fulltextsearch_elasticsearch: 24.0.1 - impersonate: 1.11.0 - logreader: 2.9.0 - lookup_server_connector: 1.12.0 - maps: 0.2.1 - metadata: 0.17.0 - news: 19.0.0 - nextcloud_announcements: 1.13.0 - notes: 4.5.1 - notifications: 2.12.1 - oauth2: 1.12.0 - password_policy: 1.14.0 - passwords: 2022.11.21 - phonetrack: 0.7.2 - photos: 1.6.0 - polls: 3.8.3 - provisioning_api: 1.14.0 - recommendations: 1.3.0 - richdocuments: 6.3.1 - serverinfo: 1.14.0 - settings: 1.6.0 - sharebymail: 1.14.0 - spreed: 14.0.6 - support: 1.7.0 - systemtags: 1.14.0 - tasks: 0.14.5 - text: 3.5.1 - twofactor_backupcodes: 1.13.0 - twofactor_nextcloud_notification: 3.4.0 - twofactor_totp: 6.4.1 - twofactor_webauthn: 0.3.3 - updatenotification: 1.14.0 - user_status: 1.4.0 - video_converter: 1.0.5 - viewer: 1.8.0 - workflowengine: 2.6.0 ``` </details> **Nextcloud configuration:** <details> ```json { "system": { "htaccess.RewriteBase": "\/", "htaccess.IgnoreFrontController": true, "memcache.local": "\\OC\\Memcache\\APCu", "apps_paths": [ { "path": "\/var\/www\/html\/apps", "url": "\/apps", "writable": false }, { "path": "\/var\/www\/html\/custom_apps", "url": "\/custom_apps", "writable": true } ], "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "nextcloud.example.com" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "24.0.7.1", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "3306", "dbtableprefix": "oc_", "mysql.utf8mb4": true, "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "mail_smtpmode": "smtp", "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_sendmailmode": "smtp", "mail_smtpport": "25", "trusted_proxies": "***REMOVED SENSITIVE VALUE***", "forwarded_for_headers": [ "HTTP_X_FORWARDED_FOR", "HTTP_X_REAL_IP" ], "maintenance": false, "loglevel": 2, "ldapIgnoreNamingRules": false, "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory", "has_internet_connection": true, "memcache.distributed": "\\OC\\Memcache\\Redis", "memcache.locking": "\\OC\\Memcache\\Redis", "redis": { "host": "***REMOVED SENSITIVE VALUE***", "password": "***REMOVED SENSITIVE VALUE***", "port": 6379 }, "enable_previews": true, "enabledPreviewProviders": [ "OC\\Preview\\PNG", "OC\\Preview\\JPEG", "OC\\Preview\\GIF", "OC\\Preview\\BMP", "OC\\Preview\\XBitmap", "OC\\Preview\\MP3", "OC\\Preview\\TXT", "OC\\Preview\\MarkDown", "OC\\Preview\\Movie", "OC\\Preview\\PDF", "OC\\Preview\\SVG" ], "overwrite.cli.url": "https:\/\/nextcloud.example.com", "overwriteprotocol": "https", "app_install_overwrite": [ "cookbook", "gpxedit" ], "default_phone_region": "US", "preview_max_x": "2048", "preview_max_y": "2048", "jpeg_quality": "60", "theme": "", "trashbin_retention_obligation": "auto, 30" } } ``` </details> ### Server log (data/nextcloud.log) Not applicable. At least, I'm not seeing any messages that look related to this issue.

Possibly related:

github.com/nextcloud/server

Large number of DB queries during execution of a single API call

opened 07:58PM - 18 Jul 22 UTC

closed 09:20PM - 27 Aug 22 UTC

starypatyk

0. Needs triage performance 🚀

When diagnosing [slow loading of chat messages in Nextcloud Talk application](ht…tps://github.com/nextcloud/talk-android/issues/2228) I have noticed that in my environment (NC 24.0.3, MySQL database), during execution of a **single** API call (retrieve chat messages), NC sends **over 2000 DB queries**. I use the following call (same as the Nextcloud Talk Android app): ``` https://<server>/ocs/v2.php/apps/spreed/api/v1/chat/<room-token>?lastKnownMessageId=<msgid>&includeLastKnown=1&limit=100&setReadMarker=1&timeout=0&lookIntoFuture=0 ``` Most of the queries (over 2100 from about 2500) are: ``` INSERT INTO `oc_mounts` (`storage_id`,`root_id`,`user_id`,`mount_point`,`mount_id`,`mount_provider_class`) SELECT ?,?,?,?,?,? FROM `oc_mounts` WHERE `root_id` = ? AND `user_id` = ? HAVING COUNT(*) = 0 ``` There are about 1300 records in the **x_mounts** table. Majority of them (about 1100) have **NULL** in the **mount_provider_class** column. This causes code in the [**registerMounts** method of the **UserMountCache** class](https://github.com/nextcloud/server/blob/42bcbe269f09bac30251ac102f202fc0091602c9/lib/private/Files/Config/UserMountCache.php#L94) to ignore the existing cache entries and try to re-add them - [see **addToCache** call](https://github.com/nextcloud/server/blob/42bcbe269f09bac30251ac102f202fc0091602c9/lib/private/Files/Config/UserMountCache.php#L120). However, the correct records are **not** added to the **x_mounts** table, since records with the same **root_id** and **user_id** already exist (the ones with **NULL** in the **mount_provider_class** column). And the same situation occurs on each subsequent API call. As I understand the **mount_provider_class** column [has been added recently](https://github.com/nextcloud/server/pull/30978). However, it seems that all the records that existed earlier in the **x_mounts** table, get **NULL** in the new column after upgrade. This in turn leads to the observed behaviour. I am not sure how the issue should be fixed. - Is it safe to delete all records from the **x_mounts** table? Will they be recreated properly by the **registerMounts** code? - If yes to above - should the upgrade delete the records after adding the column?

meonkeys · January 8, 2023, 9:57pm

Interesting, the bout of slowness (as well as the excessive INSERTs and DELETEs) stopped after a few hours. My server is back to normal: lightning fast web UI, WebDAV. Desktop and mobile clients are happy (not timing out). Maybe there’s some periodic (weekly?) Nextcloud clean-up job gone awry?

meonkeys · March 15, 2023, 3:07am

Latest thing I’ve tried: deleting rows from oc_mounts with NULL in mount_provider_class.

github.com/nextcloud/server

high rate of churn for oc_mounts table

opened 06:40PM - 21 Nov 22 UTC

meonkeys

bug 0. Needs triage performance 🚀 24-feedback 25-feedback

### How to use GitHub * Please use the 👍 [reaction](https://blog.github.com/2…016-03-10-add-reactions-to-pull-requests-issues-and-comments/) to show that you are affected by the same issue. * Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue. * Subscribe to receive notifications on status change and new comments. --- ## Steps to reproduce ```sql SELECT MAX(id) FROM oc_mounts ``` ## Issue description I'm seeing what appears to be a massive amount of what I'll call churn against the `oc_mounts` table, and I think the root cause might be Nextcloud media/files shared in Talk. I've been using Talk for a while and have shared lots of media/files in chats. This is either a bug report or performance enhancement idea. Recently I experienced https://github.com/nextcloud/server/issues/33289 . Things improved when I upgraded to 24.0.7, but I'm still seeing lots of DML (mostly DELETEs and INSERTs) against `oc_mounts`. I looked for slow queries by enabling the MariaDB slow query log with a threshold of 10 seconds. I have none. I took row counts for all tables in my Nextcloud database. None are over 500k and most are much smaller. I looked at `MAX(id)` for every table, and found that `oc_mounts` had only 7k rows, but the latest inserted rows had IDs greater than 6 million. When I cleared all messages from a Talk chat with lots of attachments, I noticed the number of rows in `oc_mounts` decreased. I don't have a stand-alone repro. The churn could be all due to https://github.com/nextcloud/server/issues/33289 , I don't know. I'm hoping this leads to (a) nothing, (b) a known design choice we can improve or (c) a bug we can fix. If nextcloud/spreed isn't the correct project, maybe nextcloud/server is? ## Talk app **Talk app version:** 14.0.6 **Custom Signaling server configured:** no **Custom TURN server configured:** yes, (private / self-hosted coturn / seems to work OK) **Custom STUN server configured:** yes, `stun.nextcloud.com:443` ## Server configuration  **Operating system**: Ubuntu 20.04 LTS 64-bit server **Web server:** Apache **Database:** MariaDB **PHP version:** 8.0.25 **Nextcloud Version:** 24.0.7 (using Docker image) **List of activated apps:** <details> ``` - accessibility: 1.10.0 - activity: 2.16.0 - admin_audit: 1.14.0 - bruteforcesettings: 2.4.0 - calendar: 3.5.2 - circles: 24.0.1 - cloud_federation_api: 1.7.0 - comments: 1.14.0 - contacts: 4.2.2 - contactsinteraction: 1.5.0 - cookbook: 0.9.18 - dav: 1.22.0 - deck: 1.7.2 - event_update_notification: 2.0.0 - federatedfilesharing: 1.14.0 - files: 1.19.0 - files_automatedtagging: 1.14.0 - files_external: 1.16.1 - files_fulltextsearch: 24.0.1 - files_pdfviewer: 2.5.0 - files_retention: 1.13.2 - files_rightclick: 1.3.0 - files_sharing: 1.16.2 - files_trashbin: 1.14.0 - files_versions: 1.17.0 - files_videoplayer: 1.13.0 - firstrunwizard: 2.13.0 - forms: 2.5.1 - fulltextsearch: 24.0.0 - fulltextsearch_elasticsearch: 24.0.1 - impersonate: 1.11.0 - logreader: 2.9.0 - lookup_server_connector: 1.12.0 - maps: 0.2.1 - metadata: 0.17.0 - news: 19.0.0 - nextcloud_announcements: 1.13.0 - notes: 4.5.1 - notifications: 2.12.1 - oauth2: 1.12.0 - password_policy: 1.14.0 - passwords: 2022.11.21 - phonetrack: 0.7.2 - photos: 1.6.0 - polls: 3.8.3 - provisioning_api: 1.14.0 - recommendations: 1.3.0 - richdocuments: 6.3.1 - serverinfo: 1.14.0 - settings: 1.6.0 - sharebymail: 1.14.0 - spreed: 14.0.6 - support: 1.7.0 - systemtags: 1.14.0 - tasks: 0.14.5 - text: 3.5.1 - twofactor_backupcodes: 1.13.0 - twofactor_nextcloud_notification: 3.4.0 - twofactor_totp: 6.4.1 - twofactor_webauthn: 0.3.3 - updatenotification: 1.14.0 - user_status: 1.4.0 - video_converter: 1.0.5 - viewer: 1.8.0 - workflowengine: 2.6.0 ``` </details> **Nextcloud configuration:** <details> ```json { "system": { "htaccess.RewriteBase": "\/", "htaccess.IgnoreFrontController": true, "memcache.local": "\\OC\\Memcache\\APCu", "apps_paths": [ { "path": "\/var\/www\/html\/apps", "url": "\/apps", "writable": false }, { "path": "\/var\/www\/html\/custom_apps", "url": "\/custom_apps", "writable": true } ], "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "nextcloud.example.com" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "24.0.7.1", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "3306", "dbtableprefix": "oc_", "mysql.utf8mb4": true, "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "mail_smtpmode": "smtp", "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_sendmailmode": "smtp", "mail_smtpport": "25", "trusted_proxies": "***REMOVED SENSITIVE VALUE***", "forwarded_for_headers": [ "HTTP_X_FORWARDED_FOR", "HTTP_X_REAL_IP" ], "maintenance": false, "loglevel": 2, "ldapIgnoreNamingRules": false, "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory", "has_internet_connection": true, "memcache.distributed": "\\OC\\Memcache\\Redis", "memcache.locking": "\\OC\\Memcache\\Redis", "redis": { "host": "***REMOVED SENSITIVE VALUE***", "password": "***REMOVED SENSITIVE VALUE***", "port": 6379 }, "enable_previews": true, "enabledPreviewProviders": [ "OC\\Preview\\PNG", "OC\\Preview\\JPEG", "OC\\Preview\\GIF", "OC\\Preview\\BMP", "OC\\Preview\\XBitmap", "OC\\Preview\\MP3", "OC\\Preview\\TXT", "OC\\Preview\\MarkDown", "OC\\Preview\\Movie", "OC\\Preview\\PDF", "OC\\Preview\\SVG" ], "overwrite.cli.url": "https:\/\/nextcloud.example.com", "overwriteprotocol": "https", "app_install_overwrite": [ "cookbook", "gpxedit" ], "default_phone_region": "US", "preview_max_x": "2048", "preview_max_y": "2048", "jpeg_quality": "60", "theme": "", "trashbin_retention_obligation": "auto, 30" } } ``` </details> ### Server log (data/nextcloud.log) Not applicable. At least, I'm not seeing any messages that look related to this issue.

meonkeys · March 15, 2023, 3:08am

I’m also wondering if its safe to do some cleanup on oc_mounts. I’ll copy my question here from https://github.com/nextcloud/server/issues/35311#issuecomment-1468851352…

I have thousands of rows in my oc_mounts table where root_id doesn’t relate to any rows in oc_filecache. Is this expected?

Here’s a few made-up rows that look like what I have about 4,700 of in my oc_mounts table:

id	storage_id	root_id	user_id	mount_point	mount_id	mount_provider_class
1994832	17	3502192	fred	/fred/files/Photos/	4	OCA\Files_External\Config\ConfigAdapter
1994833	17	3502192	bob	/bob/files/Photos/	4	OCA\Files_External\Config\ConfigAdapter
1994834	17	3502192	joe	/joe/files/Photos/	4	OCA\Files_External\Config\ConfigAdapter

But there’s no row in oc_filecache with a fileid of 3502192.