Xen-Devel Archive on lore.kernel.org
 help / color / Atom feed
From: Ian Jackson <ian.jackson@eu.citrix.com>
To: xen-devel@lists.xenproject.org
Cc: Ian Jackson <ian.jackson@eu.citrix.com>
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> (raw)
In-Reply-To: <20200731113820.5765-1-ian.jackson@eu.citrix.com>

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 <ian.jackson@eu.citrix.com>
---
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.
 
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;
 
 sub mainquery () {
-    our $valcond = join " OR ", map { "val = ?" } keys %hosts;
-    our @params = keys %hosts;
-
     our $runvarq //= db_prepare(<<END);
-	SELECT flight, job, name, val, status
+	SELECT flight, job, name, status
 	  FROM runvars
           JOIN jobs USING (flight, job)
-	 WHERE $namecond
-	   AND ($valcond)
+	 WHERE (name = 'host' OR name LIKE '%\_host')
+	   AND val = ?
 	   AND $flightcond
            AND $restrictflight_cond
            AND flight > ?
 	 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);
 
-    push @params, $minflight;
-    push @params, scalar keys %hosts;
-
-    print DEBUG "MAINQUERY...\n";
-    $runvarq->execute(@params);
-
-    print DEBUG "FIRST PASS\n";
-    while (my $jr= $runvarq->fetchrow_hashref()) {
-	print DEBUG " $jr->{flight}.$jr->{job} ";
-	push @{ $hosts{$jr->{val}} }, $jr;
+	$hosts{$host} = $runvarq->fetchall_arrayref({});
+	print DEBUG "MAINQUERY $host got ".(scalar @{ $hosts{$host} })."\n";
     }
-    print DEBUG "\n";
 }
 
 sub reporthost ($) {
-- 
2.20.1



  parent reply index

Thread overview: 48+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2020-07-31 11:37 [OSSTEST PATCH v2 00/41] Performance work Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 01/41] Add cperl-indent-level to .dir-locals.el Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 02/41] SQL: Use "LIKE" rather than "like", etc Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 03/41] SQL: Fix incorrect LIKE pattern syntax (literals) Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 04/41] SQL: Fix incorrect LIKE pattern syntax (program variables) Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 05/41] sg-report-flight: Add a comment re same-flight search narrowing Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 06/41] sg-report-flight: Sort failures by job name as last resort Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 07/41] schema: Provide indices for sg-report-flight Ian Jackson
2020-07-31 14:21   ` George Dunlap
2020-07-31 14:55     ` Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 08/41] sg-report-flight: Ask the db for flights of interest Ian Jackson
2020-07-31 14:17   ` George Dunlap
2020-07-31 15:43     ` Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 09/41] sg-report-flight: Use WITH to use best index use for $flightsq Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 10/41] sg-report-flight: Use WITH clause to use index for $anypassq Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 11/41] sg-report-flight: Use the job row from the intitial query Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 12/41] Executive: Use index for report__find_test Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 13/41] duration_estimator: Ignore truncated jobs unless we know the step Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 14/41] duration_estimator: Introduce some _qtxt variables Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 15/41] duration_estimator: Explicitly provide null in general host q Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 16/41] duration_estimator: Return job column in first query Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 17/41] duration_estimator: Move $uptincl_testid to separate @x_params Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 18/41] duration_estimator: Move duration query loop into database Ian Jackson
2020-07-31 11:37 ` [OSSTEST PATCH v2 19/41] Executive: Drop redundant AND clause Ian Jackson
2020-07-31 14:17   ` George Dunlap
2020-07-31 11:37 ` [OSSTEST PATCH v2 20/41] schema: Add index for quick lookup by host Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 21/41] sg-report-host-history: Find flight limit by flight start date Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 22/41] sg-report-host-history: Drop per-job debug etc Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 23/41] Executive: Export opendb_tests Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 24/41] sg-report-host-history: Add a debug print after sorting jobs Ian Jackson
2020-07-31 11:38 ` Ian Jackson [this message]
2020-07-31 11:38 ` [OSSTEST PATCH v2 26/41] sg-report-host-history: Rerganisation: Make mainquery per-host Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 27/41] sg-report-host-history: Rerganisation: Read old logs later Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 28/41] sg-report-host-history: Rerganisation: Change loops Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 29/41] sg-report-host-history: Drop a redundznt AND clause Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 30/41] sg-report-host-history: Fork Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 31/41] schema: Add index to help cs-bisection-step Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 32/41] adhoc-revtuple-generator: Fix an undef warning in a debug print Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 33/41] cs-bisection-step: Generalise qtxt_common_rev_ok Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 34/41] cs-bisection-step: Move an AND Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 35/41] cs-bisection-step: Break out qtxt_common_ok Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 36/41] cs-bisection-step: Use db_prepare a few times instead of ->do Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 37/41] cs-bisection-step: temporary table: Insert only rows we care about Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 38/41] SQL: Change LIKE E'...\\_...' to LIKE '...\_...' Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 39/41] cs-bisection-step: Add a debug print when we run dot(1) Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 40/41] cs-bisection-step: Lay out the revision tuple graph once Ian Jackson
2020-07-31 11:38 ` [OSSTEST PATCH v2 41/41] duration_estimator: Clarify recentflights query a bit Ian Jackson
2020-07-31 14:04   ` George Dunlap

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20200731113820.5765-26-ian.jackson@eu.citrix.com \
    --to=ian.jackson@eu.citrix.com \
    --cc=xen-devel@lists.xenproject.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

Xen-Devel Archive on lore.kernel.org

Archives are clonable:
	git clone --mirror https://lore.kernel.org/xen-devel/0 xen-devel/git/0.git
	git clone --mirror https://lore.kernel.org/xen-devel/1 xen-devel/git/1.git

	# If you have public-inbox 1.1+ installed, you may
	# initialize and index your mirror using the following commands:
	public-inbox-init -V2 xen-devel xen-devel/ https://lore.kernel.org/xen-devel \
		xen-devel@lists.xenproject.org xen-devel@lists.xen.org
	public-inbox-index xen-devel

Example config snippet for mirrors

Newsgroup available over NNTP:
	nntp://nntp.lore.kernel.org/org.xenproject.lists.xen-devel


AGPL code for this site: git clone https://public-inbox.org/public-inbox.git