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

stafula commented 6 years ago

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

OC 10.0.7.2
"oc_filecache" table with more than 1000000 lines for a specific "storage"
"oc_filecache" table with more than 37000 lines for a path like 'files_trashbin/%'
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

cpu-week

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 commented 6 years ago

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

ownclouders commented 6 years ago

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 commented 6 years ago

@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 commented 6 years ago

@DeepDiver1975 @butonic add index ?

gustavotrott commented 2 years ago

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 commented 2 years ago

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);

owncloud / core