Removing files extremely slow (Long MySql query)

ℹ️ Support
1
Support intro

Sorry to hear you’re facing problems :slightly_frowning_face:

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can :heart:

Nextcloud version: 13.0.4
Operating system and version: Ubuntu 16.04.4 LTS
Apache or nginx version : Apache/2.4.18 (Ubuntu)
PHP version: 7.0.30
MySql version: 5.7.23

The issue you are facing:
Removing single photo file takes up to 10min.

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';

Is this the first time you’ve seen this error? (Y/N):N

Steps to replicate it:

  1. Add lots of Photos
  2. Generate Thumbnails for all of them
  3. Try to remove on photo.
  4. Nextcloud hangs for 10min.

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'instanceid' => '',
  'passwordsalt' => '',
  'secret' => '',
  'trusted_domains' => 
  array (
    0 => '',
  ),
  'datadirectory' => '',
  'overwrite.cli.url' => '',
  'dbtype' => 'mysql',
  'version' => '13.0.4.0',
  'dbname' => '',
  'dbhost' => '',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => '',
  'dbpassword' => '',
  'installed' => true,
  'updater.release.channel' => 'stable',
  'maintenance' => false,
  'preview_max_x' => 1536,
  'preview_max_y' => 1536,
  'theme' => '',
  'loglevel' => 2,
);

The output of your Apache/nginx/system log in /var/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
2

In general, you can check the cache settings of your database and if you have enough RAM, you can improve the overall speed. The mentioned query seems to ask for all files of a certain file type, question why is this required when you remove a single file. @nickvergessen can perhaps help us a bit or get in contact with someone responsible for the thumbnails…

If you don’t get any answer, this probably also qualifies as a bug you could report to the main repository: github.com/nextcloud/server/issue