X Tutup
Skip to content

fix: add index on oc_filecache over size, parent and storage to speed…#40633

Merged
DeepDiver1975 merged 2 commits intomasterfrom
fix/filecache-index-size
Feb 13, 2023
Merged

fix: add index on oc_filecache over size, parent and storage to speed…#40633
DeepDiver1975 merged 2 commits intomasterfrom
fix/filecache-index-size

Conversation

@DeepDiver1975
Copy link
Member

@DeepDiver1975 DeepDiver1975 commented Feb 10, 2023 •
edited
Loading

… up Cache::calculateFolderSize

Description

Folder sizes are computed quite often - especially on massive file upload.

Before

-> Aggregate: sum(oc_filecache.size), min(oc_filecache.size)  (cost=1.26 rows=1) (actual time=0.118..0.119 rows=1 loops=1)
    -> Filter: (oc_filecache.`storage` = 1)  (cost=1.25 rows=0.05) (actual time=0.109..0.112 rows=5 loops=1)
        -> Index lookup on oc_filecache using fs_parent_name_hash (parent=1), with index condition: (oc_filecache.parent = 1)  (cost=1.25 rows=5) (actual time=0.108..0.110 rows=5 loops=1)

After

-> Aggregate: sum(oc_filecache.size)  (cost=1.63 rows=1) (actual time=0.013..0.013 rows=1 loops=1)
    -> Filter: (oc_filecache.parent = 1)  (cost=1.13 rows=5) (actual time=0.008..0.011 rows=5 loops=1)
        -> Covering index lookup on oc_filecache using fs_parent_storage_size (parent=1, storage=1)  (cost=1.13 rows=5) (actual time=0.006..0.008 rows=5 loops=1)

Related Issue

How Has This Been Tested?

  • 🤖

Screenshots (if appropriate):

Types of changes

  • Bug fix (non-breaking change which fixes an issue)
  • New feature (non-breaking change which adds functionality)
  • Database schema changes (next release will require increase of minor version instead of patch)
  • Breaking change (fix or feature that would cause existing functionality to change)
  • Technical debt
  • Tests only (no source changes)

Checklist:

  • Code changes
  • Unit tests added
  • Acceptance tests added
  • Documentation ticket raised:
  • Changelog item, see TEMPLATE

@update-docs

This comment was marked as outdated.

Sign in to view
@DeepDiver1975 DeepDiver1975 force-pushed the fix/filecache-index-size branch 2 times, most recently from cf16337 to 9306b8e Compare February 10, 2023 08:04
IljaN
IljaN suggested changes Feb 10, 2023
View reviewed changes
core/Migrations/Version20230210073645.php Outdated
public function changeSchema(Schema $schema, array $options): void {
$prefix = $options['tablePrefix'];
$table = $schema->getTable("{$prefix}filecache");
$table->addIndex(['size', 'parent', 'storage']);
Copy link
Contributor

@IljaN IljaN Feb 10, 2023 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Some customers propose to change the order and put size last to optimize the index further

Copy link
Contributor

@IljaN IljaN Feb 10, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Also keep in mind that the index needs to be updated if size changes.

@DeepDiver1975 DeepDiver1975 force-pushed the fix/filecache-index-size branch from 9306b8e to 701777d Compare February 10, 2023 15:45
@ownclouders
Copy link
Contributor

ownclouders commented Feb 10, 2023 •
edited
Loading

💥 Acceptance tests pipeline apiProxySmoke-8-7-mariadb10.2-php7.4 failed. The build has been cancelled.

https://drone.owncloud.com/owncloud/core/37921/173

@ownclouders
Copy link
Contributor

ownclouders commented Feb 10, 2023

💥 Acceptance tests pipeline webUISharingExt1-git-chrome-mariadb10.2-php7.4 failed. The build has been cancelled.

https://drone.owncloud.com/owncloud/core/37920/152

@ownclouders
Copy link
Contributor

ownclouders commented Feb 10, 2023

💥 Acceptance tests pipeline webUISharingExt2-latest-chrome-mariadb10.2-php7.4 failed. The build has been cancelled.

https://drone.owncloud.com/owncloud/core/37920/156

@IljaN IljaN self-requested a review February 10, 2023 16:56
@sonarqubecloud
Copy link

sonarqubecloud bot commented Feb 13, 2023

Kudos, SonarCloud Quality Gate passed!    Quality Gate passed

Bug A 0 Bugs
Vulnerability A 0 Vulnerabilities
Security Hotspot A 0 Security Hotspots
Code Smell A 3 Code Smells

0.0% 0.0% Coverage
0.0% 0.0% Duplication

@DeepDiver1975 DeepDiver1975 merged commit 782235a into master Feb 13, 2023
@delete-merged-branch delete-merged-branch bot deleted the fix/filecache-index-size branch February 13, 2023 13:26
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

1 more reviewer

@IljaN IljaN IljaN approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

3 - To Review

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@DeepDiver1975 @ownclouders @IljaN
X Tutup