nextcloud / nextcloud/server

Performance degradation nextcloud 20 server mariadb ubuntu since Januar 22 or short later

Open
#25,386 27 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

0. Needs triage 25-feedback bug feature: database feature: filesystem feature: previews and thumbnails needs review performance 🚀
Dominant language
PHP
Stars
36.9k
Forks
5.2k
Avg merge
2d 3h
Merged PRs (30d)
713

Description

Steps to reproduce

running nextcloud (see version below). Having about 50 clients connecting by WEBDAV and CALDAV.
look in slow_query.log.

The issue is new and possibly related to an upgrade to a new version (issue still persists in 20.0.6) or due to an upgrade of an app.

The nextcloud ran well and performant for years. The performance degradation started since about one week.

It seems the DB became a bottleneck. DB system is mariaDB on ubuntu 18 (version details below).
After restarting mariaDB the symptoms are cured for some time.

We already tried some tunings of the DB (which ran well for years) without success.

Maybe related: We find some slow queries (between 8 and 100 seconds) of the pattern:

SELECT `a`.`name` FROM `oc_filecache` `a` LEFT JOIN `oc_filecache` `b` ON `a`.`name` = `b`.`fileid` WHERE (`b`.`fileid` IS NULL) AND (`a`.`path` LIKE 'appdata\\_oc2rk8c8h02y/preview/_/_/_/_/_/_/_/%') AND (`a`.`mimetype` = '2');

Regards, Dirk

Expected behaviour

Normal Performance while serving 50 clients

Actual behaviour

After some time performance becomes very bad. restart of mysqld helps for some time (mariadb, ubuntu 18)

Server configuration detail

Operating system: Linux 4.15.0-135-generic #139-Ubuntu SMP Mon Jan 18 17:38:24 UTC 2021 x86_64

Webserver: Apache (fpm-fcgi)

Database: mysql 10.1.47

PHP version:

7.4.14
Modules loaded: Core, date, libxml, openssl, pcre, zlib, bz2, calendar, ctype, hash, filter, ftp, gettext, gmp, SPL, iconv, Reflection, session, standard, SimpleXML, sockets, mbstring, tokenizer, xml, cgi-fcgi, mysqlnd, bcmath, curl, dba, dom, enchant, fileinfo, gd, imagick, imap, intl, json, ldap, exif, mysqli, odbc, PDO, pdo_mysql, PDO_ODBC, pdo_pgsql, pdo_sqlite, pgsql, Phar, posix, pspell, redis, soap, sodium, sqlite3, sysvmsg, sysvsem, sysvshm, tidy, xmlreader, xmlrpc, xmlwriter, xsl, zip, ionCube Loader, Zend OPcache

Nextcloud version: 20.0.6 - 20.0.6.1

Updated from an older Nextcloud/ownCloud or fresh install:

Where did you install Nextcloud from: unknown

Signing status

Array
(
)

List of activated apps 
Enabled:
 - accessibility: 1.6.0
 - activity: 2.13.4
 - analytics: 3.2.0
 - announcementcenter: 4.0.0
 - apporder: 0.11.0
 - bookmarks: 4.0.8
 - bruteforcesettings: 2.0.1
 - calendar: 2.1.3
 - cloud_federation_api: 1.3.0
 - comments: 1.10.0
 - contacts: 3.4.3
 - contactsinteraction: 1.1.0
 - dashboard: 7.0.0
 - data_request: 1.7.0
 - dav: 1.16.2
 - drawio: 0.9.8
 - dropit: 0.4.0
 - emlviewer: 0.0.21
 - federatedfilesharing: 1.10.2
 - federation: 1.10.1
 - files: 1.15.0
 - files_accesscontrol: 1.10.2
 - files_automatedtagging: 1.10.1
 - files_downloadactivity: 1.9.0
 - files_external: 1.11.1
 - files_fulltextsearch: 20.0.0
 - files_fulltextsearch_tesseract: 20.0.1
 - files_pdfviewer: 2.0.1
 - files_readmemd: 1.2.0
 - files_rightclick: 0.17.0
 - files_sharing: 1.12.2
 - files_trackdownloads: 1.9.0
 - files_trashbin: 1.10.1
 - files_versions: 1.13.0
 - files_videoplayer: 1.9.0
 - firstrunwizard: 2.9.0
 - fulltextsearch: 20.0.0
 - fulltextsearch_elasticsearch: 20.0.0
 - groupfolders: 8.2.0
 - impersonate: 1.7.0
 - issuetemplate: 0.7.0
 - keeweb: 0.6.4
 - logreader: 2.5.0
 - lookup_server_connector: 1.8.0
 - mail: 1.8.0
 - news: 15.1.1
 - nextcloud_announcements: 1.9.0
 - notes: 4.0.2
 - notifications: 2.8.0
 - oauth2: 1.8.0
 - onlyoffice: 6.2.0
 - password_policy: 1.10.1
 - photos: 1.2.3
 - polls: 1.6.3
 - privacy: 1.4.0
 - provisioning_api: 1.10.0
 - quota_warning: 1.9.1
 - ransomware_detection: 0.10.0
 - ransomware_protection: 1.8.0
 - recommendations: 0.8.0
 - serverinfo: 1.10.0
 - settings: 1.2.0
 - sharebymail: 1.10.0
 - sharerenamer: 2.7.3
 - support: 1.3.0
 - systemtags: 1.10.0
 - tasks: 0.13.6
 - terms_of_service: 1.6.1
 - text: 3.1.0
 - theming: 1.11.0
 - twofactor_backupcodes: 1.9.0
 - updatenotification: 1.10.0
 - user_status: 1.0.1
 - viewer: 1.4.0
 - weather_status: 1.0.0
 - workflow_pdf_converter: 1.5.1
 - workflow_script: 1.5.1
 - workflowengine: 2.2.0
Disabled:
 - admin_audit
 - encryption
 - survey_client
 - user_ldap
Configuration (config/config.php) 
{
    "instanceid": "***REMOVED SENSITIVE VALUE***",
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "oc.pape-und-co.de"
    ],
    "datadirectory": "***REMOVED SENSITIVE VALUE***",
    "version": "20.0.6.1",
    "dbtype": "mysql",
    "dbhost": "***REMOVED SENSITIVE VALUE***",
    "dbname": "***REMOVED SENSITIVE VALUE***",
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "dbtableprefix": "oc_",
    "installed": true,
    "default_language": "de_DE",
    "defaultapp": "files",
    "mail_domain": "***REMOVED SENSITIVE VALUE***",
    "mail_from_address": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpmode": "sendmail",
    "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpport": "25",
    "mail_smtpauthtype": "LOGIN",
    "overwritehost": "oc.pape-und-co.de",
    "overwriteprotocol": "https",
    "overwrite.cli.url": "https:\/\/oc.pape-und-co.de",
    "htaccess.RewriteBase": "\/",
    "trashbin_retention_obligation": "auto",
    "updater.release.channel": "stable",
    "loglevel": 0,
    "log_rotate_size": 2097152,
    "enable_previews": true,
    "maintenance": false,
    "memcache.local": "\\OC\\Memcache\\Redis",
    "memcache.distributed": "\\OC\\Memcache\\Redis",
    "redis": {
        "host": "***REMOVED SENSITIVE VALUE***",
        "port": 6379,
        "timeout": 0,
        "dbindex": 0
    },
    "mysql.utf8mb4": true,
    "tempdirectory": "\/var\/www\/vhosts\/oc.pape-und-co.de\/oc.pape-und-co.de\/owncloud-temp",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "filelocking.enabled": "true",
    "memcache.locking": "\\OC\\Memcache\\Redis",
    "debug": false,
    "appstore.experimental.enabled": true,
    "asset-pipeline.enabled": false,
    "app_install_overwrite": [
        "sharerenamer",
        "dropit",
        "keeweb",
        "files_readmemd",
        "emlviewer",
        "drop_account"
    ],
    "enabledPreviewProviders": [
        "OC\\Preview\\PNG",
        "OC\\Preview\\JPEG",
        "OC\\Preview\\GIF",
        "OC\\Preview\\HEIC",
        "OC\\Preview\\BMP",
        "OC\\Preview\\XBitmap",
        "OC\\Preview\\MP3",
        "OC\\Preview\\TXT",
        "OC\\Preview\\MarkDown",
        "OC\\Preview\\SVG"
    ],
    "lost_password_link": "disabled",
    "custom_csp_policy": "default-src 'self'; script-src 'self' 'unsafe-inline' 'unsafe-eval'; style-src 'self' 'unsafe-inline'; frame-src *; img-src *; font-src 'self' data:; media-src *",
    "encryption.legacy_format_support": false,
    "encryption.key_storage_migrated": false,
    "updater.secret": "***REMOVED SENSITIVE VALUE***"
}

Are you using external storage, if yes which one: local/smb/sftp/...

Are you using encryption:

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...

Client configuration

Browser: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0.2 Safari/605.1.15

Operating system:

Logs

Web server error log 
Insert your web server log here
Nextcloud log 
Insert your Nextcloud log here
Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the reported slow_query.log entries and the sample SELECT against oc_filecache, then compare database behavior before and after restarting MariaDB. The issue provides no source file or test entry point; done would require identifying and resolving the cause of the sustained performance degradation for the reported client workload.

Written by the indexing model from the issue text.

Assessment

Tech stack
mariadb, php, ubuntu
Domain
backend, databases, performance
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
20/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.