From nobody Thu Apr 25 01:32:42 2024 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=1595356967; cv=none; d=zohomail.com; s=zohoarc; b=iSHe55krAbqCRzV6JqoJSAVNMNsyojB82bC0gnx62Tv+Cwdf4l8JuxI3c8qMWsWDYMnO/HqQJ4nJbph7f6N+AIlAFSfRgBwqADdTPc04ugDVuj7oh2wucljlwF9ndqSpVC7R2NgF5oVarfNXCmG949mJNYr797n5EgQiAzTxw1Q= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595356967; 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=ZyVIuVXiW2/pMWMoqgn4oX0hxvhfWy8J7Cy7143Xkrg=; b=LeCJwxyzUL4azGuo3oTPFNQ+BdJODx9aKTHUWoDqY04fG14woE5SX4YVPmV4V5YRep8F5VFzPvnlNYKmOCkXgShr6fc0QoX2fCz7t9A071Q4QC2aWOGFocm8rp1xbdP8f9co+kg8d7S3ryEpscoR8yJBPviNy3kCdaFx7dsUcf0= 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 15953569678901009.3562548653858; Tue, 21 Jul 2020 11:42:47 -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 1jxxDb-0001yT-0S; Tue, 21 Jul 2020 18:42:23 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxDZ-0001xV-Lz for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:21 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e7aef92c-cb81-11ea-85a2-bc764e2007e4; Tue, 21 Jul 2020 18:42:19 +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 1jxxDW-0001u7-8s; Tue, 21 Jul 2020 19:42:18 +0100 X-Inumbo-ID: e7aef92c-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 01/14] sg-report-flight: Add a comment re same-flight search narrowing Date: Tue, 21 Jul 2020 19:41:52 +0100 Message-Id: <20200721184205.15232-2-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" In afe851ca1771e5da6395b596afa69e509dbbc278 sg-report-flight: When justifying, disregard out-of-flight build jobs we narrowed sg-report-flight's search algorith. An extensive justification is in the commit message. I think much of this information belongs in-tree, so c&p it (with slight edits) here. No code change. Signed-off-by: Ian Jackson --- sg-report-flight | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) diff --git a/sg-report-flight b/sg-report-flight index 6c481f6f..927ea37d 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -242,9 +242,27 @@ END # jobs. We start with all jobs in $tflight, and for each job # we also process any other jobs it refers to in *buildjob runvars. # + # The real thing we want to check that the build jobs *in the + # same flight as the justifying job* used the right revisions. + # Build jobs from other flights were either (i) build jobs for + # components not being targed for testing by this branch, but + # which were necessary for the justifying job and for which we + # decided to reuse another build job (in which case we don't + # really care what versions they used, even if underlying it + # all there might be a different version of a tree we are + # actually interested in (ii) the kind of continuous update + # thing seen with freebsdbuildjob. + # + # (This is rather different to cs-bisection-step, which is + # less focused on changes in a particular set of trees.) + # + # So we limit the scope of our recursive descent into build + # jobs, to jobs in the same flight. + # # We don't actually use a recursive algorithm because that # would involve recursive use of the same sql query object; # hence the @binfos_todo queue. + my @binfos_todo; my $binfos_queue =3D sub { my ($inflight,$q,$why) =3D @_; --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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=1595356968; cv=none; d=zohomail.com; s=zohoarc; b=LNhJ6k+SW7EizLxbqF8OIjjT7MeVyh9cdMBAILGLQGchhEPfIZmPmHJjijagm0tAujMJ0I8Db9rN09vXnDKYNaXH1k60puQjkBUjQ5niTDFXXoqBgOvH/xPEoOVmHGwnaXTB7ZPj8NSFmWDzM0OoMxqZxVbDlhG6cK83SY0uxEg= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595356968; 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=D2X+rHZDLKhV0ZrVcWqxUqnYQXFoKTWzHllJkqKwlWg=; b=AbCJ/+dy9arhHPdNkbIOVCKE+Z7qSCXiITN46a+JTY1k+n+OI2ZMAagpVvVuOXrsDdBqSSBVNnKjz2T/2PCJXmwNKwJJBJ85c9Vz3f2v8e1fhPuew63YQnDsyZGG3EOSbJxDUlWkikohxrv/DYzI5hjcrWS6CN3aLcuJyjbI2lk= 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 1595356968400158.45003511370555; Tue, 21 Jul 2020 11:42:48 -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 1jxxDg-0001z7-JS; Tue, 21 Jul 2020 18:42:28 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxDe-0001xV-ME for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:26 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e8816efc-cb81-11ea-85a2-bc764e2007e4; Tue, 21 Jul 2020 18:42:20 +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 1jxxDX-0001u7-IL; Tue, 21 Jul 2020 19:42:19 +0100 X-Inumbo-ID: e8816efc-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 02/14] sg-report-flight: Sort failures by job name as last resort Date: Tue, 21 Jul 2020 19:41:53 +0100 Message-Id: <20200721184205.15232-3-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" This removes some nondeterminism from the output. Signed-off-by: Ian Jackson --- sg-report-flight | 1 + 1 file changed, 1 insertion(+) diff --git a/sg-report-flight b/sg-report-flight index 927ea37d..70def778 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -813,6 +813,7 @@ END # they finished in the same second, we pick the lower-numbered # step, which is the earlier one (if they are sequential at # all). + or $a->{Job} cmp $b->{Job} } @failures; =20 --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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=1595356968; cv=none; d=zohomail.com; s=zohoarc; b=clyMeuY3kFvj1iOwflxsrUz5PtVZ2D1HULZP9+5i6bDatSWEvHHiCaM7Q0MQE0i2lqFE+WytSqod58sBP9vgQdSLdAGnJE/lVLOeEnP0ODMiBUAyOoYrsqF3EujWG5a3DKk5SlguF2LYR+5JNh9owwqdo/PnVYr2vQn1KNkAl6c= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595356968; 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=upTZLe1y/KQAQBYTSI1MNI61RdRoP43vrtdiABQAQ/Y=; b=FslOLy8yzm3cSainYfljmDb2djtpC6wNnTSt++Y40V21BYZSnoNcODOVDhkZh8uLQRFdUxpTeIXq5HQoyP+YVOLBZWryEKc6s2WXdENXrW8A+q5TI2bViZrtNmbTIZS5ELPvKOgksAi1/11Yjnvc6yneJbGofTnEzS6+8iLuTYE= 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 1595356968614220.63164725556544; Tue, 21 Jul 2020 11:42:48 -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 1jxxDk-00020c-VM; Tue, 21 Jul 2020 18:42:32 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxDj-0001xV-MS for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:31 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e7f1f129-cb81-11ea-85a2-bc764e2007e4; Tue, 21 Jul 2020 18:42:20 +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 1jxxDX-0001u7-TP; Tue, 21 Jul 2020 19:42:19 +0100 X-Inumbo-ID: e7f1f129-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 03/14] schema: Provide indices for sg-report-flight Date: Tue, 21 Jul 2020 19:41:54 +0100 Message-Id: <20200721184205.15232-4-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" These indexes allow very fast lookup of "relevant" flights eg when trying to justify failures. In my ad-hoc test case, these indices (along with the subsequent changes to sg-report-flight and Executive.pm, reduce the runtime of sg-report-flight from 2-3ks (unacceptably long!) to as little as 5-7s seconds - a speedup of about 500x. (Getting the database snapshot may take a while first, but deploying this code should help with that too by reducing long-running transactions. Quoted perf timings are from snapshot acquisition.) Without these new indexes there may be a performance change from the query changes. I haven't benchmarked this so I am setting the schema updates to be Preparatory/Needed (ie, "Schema first" as schema/README.updates has it), to say that the index should be created before the new code is deployed. Testing: I have tested this series by creating experimental indices "trial_..." in the actual production instance. (Transactional DDL was very helpful with this.) I have verified with \d that schema update instructions in this commit generate indexes which are equivalent to the trial indices. Deployment: AFter these schema updates are applied, the trial indices are redundant duplicates and should be deleted. CC: George Dunlap Signed-off-by: Ian Jackson --- schema/runvars-built-index.sql | 7 +++++++ schema/runvars-revision-index.sql | 7 +++++++ schema/steps-job-index.sql | 7 +++++++ 3 files changed, 21 insertions(+) create mode 100644 schema/runvars-built-index.sql create mode 100644 schema/runvars-revision-index.sql create mode 100644 schema/steps-job-index.sql diff --git a/schema/runvars-built-index.sql b/schema/runvars-built-index.sql new file mode 100644 index 00000000..94f85ed8 --- /dev/null +++ b/schema/runvars-built-index.sql @@ -0,0 +1,7 @@ +-- ##OSSTEST## 007 Preparatory +-- +-- This index helps sg-report-flight find relevant flights. + +CREATE INDEX runvars_built_revision_idx + ON runvars (val) + WHERE name LIKE 'built_revision_%'; diff --git a/schema/runvars-revision-index.sql b/schema/runvars-revision-in= dex.sql new file mode 100644 index 00000000..a2e3be13 --- /dev/null +++ b/schema/runvars-revision-index.sql @@ -0,0 +1,7 @@ +-- ##OSSTEST## 008 Preparatory +-- +-- This index helps Executive::report__find_test find relevant flights. + +CREATE INDEX runvars_revision_idx + ON runvars (val) + WHERE name LIKE 'revision_%'; diff --git a/schema/steps-job-index.sql b/schema/steps-job-index.sql new file mode 100644 index 00000000..07dc5a30 --- /dev/null +++ b/schema/steps-job-index.sql @@ -0,0 +1,7 @@ +-- ##OSSTEST## 006 Preparatory +-- +-- This index helps sg-report-flight find if a test ever passed. + +CREATE INDEX steps_job_testid_status_idx + ON steps (job, testid, status); + --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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 < (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 1595356979332847.2883333731872; Tue, 21 Jul 2020 11:42:59 -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 1jxxDu-00024h-PK; Tue, 21 Jul 2020 18:42:42 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxDt-0001xV-Mo for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:41 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e7f1f12a-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-F0; Tue, 21 Jul 2020 19:42:20 +0100 X-Inumbo-ID: e7f1f12a-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 05/14] sg-report-flight: Use WITH to use best index use for $flightsq Date: Tue, 21 Jul 2020 19:41:56 +0100 Message-Id: <20200721184205.15232-6-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" While we're here, convert this EXISTS subquery to a JOIN. Perf: runtime of my test case now ~200-300s. Example query before (from the Perl DBI trace): 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 bind variables: "test-armhf-armhf-libvirt" 'built_revision_xen' '165f3afbfc3db70fcfdccad07085cde0a03c858b' After: WITH sub AS ( 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 r1.name LIKE 'built_revision_%' AND r1.name =3D ? AND r1.val=3D ? ORDER BY flight DESC LIMIT 1000 ) SELECT * FROM sub JOIN jobs USING (flight) WHERE (1=3D1) AND jobs.job =3D ? ORDER BY blessing ASC, flight DESC With bind variables: 'built_revision_xen' '165f3afbfc3db70fcfdccad07085cde0a03c858b' "test-armhf-armhf-libvirt" Diff to the query: - SELECT * FROM ( + WITH sub AS ( 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 + ) + SELECT * + FROM sub + JOIN jobs USING (flight) + + WHERE (1=3D1) + AND jobs.job =3D ? + ORDER BY blessing ASC, flight DESC CC: George Dunlap Signed-off-by: Ian Jackson --- sg-report-flight | 39 ++++++++++++++++++++++++--------------- 1 file changed, 24 insertions(+), 15 deletions(-) diff --git a/sg-report-flight b/sg-report-flight index 61aec7a8..b5398573 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -180,18 +180,6 @@ END return undef; } =20 - my @flightsq_params; - my $flightsq_jobcond=3D'(1=3D1)'; - if (defined $job) { - push @flightsq_params, $job; - $flightsq_jobcond =3D < (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 1595356982409744.5226304545835; Tue, 21 Jul 2020 11:43:02 -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 1jxxDz-00026f-4m; Tue, 21 Jul 2020 18:42:47 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxDy-0001xV-N5 for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:46 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e926bf38-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-Nz; Tue, 21 Jul 2020 19:42:20 +0100 X-Inumbo-ID: e926bf38-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 06/14] sg-report-flight: Use WITH clause to use index for $anypassq Date: Tue, 21 Jul 2020 19:41:57 +0100 Message-Id: <20200721184205.15232-7-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" Perf: runtime of my test case now ~11s Example query before (from the Perl DBI trace): SELECT * FROM flights JOIN steps USING (flight) WHERE (branch=3D'xen-unstable') AND job=3D? and testid=3D? and status=3D'pass' AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) LIMIT 1 After: WITH s AS ( SELECT * FROM steps WHERE job=3D? and testid=3D? and status=3D'pass' ) SELECT * FROM flights JOIN s USING (flight) WHERE (branch=3D'xen-unstable') AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) LIMIT 1 In both cases with bind vars: "test-amd64-i386-xl-pvshim" "guest-start" Diff to the query: - SELECT * FROM flights JOIN steps USING (flight) + WITH s AS + ( + SELECT * FROM steps + WHERE job=3D? and testid=3D? and status=3D'pass' + ) + SELECT * FROM flights JOIN s USING (flight) WHERE (branch=3D'xen-unstable') - AND job=3D? and testid=3D? and status=3D'pass' AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) LIMIT 1 CC: George Dunlap Signed-off-by: Ian Jackson Reviewed-by: George Dunlap --- schema/steps-job-index.sql | 2 +- sg-report-flight | 14 ++++++++++++-- 2 files changed, 13 insertions(+), 3 deletions(-) diff --git a/schema/steps-job-index.sql b/schema/steps-job-index.sql index 07dc5a30..2c33af72 100644 --- a/schema/steps-job-index.sql +++ b/schema/steps-job-index.sql @@ -1,4 +1,4 @@ --- ##OSSTEST## 006 Preparatory +-- ##OSSTEST## 006 Needed -- -- This index helps sg-report-flight find if a test ever passed. =20 diff --git a/sg-report-flight b/sg-report-flight index b5398573..b8d948da 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -849,10 +849,20 @@ sub justifyfailures ($;$) { =20 my @failures=3D values %{ $fi->{Failures} }; =20 + # In psql 9.6 this WITH clause makes postgresql do the steps query + # first. This is good because if this test never passed we can + # determine that really quickly using the new index, without + # having to scan the flights table. (If the test passed we will + # probably not have to look at many flights to find one, so in + # that case this is not much worse.) my $anypassq=3D < (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 1595356985192282.0432688524494; Tue, 21 Jul 2020 11:43:05 -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 1jxxE4-0002AF-H7; Tue, 21 Jul 2020 18:42:52 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxE3-0001xV-NL for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:51 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e8d90019-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 1jxxDZ-0001u7-0G; Tue, 21 Jul 2020 19:42:21 +0100 X-Inumbo-ID: e8d90019-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 07/14] sg-report-flight: Use the job row from the intitial query Date: Tue, 21 Jul 2020 19:41:58 +0100 Message-Id: <20200721184205.15232-8-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" $jcheckq is redundant: we looked this up right at the start. This is not expected to speed things up very much, but it makes things somewhat cleaner and clearer. Signed-off-by: Ian Jackson --- sg-report-flight | 11 +++-------- 1 file changed, 3 insertions(+), 8 deletions(-) diff --git a/sg-report-flight b/sg-report-flight index b8d948da..bcb0d427 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -160,10 +160,6 @@ sub findaflight ($$$$$) { return undef; } =20 - my $jcheckq=3D db_prepare(<fetchrow_array) { + while (my ($tflight, $tjstatus) =3D $flightsq->fetchrow_array) { # Recurse from the starting flight looking for relevant build # jobs. We start with all jobs in $tflight, and for each job # we also process any other jobs it refers to in *buildjob runvars. @@ -407,8 +403,7 @@ END $checkq->execute($tflight, $job, $testid); ($chkst) =3D $checkq->fetchrow_array(); if (!defined $chkst) { - $jcheckq->execute($tflight, $job); - my ($jchkst) =3D $jcheckq->fetchrow_array(); + my $jchkst =3D $tflight->{status}; $chkst =3D $jchkst if $jchkst eq 'starved'; } } --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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=1595356994; cv=none; d=zohomail.com; s=zohoarc; b=Cdp3j31CS2fk20AdDCveyUXdW3Mm1y/FJ7/ap1ckZ14gr4OUg/ORl2UKBMjiij1aGl1HMgimdzPvdWeHPsdq/Z+lzo6y5Px3d5Hy9bfPqFjOKtsSsIsTcvW/gX0iy89v7BTK7oScFlevxtFJCEoot0/P9QAS3x8+mNKER8yPa3w= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595356994; 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=9UIP2WpFnVSOyuUcsZ79CgxMKHwsu4GWisQCiZGLFUY=; b=n2KsjeT1najlSK1SMnOhGbTgDBSs3pveSLlOX9VFNuZ+lrnjVwp88ZEJg9uv5+t85G6ph0E0MODOR7NpJxPazbjKuxEHWNI2Bf016HBIkNyKWz1pkPWLBHd6VIPR0SzFAthory3e55Tp4XwBigXkqexeTajcURYEMqPEfL2PFrc= 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 1595356994644384.5896564048853; Tue, 21 Jul 2020 11:43:14 -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 1jxxE9-0002Dp-TO; Tue, 21 Jul 2020 18:42:57 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxE8-0001xV-NN for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:42:56 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e8d9001a-cb81-11ea-85a2-bc764e2007e4; Tue, 21 Jul 2020 18:42:22 +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 1jxxDZ-0001u7-A6; Tue, 21 Jul 2020 19:42:21 +0100 X-Inumbo-ID: e8d9001a-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 08/14] Executive: Use index for report__find_test Date: Tue, 21 Jul 2020 19:41:59 +0100 Message-Id: <20200721184205.15232-9-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" After we refactor this query then we can enable the index use. (Both of these things together in this commit because I haven't perf tested the version with just the refactoring.) (We have provided an index that can answer this question really quickly if a version is specified. But the query planner couldn't see that because it works without seeing the bind variables, so doesn't know that the value of name is going to be suitable for this index.) * Convert the two EXISTS subqueries into JOIN/AND with a DISTINCT clause naming the fields on flights, so as to replicate the previous result rows. Then do $selection field last. The subquery is a convenient way to let this do the previous thing for all the values of $selection (including, notably, *). * Add the additional AND clause for r.name, which has no logical effect given the actual values of name, enabling the query planner to use this index. Perf: In my test case the sg-report-flight runtime is now ~8s. I am reasonably confident that this will not make other use cases of this code worse. Perf: runtime of my test case now ~11s Example query before (from the Perl DBI trace): SELECT * FROM flights f WHERE EXISTS ( SELECT 1 FROM runvars r WHERE name=3D? AND val=3D? AND r.flight=3Df.flight AND ( (CASE WHEN (r.job) LIKE 'build-%-prev' THEN 'xprev' WHEN ((r.job) LIKE 'build-%-freebsd' AND 'x' =3D 'freebsdbuildjob') THEN 'DISCARD' ELSE '' END) =3D '') ) AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) AND (branch=3D?) ORDER BY flight DESC LIMIT 1 After: SELECT * FROM ( SELECT DISTINCT flight, started, blessing, branch, intended FROM flights f JOIN runvars r USING (flight) WHERE name=3D? AND name LIKE 'revision_%' AND val=3D? AND r.flight=3Df.flight AND ( (CASE WHEN (r.job) LIKE 'build-%-prev' THEN 'xprev' WHEN ((r.job) LIKE 'build-%-freebsd' AND 'x' =3D 'freebsdbuildjob') THEN 'DISCARD' ELSE '' END) =3D '') AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) AND (branch=3D?) ) AS sub WHERE TRUE ORDER BY flight DESC LIMIT 1 In both cases with bind vars: 'revision_xen' '165f3afbfc3db70fcfdccad07085cde0a03c858b' "xen-unstable" Diff to the example query: @@ -1,10 +1,10 @@ SELECT * + FROM ( SELECT DISTINCT + flight, started, blessing, branch, intended FROM flights f - WHERE - EXISTS ( - SELECT 1 - FROM runvars r + JOIN runvars r USING (flight) WHERE name=3D? + AND name LIKE 'revision_%' AND val=3D? AND r.flight=3Df.flight AND ( (CASE @@ -14,8 +14,8 @@ ELSE '' END) =3D '') - ) AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) AND (branch=3D?) +) AS sub WHERE TRUE ORDER BY flight DESC LIMIT 1 CC: George Dunlap Signed-off-by: Ian Jackson --- Osstest/Executive.pm | 20 ++++++++------------ schema/runvars-revision-index.sql | 2 +- 2 files changed, 9 insertions(+), 13 deletions(-) diff --git a/Osstest/Executive.pm b/Osstest/Executive.pm index c3dc1261..c272e9f2 100644 --- a/Osstest/Executive.pm +++ b/Osstest/Executive.pm @@ -415,37 +415,32 @@ sub report__find_test ($$$$$$$) { =20 my $querytext =3D < (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 1595356998806499.3518356729322; Tue, 21 Jul 2020 11:43:18 -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 1jxxEE-0002Ih-EB; Tue, 21 Jul 2020 18:43:02 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxED-0001xV-Nj for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 18:43:01 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id e9bcdb94-cb81-11ea-85a2-bc764e2007e4; Tue, 21 Jul 2020 18:42:22 +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 1jxxDZ-0001u7-MD; Tue, 21 Jul 2020 19:42:21 +0100 X-Inumbo-ID: e9bcdb94-cb81-11ea-85a2-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 09/14] duration_estimator: Ignore truncated jobs unless we know the step Date: Tue, 21 Jul 2020 19:42:00 +0100 Message-Id: <20200721184205.15232-10-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" If we are looking for a particular step then we will ignore jobs without that step, so any job which was truncated before it will be ignored. Otherwise we are looking for the whole job duration and a truncated job is not a good representative. This is a bugfix (to duration estimation), not a performance improvement like the preceding and subsequent changes. Signed-off-by: Ian Jackson --- Osstest/Executive.pm | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/Osstest/Executive.pm b/Osstest/Executive.pm index c272e9f2..3cd37c14 100644 --- a/Osstest/Executive.pm +++ b/Osstest/Executive.pm @@ -1142,6 +1142,10 @@ sub duration_estimator ($$;$$) { # estimated (and only jobs which contained that step will be # considered). =20 + my $or_status_truncated =3D ''; + if ($will_uptoincl_testid) { + $or_status_truncated =3D "OR j.status=3D'truncated'!"; + } my $recentflights_q=3D $dbh_tests->prepare(<=3D ? ORDER BY f.started DESC --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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=1595358432; cv=none; d=zohomail.com; s=zohoarc; b=HaYK0EodxY7E7YoJlPkFC29WvNEl1ML5X48sKBd2kNh1zYt4t9eAhL+tcXmDexHa/ToSD+PwxMLna0K8dYS34quGBPan1bN1sorljdCrBzQF4XCO/S55JUjDlVwHb2GqDOoIFJHNlTD1l8VW3CakUrl0VM7VZCJiFs6qmRuiHxA= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595358432; 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=lHFvgyF8gOlhD+id6+o8O11LAckCOPIWZH4FQosKRyU=; b=eIpYXhSTsY/p0KIoLUEAVjDqKY9OeCuC+MvEEFcEU1/8J2/8fgoqB7XZwmo+zdfGUsIp0QS13W/hdcQ67mo4JcV7ubbw9B4UL8CPBKFqiczQO/L2ugClx4pxqQnqhEHF0Bq60afXsNW6ld+SghBD9errL21DITbJ1k0nufT8wzY= 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 1595358432508470.49741046166105; Tue, 21 Jul 2020 12:07:12 -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 1jxxbB-0004aY-He; Tue, 21 Jul 2020 19:06:45 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxbA-0004aT-LH for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 19:06:44 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 50ae2986-cb85-11ea-85a6-bc764e2007e4; Tue, 21 Jul 2020 19:06:43 +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 1jxxDZ-0001u7-UT; Tue, 21 Jul 2020 19:42:22 +0100 X-Inumbo-ID: 50ae2986-cb85-11ea-85a6-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 10/14] duration_estimator: Introduce some _qtxt variables Date: Tue, 21 Jul 2020 19:42:01 +0100 Message-Id: <20200721184205.15232-11-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" No functional change. Signed-off-by: Ian Jackson --- Osstest/Executive.pm | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) diff --git a/Osstest/Executive.pm b/Osstest/Executive.pm index 3cd37c14..c966a1be 100644 --- a/Osstest/Executive.pm +++ b/Osstest/Executive.pm @@ -1146,7 +1146,7 @@ sub duration_estimator ($$;$$) { if ($will_uptoincl_testid) { $or_status_truncated =3D "OR j.status=3D'truncated'!"; } - my $recentflights_q=3D $dbh_tests->prepare(<prepare(<prepare($recentflights_qtxt); + my $duration_anyref_q=3D $dbh_tests->prepare($duration_anyref_qtxt); my $duration_duration_q =3D $dbh_tests->prepare($duration_duration_qtx= t); =20 return sub { --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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=1595358439; cv=none; d=zohomail.com; s=zohoarc; b=cLWhU5vAWu25fCql3yjZtn6FDHeTJEFFLdjCNimUEd1Rqw5acZTGXDekEW/VnlgXiY0FMNhymhTQc9UR6beIwnCPSkr629f81vMnglqsZrkFuG7JAAi5kl2ueDXnbaMVXeVGulr5mb7LV0ndv72ur/hhdhSIftl0KO84vL2olfI= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595358439; 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=IFHqi98dmgl9TABvTvnPU7mqfC2WGGcOiVzEUmsNU90=; b=aII1IpEHzQPEEbd0aL51xHtnxQKEEhvrcF7GRSwKMF+Yp/4K2Kjp2OhOFGDgsGMRpXs7k5eQbjmemSK6YH9kOn8H2LkbEAfSM1unYSrNqoAp7g/CjNzFiCjBfr2iSsM0uXBKyeTaYdWn84kzhNsdLAD1k0Xc6vk/TCtSQQi3FB0= 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 1595358439265815.8306512680559; Tue, 21 Jul 2020 12:07:19 -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 1jxxbS-0004dJ-Ez; Tue, 21 Jul 2020 19:07:02 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxbR-0004d0-7u for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 19:07:01 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 5ab65f34-cb85-11ea-85a6-bc764e2007e4; Tue, 21 Jul 2020 19:07:00 +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 1jxxDa-0001u7-7T; Tue, 21 Jul 2020 19:42:22 +0100 X-Inumbo-ID: 5ab65f34-cb85-11ea-85a6-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 11/14] duration_estimator: Explicitly provide null in general host q Date: Tue, 21 Jul 2020 19:42:02 +0100 Message-Id: <20200721184205.15232-12-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" Our spec. says we return nulls for started and status if we don't find a job matching the host spec. The way this works right now is that we look up the nonexistent entries in $refs->[0]. This is not really brilliant and is going to be troublesome as we continue to refactor. Provide these values explicitly. No functional change. Signed-off-by: Ian Jackson --- Osstest/Executive.pm | 2 ++ 1 file changed, 2 insertions(+) diff --git a/Osstest/Executive.pm b/Osstest/Executive.pm index c966a1be..ee1bf07e 100644 --- a/Osstest/Executive.pm +++ b/Osstest/Executive.pm @@ -1169,6 +1169,8 @@ END =20 my $duration_anyref_qtxt=3D < (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 15953584399381017.6625358934285; Tue, 21 Jul 2020 12:07:19 -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 1jxxbV-0004eV-Pe; Tue, 21 Jul 2020 19:07:05 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxbU-0004d0-GC for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 19:07:04 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 5c74bf78-cb85-11ea-85a6-bc764e2007e4; Tue, 21 Jul 2020 19:07:03 +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 1jxxDa-0001u7-I0; Tue, 21 Jul 2020 19:42:22 +0100 X-Inumbo-ID: 5c74bf78-cb85-11ea-85a6-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 12/14] duration_estimator: Return job column in first query Date: Tue, 21 Jul 2020 19:42:03 +0100 Message-Id: <20200721184205.15232-13-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" Right now this is pointless since the Perl code doesn't need it. But this row is going to be part of a WITH clause soon. No functional change. Diffs to two example queries (from the Perl DBI trace): SELECT f.flight AS flight, + j.job AS job, f.started AS started, j.status AS status FROM flights f JOIN jobs j USING (flight) JOIN runvars r ON f.flight=3Dr.flight AND r.name=3D? WHERE j.job=3Dr.job AND f.blessing=3D? AND f.branch=3D? AND j.job=3D? AND r.val=3D? AND (j.status=3D'pass' OR j.status=3D'fail' OR j.status=3D'truncated'!) AND f.started IS NOT NULL AND f.started >=3D ? ORDER BY f.started DESC SELECT f.flight AS flight, + s.job AS job, NULL as started, NULL as status, max(s.finished) AS max_finished FROM steps s JOIN flights f ON s.flight=3Df.flight WHERE s.job=3D? AND f.blessing=3D? AND f.branch=3D? AND s.finished IS NOT NULL AND f.started IS NOT NULL AND f.started >=3D ? - GROUP BY f.flight + GROUP BY f.flight, s.job ORDER BY max_finished DESC CC: George Dunlap Signed-off-by: Ian Jackson --- Osstest/Executive.pm | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/Osstest/Executive.pm b/Osstest/Executive.pm index ee1bf07e..8e8b3d33 100644 --- a/Osstest/Executive.pm +++ b/Osstest/Executive.pm @@ -1148,6 +1148,7 @@ sub duration_estimator ($$;$$) { } my $recentflights_qtxt=3D <=3D ? - GROUP BY f.flight + GROUP BY f.flight, s.job ORDER BY max_finished DESC END # s J J J # fix perl-mode --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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=1595358433; cv=none; d=zohomail.com; s=zohoarc; b=CoyvYdEJUjN9INlzEQqQ+lcttGrAoccP1LI+dH2vb2ipf6yBFvJBR12jatSvKAmpmjRZDPU19uI8AjPM2PL+ubfAxPkYAVk5V0dV3eEOB+oaCI50oaBw2U6Q8WgaZ1SQt0U1ehBehoKeoqbigfiXOwt8/H/aDmgzbDiloFQCeoM= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595358433; 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=0FHprp4y9s6AW1g4aCVZgjPC29Y7Rq2ViXzC04rOEh8=; b=dsZD95S8QXMgRdPDF+AxOgIxpuseHMn9TgWmZ9bAbCC6N/OaSaqf48f8CUdy18Nzt1VOBw3YfJnL6FmQOVUIoVuJ8nY+7IEajG/UQEzUpQOdUTTEGL2jq4osSR9W20aplV6UDz8m6fJ68gD8ASfgc6rVHVdt/OKiWyIBk85JNY4= 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 1595358433125230.8075948798604; Tue, 21 Jul 2020 12:07:13 -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 1jxxbL-0004bd-4x; Tue, 21 Jul 2020 19:06:55 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxbK-0004aT-GG for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 19:06:54 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 55929932-cb85-11ea-85a6-bc764e2007e4; Tue, 21 Jul 2020 19:06:51 +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 1jxxDa-0001u7-QU; Tue, 21 Jul 2020 19:42:22 +0100 X-Inumbo-ID: 55929932-cb85-11ea-85a6-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 13/14] duration_estimator: Move $uptincl_testid to separate @x_params Date: Tue, 21 Jul 2020 19:42:04 +0100 Message-Id: <20200721184205.15232-14-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" This is going to be useful soon. No functional change. Signed-off-by: Ian Jackson --- Osstest/Executive.pm | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/Osstest/Executive.pm b/Osstest/Executive.pm index 8e8b3d33..621153ee 100644 --- a/Osstest/Executive.pm +++ b/Osstest/Executive.pm @@ -1223,6 +1223,9 @@ END_ALWAYS return sub { my ($job, $hostidname, $onhost, $uptoincl_testid) =3D @_; =20 + my @x_params; + push @x_params, $uptoincl_testid if $will_uptoincl_testid; + my $dbg=3D $debug ? sub { $debug->("DUR $branch $blessing $job $hostidname $onhost @_"); } : sub { }; @@ -1257,7 +1260,7 @@ END_ALWAYS my $duration_max=3D 0; foreach my $ref (@$refs) { my @d_d_args =3D ($ref->{flight}, $job); - push @d_d_args, $uptoincl_testid if $will_uptoincl_testid; + push @d_d_args, @x_params; $duration_duration_q->execute(@d_d_args); my ($duration) =3D $duration_duration_q->fetchrow_array(); $duration_duration_q->finish(); --=20 2.20.1 From nobody Thu Apr 25 01:32:42 2024 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=1595358433; cv=none; d=zohomail.com; s=zohoarc; b=hhwDZ+Q4l0+0WIxO+X+JhJhaM7YSCkuCtbqw9rPv+xnHhQTa5OF4KpovL7iKbqIgXa35Xy/lVSPce+azDDbs4+3nyfuiSgL0eW4dXfG6OtB5EagWBA1e6k2nwIGeFyM8gBvX9XuLLYVk52rkO+8kvir44xZiOk8sh7e++YAd4EI= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1595358433; 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=lz6r8rNgFJX/x6yo4gGvnNp99XFcDbXMDAIxT3Vbtzc=; b=HR6+1vqDDl9aMlGId2q/SB9SFmVMzigVs2x6k6CA3dY1et685FVozU8d8q5tPOMeX8ub0VsXSGfCZ5sZAyfa1rZ7/gsMLpMoHL18jQ2jJ3R7nUNccrFRs8YSuCs8nGDlOg7k9lLaEQ5eKVcHVNeBKVnKcLnmsqOhQplvy4VSxaE= 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 15953584333882.4074460132086415; Tue, 21 Jul 2020 12:07:13 -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 1jxxbG-0004ap-Qw; Tue, 21 Jul 2020 19:06:50 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1jxxbF-0004aT-GF for xen-devel@lists.xenproject.org; Tue, 21 Jul 2020 19:06:49 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 51f94f28-cb85-11ea-85a6-bc764e2007e4; Tue, 21 Jul 2020 19:06:45 +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 1jxxDb-0001u7-6c; Tue, 21 Jul 2020 19:42:23 +0100 X-Inumbo-ID: 51f94f28-cb85-11ea-85a6-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH 14/14] duration_estimator: Move duration query loop into database Date: Tue, 21 Jul 2020 19:42:05 +0100 Message-Id: <20200721184205.15232-15-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" Stuff the two queries together: we use the firsty query as a WITH clause. This is significantly faster, perhaps because the query optimiser does a better job but probably just because it saves on round trips. No functional change. Perf: subjectively this seemed to help when the cache was cold. Now I have a warm cache and it doesn't seem to make much difference. Perf: runtime of my test case now ~5-7s. Example queries before (from the debugging output): Query A part I: SELECT f.flight AS flight, j.job AS job, f.started AS started, j.status AS status FROM flights f JOIN jobs j USING (flight) JOIN runvars r ON f.flight=3Dr.flight AND r.name=3D? WHERE j.job=3Dr.job AND f.blessing=3D? AND f.branch=3D? AND j.job=3D? AND r.val=3D? AND (j.status=3D'pass' OR j.status=3D'fail' OR j.status=3D'truncated'!) AND f.started IS NOT NULL AND f.started >=3D ? ORDER BY f.started DESC With bind variables: "test-amd64-i386-xl-pvshim" "guest-start" Query B part I: SELECT f.flight AS flight, s.job AS job, NULL as started, NULL as status, max(s.finished) AS max_finished FROM steps s JOIN flights f ON s.flight=3Df.flight WHERE s.job=3D? AND f.blessing=3D? AND f.branch=3D? AND s.finished IS NOT NULL AND f.started IS NOT NULL AND f.started >=3D ? GROUP BY f.flight, s.job ORDER BY max_finished DESC With bind variables: "test-armhf-armhf-libvirt" 'real' "xen-unstable" 1594144469 Query common part II: WITH tsteps AS ( SELECT * FROM steps WHERE flight=3D? AND job=3D? ) , tsteps2 AS ( SELECT * FROM tsteps WHERE finished <=3D (SELECT finished FROM tsteps WHERE tsteps.testid =3D ?) ) SELECT ( SELECT max(finished)-min(started) FROM tsteps2 ) - ( SELECT sum(finished-started) FROM tsteps2 WHERE step =3D 'ts-hosts-allocate' ) AS duration With bind variables from previous query, eg: 152045 "test-armhf-armhf-libvirt" "guest-start.2" After: Query A (combined): WITH f AS ( SELECT f.flight AS flight, j.job AS job, f.started AS started, j.status AS status FROM flights f JOIN jobs j USING (flight) JOIN runvars r ON f.flight=3Dr.flight AND r.name=3D? WHERE j.job=3Dr.job AND f.blessing=3D? AND f.branch=3D? AND j.job=3D? AND r.val=3D? AND (j.status=3D'pass' OR j.status=3D'fail' OR j.status=3D'truncated'!) AND f.started IS NOT NULL AND f.started >=3D ? ORDER BY f.started DESC ) SELECT flight, max_finished, job, started, status, ( WITH tsteps AS ( SELECT * FROM steps WHERE flight=3Df.flight AND job=3Df.job ) , tsteps2 AS ( SELECT * FROM tsteps WHERE finished <=3D (SELECT finished FROM tsteps WHERE tsteps.testid =3D ?) ) SELECT ( SELECT max(finished)-min(started) FROM tsteps2 ) - ( SELECT sum(finished-started) FROM tsteps2 WHERE step =3D 'ts-hosts-allocate' ) AS duration ) FROM f Query B (combined): WITH f AS ( SELECT f.flight AS flight, s.job AS job, NULL as started, NULL as status, max(s.finished) AS max_finished FROM steps s JOIN flights f ON s.flight=3Df.flight WHERE s.job=3D? AND f.blessing=3D? AND f.branch=3D? AND s.finished IS NOT NULL AND f.started IS NOT NULL AND f.started >=3D ? GROUP BY f.flight, s.job ORDER BY max_finished DESC ) SELECT flight, max_finished, job, started, status, ( WITH tsteps AS ( SELECT * FROM steps WHERE flight=3Df.flight AND job=3Df.job ) , tsteps2 AS ( SELECT * FROM tsteps WHERE finished <=3D (SELECT finished FROM tsteps WHERE tsteps.testid =3D ?) ) SELECT ( SELECT max(finished)-min(started) FROM tsteps2 ) - ( SELECT sum(finished-started) FROM tsteps2 WHERE step =3D 'ts-hosts-allocate' ) AS duration ) FROM f Diff for query A: @@ -1,3 +1,4 @@ + WITH f AS ( SELECT f.flight AS flight, j.job AS job, f.started AS started, @@ -18,11 +19,14 @@ AND f.started >=3D ? ORDER BY f.started DESC + ) + SELECT flight, max_finished, job, started, status, + ( WITH tsteps AS ( SELECT * FROM steps - WHERE flight=3D? AND job=3D? + WHERE flight=3Df.flight AND job=3Df.job ) , tsteps2 AS ( @@ -42,3 +46,5 @@ WHERE step =3D 'ts-hosts-allocate' ) AS duration + + ) FROM f Diff for query B: @@ -1,3 +1,4 @@ + WITH f AS ( SELECT f.flight AS flight, s.job AS job, NULL as started, @@ -12,11 +13,14 @@ GROUP BY f.flight, s.job ORDER BY max_finished DESC + ) + SELECT flight, max_finished, job, started, status, + ( WITH tsteps AS ( SELECT * FROM steps - WHERE flight=3D? AND job=3D? + WHERE flight=3Df.flight AND job=3Df.job ) , tsteps2 AS ( @@ -36,3 +40,5 @@ WHERE step =3D 'ts-hosts-allocate' ) AS duration + + ) FROM f CC: George Dunlap Signed-off-by: Ian Jackson --- Osstest/Executive.pm | 31 ++++++++++++++++++++----------- 1 file changed, 20 insertions(+), 11 deletions(-) diff --git a/Osstest/Executive.pm b/Osstest/Executive.pm index 621153ee..66c93ab9 100644 --- a/Osstest/Executive.pm +++ b/Osstest/Executive.pm @@ -1192,7 +1192,7 @@ END ( SELECT * FROM steps - WHERE flight=3D? AND job=3D? + WHERE flight=3Df.flight AND job=3Df.job ) END_ALWAYS , tsteps2 AS @@ -1216,9 +1216,20 @@ END_UPTOINCL AS duration END_ALWAYS =09 - my $recentflights_q=3D $dbh_tests->prepare($recentflights_qtxt); - my $duration_anyref_q=3D $dbh_tests->prepare($duration_anyref_qtxt); - my $duration_duration_q =3D $dbh_tests->prepare($duration_duration_qtx= t); + my $prepare_combi =3D sub { + db_prepare(<($recentflights_qtxt); + my $duration_anyref_q=3D $prepare_combi->($duration_anyref_qtxt); =20 return sub { my ($job, $hostidname, $onhost, $uptoincl_testid) =3D @_; @@ -1239,14 +1250,16 @@ END_ALWAYS $branch, $job, $onhost, - $limit); + $limit, + @x_params); $refs=3D $recentflights_q->fetchall_arrayref({}); $recentflights_q->finish(); $dbg->("SAME-HOST GOT ".scalar(@$refs)); } =20 if (!@$refs) { - $duration_anyref_q->execute($job, $blessing, $branch, $limit); + $duration_anyref_q->execute($job, $blessing, $branch, $limit, + @x_params); $refs=3D $duration_anyref_q->fetchall_arrayref({}); $duration_anyref_q->finish(); $dbg->("ANY-HOST GOT ".scalar(@$refs)); @@ -1259,11 +1272,7 @@ END_ALWAYS =20 my $duration_max=3D 0; foreach my $ref (@$refs) { - my @d_d_args =3D ($ref->{flight}, $job); - push @d_d_args, @x_params; - $duration_duration_q->execute(@d_d_args); - my ($duration) =3D $duration_duration_q->fetchrow_array(); - $duration_duration_q->finish(); + my ($duration) =3D $ref->{duration}; if ($duration) { $dbg->("REF $ref->{flight} DURATION $duration ". ($ref->{status} // '')); --=20 2.20.1