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

Use in_batches.destroy_all instead of destroy_all #2802

Merged
merged 1 commit into from
May 4, 2017
Merged

Use in_batches.destroy_all instead of destroy_all #2802

merged 1 commit into from
May 4, 2017

Conversation

alpaca-tc
Copy link
Contributor

What is different?
Example for suspending account with huge follows.

  • destroy_all
    • It locks all of associated records until complete deleting them. Therefore associated followers can not update Account of themselves for a while.
    • It loads all records at the first. It is very heavy.
  • in_batches.destroy_all
    • It locks and releases for each record while saving.
    • It works with a batch of records.

Before association.destroy_all

SELECT "follows".* FROM "follows" WHERE "follows"."account_id" = $1  [["account_id", 2]]

BEGIN
  SELECT  "notifications".* FROM "notifications" WHERE "notifications"."activity_id" = $1 AND "notifications"."activity_type" = $2 LIMIT $3  [["activity_id", 1], ["activity_type", "Follow"], ["LIMIT", 1]]
  DELETE FROM "follows" WHERE "follows"."id" = $1  [["id", 1]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  UPDATE "accounts" SET "following_count" = COALESCE("following_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 2]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
  UPDATE "accounts" SET "followers_count" = COALESCE("followers_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 3]]

  SELECT  "notifications".* FROM "notifications" WHERE "notifications"."activity_id" = $1 AND "notifications"."activity_type" = $2 LIMIT $3  [["activity_id", 2], ["activity_type", "Follow"], ["LIMIT", 1]]
  DELETE FROM "follows" WHERE "follows"."id" = $1  [["id", 2]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  UPDATE "accounts" SET "following_count" = COALESCE("following_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 2]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  UPDATE "accounts" SET "followers_count" = COALESCE("followers_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 4]]

  ...
COMMIT

After association.in_batches.destroy_all

SELECT  "follows"."id" FROM "follows" WHERE "follows"."account_id" = $1 ORDER BY "follows"."id" ASC LIMIT $2  [["account_id", 2], ["LIMIT", 1000]]
SELECT "follows".* FROM "follows" WHERE "follows"."account_id" = $1 AND "follows"."id" IN (1, 2, ...)  [["account_id", 2]]

BEGIN
  SELECT  "notifications".* FROM "notifications" WHERE "notifications"."activity_id" = $1 AND "notifications"."activity_type" = $2 LIMIT $3  [["activity_id", 1], ["activity_type", "Follow"], ["LIMIT", 1]]
  DELETE FROM "follows" WHERE "follows"."id" = $1  [["id", 1]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  UPDATE "accounts" SET "following_count" = COALESCE("following_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 2]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
  UPDATE "accounts" SET "followers_count" = COALESCE("followers_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 3]]
COMMIT

BEGIN
  SELECT  "notifications".* FROM "notifications" WHERE "notifications"."activity_id" = $1 AND "notifications"."activity_type" = $2 LIMIT $3  [["activity_id", 2], ["activity_type", "Follow"], ["LIMIT", 1]]
  DELETE FROM "follows" WHERE "follows"."id" = $1  [["id", 2]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  UPDATE "accounts" SET "following_count" = COALESCE("following_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 2]]
  SELECT  "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  UPDATE "accounts" SET "followers_count" = COALESCE("followers_count", 0) - 1 WHERE "accounts"."id" = $1  [["id", 4]]
COMMIT
...

@alpaca-tc alpaca-tc changed the title Use in_batches.destroy_all instead of destroy_all Use in_batches.destroy_all instead of destroy_all May 4, 2017
@Gargron Gargron merged commit 74c8ca6 into mastodon:master May 4, 2017
@alpaca-tc alpaca-tc deleted the destroy_all_in_batches branch May 4, 2017 22:04
alpaca-tc added a commit to pixiv/mastodon that referenced this pull request May 10, 2017
@alpaca-tc
Delete records in smaller transaction (mastodon#2802)
2f3de9d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@Gargron Gargron Gargron approved these changes

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
2 participants
@alpaca-tc @Gargron