eeight
October 20, 2021, 8:34pm
1
I am facing this new issue and I was wondering if other users fixed it.
Everyday at 10AM UTC the CPU of my MariaDB (happened also on Mysql) goes to 100% for ±10 hours. When showing the process list I see a lot of queries like this one:
SELECT `f`.`fileid`,
`mapping_type`,
`mapping_id`,
`mask`,
`a`.`permissions`,
`path`
FROM `oc_filecache` `f`
LEFT JOIN `oc_group_folders_acl` `a`
ON `f`.`fileid` = `a`.`fileid`
WHERE ( `parent` = 5221946 )
AND ( ( ( `mapping_type` IS NULL )
AND ( `mapping_id` IS NULL ) )
OR ( ( `mapping_type` = 'user' )
AND ( `mapping_id` = '...' ) )
OR ( ( `mapping_type` = 'group' )
AND ( `mapping_id` = '....' ) )
OR ( ( `mapping_type` = 'group' )
AND ( `mapping_id` = '...' ) )
OR ( ( `mapping_type` = 'group' )
AND ( `mapping_id` = '....' ) )
OR ( ( `mapping_type` = 'group' )
AND ( `mapping_id` = '...' ) ) )
I have 1,105,155 entries in oc_filecache & 4,521 in oc_group_folders_acl.
eeight
October 21, 2021, 6:27pm
2
Added some information in an existing issue (might be related):
opened 08:19PM - 12 May 21 UTC
### Steps to reproduce
1. Show Files page for a user who is in 100's of group f… olders
2. Monitor the queries to the database server
3.
### Expected behaviour
Files tab should appear reasonably quickly
### Actual behaviour
Files tab takes ages (10 sec or more) to appear. The database registers 10,000 queries per second until it appears.
I suspect the problem is here in lib/private/Files/View.php in the getPath function:
```
foreach ($mounts as $mount) {
/**
* @var \OC\Files\Mount\MountPoint $mount
*/
if ($mount->getStorage()) {
$cache = $mount->getStorage()->getCache();
$internalPath = $cache->getPathById($id);
if (is_string($internalPath)) {
$fullPath = $mount->getMountPoint() . $internalPath;
if (!is_null($path = $this->getRelativePath($fullPath))) {
return $path;
}
}
}
}
```
Nextcloud is displaying about 800 folders, some of which have about 800 mounts. Every folder, times every mount, results in a query to the database!
I notice in version 20, this is improved with an optional storageId parameter, which eliminates all the mounts that are not using that storageId (if the parameter is supplied).
I'm no good at PHP (it's read-only, as far as I am concerned), but is seems to me that a simple query to the filecache to find the file by id would be far quicker, rather than repeatedly looking for it again and again until you find a mount point that has it. Probably I'm wrong, and there is an app or something that doesn't store stuff in the filecache, but you would hope there was a more efficient way?
### Server configuration
**Operating system:** Debian buster
**Web server:** nginx
**Database:** mariadb
**PHP version:** 7.3
**Nextcloud version:** 19.0.10
**Updated from an older Nextcloud/ownCloud or fresh install:** Updated
**Where did you install Nextcloud from:** Don't remember. Updated with your updater (many times)
**List of activated apps:**
<details>
<summary>App list</summary>
Accessibility 1.6.0 Featured
Activity 2.13.4 Featured
AppOrder 0.12.0
Automated PDF conversion 1.5.1
Calendar 2.2.1 Featured
Comments 1.10.0 Featured
Contacts Interaction 1.1.0 Featured
Custom CSS 1.8.0
Dashboard 7.0.0 Featured
Deck 1.2.7 Featured
Deleted files 1.10.1 Featured
External sites 3.7.2 Featured
File sharing 1.12.2 Featured
First run wizard 2.9.0 Featured
Forms 2.2.4 Featured
Group folders 8.2.1 Featured
Group quota 0.1.5
Impersonate 1.7.0 Featured
Link editor 1.1.5
Log Reader 2.5.0 Featured
Markdown Editor 2.3.3
Monitoring 1.10.0 Featured
Nextcloud announcements 1.9.0 Featured
Notifications 2.8.0 Featured
ONLYOFFICE 6.0.0 Featured
Password policy 1.10.1 Featured
PDF viewer 2.0.1 Featured
Plain text editor 2.14.0
Polls 1.8.3
Privacy 1.4.0 Featured
Right click 0.17.0 Featured
Share by mail 1.10.0 Featured
Social Login 3.3.0
Support 1.3.0 Featured
Text 3.1.0 Featured
Theming 1.11.0 Featured
Update notification 1.10.0 Featured
Usage survey 1.8.0 Featured
User status 1.0.1 Featured
Versions 1.13.0 Featured
Video player 1.9.0 Featured
Weather status 1.0.0 Featured
```
If you have access to your command line run e.g.:
sudo -u www-data php occ app:list
from within your Nextcloud installation folder
```
</details>
**Nextcloud configuration:**
<details>
<summary>Config report</summary>
```
<?php
$CONFIG = array (
'passwordsalt' => 'removed',
'secret' => 'removed',
'trusted_domains' =>
array (
0 => 'localhost',
1 => 'removed',
),
'datadirectory' => '/data/nextcloud/ncdata',
'dbtype' => 'mysql',
'version' => '19.0.4.2',
'overwrite.cli.url' => 'http://localhost',
'dbname' => 'nextcloud',
'dbhost' => 'localhost',
'dbport' => '',
'dbtableprefix' => 'oc_',
'dbuser' => 'nextcloud',
'dbpassword' => 'removed',
'installed' => true,
'instanceid' => 'removed',
'activity_expire_days' => 1,
'memcache.locking' => '\\OC\\Memcache\\Redis',
'redis' =>
array (
'host' => 'localhost',
'port' => 6379,
'timeout' => 1.5,
'password' => '',
),
'memcache.distributed' => '\\OC\\Memcache\\Redis',
'memcache.local' => '\\OC\\Memcache\\APCu',
'mail_from_address' => 'ddat+cloud',
'mail_smtpmode' => 'smtp',
'mail_sendmailmode' => 'smtp',
'mail_domain' => 'removed',
'mail_smtphost' => '127.0.0.1',
'mail_smtpport' => '25',
'maintenance' => false,
'default_locale' => 'en-GB',
'default_language' => 'en-GB',
'loglevel' => 2,
'updater.release.channel' => 'stable',
'theme' => '',
);
```
</details>
**Are you using external storage, if yes which one:** no
**Are you using encryption:** no
**Are you using an external user-backend, if yes which one:** no
### Client configuration
**Browser:** Chrome
**Operating system:** Windows 10
### Logs
Nothing significant
#### Web server error log
<details>
<summary>Web server error log</summary>
```
Nothing significant
```
</details>