From patchwork Tue Mar 17 13:59:14 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Daniel Axtens X-Patchwork-Id: 1256495 Return-Path: X-Original-To: incoming@patchwork.ozlabs.org Delivered-To: patchwork-incoming@bilbo.ozlabs.org Received: from lists.ozlabs.org (lists.ozlabs.org [203.11.71.2]) (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 48hblx6HLwz9sSY for ; Wed, 18 Mar 2020 01:54:05 +1100 (AEDT) Authentication-Results: ozlabs.org; dmarc=none (p=none dis=none) header.from=axtens.net 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=P5jkOwfB; dkim-atps=neutral Received: from lists.ozlabs.org (lists.ozlabs.org [IPv6:2401:3900:2:1::3]) by lists.ozlabs.org (Postfix) with ESMTP id 48hblx3rNDzDqcm for ; Wed, 18 Mar 2020 01:54:05 +1100 (AEDT) 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::1042; helo=mail-pj1-x1042.google.com; envelope-from=dja@axtens.net; receiver=) Authentication-Results: lists.ozlabs.org; dmarc=none (p=none dis=none) header.from=axtens.net 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=P5jkOwfB; dkim-atps=neutral Received: from mail-pj1-x1042.google.com (mail-pj1-x1042.google.com [IPv6:2607:f8b0:4864:20::1042]) (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 48hZY15C3BzDqNL for ; Wed, 18 Mar 2020 00:59:33 +1100 (AEDT) Received: by mail-pj1-x1042.google.com with SMTP id mj6so10030425pjb.5 for ; Tue, 17 Mar 2020 06:59:33 -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=jk93mgBBQ3N41PsDYd+EChXZD9kB+t0iPy8KO8OiZ/8=; b=P5jkOwfBRotfxGNLz0FkGSkwpx5i23jcVf10hobYgpSEylPAJbFPbaxwHhxPzhlKYa fGC/xv89aBWERSaCK6IyMqdjmiW5yigLiAOMQyv16/TymT/ztnYZF2Xr9xV8IWWCl5ZZ Zdq5KoEx/CANxCdDDMEDjABE9+tchEpWNZMm4= 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=jk93mgBBQ3N41PsDYd+EChXZD9kB+t0iPy8KO8OiZ/8=; b=Ou9jwhXgagcU6e0kA5wivsS8u0wUAk53Sdn/LWvrj0wVwdkD4dXvnpZVldyIJQMYBs TCS9/1JR+p+m5SWdnTpINnol7jeEyzPDzCfnDqMI/K6/w2DFVM496UiFGfSPHArmUI0n 1VdRr4nZfunUGLjobYasCGdZPzA551MLQMHuxNrSgCxpzdn9FaGcPdNJlO/SN2vte9Ps JWTcJEXze/D6UBfCxaDr0fDjwQFDXnot+4u7jEj0PNX6VY5otWIaoMcEFXmNA3bIOGVP 07fNv1mWMkadte/q+kNW1UJlt+R+oe9aYG+1V4fjIaIhWipSeNCDTzeWYhU76VA1bxtt rKVg== X-Gm-Message-State: ANhLgQ21WqkIzhhfX8p2HaCk5ukXIzod8g6OT7EuR8lGD+YsBHqyI8vW ECjkKbBhp45YoETrNsJm2LKc0LJlHNw= X-Google-Smtp-Source: ADFU+vvp03k2GOllUk8+oC1BGYfXVMlPQIgknxRT3KcDacvuOVeoWf+m3SHa6cvYQ2qxdGJ31oGj2w== X-Received: by 2002:a17:902:be17:: with SMTP id r23mr4342094pls.174.1584453570765; Tue, 17 Mar 2020 06:59:30 -0700 (PDT) Received: from localhost (2001-44b8-111e-5c00-11e1-e7cb-3c10-05d6.static.ipv6.internode.on.net. [2001:44b8:111e:5c00:11e1:e7cb:3c10:5d6]) by smtp.gmail.com with ESMTPSA id b20sm2451665pff.51.2020.03.17.06.59.29 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 17 Mar 2020 06:59:30 -0700 (PDT) From: Daniel Axtens To: patchwork@lists.ozlabs.org Subject: [PATCH 2/4] REST: fix patch listing query Date: Wed, 18 Mar 2020 00:59:14 +1100 Message-Id: <20200317135916.13691-3-dja@axtens.net> X-Mailer: git-send-email 2.20.1 In-Reply-To: <20200317135916.13691-1-dja@axtens.net> References: <20200317135916.13691-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: , Errors-To: patchwork-bounces+incoming=patchwork.ozlabs.org@lists.ozlabs.org Sender: "Patchwork" The patch listing query is punishingly slow under even very simple filters. The new data model in 3.0 will help _a lot_, so this is a simple fix: I did try indexes but haven't got really deeply into the weeds of what we can do with them. Move a number of things from select_related to prefetch_related: we trade off one big, inefficient query for a slightly larger number of significantly more efficient queries. On my laptop with 2 copies of the canonical kernel team list loaded into the database, and considering only the API view (the JSON view is always faster) with warm caches and considering the entire set of SQL queries: - /api/patches/?project=1 ~1.4-1.5s -> <100ms, something like 14x better - /api/patches/?project=1&since=2010-11-01T00:00:00 ~1.7-1.8s -> <560ms, something like 3x better (now dominated by the counting query only invoked on the HTML API view, not the pure JSON API view.) The things I moved: * project: this was generating SQL that looked like: INNER JOIN `patchwork_project` T5 ON (`patchwork_submission`.`project_id` = T5.`id`) This is correct but we've already had to join the patchwork_submission table and perhaps as a result it seems to be inefficient. * series__project: Likewise we've already had to join the series table, doing another join is possibly why it is inefficient. * delegate: I do not know why this was tanking performance. I think it might relate to the strategy mysql was using. Reported-by: Konstantin Ryabitsev Signed-off-by: Daniel Axtens --- patchwork/api/patch.py | 9 ++++++--- patchwork/tests/api/test_patch.py | 4 ++-- 2 files changed, 8 insertions(+), 5 deletions(-) diff --git a/patchwork/api/patch.py b/patchwork/api/patch.py index efa38e1f0788..000100cec6bd 100644 --- a/patchwork/api/patch.py +++ b/patchwork/api/patch.py @@ -288,10 +288,13 @@ class PatchList(ListAPIView): ordering = 'id' def get_queryset(self): + # TODO(dja): we need to revisit this after the patch migration, paying + # particular attention to cases with filtering return Patch.objects.all()\ - .prefetch_related('check_set', 'related__patches__project')\ - .select_related('project', 'state', 'submitter', 'delegate', - 'series__project')\ + .prefetch_related('check_set', 'delegate', 'project', + 'series__project', + 'related__patches__project',)\ + .select_related('state', 'submitter', 'series')\ .defer('content', 'diff', 'headers') diff --git a/patchwork/tests/api/test_patch.py b/patchwork/tests/api/test_patch.py index aba92b92082e..b24c5ab28947 100644 --- a/patchwork/tests/api/test_patch.py +++ b/patchwork/tests/api/test_patch.py @@ -211,11 +211,11 @@ class TestPatchAPI(utils.APITestCase): self.assertNotIn('web_url', resp.data[0]) def test_list_bug_335(self): - """Ensure we retrieve the embedded series project once.""" + """Ensure we retrieve the embedded series project in O(1).""" series = create_series() create_patches(5, series=series) - with self.assertNumQueries(4): + with self.assertNumQueries(7): self.client.get(self.api_url()) @utils.store_samples('patch-detail')