Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Database based file locking creates many DELETE statements #22819

Closed
MorrisJobke opened this issue Mar 3, 2016 · 9 comments · Fixed by #22865
Closed

Database based file locking creates many DELETE statements #22819

MorrisJobke opened this issue Mar 3, 2016 · 9 comments · Fixed by #22865
Labels
blue-ticket feature:locking performance sev2-high
Milestone
9.0

Comments

@MorrisJobke
Copy link
Contributor

We noticed this.

| 18612 | USER   | owncloud07f:38769 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18616 | USER   | owncloud08f:50991 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18619 | USER   | owncloud06f:41700 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18622 | USER   | owncloud13f:47114 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18624 | USER   | owncloud04f:48897 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18632 | USER   | owncloud10f:48552 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18636 | USER   | owncloud06f:41709 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18642 | USER   | owncloud09f:51387 | USER   | Query   | 2774 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002443' |
| 18655 | USER   | owncloud04f:48906 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18659 | USER   | owncloud06f:41714 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18661 | USER   | owncloud07f:38782 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18664 | USER   | owncloud08f:51000 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18669 | USER   | owncloud07f:38787 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18676 | USER   | owncloud15f:56114 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18677 | USER   | owncloud02f:40891 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18678 | USER   | owncloud15f:56117 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18684 | USER   | owncloud14f:58473 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18687 | USER   | owncloud06f:41726 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18688 | USER   | owncloud09f:51394 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18695 | USER   | owncloud11f:41819 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18696 | USER   | owncloud15f:56126 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18701 | USER   | owncloud09f:51400 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18713 | USER   | owncloud13f:47147 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18717 | USER   | owncloud10f:48569 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18719 | USER   | owncloud12f:45251 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18733 | USER   | owncloud15f:56138 | USER   | Query   | 2773 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002444' |
| 18750 | USER   | owncloud12f:45263 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18751 | USER   | owncloud13f:47156 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18754 | USER   | owncloud07f:38805 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18757 | USER   | owncloud05f:42273 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18766 | USER   | owncloud10f:48582 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18767 | USER   | owncloud04f:48942 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18775 | USER   | owncloud13f:47162 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18787 | USER   | owncloud11f:41837 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18796 | USER   | owncloud09f:51416 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18797 | USER   | owncloud11f:41840 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18804 | USER   | owncloud06f:41741 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18806 | USER   | owncloud11f:41846 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18810 | USER   | owncloud04f:48961 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18813 | USER   | owncloud04f:48966 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18846 | USER   | owncloud10f:48603 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18848 | USER   | owncloud05f:42307 | USER   | Query   | 2772 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002445' |
| 18861 | USER   | owncloud11f:41855 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18863 | USER   | owncloud11f:41861 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18865 | USER   | owncloud05f:42310 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18872 | USER   | owncloud13f:47179 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18873 | USER   | owncloud04f:48987 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18874 | USER   | owncloud04f:48990 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18877 | USER   | owncloud10f:48618 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18884 | USER   | owncloud15f:56153 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18886 | USER   | owncloud10f:48621 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18894 | USER   | owncloud05f:42318 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18904 | USER   | owncloud15f:56159 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18911 | USER   | owncloud14f:58509 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18924 | USER   | owncloud13f:47194 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18925 | USER   | owncloud04f:49005 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18927 | USER   | owncloud12f:45305 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18944 | USER   | owncloud10f:48634 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18946 | USER   | owncloud04f:49014 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18955 | USER   | owncloud07f:38841 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18960 | USER   | owncloud12f:45308 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 18967 | USER   | owncloud02f:40950 | USER   | Query   | 2771 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002446' |
| 18972 | USER   | owncloud06f:41784 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 18978 | USER   | owncloud03f:37239 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 18984 | USER   | owncloud15f:56175 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 18988 | USER   | owncloud06f:41786 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 18997 | USER   | owncloud05f:42337 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 19008 | USER   | owncloud11f:41885 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 19013 | USER   | owncloud02f:40964 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 19024 | USER   | owncloud09f:51463 | USER   | Query   | 2769 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002448' |
| 19027 | USER   | owncloud02f:40976 | USER   | Query   | 2770 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002447' |
| 19228 | USER   | owncloud03f:37322 | USER   | Query   | 2744 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002473' |
| 19232 | USER   | owncloud11f:42050 | USER   | Query   | 2744 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002473' |
| 19285 | USER   | owncloud11f:42057 | USER   | Query   | 2740 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002477' |
| 19286 | USER   | owncloud13f:47304 | USER   | Query   | 2740 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002477' |
| 19288 | USER   | owncloud15f:56319 | USER   | Query   | 2740 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002477' |
| 19296 | USER   | owncloud11f:42062 | USER   | Query   | 2740 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002477' |
| 19298 | USER   | owncloud13f:47310 | USER   | Query   | 2740 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002477' |
| 19302 | USER   | owncloud05f:42451 | USER   | Query   | 2740 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002477' |
| 19305 | USER   | owncloud13f:47313 | USER   | Query   | 2740 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002477' |
| 19331 | USER   | owncloud02f:41148 | USER   | Query   | 2714 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002503' |
| 19333 | USER   | owncloud15f:56340 | USER   | Query   | 2714 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002503' |
| 19334 | USER   | owncloud11f:42074 | USER   | Query   | 2714 | updating                     | DELETE FROM `oc_file_locks` WHERE `ttl` < '1457002503'

@icewind1991 @PVince81 Could this be caused by

core/lib/private/lock/dblockingprovider.php

Line 265 in e191953

$this->cleanExpiredLocks();

I guess we need to move to a background job?
@MorrisJobke MorrisJobke added this to the 9.1-next milestone Mar 3, 2016
@PVince81
Copy link
Contributor

PVince81 commented Mar 3, 2016

I thought we originally already said this has to be done in a background job ? @DeepDiver1975 @icewind1991

@MorrisJobke
Copy link
Contributor Author

It's a 8.2.2 instance.

@MorrisJobke
Copy link
Contributor Author

This killed a bigger instance -> raising sev

@MorrisJobke
Copy link
Contributor Author

Workaround:

Commenting

core/lib/private/lock/dblockingprovider.php

Line 265 in e191953

$this->cleanExpiredLocks();
out and then let this run in a cronjob:

mysql DBNAME --execute "DELETE FROM oc_file_locks WHERE ttl < $(date +%s);"

@MorrisJobke MorrisJobke self-assigned this Mar 3, 2016
@PVince81 PVince81 modified the milestones: 9.0.1-next-maintenance, 9.1-next Mar 3, 2016
@PVince81
Copy link
Contributor

PVince81 commented Mar 3, 2016

Moving to 9.0.1 then, doesn't look good

@rullzer
Copy link
Contributor

rullzer commented Mar 4, 2016

I agree that it should be fixed... but why is a larger instance using db locking?

@MorrisJobke
Copy link
Contributor Author

I agree that it should be fixed... but why is a larger instance using db locking?

Why not - only drawback should be "it is slower" but not "it kills the whole DB cluster". The DB is flooded with those statements.

@MorrisJobke MorrisJobke mentioned this issue Mar 4, 2016
Merged
@MorrisJobke
Copy link
Contributor Author

Fix is here: #22865

MorrisJobke added a commit that referenced this issue Mar 4, 2016
@MorrisJobke
Run cleanup of expired DB file locks to background job
138219d 
* fixes #22819

The old way fired a DELETE statement on each destruction of the
DBLockingProvider. Which could cause a lot of queries. It's enough
to run this every 5 minutes in a background job, which in the end
could result in file locks that exists 5 minutes longer - in the
worst case and for not properly released locks.

This makes the DB based locking a lot more performant and could
result in a similar performance to the Redis based locking provider.
MorrisJobke added a commit that referenced this issue Mar 4, 2016
@MorrisJobke
Run cleanup of expired DB file locks to background job
8a5dd9e 
* fixes #22819

The old way fired a DELETE statement on each destruction of the
DBLockingProvider. Which could cause a lot of queries. It's enough
to run this every 5 minutes in a background job, which in the end
could result in file locks that exists 5 minutes longer - in the
worst case and for not properly released locks.

This makes the DB based locking a lot more performant and could
result in a similar performance to the Redis based locking provider.
@MorrisJobke MorrisJobke mentioned this issue Mar 4, 2016
Merged
MorrisJobke added a commit that referenced this issue Mar 4, 2016
@MorrisJobke
Run cleanup of expired DB file locks to background job
12e006c 
* fixes #22819

The old way fired a DELETE statement on each destruction of the
DBLockingProvider. Which could cause a lot of queries. It's enough
to run this every 5 minutes in a background job, which in the end
could result in file locks that exists 5 minutes longer - in the
worst case and for not properly released locks.

This makes the DB based locking a lot more performant and could
result in a similar performance to the Redis based locking provider.
@MorrisJobke MorrisJobke mentioned this issue Mar 4, 2016
Merged
MorrisJobke added a commit that referenced this issue Mar 4, 2016
@MorrisJobke
Run cleanup of expired DB file locks to background job
9737290 
* fixes #22819

The old way fired a DELETE statement on each destruction of the
DBLockingProvider. Which could cause a lot of queries. It's enough
to run this every 5 minutes in a background job, which in the end
could result in file locks that exists 5 minutes longer - in the
worst case and for not properly released locks.

This makes the DB based locking a lot more performant and could
result in a similar performance to the Redis based locking provider.
@ghost ghost closed this as completed in #22865 Mar 4, 2016
@MorrisJobke MorrisJobke modified the milestones: 9.0-current, 9.0.1-next-maintenance Mar 4, 2016
@MorrisJobke MorrisJobke removed their assignment Mar 4, 2016
@lock
Copy link

lock bot commented Aug 5, 2019

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

@lock lock bot locked as resolved and limited conversation to collaborators Aug 5, 2019
This issue was closed.
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
blue-ticket feature:locking performance sev2-high
Projects
None yet
Milestone
9.0
Development

Successfully merging a pull request may close this issue.

Run cleanup of expired DB file locks to background job owncloud/core
4 participants
@rullzer @MorrisJobke @PVince81 @DeepDiver1975