Sudden DB load peak caused by (apparent) old files cleanup.
Open
Nobody has claimed this yet.
Type:Bug
- Dominant language
- PHP
- Stars
- 8.8k
- Forks
- 2.1k
- Avg merge
- 20h 7m
- Merged PRs (30d)
- 41
Description
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
- 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);
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
The report points to system cron maintenance and slow queries on oc_filecache, including the storage/name lookup; start by tracing the maintenance entry point that issues this query and reviewing the existing indexes. Reproduce with the stated large table sizes and MariaDB slow-query log, then verify the maintenance query avoids a full scan and completes without the reported load spike.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mariadb, php
- Domain
- database, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Needs clarification
- Newbie friendliness
- 38/100