Unused nextcloud installation hitting database with 10,000 queries per second!

[nikkilocke]

Steps to reproduce

1. Show Files page for a user who is in 100's of group folders
2. Monitor the queries to the database server

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:

App list

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 ```

Nextcloud configuration:

Config report

<?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' => '',
);

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

Web server error log

Nothing significant

[patricksebastien]

I am using around 50 groupfolders (with advanced permission), monitoring the cpu of my server - I see that mysql (still using 5.7) is always at 100% cpu... it might be related to ^

[nikkilocke]

It might. What version are you on? V20 is far better than v19 - there is an optimisation that helps a bit. On 27 May 2021 01:18, patricksebastien ***@***.***> wrote: I am using around 50 groupfolders (with advanced permission), monitoring the cpu of my server - I see that mysql (still using 5.7) is always at 100% cpu... it might be related to ^ —You are receiving this because you authored the thread.Reply to this email directly, view it on GitHub, or unsubscribe.

[patricksebastien]

Always on stable (V20 for now yes). Last version of groupfolder too. Mysql is often at 100% CPU.

[patricksebastien]

Switched to MariaDB 10.5.9 on a dedicated RDS instance. I am still having the issue (using now NC 21 and GF 9.0.3). From what I see / understand, this query is eating all my database CPU:

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` = '...' ) ) ) 

innodb rows read.avg = +-350k

I have 1,105,155 entries in oc_filecache & 4,521 in oc_group_folders_acl. One groupfolder have around 25k .png files in it.

I am trying to help here, but I see this index on oc_filecache:

Here's the index on oc_group_folders_acl:

Are we missing something (a index on parent only for where)?

Now I am getting this error when I try to groupfolder:scan

An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure  
  : 1213 Deadlock found when trying to get lock; try restarting transaction

@nikkilocke do you still have the issue?

[PVince81]

please try again with more recent versions, we added many performance optimizations

I can't quickly find the PRs in question, they might have been in the server.

@CarlSchwan do you remember ?

[nikkilocke]

I am now on 23.0.7. This is much worse than the previous version we were on.
I have given the Mariadb database 1GB of cache (which helps a little - files page display time reduced from 15 secs to 8).

I logged the queries again - 57,000 total queries loading the path from the file id (in `lib/private/Files/Cache/Cache.php getPathById). Many of these queries repeated over 100 times each.

The issue is down to lib/private/Files/View.php function getPath. It starts knowing only the id of the file, possibly the storageId, and the list of mount points for the user. It looks in all the mount points (just the ones using the same storageId if it knows that), and calls getPathById on each one.

All of the mounts here will be group folders or shares. GetPathById for these calls Cache.GetPathById, then checks the resulting path to see if it is in the appropriate folder. So the database is queried for every mount, until it finds one where the path matches.

Clearly this is very non-optimal, as a user may have hundreds of mounts (one for each group folder, plus one for each share).

The code needs refactoring so that either:

• View.GetPath knows which mount to query, or
• It queries the database once to get the path, then asks each mount if the path belongs to it

Unfortunately, I am not familiar enough with the code to do anything about it. What I have discovered is that most of the calls to View.GetPath are from SharedStorage.init. But it is not clear from my viewing of the code whether SharedStorage knows (or can find out) what mount point it is mounted on. If it did, it could pass that to View.GetPath, and the database would only have to be queried once.

If anyone can give advice on this, or a hint as to how to solve the problem, I am happy to do some coding and put in a merge request.

[patricksebastien]

Could you try with Nextcloud 24? Marketed at 4x faster (db), maybe they worked on it!?!

[PVince81]

@nikkilocke in Nextcloud 24 some of the mount point code in the server has been reworked to make it cacheable, so that it only resolves the mount that is needed, not everything, for most code paths. I'm not 100% sure if getPathById() was affected as well.
Maybe @icewind1991 can fill in on the details

[PVince81]

ref: nextcloud/server#31265
this is not the only change, there was a series of additions and also some apps needed to be adjusted

[nikkilocke]

I have been laboriously debugging this. I discovered that calls to SharedStorage::getWatcher (in apps/files_sharing/lib) are taking about 4 seconds in my installation. The code is:

	public function getWatcher($path = '', $storage = null): Watcher {
		$mountManager = \OC::$server->getMountManager();

		// Get node informations
		$node = $this->getShare()->getNodeCacheEntry();
		if ($node) {
			$mount = $mountManager->findByNumericId($node->getStorageId());
			// If the share is originating from an external storage
			if (count($mount) > 0 && $mount[0] instanceof ExternalMountPoint) {
				// Propagate original storage scan
				return parent::getWatcher($path, $storage);
			}
		}

		// cache updating is handled by the share source
		return new NullWatcher();
	}

We do not use external storage.

Is there a reason why this method ignores the $storage passed in to it, and instead goes the long way round to obtain the storage id?

Is there somewhere else useful I could ask this question?