From nobody Mon Feb 9 09:34:19 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=1596197585; cv=none; d=zohomail.com; s=zohoarc; b=E22SWBC5JVRbg+pmYfKElU54bSP4qEU6QFQdEJbYk0BekT7+IBhbu8dPvvUKOQu3uyIYvOAIUzMDKC5Xj3H9KiZHRAKDEmZVWwep5iqn5Guq6NYAE09/Aw2Znky5jBmQxxFQ42Qif9fq1TphhFwPkqdbTauyZCANTQonACBOTCk= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1596197585; 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=gz8iLvjXjYgOL1IAL0xayLO1YRaBB5aeoWBaBjpybsE=; b=YmM/JBWxNJEGWScndaO6zmlkeeGkkADosRyO5CYqWgiusaq3CxX91FLT1+Qezk+f6Krnx65c9PD2TQuFPyR7OU6HmFjLP67OhqX9jFSvoKAl9DrZVIrxlvK30ECdISpFE4nxrjDvwdmc9Y+c+GIaJt3Hkmk/pnKK+DnyDuq1BXc= 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 1596197585485366.48930840552975; Fri, 31 Jul 2020 05:13: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 1k1Ttz-00047x-25; Fri, 31 Jul 2020 12:12:43 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1Tty-00047s-0v for xen-devel@lists.xenproject.org; Fri, 31 Jul 2020 12:12:42 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 2140508a-d327-11ea-8e2c-bc764e2007e4; Fri, 31 Jul 2020 12:12:40 +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 1k1TMv-0001W4-2L; Fri, 31 Jul 2020 12:38:33 +0100 X-Inumbo-ID: 2140508a-d327-11ea-8e2c-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH v2 18/41] duration_estimator: Move duration query loop into database Date: Fri, 31 Jul 2020 12:37:57 +0100 Message-Id: <20200731113820.5765-19-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: 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 Reviewed-by: 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 fb975dac..684cafc3 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