Very High CPU

[finalls]

#15995 # Steps to reproduce

1. Update NC 14 to 15.0.8

Actual behaviour

Very High CPU.
The process looks like this. Which loads the processor. 100%

Server configuration

Operating system: Centos 7

Web server: nginx

Database: Postgress 10

PHP version: 7.1

Nextcloud version: 15.0.8

Updated from an older Nextcloud/ownCloud or fresh install:
fresh install NC12

List of activated apps:

App list

Enabled:
  - accessibility: 1.1.0
  - activity: 2.8.2
  - admin_audit: 1.5.0
  - announcementcenter: 3.4.1
  - calendar: 1.6.5
  - circles: 0.16.5
  - cloud_federation_api: 0.1.0
  - comments: 1.5.0
  - dav: 1.8.2
  - federatedfilesharing: 1.5.0
  - federation: 1.5.0
  - files: 1.10.0
  - files_accesscontrol: 1.5.0
  - files_automatedtagging: 1.5.0
  - files_external: 1.6.0
  - files_pdfviewer: 1.4.0
  - files_sharing: 1.7.0
  - files_texteditor: 2.7.0
  - files_trashbin: 1.5.0
  - files_versions: 1.8.0
  - files_videoplayer: 1.4.0
  - firstrunwizard: 2.4.0
  - gallery: 18.2.0
  - groupfolders: 3.0.2
  - impersonate: 1.2.0
  - logreader: 2.0.0
  - lookup_server_connector: 1.3.0
  - music: 0.9.5
  - nextcloud_announcements: 1.4.0
  - notes: 3.0.0
  - notifications: 2.3.0
  - oauth2: 1.3.0
  - password_policy: 1.5.0
  - provisioning_api: 1.5.0
  - quota_warning: 1.4.0
  - richdocuments: 3.3.8
  - serverinfo: 1.5.0
  - sharebymail: 1.5.0
  - support: 1.0.0
  - survey_client: 1.3.0
  - systemtags: 1.5.0
  - theming: 1.6.0
  - twofactor_backupcodes: 1.4.1
  - twofactor_totp: 2.1.2
  - updatenotification: 1.5.0
  - workflowengine: 1.5.0
Disabled:
  - deck
  - encryption
  - onlyoffice
  - user_ldap

Nextcloud configuration:

Config report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***",
            "***REMOVED SENSITIVE VALUE***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "https:\/\/SENSITIVE VALUE",
        "dbtype": "pgsql",
        "version": "15.0.8.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "session_lifetime": 86400,
        "remember_login_cookie_lifetime": 86400,
        "session_keepalive": false,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "default_language": "ru",
        "installed": true,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_smtpauthtype": "LOGIN",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "loglevel": 0,
        "maintenance": false,
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "skeletondirectory": "",
        "versions_retention_obligation": "7,auto",
        "has_internet_connection": true,
        "log_rotate_size": "104857600",
        "updatechecker": true,
        "theme": "",
        "filelocking.enabled": true,
        "proxy": "proxy.SENSITIVE VALUE",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "memcache.local": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 0,
            "password": "***REMOVED SENSITIVE VALUE***"
        },
        "updater.release.channel": "stable"
    }
}

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

Are you using encryption: no

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

Client configuration

Browser: Opera

Operating system: Windows 10

Logs

Web server error log

Web server error log

none

Nextcloud log (data/nextcloud.log)

Nextcloud log

none

Browser log

Browser log

none

-Pgsql select-

SELECT "f". * FROM "oc_filecache", "f" WHERE ("f". "Storage" IN ($ 1, $ 2, $ 3)) AND (("f". "Mimetype" <> $ 4) OR ("f". "size" = 0)) AND ("f". "path
"DO NOT LIKE $ 5) AND (" f "." Path "DO NOT LIKE $ 6) ORDER" f "." Mtime "DESC LIMIT 500 OFFSET 987500

How can I optimize this problem? And what is this heavy process?

[Calvario]

Hello @finalls,

I got the same issue one or two months ago and it's was releated with two plugins:

• Group folders
• Preview Generator

I ended by removing the Group folder plugin because in fact the query that you sent was grabing all the Previews generated (maybe a bug on Preview Generator) and it was impossible to have the "Recent" view on the "File" feature of an user that has a group folder.

I'didn't reported it yet because I wanted to replicate it on a staging env before that, but didn't got the time to do it. Maybe your case is different but it's still a clue.

Thank you

[finalls]

Developers please tell me. Is there a problem from here?

OC\Repair\NC13
nextcloud/lib/private/Repair/NC13/RepairInvalidPaths.php

Very similar to the query that I wrote above.

[finalls]

After update

nextcloud=# select * from pg_stat_all_tables where relname = 'oc_filecache';

-[ RECORD 1 ]-------+-------------
relid | 16437
schemaname | public
relname | oc_filecache
seq_scan | 0
seq_tup_read | 0
idx_scan | 490842
idx_tup_fetch | 1453742068
n_tup_ins | 36
n_tup_upd | 550
n_tup_del | 17
n_tup_hot_upd | 71
n_live_tup | 20
n_dead_tup | 503
n_mod_since_analyze | 603
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0

[kesselb]

OC\Repair\NC13
nextcloud/lib/private/Repair/NC13/RepairInvalidPaths.php

server/lib/private/Repair/NC13/RepairInvalidPaths.php

Lines 184 to 186 in 4ff3978

	$shouldRun = version_compare($versionFromBeforeUpdate, '11.0.5.2', '<');
	$shouldRun |= version_compare($versionFromBeforeUpdate, '12.0.0.0', '>=') && version_compare($versionFromBeforeUpdate, '12.0.0.30', '<');
	$shouldRun |= version_compare($versionFromBeforeUpdate, '13.0.0.0', '==');

Should run if you upgrade to nextcloud 13.

SELECT "f". * FROM "oc_filecache", "f" WHERE ("f". "Storage" IN ($ 1, $ 2, $ 3)) AND (("f". "Mimetype" <> $ 4) OR ("f". "size" = 0)) AND ("f". "path
"DO NOT LIKE $ 5) AND (" f "." Path "DO NOT LIKE $ 6) ORDER" f "." Mtime "DESC LIMIT 500 OFFSET 987500

server/lib/private/Files/Node/Folder.php

Lines 406 to 423 in 67dd4b0

	private function recentSearch($limit, $offset, $storageIds, $folderMimetype) {
	$builder = \OC::$server->getDatabaseConnection()->getQueryBuilder();
	$query = $builder
	->select('f.*')
	->from('filecache', 'f')
	->andWhere($builder->expr()->in('f.storage', $builder->createNamedParameter($storageIds, IQueryBuilder::PARAM_INT_ARRAY)))
	->andWhere($builder->expr()->orX(
	// handle non empty folders separate
	$builder->expr()->neq('f.mimetype', $builder->createNamedParameter($folderMimetype, IQueryBuilder::PARAM_INT)),
	$builder->expr()->eq('f.size', new Literal(0))
	))
	->andWhere($builder->expr()->notLike('f.path', $builder->createNamedParameter('files_versions/%')))
	->andWhere($builder->expr()->notLike('f.path', $builder->createNamedParameter('files_trashbin/%')))
	->orderBy('f.mtime', 'DESC')
	->setMaxResults($limit)
	->setFirstResult($offset);
	return $query->execute()->fetchAll();
	}

[kesselb]

Mind to update your first post and add the issue template: https://github.com/nextcloud/server/blob/master/.github/ISSUE_TEMPLATE/Bug_report.md

Without it's much harder/impossible to reproduce your problem.

[finalls]

Hello @kesselb
added information.

[finalls]

@kesselb
server/lib/private/Files/Node/Folder.php
Tell me how you can optimize or remove this query? What is he responsible for?
I am not very strong in php.
Thank's.

[finalls]

Up! need help.

[neo5779777]

Any progress?

[finalls]

@JyBigBoss

Any progress?
No =(