performance issue when deleting folder of user with many files

[thejulian]

Steps to reproduce

1. Create Folder
2. Delete Folder

Expected behaviour

Folder should be deleted immediately

Actual behaviour

Folder exists over several minutes until it gets deleted or it don't even get deleted

Server configuration

Operating system:
Centos 7.3.1611
Web server:
Apache 2.4.6
Database:
Mysql
PHP version:
7.0.14
Nextcloud version: (see Nextcloud admin page)
11.0
Updated from an older Nextcloud/ownCloud or fresh install:
10.0.2
Where did you install Nextcloud from:
Downloaded TAR-archive from website
Signing status:

Signing status

No errors have been found.

List of activated apps:

App list

- activity: 2.4.1
  - admin_audit: 1.1.0
  - comments: 1.1.0
  - dav: 1.1.1
  - federatedfilesharing: 1.1.1
  - federation: 1.1.1
  - files: 1.6.1
  - files_accesscontrol: 1.1.2
  - files_pdfviewer: 1.0.1
  - files_sharing: 1.1.1
  - files_texteditor: 2.2
  - files_trashbin: 1.1.0
  - files_versions: 1.4.0
  - files_videoplayer: 1.0.0
  - firstrunwizard: 2.0
  - gallery: 16.0.0
  - logreader: 2.0.0
  - lookup_server_connector: 1.0.0
  - nextcloud_announcements: 1.0
  - notifications: 1.0.1
  - password_policy: 1.1.0
  - provisioning_api: 1.1.0
  - serverinfo: 1.1.1
  - sharebymail: 1.0.1
  - survey_client: 0.1.5
  - systemtags: 1.1.3
  - templateeditor: 0.2
  - theming: 1.1.1
  - twofactor_backupcodes: 1.0.0
  - updatenotification: 1.1.1
  - workflowengine: 1.1.1

The content of config/config.php:

Config report

{
    "system": {
        "instanceid": "ocla6eei09l6",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud",
         ],
        "datadirectory": "\/shared\/nextcloud_data",
        "overwrite.cli.url": "http:\/\/cloud",
        "dbtype": "mysql",
        "version": "11.0.0.10",
        "dbname": "nextcloud_db",
        "dbhost": "localhost",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "UTC",
        "installed": true,
        "loglevel": 0,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_from_address": "cloud",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "updater.release.channel": "stable",
        "trashbin_retention_obligation": "auto,14",
        "filesystem_check_changes": 1,
        "maintenance": false,
        "theme": "",
        "filelocking.enabled": true,
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "localhost",
            "port": 6379
        }
    }
}

Are you using external storage, if yes which one: local/smb/sftp/...
no
Are you using encryption: yes/no
no
Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...
no

Client configuration

Browser:
Chromium
Operating system:
Debian 8

Logs

Web server error log

Web server error log

``` No errors [Wed Jan 11 09:07:54.887290 2017] [mpm_prefork:notice] [pid 22986] AH00163: Apache/2.4.6 (CentOS) PHP/7.0.14 configured -- resuming normal operations [Wed Jan 11 09:07:54.887307 2017] [core:notice] [pid 22986] AH00094: Command line: '/sbin/httpd -D STATUS -f /etc/httpd/conf/httpd.conf -c PidFile /var/run//httpd.pid' ```

Nextcloud log (data/nextcloud.log)

Nextcloud log

``` {"reqId":"WHXaFRf1NgSYKRgERHO25gAAAAA","remoteAddr":"10.10.11.135","app":"admin_audit","message":"File deleted: \"\/private\/home\/olaf\"","level":1,"time":"2017-01-11T07:09:09+00:00","method":"DELETE","url":"\/remote.php\/webdav\/private/home\/olaf","user":"julian,"version":"11.0.0.10"} ```

Browser log

Browser log

``` Didn't find any useful stuff ```

Additional notes

cron.php running via cronjob. Folder gets locked after "delete" when I check the REDIS-DB. I will update the post as soon i know more.
Also it seems that only one account (ACC A) who owns the folder has the problem. If I create a folder with USER A it gets this strange behavior. With USER B not. Also if User A shares a folder, USER B also isn't able to delete the folder. But if USER B creates the Folder, USER A can delete. So i assume something with the User is broke. But what? It is also only a guess - i only have two accounts. Mine and Admin who one ~1TB of files.

UPDATE 2017/01/12

Created a Folder and created 2000 txt-files in my cloud directory. Deleted it after and it got stuck by syncing 0 of 2000 files. After 3 to 5 minutes I "timed out" - but I didn't - i was deleted after the Error was shown.

[nickvergessen]

Can you explain the steps a bit more?
Are you creating/deleting the folder via the sync client, or web UI? Is the folder shared? Does it contain any files?

[thejulian]

Hallo @nickvergessen ,

I tried it with both. Creating/Deleting on WEBUI is as discbribed above. Creating/Deleting with Client is nearly the same. The nextcloud.log shows immediately, that it is deleted. It is deleted from my local SSD but needs his time until it is removed from the server.
Doesn't matter if the folder is shared or not and if it is empty or not. Also thought it depends on hidden files in the folder but it don't.
I also checked the CPU/RAM and IO of the server disks. Looks fine everything. Load is 2-3 (i have 16 cores) and RAM is used 2GB of 32. Disk is running on iowait of 0.09 ... so it shouldnt be a HW problem. maybe the huge amount of files on this account is the problem?

PS: Accidentely closed this thread .. opened again.

[thejulian]

I think i have figured it out, but i don't know how to resolve it. I activated log_slow_queris and saw this when I wanted to delete the folder "New folder". The table "oc_filecache" had over 2.1 mio entries and has now also after a few minutes over 300.000 again. It seems that everything is stored in this table. Because I deleted every entry and also the shares disappeared and the size of the files are change from XY MB to "Pending .." after opening the folder it shows the size again. Deleting of the files is now fast again. But will become slow because the Table get spammed.

#Time: 170112 14:18:53
#User@Host: nextcloud[nextcloud] @ localhost []
#Thread_id: 60  Schema: nextcloud_db  QC_hit: No
#Query_time: 125.545569  Lock_time: 0.000015  Rows_sent: 0  Rows_examined: 2184503
SET timestamp=1484227133;
SELECT `path`, `fileid` FROM `oc_filecache` WHERE `storage` = '1' AND `path` LIKE 'files/Administration/New folder/%';

[thejulian]

After one night of caching and nobody using the cloud the Table "oc_filecache" has again 2.1 mio entries. Should the cache not be emptied after n hours of not using a specific file/folder ?

[nickvergessen]

The oc_filecache table is not a cache. It's an index. The functionality changed back in the days but the table can be very huge so renaming is not an option.

When you get so many entries, please have a look which client is creating these New folders. Maybe the accesslog or nextcloud log has some information about that.

[thejulian]

Hi!
It was me. I wanted to now which query is executed when a folder gets deleted. The oc_filecache is this big, because my Cloud stores about 1TB of Documents on one account. This is also the reason why deleting is only slow on this account. Because my private ACC has no this much entries. USER A has over 2mio entries when SELECT path, fileid FROM oc_filecache WHERE storage = '1' AND path LIKE "/files/blubberfasel/bla" and USER B has 200 when SELECT path, fileid FROM oc_filecache WHERE storage = '2' AND path LIKE "/files/blubberfasel/bla". 99% of the files are owned by USER A and is shared with every other USER B/C/D/E. They upload their files to USER A shares.

We are currently running in a BETA Test with the cloud, so I can test and check everything - my cloud isn't productiv yet.

Is their a solution ? I don't want to create several accounts and split the files to them only that the query gets faster.
It would be really nice to get it work.

PS: If you don't understand why, I can explain in german, because my english isn't that good as you see ;-)

[nickvergessen]

@icewind1991 performance issue on deleting a folder when you have many files, something we can do here?

[thejulian]

Which statement is executed when I delete a file ? Because this works fast. A empty folder and an empty textfile on the same location are deleted at the same time. File disappeared immediately, folder took his time. File was deleted also on filesystem in the moment I deleted it on the WEB UI. Folder was on the Filesystem until it was removed from the UI.

Is there a way the handle a folder like a file?

[thejulian]

Moved the Database to a SSD. Perfomance is the same .. Maybe I will do the workaround and split the files in to several storages of different users.

[thejulian]

@nickvergessen @icewind1991
It would be nice to get rid off the query "AND PATH LIKE .." and make a folder_id or something like that. Files in a folder get the id of a folder in a seperate column. Instead of AND PATH LIKE, it would be AND FOLDER_ID = "12345". Because just listing the file_id withe PATH LIKE I get them within 1.16sec. Or make the folder invisible right after clicking delete and then go on with the statement. My Nextcloud isn't productive because of this error and I'm really looking forward to this 👍 Thanks for reading !

[nickvergessen]

The problem is, that a file can have multiple parent folders, so this would cause either an OR which is also slow, or an recursive query for each subfolder.
We know this problem, there is just no good way around this atm.

[thejulian]

And just make the folder invisible before the Statement is executed? No one needs to know that the folder is still there. Why is the statement executed ? Because I need to know which files are in this folder or? Because when I delete a file, there isn't this statement. And generating a table, where every folder gets the fileid's from files under him? And when a file is in Folder /BLI/BLA/BLUB/BLO it will appear 4 Times. It would be a much bigger database, but maybe it would be faster? Sorry if I'm annoying ..

[chrsch]

Just want to second that handling many files (not only on deletion) is an issue with nextcloud. From my experience ist has impact on user experience and trust in the cloud.

[Baltix]

I've noticed, that every operation with any file from folder with thousands of files through webdav protocol generates thousands queries (one or 2 query for every file from that folder):
SELECT * FROM "oc_properties" WHERE "userid" = $1 AND "propertypath" = $2 AND "propertyname" in ($3)
This is a bug, because I access (for example copy) only one file, not all files from folder.
I've increased query speed 10x by creating and index for both columns - userid and propertypath:
CREATE INDEX properties_path_index ON oc_properties USING btree (userid, propertypath);
(this is PostgreSQL syntax)

I've created separate issue #8962

[mario]

Can you test if this was fixed by #20716?

[nickvergessen]

Closing as this issue is very old.
If it is still happening please create a new issue using the new issue template