[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Xen-devel] [OSSTEST PATCH 2/6] sg-report-job-history: Avoid full runvars table scan (!)

sg-report-job-history wnats to know the potential names of runvars
relating to hosts.  To do this it tries to find a list of distinct
runvar names which exist in the flights it's processing.

However, it fails to limit the runvar query appropriately, and as a
result postgresql must scan almost the complete runvars table to
produce an answer.  This is very slow if the table is bigger than the
database server's RAM.

Fix this by limiting the runvars table query to relevant flights.


 * Break the `100' from the LIMIT clause on the flights search
   into a local variable $limit.
 * Break the bulk of the flights search sql statement text into
   a local variable $fromstuff.
 * In the runvars statement, add a condition on flights which uses
   LIMIT and OFFSET, based on results of the the flights query.

Signed-off-by: Ian Jackson <Ian.Jackson@xxxxxxxxxxxxx>
 sg-report-job-history |   17 +++++++++++++----
 1 file changed, 13 insertions(+), 4 deletions(-)

diff --git a/sg-report-job-history b/sg-report-job-history
index e7052a3..d59717f 100755
--- a/sg-report-job-history
+++ b/sg-report-job-history
@@ -163,12 +163,17 @@ sub processjobbranch ($$) {
         push @params, $bra;
-    my $flightsq= $dbh_tests->prepare(<<END);
-        SELECT *
+    my $limit= 100;
+    my $offset= $limit-1;
+    my $fromstuff= <<END;
           FROM jobs JOIN flights USING (flight)
          WHERE ($cond)
       ORDER BY flight DESC
-         LIMIT 100
+    my $flightsq= $dbh_tests->prepare(<<END);
+        SELECT * $fromstuff
+         LIMIT $limit
@@ -177,9 +182,13 @@ END
         FROM runvars
         JOIN flights USING (flight)
        WHERE ($cond)
+         AND flight >= (
+             SELECT flight $fromstuff
+             LIMIT 1 OFFSET $offset
+          )
      ORDER BY name;
-    $hostsq->execute(@params);
+    $hostsq->execute(@params, @params);
     my @hostvarcols;
     while (my ($hostvar) = $hostsq->fetchrow_array()) {
        next unless $hostvar =~ m/(^|_)host$/;

Xen-devel mailing list



Lists.xenproject.org is hosted with RackSpace, monitoring our
servers 24x7x365 and backed by RackSpace's Fanatical Support®.