Removing single photo file extremely slow (Long MySql query)

[Silvan85]

Details:

On my NextCloud Server i have:
Users: 4
Files: 99206
Most of them are photos. I generate thumbnails for all of them with ThumbnailGenerator.
So I have around 1567411 thumbnail files.

When I try to delete single file via Web interface it could take up to 10min.
For this time mysqld take 100% of CPU probably with this query:

Time: 2018-08-21T10:01:16.123892Z
User@Host: user_owncloud[user_owncloud] @ localhost []  Id:    11
Query_time: 638.434135  Lock_time: 0.000111 Rows_sent: 1567446  Rows_examined: 1658536
SET timestamp=1534845676;
SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `storage_mtime`, `mtime`, `encrypted`, `etag`, `permissions`, `checksum`
				FROM `oc_filecache` WHERE `mimepart` = '11' AND `storage` = '8';

Steps to reproduce

Add lots of Photos (100 000)
Generate Thumbnails for all of them
Try to remove one photo.

Expected behaviour

Single photo should be removed in instance.

Actual behaviour

Nextcloud hangs for 10min.

Server configuration detail

Operating system: Linux 4.14.5-92 #1 SMP PREEMPT Mon Dec 11 15:48:15 UTC 2017 armv7l

Webserver: Apache/2.4.18 (Ubuntu) (apache2handler)

Database: mysql 5.7.23

PHP version:

7.0.30-0ubuntu0.16.04.1
Modules loaded: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, apache2handler, mysqlnd, PDO, xml, apcu, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, iconv, json, exif, mysqli, pdo_mysql, Phar, posix, readline, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, Zend OPcache

Nextcloud version: 13.0.5 - 13.0.5.2

Updated from an older Nextcloud/ownCloud or fresh install:

Where did you install Nextcloud from: unknown

Signing status

Array
(
[core] => Array
(
[INVALID_HASH] => Array
(
[lib/private/Files/Storage/Local.php] => Array
(
[expected] => 6e49af563d7ca3d2716925b5802c0dfbfc0c019fc63b49360232e0cd38452cd8bd119e4f1bd099a6fe13293d190008a569d7df168fdb0b2efb4517cdeca03c81
[current] => 4ebf2a059a1ae15ec6a81eec33abb9e1762fd547e4d3800e1c77f9057a3677e8737814fad16bc337bf8726f78906241c034dcc4526d0c8aa74db46510a58aa54
)

            )

    )

)

Modification to follow symbolic links.

List of activated apps

Enabled:
 - activity: 2.6.1
 - bruteforcesettings: 1.1.0
 - comments: 1.3.0
 - dav: 1.4.7
 - federatedfilesharing: 1.3.1
 - federation: 1.3.0
 - files: 1.8.0
 - files_pdfviewer: 1.2.1
 - files_sharing: 1.5.0
 - files_texteditor: 2.5.1
 - files_trashbin: 1.3.0
 - files_versions: 1.6.0
 - files_videoplayer: 1.2.0
 - firstrunwizard: 2.2.1
 - gallery: 18.0.0
 - gpxedit: 0.0.8
 - issuetemplate: 0.4.0
 - logreader: 2.0.0
 - lookup_server_connector: 1.1.0
 - music: 0.9.0
 - nextcloud_announcements: 1.2.0
 - notifications: 2.1.2
 - oauth2: 1.1.1
 - password_policy: 1.3.0
 - previewgenerator: 1.1.0
 - provisioning_api: 1.3.0
 - serverinfo: 1.3.0
 - sharebymail: 1.3.0
 - spreed: 3.2.5
 - survey_client: 1.1.0
 - systemtags: 1.3.0
 - theming: 1.4.5
 - twofactor_backupcodes: 1.2.3
 - updatenotification: 1.3.0
 - workflowengine: 1.3.0
Disabled:
 - admin_audit
 - encryption
 - files_external
 - previewgenerator.new
 - user_external
 - user_ldap

Configuration (config/config.php)

{
    "instanceid": "***REMOVED SENSITIVE VALUE***",
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "oreccy.pl"
    ],
    "datadirectory": "***REMOVED SENSITIVE VALUE***",
    "overwrite.cli.url": "https:\/\/oreccy.pl\/nextcloud",
    "dbtype": "mysql",
    "version": "13.0.5.2",
    "dbname": "***REMOVED SENSITIVE VALUE***",
    "dbhost": "***REMOVED SENSITIVE VALUE***",
    "dbport": "",
    "dbtableprefix": "oc_",
    "mysql.utf8mb4": true,
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "installed": true,
    "updater.release.channel": "stable",
    "maintenance": false,
    "preview_max_x": 1536,
    "preview_max_y": 1536,
    "theme": "",
    "loglevel": 2,
    "memcache.local": "\\OC\\Memcache\\APCu"
}

Are you using external storage, if yes which one: no

Are you using encryption: false

Are you using an external user-backend, if yes which one: no

Client configuration

Browser: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36

Operating system:

Logs

Browser log

This is MySql query that hang up Nextcloud. 

Time: 2018-08-21T10:01:16.123892Z
User@Host: user_owncloud[user_owncloud] @ localhost []  Id:    11
Query_time: 638.434135  Lock_time: 0.000111 Rows_sent: 1567446  Rows_examined: 1658536
SET timestamp=1534845676;
SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `storage_mtime`, `mtime`, `encrypted`, `etag`, `permissions`, `checksum`
				FROM `oc_filecache` WHERE `mimepart` = '11' AND `storage` = '8';

Nextcloud log

Errors about connectiong to mysql comes from me restarting mysql to unfreez nextcloud.

    [Tue Aug 21 11:14:34.936716 2018] [:error] [pid 10878] [client 192.168.10.1:41564] PHP Fatal error:  Uncaught Error: Call to undefined function request_filesystem_credentials() in /media/ExternalHD
    D/www/wordpress/eatthis/wp-admin/includes/class-wp-upgrader-skin.php:93\nStack trace:\n#0 /media/ExternalHDD/www/wordpress/eatthis/wp-admin/includes/class-automatic-upgrader-skin.php(47): WP_Upgrad
    er_Skin->request_filesystem_credentials(false, '/media/External...', true)\n#1 /media/ExternalHDD/www/wordpress/eatthis/wp-admin/includes/class-wp-automatic-updater.php(144): Automatic_Upgrader_Ski
    n->request_filesystem_credentials(false, '/media/External...', true)\n#2 /media/ExternalHDD/www/wordpress/eatthis/wp-admin/includes/update.php(85): WP_Automatic_Updater->should_update('core', Objec
    t(stdClass), '/media/External...')\n#3 /media/ExternalHDD/www/wordpress/eatthis/wp-includes/update.php(215): find_core_auto_update()\n#4 /media/ExternalHDD/www/wordpress/eatthis/wp-includes/class-w
    p-hook.php(286): wp_version_check()\n#5 /media/ExternalHDD/www/wordpress/eatthis/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters('', Arr in /media/ExternalHDD/www/wordpress/eatthis/wp-ad
    min/includes/class-wp-upgrader-skin.php on line 93, referer: https://oreccy.pl/eatthis/wp-cron.php?doing_wp_cron=1534842873.6498479843139648437500
    [Tue Aug 21 11:38:02.712233 2018] [:error] [pid 10880] [client 185.33.37.188:63039] PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in /medi
    a/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:105\nStack trace:\n#0 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/P
    DOStatement.php(105): PDOStatement->execute(NULL)\n#1 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1006): Doctrine\\DBAL\\Driver\\PDOStatement->execute()
    \n#2 /media/ExternalHDD/www/nextcloud/lib/private/DB/Connection.php(216): Doctrine\\DBAL\\Connection->executeUpdate('UPDATE `oc_file...', Array, Array)\n#3 /media/ExternalHDD/www/nextcloud/3rdparty
    /doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(208): OC\\DB\\Connection->executeUpdate('UPDATE `oc_file...', Array, Array)\n#4 /media/ExternalHDD/www/nextcloud/lib/private/DB/QueryBuilder/
    QueryBuilder.php(214): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /media/ExternalHDD/www/nextcloud/lib/private/Lock/DBLockingProvider.php(280): OC\\DB\\ in /media/ExternalHDD/www/nextcloud/
    3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php on line 115
    [Tue Aug 21 11:38:17.683436 2018] [:error] [pid 11796] [client 185.33.37.188:57229] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured
     in driver: SQLSTATE[HY000] [2002] No such file or directory in /media/ExternalHDD/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /media/ExternalHDD/www/nextcloud/3rdparty/doctrin
    e/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connec
    tion->getDatabasePlatformVersion()\n#2 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /media
    /ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /media/ExternalHDD/www/nextcloud/lib/private/DB/Connec
    tion.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManage in /media/ExternalHDD/www/nextcloud/
    lib/private/DB/Connection.php on line 64
    [Tue Aug 21 11:38:17.684365 2018] [:error] [pid 11796] [client 185.33.37.188:57229] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured
     in driver: SQLSTATE[HY000] [2002] No such file or directory in /media/ExternalHDD/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /media/ExternalHDD/www/nextcloud/3rdparty/doctrin
    e/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connec
    tion->getDatabasePlatformVersion()\n#2 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /media
    /ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /media/ExternalHDD/www/nextcloud/lib/private/DB/Connec
    tion.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManage in /media/ExternalHDD/www/nextcloud/
    lib/private/DB/Connection.php on line 64
    [Tue Aug 21 11:42:27.985969 2018] [:error] [pid 10878] [client 157.25.138.2:39807] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured 
    in driver: SQLSTATE[HY000] [2002] No such file or directory in /media/ExternalHDD/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine
    /dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connect
    ion->getDatabasePlatformVersion()\n#2 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /media/
    ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /media/ExternalHDD/www/nextcloud/lib/private/DB/Connect
    ion.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManage in /media/ExternalHDD/www/nextcloud/l
    ib/private/DB/Connection.php on line 64
    [Tue Aug 21 11:49:36.277810 2018] [:error] [pid 16252] [client 157.25.138.2:60945] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured 
    in driver: SQLSTATE[HY000] [2002] No such file or directory in /media/ExternalHDD/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine
    /dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connect
    ion->getDatabasePlatformVersion()\n#2 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /media/
    ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /media/ExternalHDD/www/nextcloud/lib/private/DB/Connect
    ion.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /media/ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManage in /media/ExternalHDD/www/nextcloud/l
    ib/private/DB/Connection.php on line 64
    [Tue Aug 21 12:01:16.126089 2018] [:error] [pid 10878] [client 185.33.37.188:60727] PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 351107904 bytes) in /media/
    ExternalHDD/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 105

Browser log

This is MySql query that hang up Nextcloud.

Time: 2018-08-21T10:01:16.123892Z
User@Host: user_owncloud[user_owncloud] @ localhost [] Id: 11
Query_time: 638.434135 Lock_time: 0.000111 Rows_sent: 1567446 Rows_examined: 1658536
SET timestamp=1534845676;
SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, storage_mtime, mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE mimepart = '11' AND storage = '8';

[nextcloud-bot]

GitMate.io thinks possibly related issues are #10440 (MYSQL), #3145 (Old file versions not removed), #3574 (Very slow uploading files), #6017 (Upgrade 12.0.0 to 12.0.1: Takes forever, High MySQL load, long query time), and #2928 (Single File with Multiple Paths).

[Silvan85]

I found that this is caused by Application Music in scanner.php file . Will investigate this further.

[Silvan85]

As describe in Music App Issue. Problem was caused by Music App but root cause was starge implementation in NextCloud Server.