From nobody Mon Feb 9 14:37:48 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=1596196907; cv=none; d=zohomail.com; s=zohoarc; b=Kao4E97EXUlHn23D7qX2E9LLLEq6bXMv7sAeW0gvmI2eiRGY3qSHbv5iIg2aeTSD+Xb7Ci178/PLPo+if6dZ+PmKdUNdKCGz5xVYBqVJShvcqs58OMDoQ2eerh+oKPqDd1Isnhemy+iu+r1RJCIZBabjWYdKMwb++5cny0c26r4= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1596196907; 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=ri4jaXBUHrFflgSrLbWZI8QG4Lwg+r81yaLZb3cP1fA=; b=Fm8LnjpEAbKoLywSV132sX/6SpSYhaYP0HDYNU5pAGscnyPP88bAUVBIJh20SsbNazfHnlKLnvTubnSW2S2KbLqU4KOury7mxMTuanVyZxfciS4RKLYh90tV+UA+2JMQPQ5GD/pvmCgziEFbL20lskDaaoiRAvIpSMbVmBqKoPo= 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 1596196907647834.3134580067485; Fri, 31 Jul 2020 05:01: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 1k1Tj7-0001cQ-Ag; Fri, 31 Jul 2020 12:01:29 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1Tj6-0001R9-3c for xen-devel@lists.xenproject.org; Fri, 31 Jul 2020 12:01:28 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 8fd671a2-d325-11ea-8e2b-bc764e2007e4; Fri, 31 Jul 2020 12:01:27 +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 1k1TMz-0001W4-DE; Fri, 31 Jul 2020 12:38:37 +0100 X-Inumbo-ID: 8fd671a2-d325-11ea-8e2b-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH v2 25/41] sg-report-host-history: Do the main query per host Date: Fri, 31 Jul 2020 12:38:04 +0100 Message-Id: <20200731113820.5765-26-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 Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" In f6001d628c3b3fd42b10cd15351981a04bc02572 we combined these queries into one: sg-report-host-history: Aggregate runvars query for all hosts Now that we have an index, there is a faster way for the db to do this query: via that index. But it doesn't like to do that if be aggregate the queries. Experimentally, doing this query separately once per host is significantly faster. Also, later, it will allow us to parallelise this work. So, we undo that. (Not by reverting, though.) Signed-off-by: Ian Jackson --- v2: Use proper \ escaping for underscores in LIKE --- schema/runvars-host-index.sql | 2 +- sg-report-host-history | 27 +++++++++------------------ 2 files changed, 10 insertions(+), 19 deletions(-) diff --git a/schema/runvars-host-index.sql b/schema/runvars-host-index.sql index 222a0a30..6a3ef377 100644 --- a/schema/runvars-host-index.sql +++ b/schema/runvars-host-index.sql @@ -1,4 +1,4 @@ --- ##OSSTEST## 009 Preparatory +-- ##OSSTEST## 009 Needed -- -- This index helps sg-report-host-history find relevant flights. =20 diff --git a/sg-report-host-history b/sg-report-host-history index 1c2d19ae..15866ab6 100755 --- a/sg-report-host-history +++ b/sg-report-host-history @@ -165,34 +165,25 @@ sub jobquery ($$$) { our %hosts; =20 sub mainquery () { - our $valcond =3D join " OR ", map { "val =3D ?" } keys %hosts; - our @params =3D keys %hosts; - our $runvarq //=3D db_prepare(< ? ORDER BY flight DESC - LIMIT ($limit * 3 + 100) * ? + LIMIT $limit * 2 END + foreach my $host (sort keys %hosts) { + print DEBUG "MAINQUERY $host...\n"; + $runvarq->execute($host, $minflight); =20 - push @params, $minflight; - push @params, scalar keys %hosts; - - print DEBUG "MAINQUERY...\n"; - $runvarq->execute(@params); - - print DEBUG "FIRST PASS\n"; - while (my $jr=3D $runvarq->fetchrow_hashref()) { - print DEBUG " $jr->{flight}.$jr->{job} "; - push @{ $hosts{$jr->{val}} }, $jr; + $hosts{$host} =3D $runvarq->fetchall_arrayref({}); + print DEBUG "MAINQUERY $host got ".(scalar @{ $hosts{$host} })."\n"; } - print DEBUG "\n"; } =20 sub reporthost ($) { --=20 2.20.1