Improve performance of sg-report-host-history
cover Improve performance of sg-report-host-history
This is reasonably straightforward.
In my tests it reduces the time for an incremental run, with a hot cache, from several hundred seconds to a handful of seconds.
This series really wants to go after the flight report improvements, although the schema updates could be combined.
Ian Jackson (11):
schema: Add index for quick lookup by host
sg-report-host-history: Find flight limit by flight start date
sg-report-host-history: Drop per-job debug etc.
Executive: Export opendb_tests
sg-report-host-history: Add a debug print after sorting jobs
sg-report-host-history: Do the main query per host
sg-report-host-history: Rerganisation: Make mainquery per-host
sg-report-host-history: Rerganisation: Read old logs later
sg-report-host-history: Rerganisation: Change loops
sg-report-host-history: Drop a redundznt AND clause
sg-report-host-history: Fork
Osstest/Executive.pm | 2 +- schema/runvars-host-index.sql | 8 ++ sg-report-host-history | 152 +++++++++++++++++++--------------- 3 files changed, 94 insertions(+), 68 deletions(-) create mode 100644 schema/runvars-host-index.sql
Signature · 1 line
2.20.1
PATCH 1/11 schema: Add index for quick lookup by host
--- schema/runvars-host-index.sql | 8 ++++++++ 1 file changed, 8 insertions(+) create mode 100644 schema/runvars-host-index.sql
schema/runvars-host-index.sql new file +8 -0
@@ -0,0 +1,8 @@ +-- ##OSSTEST## 009 Preparatory +-- +-- This index helps sg-report-host-history find relevant flights. + +CREATE INDEX runvars_host_idx + ON runvars (val, flight) + WHERE name ='host' + OR name LIKE '%_host';
Signature · 1 line
2.20.1
PATCH 2/11 sg-report-host-history: Find flight limit by flight start date
In reply to Ian JacksonBy default we look for anything in (roughly) the last year.
This query is in fact quite fast because the flights table is small.
There is still the per-host limit of $limit (2000) recent runs.
--- sg-report-host-history | 56 ++++++++++++++++++++---------------------- 1 file changed, 27 insertions(+), 29 deletions(-)
sg-report-host-history +27 -29
@@ -29,6 +29,7 @@ use POSIX; use Osstest::Executive qw(:DEFAULT :colours); our $limit= 2000; +our $timelimit= 86400 * (366 + 14); our $flightlimit; our $htmlout = "."; our $read_existing=1;
@@ -45,6 +46,8 @@ while (@ARGV && $ARGV[0] =~ m/^-/) { last if m/^--?$/; if (m/^--(limit)\=([1-9]\d*)$/) { $$1= $2; + } elsif (m/^--time-limit\=([1-9]\d*)$/) { + $timelimit= $1; } elsif (m/^--flight-limit\=([1-9]\d*)$/) { $flightlimit= $1; } elsif (restrictflight_arg($_)) {
@@ -108,38 +111,33 @@ sub read_existing_logs ($) { } sub computeflightsrange () { - if (!$flightlimit) { - my $flagscond = - '('.join(' OR ', map { "f.hostflag = 'blessed-$_'" } @blessings).')'; - my $nhostsq = db_prepare(<<END); - SELECT count(*) - FROM resources r - WHERE restype='host' - AND EXISTS (SELECT 1 - FROM hostflags f - WHERE f.hostname=r.resname - AND $flagscond) + if ($flightlimit) { + my $minflightsq = db_prepare(<<END); + SELECT flight + FROM ( + SELECT flight + FROM flights + WHERE $restrictflight_cond + ORDER BY flight DESC + LIMIT $flightlimit + ) f + ORDER BY flight ASC + LIMIT 1 END - $nhostsq->execute(); - my ($nhosts) = $nhostsq->fetchrow_array(); - print DEBUG "COUNTED $nhosts hosts\n"; - $flightlimit = $nhosts * $limit * 2; - } - - my $minflightsq = db_prepare(<<END); - SELECT flight - FROM ( + $minflightsq->execute(); + ($minflight,) = $minflightsq->fetchrow_array(); + } else { + my $minflightsq = db_prepare(<<END); SELECT flight - FROM flights - WHERE $restrictflight_cond - ORDER BY flight DESC - LIMIT $flightlimit - ) f - ORDER BY flight ASC - LIMIT 1 + FROM flights + WHERE started >= ? + ORDER BY flight ASC + LIMIT 1 END - $minflightsq->execute(); - ($minflight,) = $minflightsq->fetchrow_array(); + my $now = time // die $!; + $minflightsq->execute($now - $timelimit); + ($minflight,) = $minflightsq->fetchrow_array(); + } $minflight //= 0; $flightcond = "(flight > $minflight)";
Signature · 1 line
2.20.1
PATCH 3/11 sg-report-host-history: Drop per-job debug etc.
In reply to Ian JacksonThis printing has a significant effect on the performance of this program, at least after we optimise various other things.
--- sg-report-host-history | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-)
sg-report-host-history +7 -7
@@ -102,9 +102,9 @@ sub read_existing_logs ($) { my $k = $1; s{\%([0-9a-f]{2})}{ chr hex $1 }ge; $ch->{$k} = $_; - print DEBUG "GOTCACHE $hostname $k\n"; +# print DEBUG "GOTCACHE $hostname $k\n"; } - print DEBUG "GOTCACHE $hostname \@ $jr->{flight} $jr->{job} $jr->{status},$jr->{name}\n"; +# print DEBUG "GOTCACHE $hostname \@ $jr->{flight} $jr->{job} $jr->{status},$jr->{name}\n"; $tcache->{$jr->{flight},$jr->{job},$jr->{status},$jr->{name}} = $jr; } close H;
@@ -272,7 +272,7 @@ END my @rows; my $cachehits = 0; foreach my $jr (@$inrows) { - print DEBUG "JOB $jr->{flight}.$jr->{job} "; + #print DEBUG "JOB $jr->{flight}.$jr->{job} "; my $cacherow = $tcache->{$jr->{flight},$jr->{job},$jr->{status},$jr->{name}};
@@ -283,11 +283,11 @@ END my $endedrow = jobquery($endedq, $jr, 'e'); if (!$endedrow) { - print DEBUG "no-finished\n"; + #print DEBUG "no-finished\n"; next; } - print DEBUG join " ", map { $endedrow->{$_} } sort keys %$endedrow; - print DEBUG ".\n"; + #print DEBUG join " ", map { $endedrow->{$_} } sort keys %$endedrow; + #print DEBUG ".\n"; push @rows, { %$jr, %$endedrow }; }
@@ -329,7 +329,7 @@ END next; } - print DEBUG "JR $jr->{flight}.$jr->{job}\n"; + #print DEBUG "JR $jr->{flight}.$jr->{job}\n"; my $ir = jobquery($infoq, $jr, 'i'); my $ar = jobquery($allocdq, $jr, 'a'); my $ident = $jr->{name};
Signature · 1 line
2.20.1
PATCH 4/11 Executive: Export opendb_tests
In reply to Ian Jacksonsg-report-host-history is going to want this in a moment
--- Osstest/Executive.pm | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
Osstest/Executive.pm +1 -1
@@ -49,7 +49,7 @@ BEGIN { task_spec_desc findtask findtask_spec @all_lock_tables restrictflight_arg restrictflight_cond report_run_getinfo report_altcolour - report_altchangecolour + report_altchangecolour opendb_tests report_blessingscond report_find_push_age_info tcpconnect_queuedaemon plan_search manual_allocation_base_jobinfo
Signature · 1 line
2.20.1
PATCH 5/11 sg-report-host-history: Add a debug print after sorting jobs
In reply to Ian JacksonThis helps rule this sort out as a source of slowness.
--- sg-report-host-history | 2 ++ 1 file changed, 2 insertions(+)
sg-report-host-history +2 -0
@@ -318,6 +318,8 @@ END @rows = sort { $b->{finished} <=> $a->{finished} } @rows; + print DEBUG "SORTED\n"; + my $alternate = 0; my $wrote = 0; my $runvarq_hits = 0;
Signature · 1 line
2.20.1
PATCH 6/11 sg-report-host-history: Do the main query per host
In reply to Ian JacksonIn 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.)
--- schema/runvars-host-index.sql | 2 +- sg-report-host-history | 27 +++++++++------------------ 2 files changed, 10 insertions(+), 19 deletions(-)
schema/runvars-host-index.sql +1 -1
@@ -1,4 +1,4 @@ --- ##OSSTEST## 009 Preparatory +-- ##OSSTEST## 009 Needed -- -- This index helps sg-report-host-history find relevant flights.
sg-report-host-history +9 -18
@@ -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 ($) {
Signature · 1 line
2.20.1
PATCH 7/11 sg-report-host-history: Rerganisation: Make mainquery per-host
In reply to Ian JacksonThis moves the loop over hosts into the main program. We are working our way to a new code structure.
No functional change.
--- sg-report-host-history | 19 +++++++++++-------- 1 file changed, 11 insertions(+), 8 deletions(-)
sg-report-host-history +11 -8
@@ -164,7 +164,9 @@ sub jobquery ($$$) { our %hosts; -sub mainquery () { +sub mainquery ($) { + my ($host) = @_; + our $runvarq //= db_prepare(<<END); SELECT flight, job, name, status FROM runvars
@@ -177,13 +179,12 @@ sub mainquery () { ORDER BY flight DESC LIMIT $limit * 2 END - foreach my $host (sort keys %hosts) { - print DEBUG "MAINQUERY $host...\n"; - $runvarq->execute($host, $minflight); - $hosts{$host} = $runvarq->fetchall_arrayref({}); - print DEBUG "MAINQUERY $host got ".(scalar @{ $hosts{$host} })."\n"; - } + print DEBUG "MAINQUERY $host...\n"; + $runvarq->execute($host, $minflight); + + $hosts{$host} = $runvarq->fetchall_arrayref({}); + print DEBUG "MAINQUERY $host got ".(scalar @{ $hosts{$host} })."\n"; } sub reporthost ($) {
@@ -473,7 +474,9 @@ db_retry($dbh_tests, [], sub { }); db_retry($dbh_tests, [], sub { - mainquery(); + foreach my $host (sort keys %hosts) { + mainquery($host); + } }); foreach my $host (sort keys %hosts) {
Signature · 1 line
2.20.1
PATCH 8/11 sg-report-host-history: Rerganisation: Read old logs later
In reply to Ian JacksonPerhaps at one point something read from these logs influenced the db query for thye flights range, but that is no longer the case and it doesn't seem likely to need to come back.
We want to move the per-host stuff together.
No functional change.
--- sg-report-host-history | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-)
sg-report-host-history +4 -4
@@ -465,14 +465,14 @@ END exit 0 unless %hosts; -foreach (keys %hosts) { - read_existing_logs($_); -} - db_retry($dbh_tests, [], sub { computeflightsrange(); }); +foreach (keys %hosts) { + read_existing_logs($_); +} + db_retry($dbh_tests, [], sub { foreach my $host (sort keys %hosts) { mainquery($host);
Signature · 1 line
2.20.1
PATCH 9/11 sg-report-host-history: Rerganisation: Change loops
In reply to Ian JacksonMove the per-host code all into the same per-host loop. One effect is to transpose the db_retry and host loops for mainquery.
No functional change.
--- sg-report-host-history | 12 ++---------- 1 file changed, 2 insertions(+), 10 deletions(-)
sg-report-host-history +2 -10
@@ -469,18 +469,10 @@ db_retry($dbh_tests, [], sub { computeflightsrange(); }); -foreach (keys %hosts) { - read_existing_logs($_); -} - -db_retry($dbh_tests, [], sub { - foreach my $host (sort keys %hosts) { - mainquery($host); - } -}); - foreach my $host (sort keys %hosts) { + read_existing_logs($host); db_retry($dbh_tests, [], sub { + mainquery($host); reporthost $host; }); }
Signature · 1 line
2.20.1
PATCH 10/11 sg-report-host-history: Drop a redundznt AND clause
In reply to Ian JacksonThis condition is the same as $flightcond. (This has no effect on the db performance since the query planner figures it out, but it is confusing.)
--- sg-report-host-history | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-)
sg-report-host-history +1 -2
@@ -175,13 +175,12 @@ sub mainquery ($) { AND val = ? AND $flightcond AND $restrictflight_cond - AND flight > ? ORDER BY flight DESC LIMIT $limit * 2 END print DEBUG "MAINQUERY $host...\n"; - $runvarq->execute($host, $minflight); + $runvarq->execute($host); $hosts{$host} = $runvarq->fetchall_arrayref({}); print DEBUG "MAINQUERY $host got ".(scalar @{ $hosts{$host} })."\n";
Signature · 1 line
2.20.1
PATCH 11/11 sg-report-host-history: Fork
In reply to Ian JacksonRun each host's report in a separate child. This is considerably faster.
--- sg-report-host-history | 47 +++++++++++++++++++++++++++++++++++------- 1 file changed, 40 insertions(+), 7 deletions(-)
sg-report-host-history +40 -7
@@ -34,6 +34,7 @@ our $flightlimit; our $htmlout = "."; our $read_existing=1; our $doinstall=1; +our $maxjobs=10; our @blessings; open DEBUG, ">/dev/null";
@@ -44,7 +45,7 @@ csreadconfig(); while (@ARGV && $ARGV[0] =~ m/^-/) { $_= shift @ARGV; last if m/^--?$/; - if (m/^--(limit)\=([1-9]\d*)$/) { + if (m/^--(limit|maxjobs)\=([1-9]\d*)$/) { $$1= $2; } elsif (m/^--time-limit\=([1-9]\d*)$/) { $timelimit= $1;
@@ -468,12 +469,44 @@ db_retry($dbh_tests, [], sub { computeflightsrange(); }); +undef $dbh_tests; + +our %children; +our $worst = 0; + +sub wait_for_max_children ($) { + my ($lim) = @_; + while (keys(%children) > $lim) { + $!=0; $?=0; my $got = wait; + die "$! $got $?" unless exists $children{$got}; + my $host = $children{$got}; + delete $children{$got}; + $worst = $? if $? > $worst; + if ($?) { + print STDERR "sg-report-flight[: [$got] failed for $host: $?\n"; + } else { + print DEBUG "REAPED [$got] $host\n"; + } + } +} + foreach my $host (sort keys %hosts) { - read_existing_logs($host); - db_retry($dbh_tests, [], sub { - mainquery($host); - reporthost $host; - }); + wait_for_max_children($maxjobs); + + my $pid = fork // die $!; + if (!$pid) { + opendb_tests(); + read_existing_logs($host); + db_retry($dbh_tests, [], sub { + mainquery($host); + reporthost $host; + }); + print DEBUG "JQ CACHE ".($jqtotal-$jqcachemisses)." / $jqtotal\n"; + exit(0); + } + print DEBUG "SPAWNED [$pid] $host\n"; + $children{$pid} = $host; } -print DEBUG "JQ CACHE ".($jqtotal-$jqcachemisses)." / $jqtotal\n"; +wait_for_max_children(0); +exit $worst;
Signature · 1 line
2.20.1