From nobody Tue Feb 10 04:03:20 2026 Delivered-To: importer@patchew.org Received-SPF: pass (zohomail.com: domain of lists.xenproject.org designates 192.237.175.120 as permitted sender) client-ip=192.237.175.120; envelope-from=xen-devel-bounces@lists.xenproject.org; helo=lists.xenproject.org; Authentication-Results: mx.zohomail.com; spf=pass (zohomail.com: domain of lists.xenproject.org designates 192.237.175.120 as permitted sender) smtp.mailfrom=xen-devel-bounces@lists.xenproject.org; dmarc=fail(p=none dis=none) header.from=eu.citrix.com ARC-Seal: i=1; a=rsa-sha256; t=1595356975; cv=none; d=zohomail.com; s=zohoarc; b=GR+24LGvjWc5TPnJyLaRijjVX7e+Il+vSj9Hm8kATGrm3HwIFSdSgZWgE88p/j39yp/fd1YBi5vvpQJRxY2VPZVrb3VSxyuBErI7l/jHTEe5ILDnXf75MaA8H19K6rWS6C82V0pXg4w82sIsXYlNhdeLQrpv5vIrKwCBX5hGgFM= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595356975; h=Content-Transfer-Encoding:Cc:Date:From:In-Reply-To:List-Subscribe:List-Post:List-Id:List-Help:List-Unsubscribe:MIME-Version:Message-ID:References:Sender:Subject:To; bh=rZmJuWoEHz4U3tFK1eLWlMpdifAhScG/OlOZ6vukoY8=; b=jD4CdooK4TtUkWb/E0cpBuzRhrZ3OLesSHBM0MD4reYRGkkjJaFx+dugbR83a9HMavT1/uIdgmvW0pm9KnkfHdCjs+ZkjO4/BEfCv6NAdG3jMifFLJQsMLgWGq453xAJqwnbzx9ltMme5NeBtFe7uzRiYGDGCWgcPPKxZzFUAQs= ARC-Authentication-Results: i=1; mx.zohomail.com; spf=pass (zohomail.com: domain of lists.xenproject.org designates 192.237.175.120 as permitted sender) smtp.mailfrom=xen-devel-bounces@lists.xenproject.org; dmarc=fail header.from= (p=none dis=none) header.from= Return-Path: Received: from lists.xenproject.org (lists.xenproject.org [192.237.175.120]) by mx.zohomail.com with SMTPS id 1595356975990535.416490769472; Tue, 21 Jul 2020 11:42:55 -0700 (PDT) Received: from localhost ([127.0.0.1] helo=lists.xenproject.org) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxDp-00022F-D0; Tue, 21 Jul 2020 18:42:37 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxDo-0001xV-MZ for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:36 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e8d90018-cb81-11ea-85a2-bc764e2007e4; Tue, 21 Jul 2020 18:42:21 +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 1jxxDY-0001u7-6O; Tue, 21 Jul 2020 19:42:20 +0100 X-Inumbo-ID: e8d90018-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 04/14] sg-report-flight: Ask the db for flights of interest Date: Tue, 21 Jul 2020 19:41:55 +0100 Message-Id: <20200721184205.15232-5-ian.jackson@eu.citrix.com> X-Mailer: git-send-email 2.20.1 In-Reply-To: <20200721184205.15232-1-ian.jackson@eu.citrix.com> References: <20200721184205.15232-1-ian.jackson@eu.citrix.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable 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" Content-Type: text/plain; charset="utf-8" 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=3D'xen-unstable') AND EXISTS (SELECT 1 FROM jobs WHERE jobs.flight =3D flights.flight AND jobs.job =3D ?) AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'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=3D'xen-unstable') AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) AND EXISTS (SELECT 1 FROM jobs WHERE jobs.flight =3D flights.flight AND jobs.job =3D ?) AND r1.name LIKE 'built_revision_%' AND r1.name =3D ? AND r1.val=3D ? 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=3D'xen-unstable') + AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) AND EXISTS (SELECT 1 FROM jobs WHERE jobs.flight =3D flights.flight AND jobs.job =3D ?) - AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) + AND r1.name LIKE 'built_revision_%' + AND r1.name =3D ? + AND r1.val=3D ? + ORDER BY flight DESC LIMIT 1000 ) AS sub CC: George Dunlap Signed-off-by: Ian Jackson --- 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 94f85ed8..8582227e 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. =20 diff --git a/sg-report-flight b/sg-report-flight index 70def778..61aec7a8 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -185,19 +185,77 @@ END if (defined $job) { push @flightsq_params, $job; $flightsq_jobcond =3D <