From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on aws-us-west-2-korg-lkml-1.web.codeaurora.org X-Spam-Level: X-Spam-Status: No, score=-13.0 required=3.0 tests=BAYES_00, HEADER_FROM_DIFFERENT_DOMAINS,INCLUDES_PATCH,MAILING_LIST_MULTI,SIGNED_OFF_BY, SPF_HELO_NONE,SPF_PASS,URIBL_BLOCKED,USER_AGENT_GIT autolearn=ham autolearn_force=no version=3.4.0 Received: from mail.kernel.org (mail.kernel.org [198.145.29.99]) by smtp.lore.kernel.org (Postfix) with ESMTP id B5987C433E0 for ; Fri, 31 Jul 2020 11:39:18 +0000 (UTC) Received: from lists.xenproject.org (lists.xenproject.org [192.237.175.120]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mail.kernel.org (Postfix) with ESMTPS id 9126C20838 for ; Fri, 31 Jul 2020 11:39:18 +0000 (UTC) DMARC-Filter: OpenDMARC Filter v1.3.2 mail.kernel.org 9126C20838 Authentication-Results: mail.kernel.org; dmarc=fail (p=none dis=none) header.from=eu.citrix.com Authentication-Results: mail.kernel.org; spf=pass smtp.mailfrom=xen-devel-bounces@lists.xenproject.org Received: from localhost ([127.0.0.1] helo=lists.xenproject.org) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1TNU-00062m-0s; Fri, 31 Jul 2020 11:39:08 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1TNS-0005kZ-0g for xen-devel@lists.xenproject.org; Fri, 31 Jul 2020 11:39:06 +0000 X-Inumbo-ID: 5aa0cf15-d322-11ea-8e26-bc764e2007e4 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 5aa0cf15-d322-11ea-8e26-bc764e2007e4; Fri, 31 Jul 2020 11:38:30 +0000 (UTC) Received: from [172.18.45.5] (helo=zealot.relativity.greenend.org.uk) by chiark.greenend.org.uk (Debian Exim 4.84_2 #1) with esmtp (return-path ijackson@chiark.greenend.org.uk) id 1k1TMr-0001W4-BA; Fri, 31 Jul 2020 12:38:29 +0100 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH v2 08/41] sg-report-flight: Ask the db for flights of interest Date: Fri, 31 Jul 2020 12:37:47 +0100 Message-Id: <20200731113820.5765-9-ian.jackson@eu.citrix.com> X-Mailer: git-send-email 2.20.1 In-Reply-To: <20200731113820.5765-1-ian.jackson@eu.citrix.com> References: <20200731113820.5765-1-ian.jackson@eu.citrix.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-BeenThere: xen-devel@lists.xenproject.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: Xen developer discussion List-Unsubscribe: , List-Post: List-Help: List-Subscribe: , Cc: Ian Jackson , George Dunlap Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Specifically, we narrow the initial query to flights which have at least some job with the built_revision_foo we are looking for. This condition is strictly broader than that implemented inside the flight search loop, so there is no functional change. Perf: runtime of my test case now ~300s-500s. Example query before (from the Perl DBI trace): SELECT * FROM ( SELECT flight, blessing FROM flights WHERE (branch='xen-unstable') AND EXISTS (SELECT 1 FROM jobs WHERE jobs.flight = flights.flight AND jobs.job = ?) AND ( (TRUE AND flight <= 151903) AND (blessing='real') ) ORDER BY flight DESC LIMIT 1000 ) AS sub ORDER BY blessing ASC, flight DESC With these bind variables: "test-armhf-armhf-libvirt" After: SELECT * FROM ( SELECT DISTINCT flight, blessing FROM flights JOIN runvars r1 USING (flight) WHERE (branch='xen-unstable') AND ( (TRUE AND flight <= 151903) AND (blessing='real') ) AND EXISTS (SELECT 1 FROM jobs WHERE jobs.flight = flights.flight AND jobs.job = ?) AND r1.name LIKE 'built\_revision\_%' AND r1.name = ? AND r1.val= ? ORDER BY flight DESC LIMIT 1000 ) AS sub ORDER BY blessing ASC, flight DESC With these bind variables: "test-armhf-armhf-libvirt" 'built_revision_xen' '165f3afbfc3db70fcfdccad07085cde0a03c858b' Diff to the query: SELECT * FROM ( - SELECT flight, blessing FROM flights + SELECT DISTINCT flight, blessing + FROM flights + JOIN runvars r1 USING (flight) + WHERE (branch='xen-unstable') + AND ( (TRUE AND flight <= 151903) AND (blessing='real') ) AND EXISTS (SELECT 1 FROM jobs WHERE jobs.flight = flights.flight AND jobs.job = ?) - AND ( (TRUE AND flight <= 151903) AND (blessing='real') ) + AND r1.name LIKE 'built\_revision\_%' + AND r1.name = ? + AND r1.val= ? + ORDER BY flight DESC LIMIT 1000 ) AS sub CC: George Dunlap Signed-off-by: Ian Jackson --- v2: Use proper \ escaping for underscores in LIKE --- schema/runvars-built-index.sql | 2 +- sg-report-flight | 64 ++++++++++++++++++++++++++++++++-- 2 files changed, 62 insertions(+), 4 deletions(-) diff --git a/schema/runvars-built-index.sql b/schema/runvars-built-index.sql index 7108e0af..128e69e9 100644 --- a/schema/runvars-built-index.sql +++ b/schema/runvars-built-index.sql @@ -1,4 +1,4 @@ --- ##OSSTEST## 007 Preparatory +-- ##OSSTEST## 007 Needed -- -- This index helps sg-report-flight find relevant flights. diff --git a/sg-report-flight b/sg-report-flight index 7f2790ce..10127582 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -185,19 +185,77 @@ END if (defined $job) { push @flightsq_params, $job; $flightsq_jobcond = <