From patchwork Fri Jul 16 17:19:39 2021 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Daniel Axtens X-Patchwork-Id: 1506264 Return-Path: X-Original-To: incoming@patchwork.ozlabs.org Delivered-To: patchwork-incoming@bilbo.ozlabs.org Authentication-Results: ozlabs.org; spf=pass (sender SPF authorized) smtp.mailfrom=lists.ozlabs.org (client-ip=2404:9400:2:0:216:3eff:fee1:b9f1; helo=lists.ozlabs.org; envelope-from=patchwork-bounces+incoming=patchwork.ozlabs.org@lists.ozlabs.org; receiver=) Authentication-Results: ozlabs.org; dkim=fail reason="signature verification failed" (1024-bit key; unprotected) header.d=axtens.net header.i=@axtens.net header.a=rsa-sha256 header.s=google header.b=BBgjl/hu; dkim-atps=neutral Received: from lists.ozlabs.org (lists.ozlabs.org [IPv6:2404:9400:2:0:216:3eff:fee1:b9f1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (4096 bits)) (No client certificate requested) by ozlabs.org (Postfix) with ESMTPS id 4GRJ0c4q5Xz9sS8 for ; Sat, 17 Jul 2021 03:20:32 +1000 (AEST) Received: from boromir.ozlabs.org (localhost [IPv6:::1]) by lists.ozlabs.org (Postfix) with ESMTP id 4GRJ0c0s9vz3bXT for ; Sat, 17 Jul 2021 03:20:32 +1000 (AEST) Authentication-Results: lists.ozlabs.org; dkim=fail reason="signature verification failed" (1024-bit key; unprotected) header.d=axtens.net header.i=@axtens.net header.a=rsa-sha256 header.s=google header.b=BBgjl/hu; dkim-atps=neutral X-Original-To: patchwork@lists.ozlabs.org Delivered-To: patchwork@lists.ozlabs.org Authentication-Results: lists.ozlabs.org; spf=pass (sender SPF authorized) smtp.mailfrom=axtens.net (client-ip=2607:f8b0:4864:20::1030; helo=mail-pj1-x1030.google.com; envelope-from=dja@axtens.net; receiver=) Authentication-Results: lists.ozlabs.org; dkim=pass (1024-bit key; unprotected) header.d=axtens.net header.i=@axtens.net header.a=rsa-sha256 header.s=google header.b=BBgjl/hu; dkim-atps=neutral Received: from mail-pj1-x1030.google.com (mail-pj1-x1030.google.com [IPv6:2607:f8b0:4864:20::1030]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by lists.ozlabs.org (Postfix) with ESMTPS id 4GRHzy5YLhz3bWf for ; Sat, 17 Jul 2021 03:19:58 +1000 (AEST) Received: by mail-pj1-x1030.google.com with SMTP id p9so6818248pjl.3 for ; Fri, 16 Jul 2021 10:19:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=axtens.net; s=google; h=from:to:cc:subject:date:message-id:in-reply-to:references :mime-version:content-transfer-encoding; bh=8eAMqfnW8YLyqgbK+NIYGr6TrDb+zeYBpFLoAx+aC34=; b=BBgjl/hu66BC5o64Q4f01A8NHAU2nDdRVHXwEQmjVw1Ksp3GdK7o6JM9lytCrgS+ao wSrWx/0uWWH8w3kAqi+yE1rkgFu8eEJ3JhgxyI4iTwSz5Pa3zXmIbhEpl1Pv/8Pnjh9x 7wmr5FbYPF4tfutRmfaaQVZyTe5glmBAJzfEw= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:subject:date:message-id:in-reply-to :references:mime-version:content-transfer-encoding; bh=8eAMqfnW8YLyqgbK+NIYGr6TrDb+zeYBpFLoAx+aC34=; b=FNpoSrgA3OwtVv73uDYToBuIGsoLUzGVYmSKC9y6cjY9UJKggp/3ky/t8rP2Z71l5v HHQcGxjf8QIHPda10RadUsX25JCf5bCUF4I/A+hwdFy14OUID7zTpGll7Bhn7nxOgU4/ plIqhltDvWjX0yLVkvFVc7I17jDhjIHdjXSBrJyEvncxXx7uDGUM6BfSfK9EDgpUtWqL WTlLdo4bUPiIiM6m2b6Vf99teburBbTGDY5n3wuBcJXcQGIOArJQwb7owxrCbfUskk5x DPKgeIHGgUtSOW+ub9gC3yyQ+qFTR/OFI7/jD8tBoQKNZgEaxtUPo18NUmilB57/TTyL bHsA== X-Gm-Message-State: AOAM53220Wa4WeVVfDBg4ECvq1jZSI60nEvwuMQD0XfRqPpI0fFD3jel 2SI7rz0VB3SEt2n9xcRS2Mr3iO0eK6I9+g== X-Google-Smtp-Source: ABdhPJzCEJSjX3MskLAg0K6n65zl+DkkWDJMp8JGCPcY4U85wYUysSnt4PWYm10VExJiBS46echF2A== X-Received: by 2002:a17:90b:3a85:: with SMTP id om5mr6930700pjb.229.1626455996275; Fri, 16 Jul 2021 10:19:56 -0700 (PDT) Received: from localhost ([203.206.29.204]) by smtp.gmail.com with ESMTPSA id u24sm11189877pfm.200.2021.07.16.10.19.55 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 16 Jul 2021 10:19:55 -0700 (PDT) From: Daniel Axtens To: patchwork@lists.ozlabs.org Subject: [PATCH 3/4] migrations: 0043: split the data migration into chunks Date: Sat, 17 Jul 2021 03:19:39 +1000 Message-Id: <20210716171940.3827847-4-dja@axtens.net> X-Mailer: git-send-email 2.30.2 In-Reply-To: <20210716171940.3827847-1-dja@axtens.net> References: <20210716171940.3827847-1-dja@axtens.net> MIME-Version: 1.0 X-BeenThere: patchwork@lists.ozlabs.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: Patchwork development List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: jk@ozlabs.org Errors-To: patchwork-bounces+incoming=patchwork.ozlabs.org@lists.ozlabs.org Sender: "Patchwork" Migrate 10000 rows at a time. This: - provides a view on progress - means replication happens in manageable chunks - hopefully prevents db lockups Signed-off-by: Daniel Axtens --- .../migrations/0043_merge_patch_submission.py | 146 ++++++++++-------- 1 file changed, 82 insertions(+), 64 deletions(-) diff --git a/patchwork/migrations/0043_merge_patch_submission.py b/patchwork/migrations/0043_merge_patch_submission.py index 465e527812ba..1d072ca18230 100644 --- a/patchwork/migrations/0043_merge_patch_submission.py +++ b/patchwork/migrations/0043_merge_patch_submission.py @@ -1,76 +1,94 @@ from django.conf import settings from django.db import connection, migrations, models import django.db.models.deletion +from django.db.models import Max import patchwork.fields +CHUNK_SIZE = 10000 def migrate_data(apps, schema_editor): - if connection.vendor == 'postgresql': - schema_editor.execute( - """ - UPDATE patchwork_submission - SET archived = patchwork_patch.archived, - commit_ref = patchwork_patch.commit_ref, - delegate_id = patchwork_patch.delegate_id, - diff = patchwork_patch.diff, - hash = patchwork_patch.hash, - number = patchwork_patch.number, - pull_url = patchwork_patch.pull_url, - related_id = patchwork_patch.related_id, - series_id = patchwork_patch.series_id, - state_id = patchwork_patch.state_id - FROM patchwork_patch - WHERE patchwork_submission.id = patchwork_patch.submission_ptr_id - """ - ) - elif connection.vendor == 'mysql': - schema_editor.execute( - """ - UPDATE patchwork_submission, patchwork_patch - SET patchwork_submission.archived = patchwork_patch.archived, - patchwork_submission.commit_ref = patchwork_patch.commit_ref, - patchwork_submission.delegate_id = patchwork_patch.delegate_id, - patchwork_submission.diff = patchwork_patch.diff, - patchwork_submission.hash = patchwork_patch.hash, - patchwork_submission.number = patchwork_patch.number, - patchwork_submission.pull_url = patchwork_patch.pull_url, - patchwork_submission.related_id = patchwork_patch.related_id, - patchwork_submission.series_id = patchwork_patch.series_id, - patchwork_submission.state_id = patchwork_patch.state_id - WHERE patchwork_submission.id = patchwork_patch.submission_ptr_id - """ # noqa - ) - else: - schema_editor.execute( - """ - UPDATE patchwork_submission - SET ( - archived, commit_ref, delegate_id, diff, hash, number, - pull_url, related_id, series_id, state_id - ) = ( - SELECT - patchwork_patch.archived, - patchwork_patch.commit_ref, - patchwork_patch.delegate_id, - patchwork_patch.diff, - patchwork_patch.hash, - patchwork_patch.number, - patchwork_patch.pull_url, - patchwork_patch.related_id, - patchwork_patch.series_id, - patchwork_patch.state_id + Patch = apps.get_model('patchwork', 'patch') + max_id = Patch.objects.all().aggregate(Max('submission_ptr_id'))['submission_ptr_id__max'] + old_max = 0 + # process CHUNK_SIZE at a time. +CHUNK_SIZE+1 is to ensure that if we get + # max_id = 50000, we do a migration of ids >= 50000, < 60000 - otherwise + # we would miss id=50000 + print("\nMigrating data - %d rows" % max_id) + for new_max in range(CHUNK_SIZE, max_id+CHUNK_SIZE+1, CHUNK_SIZE): + print("Migrating rows >= %d, < %d (max %d)" % (old_max, new_max, max_id)) + if connection.vendor == 'postgresql': + schema_editor.execute( + """ + UPDATE patchwork_submission + SET archived = patchwork_patch.archived, + commit_ref = patchwork_patch.commit_ref, + delegate_id = patchwork_patch.delegate_id, + diff = patchwork_patch.diff, + hash = patchwork_patch.hash, + number = patchwork_patch.number, + pull_url = patchwork_patch.pull_url, + related_id = patchwork_patch.related_id, + series_id = patchwork_patch.series_id, + state_id = patchwork_patch.state_id FROM patchwork_patch - WHERE patchwork_patch.submission_ptr_id = patchwork_submission.id - ) - WHERE - EXISTS ( - SELECT * - FROM patchwork_patch - WHERE patchwork_patch.submission_ptr_id = patchwork_submission.id - ) - """ # noqa - ) + WHERE patchwork_patch.submission_ptr_id >= %d + AND patchwork_patch.submission_ptr_id < %d + AND patchwork_submission.id = patchwork_patch.submission_ptr_id + """ % (old_max, new_max) + ) + elif connection.vendor == 'mysql': + schema_editor.execute( + """ + UPDATE patchwork_submission, patchwork_patch + SET patchwork_submission.archived = patchwork_patch.archived, + patchwork_submission.commit_ref = patchwork_patch.commit_ref, + patchwork_submission.delegate_id = patchwork_patch.delegate_id, + patchwork_submission.diff = patchwork_patch.diff, + patchwork_submission.hash = patchwork_patch.hash, + patchwork_submission.number = patchwork_patch.number, + patchwork_submission.pull_url = patchwork_patch.pull_url, + patchwork_submission.related_id = patchwork_patch.related_id, + patchwork_submission.series_id = patchwork_patch.series_id, + patchwork_submission.state_id = patchwork_patch.state_id + WHERE patchwork_patch.submission_ptr_id >= %d + AND patchwork_patch.submission_ptr_id < %d + AND patchwork_submission.id = patchwork_patch.submission_ptr_id + """ % (old_max, new_max) # noqa + ) + else: + schema_editor.execute( + """ + UPDATE patchwork_submission + SET ( + archived, commit_ref, delegate_id, diff, hash, number, + pull_url, related_id, series_id, state_id + ) = ( + SELECT + patchwork_patch.archived, + patchwork_patch.commit_ref, + patchwork_patch.delegate_id, + patchwork_patch.diff, + patchwork_patch.hash, + patchwork_patch.number, + patchwork_patch.pull_url, + patchwork_patch.related_id, + patchwork_patch.series_id, + patchwork_patch.state_id + FROM patchwork_patch + WHERE patchwork_patch.submission_ptr_id = patchwork_submission.id + ) + WHERE + EXISTS ( + SELECT * + FROM patchwork_patch + WHERE patchwork_patch.submission_ptr_id >= %d + AND patchwork_patch.submission_ptr_id < %d + AND patchwork_patch.submission_ptr_id = patchwork_submission.id + ) + """ % (old_max, new_max) # noqa + ) + old_max = new_max class Migration(migrations.Migration):