Sudden DB load peak caused by (apparent) old files cleanup.

[stafula]

We (@fmkaiser) experienced a sudden DB load peak caused by (apparent) old files cleanup.
we suspect that the very slow queries described below are embedded in a cron task.

Steps to reproduce

1. OC 10.0.7.2
2. "oc_filecache" table with more than 1000000 lines for a specific "storage"
3. "oc_filecache" table with more than 37000 lines for a path like 'files_trashbin/%'
4. let system cron tasks run

Expected behaviour

• DB server resource consumption growth should be more proportional to the load
• Maintenance SQL queries should run in short times.

Actual behaviour

• Strong DB server resource consumption step

• SQL querys that last more than 30s

Server configuration

• Scientific Linux and Centos 7
• Nginx/1.12.2 Web server
• MariaDB galera cluster v 15.1 Distrib 10.0.35-MariaDB, for Linux (x86_64) using readline 5.1-PHP 7.1
• Updated ownCloud 10.0.7 (installed from the OC enterprise official release code)

Config.:

• Integrity test disabled.

occ config:list system

{
    "system": {
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "**REMOVED SENSITIVE VALUE***,
        "log_type": "syslog",
        "loglevel": "2",
        "version": "10.0.7.2",
        "installed": true,
        "ldapIgnoreNamingRules": false,
        "dbtype": "mysql",
        "dbname": "**REMOVED SENSITIVE VALUE***",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "**REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "theme": "**REMOVED SENSITIVE VALUE***",
        "maintenance": false,
        "instanceid": "525fae5733170",
        "overwriteprotocol": "https",
        "trusted_domains": [
            "**REMOVED SENSITIVE VALUE***",
            "**REMOVED SENSITIVE VALUE***",
            "**REMOVED SENSITIVE VALUE***",
            "**REMOVED SENSITIVE VALUE***"
        ],
        "trusted_proxies": [
            "1**REMOVED SENSITIVE VALUE***",
            "**REMOVED SENSITIVE VALUE***"
        ],
        "forwarded_for_headers": [
            "HTTP_X_FORWARDED",
            "HTTP_X_FORWARDED_FOR"
        ],
        "default_language": "en",
        "check_for_working_webdav": false,
        "check_for_working_htaccess": false,
        "updatechecker": false,
        "has_internet_connection": false,
        "appstoreenabled": false,
        "license-key": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "php",
        "memcached_servers": [
            [
                "**REMOVED SENSITIVE VALUE***",
                **REMOVED SENSITIVE VALUE***
            ]
        ],
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "**REMOVED SENSITIVE VALUE***",
            "port": **REMOVED SENSITIVE VALUE***,
            "dbindex": 1
        },
        "secret": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "https:\/\/**REMOVED SENSITIVE VALUE***",
        "knowledgebaseenabled": true,
        "asset-pipeline.enabled": true,
        "trashbin_retention_obligation": "auto",
        "integrity.check.disabled": true,
        "operation.mode": "clustered-instance",
        "htaccess.RewriteBase": "\/"
    }

List of activated apps:
/occ app:list

Enabled:
  - activity: 2.3.6
  - announcementcenter: 1.2.0
  - calendar: 1.5.7
  - comments: 0.3.0
  - configreport: 0.1.1
  - customgroups: 0.3.6
  - dav: 0.3.2
  - diagnostics: 0.1.2
  - enterprise_key: 0.1.4
  - external: 1.2
  - federatedfilesharing: 0.3.1
  - federation: 0.1.0
  - files: 1.5.1
  - files_external: 0.7.1
  - files_sharing: 0.10.1
  - files_texteditor: 2.2.1
  - files_trashbin: 0.9.1
  - files_versions: 1.3.0
  - files_videoplayer: 0.9.8
  - firstrunwizard: 1.1
  - gallery: 16.0.2
  - onlyoffice: 1.3.0
  - provisioning_api: 0.5.0
  - security: 0.0.2
  - storageusage: 2.0.0
  - systemtags: 0.3.0
  - systemtags_management: 0.2.0
  - templateeditor: 0.2
  - updatenotification: 0.2.1
  - user_ldap: 0.10.0
  - workflow: 0.2.6
Disabled:
  - admin_audit
  - encryption
  - files_antivirus
  - files_external_ftp
  - files_ldap_home
  - files_pdfviewer
  - firewall
  - guests
  - impersonate
  - market
  - notifications
  - oauth2
  - objectstore
  - password_policy
  - ransomware_protection
  - sharepoint
  - theme-example
  - twofactor_totp
  - user_external
  - user_shibboleth
  - windows_network_drive

External storage is a mapped GPFS volume

No encryption

Data Base slow query log example

[root@db1 data]# tail /data/db1-slow.log
# User@Host: owncloud[owncloud] @ ...
# Thread_id: 143532369  Schema: owncloud  QC_hit: No
# Query_time: 31.911738  Lock_time: 0.000091  Rows_sent: 0  Rows_examined: 1096699
SET timestamp=1527836336;
SELECT `fileid`, `storage`, `path`, `parent`, `name`,
                                `mimetype`, `mimepart`, `size`, `mtime`, `encrypted`,
                                `etag`, `permissions`, `checksum`
                        FROM `oc_filecache`
                        WHERE `storage` = '2200' AND `name` COLLATE utf8_general_ci LIKE '2017-01-13_10-09-39_641c52c7-d978-11e6-940d-28d24459a436.v%.d1484561759';

LDAP configuration

occ ldap:show-config

+-------------------------------+----------------------------------------------------+
| Configuration                 |                                                    |
+-------------------------------+----------------------------------------------------+
| hasMemberOfFilterSupport      | 0                                                  |
| hasPagedResultSupport         |                                                    |
| homeFolderNamingRule          |                                                    |
| lastJpegPhotoLookup           | 0                                                  |
| ldapAgentName                 | **REMOVED SENSITIVE VALUE*** |
| ldapAgentPassword             | ***                                                |
| ldapAttributesForGroupSearch  | cn                                                 |
| ldapAttributesForUserSearch   | sn;givenName;cn                                    |
| ldapBackupHost                |                                                    |
| ldapBackupPort                | 636                                                |
| ldapBase                      |**REMOVED SENSITIVE VALUE***          |
| ldapBaseGroups                | **REMOVED SENSITIVE VALUE*** |
| ldapBaseUsers                 | **REMOVED SENSITIVE VALUE*** |
| ldapCacheTTL                  | 600                                                |
| ldapConfigurationActive       | 1                                                  |
| ldapDynamicGroupMemberURL     |                                                    |
| ldapEmailAttribute            | mail                                               |
| ldapExperiencedAdmin          | 0                                                  |
| ldapExpertUUIDGroupAttr       |                                                    |
| ldapExpertUUIDUserAttr        | entryuuid                                          |
| ldapExpertUsernameAttr        | uid                                                |
| ldapGroupDisplayName          | cn                                                 |
| ldapGroupFilter               | (&(|(objectclass=groupOfUniqueNames)))             |
| ldapGroupFilterGroups         |                                                    |
| ldapGroupFilterMode           | 1                                                  |
| ldapGroupFilterObjectclass    | organizationalUnit                                 |
| ldapGroupMemberAssocAttr      | uniqueMember                                       |
| ldapHost                      | **REMOVED SENSITIVE VALUE***                    |
| ldapIgnoreNamingRules         |                                                    |
| ldapLoginFilter               | (&(objectclass=inetOrgPerson)(cn=%uid))            |
| ldapLoginFilterAttributes     |                                                    |
| ldapLoginFilterEmail          | 0                                                  |
| ldapLoginFilterMode           | 1                                                  |
| ldapLoginFilterUsername       | 1                                                  |
| ldapNestedGroups              | 0                                                  |
| ldapOverrideMainServer        | 0                                                  |
| ldapPagingSize                | 500                                                |
| ldapPort                      | 636                                                |
| ldapQuotaAttribute            | ownCloudQuota                                      |
| ldapQuotaDefault              |                                                    |
| ldapTLS                       |                                                    |
| ldapUserDisplayName           | displayname                                        |
| ldapUserDisplayName2          |                                                    |
| ldapUserFilter                | (|(objectclass=inetOrgPerson))                     |
| ldapUserFilterGroups          |                                                    |
| ldapUserFilterMode            | 1                                                  |
| ldapUserFilterObjectclass     | inetOrgPerson                                      |
| ldapUuidGroupAttribute        | auto                                               |
| ldapUuidUserAttribute         | auto                                               |
| turnOffCertCheck              | 0                                                  |
| useMemberOfToDetectMembership | 1                                                  |
+-------------------------------+----------------------------------------------------+

Short-term fix

After find a full table scan, we created a new index:

MariaDB [owncloud]> CREATE INDEX fs_storage_name ON oc_filecache (storage,name); 

[ownclouders]

GitMate.io thinks possibly related issues are #10588 (Share files/folder causes ), #25193 (Renaming old files funcionality), #10817 (Cannot share files with old users), #16567 (Renaming folder causes clients to loose files), and #27644 (Loading error of some files).

[ownclouders]

Hey, this issue has been closed because the label status/STALE is set and there were no updates for 7 days. Feel free to reopen this issue if you deem it appropriate.

(This is an automated comment from GitMate.io.)

[fmkaiser]

@PVince81 @DeepDiver1975 Does somebody at least want to comment on this?

The "LIKE" query on an unindexed column during the versions cleanup was definitely an unpleasant surprise for us. I imagine other big installations might run into the same problem...

[PVince81]

@DeepDiver1975 @butonic add index ?

[gustavotrott]

I am facing the same problem here!
The suggested index doesn't solve the problem because the index is ignored when using COLLATE utf8_general_ci.

I don't know what to do. I disabled OC Cron to avoid using 100% of CPU.
I am thinking to migrate db to Postgres.

[gustavotrott]

Yeah, I migrated to Postgres and things are much better now!
In Pgsql I am able to create index to ilike using trgm.

CREATE EXTENSION pg_trgm;
CREATE INDEX fs_name_trgm ON oc_filecache USING gin (name gin_trgm_ops);

[DeepDiver1975]

🤖 Automated issue analysis by Claude Code review agent.

Classification: confirmed-bug (still reproducible on current master)

I traced the slow query from the slow-query log in this issue through the current owncloud/core source. The unindexed name LIKE query, its cron trigger, and the missing index are all still present and unchanged.

Evidence — the exact slow query is still generated

The slow query reported here:

SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`,
       `size`, `mtime`, `encrypted`, `etag`, `permissions`, `checksum`
FROM `oc_filecache`
WHERE `storage` = '2200' AND `name` COLLATE utf8_general_ci LIKE '...v%.d1484561759';

still exists verbatim in lib/private/Files/Cache/Cache.php Cache::search():

public function search($pattern) {
    $pattern = $this->normalize($pattern);
    $sql = '
        SELECT `fileid`, `storage`, `path`, `parent`, `name`,
            `mimetype`, `mimepart`, `size`, `mtime`, `encrypted`,
            `etag`, `permissions`, `checksum`
        FROM `*PREFIX*filecache`
        WHERE `storage` = ? AND `name` ILIKE ?';
    ...
}

On MySQL/MariaDB the ILIKE operator is rewritten to the COLLATE ..._general_ci LIKE form seen in your slow log, by lib/private/DB/AdapterMySQL.php:

$statement = \str_replace(' ILIKE ', ' COLLATE ' . $characterSet . '_general_ci LIKE ', $statement);

This explains why the manually-added CREATE INDEX fs_storage_name ON oc_filecache (storage,name) did not help (as @gustavotrott reported): the COLLATE clause forces a different collation than the column/index, so MySQL cannot use the index and falls back to a full table scan.

Evidence — the cron path that triggers it (...v%.d<timestamp> pattern)

The exact name LIKE '<file>.v%.d<timestamp>' pattern comes from trashbin/version cleanup in apps/files_trashbin/lib/Trashbin.php getVersionsFromTrash():

$matches = $view->searchRaw($filename . '.v%.d' . $timestamp);

View::searchRaw() → searchCommon('search', ...) → Cache::search() (lib/private/Files/View.php).

This is reached from cleanup that runs as cron TimedJobs, matching the "embedded in a cron task" observation:

• apps/files_trashbin/lib/BackgroundJob/ExpireTrash.php — setInterval(60 * 10) (every 10 min) → expiry → Trashbin::delete() → deleteVersions() → getVersionsFromTrash().
• apps/files_versions/lib/BackgroundJob/ExpireVersions.php — setInterval(60 * 30) (every 30 min) → Storage::expireOlderThanMaxForUser().

Evidence — no covering index exists

db_structure.xml defines these indexes on *PREFIX*filecache:

• fs_storage_path_hash (storage, path_hash) — unique
• fs_parent_name_hash (parent, name)
• fs_storage_mimetype (storage, mimetype)
• fs_storage_mimepart (storage, mimepart)
• fs_storage_size (storage, size, fileid)

There is no (storage, name) index, and even if one were added it would be bypassed by the COLLATE rewrite described above. A repo-wide search for fs_storage_name returns nothing. The cleanup query therefore does a full scan of all rows for the storage (1,096,699 rows examined, 31.9s, in your log).

Proposed fix

Root cause: Cache::search() performs WHERE storage = ? AND name ILIKE ?. On MySQL the ILIKE→COLLATE ..._general_ci LIKE rewrite in AdapterMySQL.php makes any (storage, name) index unusable, so version/trash cleanup (which searches for <file>.v%.d<ts> via Trashbin::getVersionsFromTrash()) full-scans oc_filecache per file, per cron run. Large oc_filecache (>1M rows for a storage) + many trash/version entries = the DB load spike.

Suggested approach (pick based on maintenance status of core):

1. Avoid the unindexed LIKE in the cleanup hot path. The trash-version lookup only needs entries under a known directory; query by parent (which has the fs_parent_name_hash (parent, name) index) or by a storage + path_hash/path LIKE 'dir/%' prefix and filter the .v%.d<ts> suffix in PHP, instead of name ILIKE '...v%.d<ts>'. A leading-anchored path LIKE 'prefix/%' can use the index; the current trailing-wildcard name LIKE '...%...' cannot.
2. If Cache::search() itself must stay LIKE-based, make the comparison index-compatible on MySQL by removing the per-query COLLATE override and instead matching the column's own collation, then add an index on (storage, name); for case-insensitive matching, store/compare a normalized lowercased name column. (As noted, PostgreSQL users already solved this with a pg_trgm GIN index — core could document/ship an equivalent for MySQL or generate trigram-friendly SQL.)

Files to change:

• apps/files_trashbin/lib/Trashbin.php (getVersionsFromTrash) — primary cleanup hot path.
• apps/files_versions/lib/Storage.php — version expiry path.
• lib/private/Files/Cache/Cache.php (search) and lib/private/DB/AdapterMySQL.php — if fixing the generic ILIKE index incompatibility.
• db_structure.xml — if adding a supporting index.

Test approach:

• Unit/integration: seed oc_filecache with a storage containing many files_trashbin/versions/...v%.d<ts> rows; assert getVersionsFromTrash() returns the correct versions.
• Performance/regression: capture the generated SQL and run EXPLAIN on MySQL/MariaDB asserting the cleanup query is index-backed (no full table scan / type != ALL) rather than relying on the COLLATE-defeated index.
• Cross-DB: verify behavior on MySQL/MariaDB, PostgreSQL, SQLite, and Oracle (note the existing ILIKE adapter rewrites in AdapterOCI8.php / AdapterSqlite.php).

Note: owncloud/core is in maintenance mode (succeeded by Infinite Scale / OCIS), so a fix here would target installations still on classic ownCloud 10.x.

[DeepDiver1975]

🤖 Follow-up: I've opened a fix for this — #41641.

It takes approach #1 from the analysis above: the trashbin version-restore path no longer runs the non-sargable name ILIKE '<file>.v%.d<ts>' whole-cache search. Instead it lists the single known versions directory via the index-backed (parent, name) lookup (View::getDirectoryContent()) and filters the .v<version>[.d<timestamp>] suffix in PHP. No schema change and no change to the generic Cache::search() / AdapterMySQL ILIKE behavior, so the blast radius stays contained to the cleanup hot path that this issue's slow-query log pointed at.

@PVince81 — would appreciate your review on the approach, given this touches the trashbin/versions restore path.