Ian Jackson [Mon, 10 Aug 2020 15:57:44 +0000 (16:57 +0100)]
history reporting (nfc): Provide cache_set_task_print
This takes a string which gets added to the cache messages. This
will allow us to distinguish the output from different processes
when using parallel by fork.
Nothing sets this yet.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Mon, 10 Aug 2020 15:10:50 +0000 (16:10 +0100)]
sg-report-job-history (nfc): Query hosts runvars in one go
Rather than doing one query for each entry in @hostvarcols, do one
query for all the relevant runvars. This is quite a bit faster and
will enable us to use the cache.
This is correct because @hostvarcols was the union of all the host
runvars, so this produces the same answers as the individual queries.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Mon, 10 Aug 2020 14:43:33 +0000 (15:43 +0100)]
sg-report-job-history: Refactor "ALL" handling
* Make an explicit entry ALL in @branches, rather than implicitly
processing ALL as well.
* Consequently, put explicit ALL entries in @tasks too, rather than
putting in entries without a branch name.
* Pass ALL to processjobbranch rather than undef, and turn it into
the internally-used undef at the start.
When used with --flight (findflight), this has no functional change.
When used with --job, ALL must now be included in the branch
list passed to --branches. The only in-tree call is with --flight.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Tue, 4 Aug 2020 16:23:18 +0000 (17:23 +0100)]
sg-report-job-history: Use one child per report
Rather than one child per job, which then did one report per branch.
This will mean we can use the cache machinery, which is rather global
so wouldn't cope well with processing multiple job history reports
within a process.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Thu, 6 Aug 2020 11:57:31 +0000 (12:57 +0100)]
parallel by fork: Disconnect $dbh_tests as well as undefing it
If the caller is buggy and has statement handles still open, they can
still "work" even if we have thrown away the db handle.
Where, after forking, "work" means "use the same connection in
multiple processes simultaneously, without locking". This could
result in arbitrary crazy nbehaviour (eg, TLS crypto failures).
No functional change with existing callers since they don't have this
bug.
Ian Jackson [Tue, 4 Aug 2020 13:41:09 +0000 (14:41 +0100)]
history reporting: Skip undefined keys
This makes it work if the caller's cached hash contains an key which
is bound to undef.
sg-report-host-history already does this, which currently causes:
Use of uninitialized value $_ in substitution (s///) at Osstest/HistoryReport.pm line 134.
Use of uninitialized value $_ in printf at Osstest/HistoryReport.pm line 135.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Tue, 4 Aug 2020 17:19:57 +0000 (18:19 +0100)]
history reporting (nfc): Bind by name in cacheable_query
cacheable_query used to simply pass $jr->{flight} and ->{job}. But we
want this to be reuseable for other kinds of query, with different
cache keys.
So bind by name: we expect the caller to use :name placeholders in the
query. We can then look through the prepared query parameters, and
fish the corresponding values out of $jr.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Wed, 5 Aug 2020 12:23:41 +0000 (13:23 +0100)]
history reporting (nfc): Refactor to generalise, cache_read_existing
* Introduce @cache_row_key_cols and cache_row_key
* Handle $html_file pathname construction and $read_existing
at the call site.
* Rename cache_read_existing.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Mon, 17 Aug 2020 17:45:43 +0000 (18:45 +0100)]
Tcl: Use tclsh8.6
This is needed to run on buster.
I have checked that tclsh8.6 and TclX works on osstest.test-lab. TclX
seems to be provided by tcl8.4 but work with tcl8.6 (at least on
buster).
Deployment note: hosts running earlier Debian (including
osstest.xs.citrite.net, the Citrix Cambridge instance), may need
OSSTEST_DAEMON_TCLSH=tclsh8.4 or similar in ~/.xen-osstest/settings.
Signed-off-by: Ian Jackson <Ian.Jackson@eu.citrix.com>
Ian Jackson [Mon, 10 Aug 2020 15:19:16 +0000 (16:19 +0100)]
SQL: Change "... LIKE '...\_...' ..." to "... LIKE '...\\_...' ..."
Perl's "" quotes, and corresponding <<END constructs, do
\-interpolation, so remove these \ which we carefully added in e7a408dd01184df0a57ae5d9072d15225c52a99f
SQL: Change LIKE E'...\\_...' to LIKE '...\_...'
This is only not a performance problem due to query mismathes with the
available indices, because the test indices are still present in the
Massachusetts instance.
I have verified that this has the intended chanve everywhere by
1. Double-checking that eacbh of these instances is within <<END
or "" or equivalent.
2. Running git-ls-files | xargs perl -i -pe 's/\\\\_/\\_/g'
and manually examining the diff against this patch's parent.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Jim Fehlig [Thu, 23 Jul 2020 21:31:34 +0000 (22:31 +0100)]
Install libtirpc-dev for libvirt builds
The check for XDR support was changed in libvirt commit d7147b3797
to use libtirpc pkg-config instead of complicated AC_CHECK_LIB,
AC_COMPILE_IFELSE, et. al. logic. The libvirt OSSTEST has been
failing since this change hit libvirt.git master. Fix it by adding
libtirpc-dev to the list of 'extra_packages' installed for libvirt
builds.
Signed-off-by: Jim Fehlig <jfehlig@suse.com> Reviewed-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Fri, 31 Jul 2020 10:43:55 +0000 (11:43 +0100)]
duration_estimator: Clarify recentflights query a bit
The condition on r.job is more naturally thought of as a join
condition than a where condition. (This is an inner join, so the
semantics are identical.)
Also, for clarity, swap the flight and job conditions round, so that
the ON clause is a series of r.thing = otherthing.
No functional change.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com> Reviewed-by: George Dunlap <George.Dunlap@citrix.com>
---
v3: Fix daft syntax error.
v2: New patch.
Ian Jackson [Fri, 24 Jul 2020 18:03:40 +0000 (19:03 +0100)]
cs-bisection-step: Use db_prepare a few times instead of ->do
With $dbh_tests->do(...), we can only get a debug trace of the queries
by using DBI_TRACE which produces voluminous output. Using our own
db_prepare invokes our own debugging.
No functional change.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
---
v2: New patch.
Ian Jackson [Mon, 27 Jul 2020 11:22:51 +0000 (12:22 +0100)]
cs-bisection-step: Generalise qtxt_common_rev_ok
* Make it into a subref which takes a $table argument.
* Change the two references into function calls using the @{...} syntax
* Move the definition earlier in the file
No change to the generated query.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
---
v2: New patch.
Ian Jackson [Fri, 24 Jul 2020 15:51:28 +0000 (16:51 +0100)]
sg-report-host-history: Rerganisation: Read old logs later
Perhaps 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.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
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
Ian Jackson [Tue, 21 Jul 2020 14:36:11 +0000 (15:36 +0100)]
duration_estimator: Move duration query loop into database
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=r.flight
AND r.name=?
WHERE j.job=r.job
AND f.blessing=?
AND f.branch=?
AND j.job=?
AND r.val=?
AND (j.status='pass' OR j.status='fail'
OR j.status='truncated'!)
AND f.started IS NOT NULL
AND f.started >= ?
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=f.flight
WHERE s.job=? AND f.blessing=? AND f.branch=?
AND s.finished IS NOT NULL
AND f.started IS NOT NULL
AND f.started >= ?
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=? AND job=?
)
, tsteps2 AS
(
SELECT *
FROM tsteps
WHERE finished <=
(SELECT finished
FROM tsteps
WHERE tsteps.testid = ?)
)
SELECT (
SELECT max(finished)-min(started)
FROM tsteps2
) - (
SELECT sum(finished-started)
FROM tsteps2
WHERE step = '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=r.flight
AND r.name=?
WHERE j.job=r.job
AND f.blessing=?
AND f.branch=?
AND j.job=?
AND r.val=?
AND (j.status='pass' OR j.status='fail'
OR j.status='truncated'!)
AND f.started IS NOT NULL
AND f.started >= ?
ORDER BY f.started DESC
)
SELECT flight, job, started, status,
(
WITH tsteps AS
(
SELECT *
FROM steps
WHERE flight=f.flight AND job=f.job
)
, tsteps2 AS
(
SELECT *
FROM tsteps
WHERE finished <=
(SELECT finished
FROM tsteps
WHERE tsteps.testid = ?)
)
SELECT (
SELECT max(finished)-min(started)
FROM tsteps2
) - (
SELECT sum(finished-started)
FROM tsteps2
WHERE step = '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=f.flight
WHERE s.job=? AND f.blessing=? AND f.branch=?
AND s.finished IS NOT NULL
AND f.started IS NOT NULL
AND f.started >= ?
GROUP BY f.flight, s.job
ORDER BY max_finished DESC
)
SELECT flight, job, started, status,
(
WITH tsteps AS
(
SELECT *
FROM steps
WHERE flight=f.flight AND job=f.job
)
, tsteps2 AS
(
SELECT *
FROM tsteps
WHERE finished <=
(SELECT finished
FROM tsteps
WHERE tsteps.testid = ?)
)
SELECT (
SELECT max(finished)-min(started)
FROM tsteps2
) - (
SELECT sum(finished-started)
FROM tsteps2
WHERE step = '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 >= ?
ORDER BY f.started DESC
+ )
+ SELECT flight, job, started, status,
+ (
WITH tsteps AS
(
SELECT *
FROM steps
- WHERE flight=? AND job=?
+ WHERE flight=f.flight AND job=f.job
)
, tsteps2 AS
(
@@ -42,3 +46,5 @@
WHERE step = '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, job, started, status,
+ (
WITH tsteps AS
(
SELECT *
FROM steps
- WHERE flight=? AND job=?
+ WHERE flight=f.flight AND job=f.job
)
, tsteps2 AS
(
@@ -36,3 +40,5 @@
WHERE step = 'ts-hosts-allocate'
)
AS duration
+
+ ) FROM f
Reviewed-by: George Dunlap <George.Dunlap@citrix.com> Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
---
v4: Drop max_finished from new SELECT clause.
This column is present in the query A just for the ORDER BY.
It is not used by the perl code which actually processe
these rows. Which is just as well because the query B doesn't
have this column, so with max_finished that query is broken.
Ian Jackson [Tue, 21 Jul 2020 14:32:33 +0000 (15:32 +0100)]
duration_estimator: Return job column in first query
Right now this is pointless since the Perl code doesn't need it. But
this row is going to be part of a WITH clause soon.
No functional change.
Diffs to two example queries (from the Perl DBI trace):
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=r.flight
AND r.name=?
WHERE j.job=r.job
AND f.blessing=?
AND f.branch=?
AND j.job=?
AND r.val=?
AND (j.status='pass' OR j.status='fail'
OR j.status='truncated'!)
AND f.started IS NOT NULL
AND f.started >= ?
ORDER BY f.started DESC
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=f.flight
WHERE s.job=? AND f.blessing=? AND f.branch=?
AND s.finished IS NOT NULL
AND f.started IS NOT NULL
AND f.started >= ?
- GROUP BY f.flight
+ GROUP BY f.flight, s.job
ORDER BY max_finished DESC
CC: George Dunlap <George.Dunlap@citrix.com> Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Tue, 21 Jul 2020 14:22:28 +0000 (15:22 +0100)]
duration_estimator: Explicitly provide null in general host q
Our spec. says we return nulls for started and status if we don't find
a job matching the host spec.
The way this works right now is that we look up the nonexistent
entries in $refs->[0]. This is not really brilliant and is going to
be troublesome as we continue to refactor.
Provide these values explicitly. No functional change.
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
Ian Jackson [Mon, 20 Jul 2020 16:36:54 +0000 (17:36 +0100)]
Executive: Use index for report__find_test
After we refactor this query then we can enable the index use.
(Both of these things together in this commit because I haven't perf
tested the version with just the refactoring.)
(We have provided an index that can answer this question really
quickly if a version is specified. But the query planner couldn't see
that because it works without seeing the bind variables, so doesn't
know that the value of name is going to be suitable for this index.)
* Convert the two EXISTS subqueries into JOIN/AND with a DISTINCT
clause naming the fields on flights, so as to replicate the previous
result rows. Then do $selection field last. The subquery is a
convenient way to let this do the previous thing for all the values
of $selection (including, notably, *).
* Add the additional AND clause for r.name, which has no logical
effect given the actual values of name, enabling the query planner
to use this index.
Perf: In my test case the sg-report-flight runtime is now ~8s. I am
reasonably confident that this will not make other use cases of this
code worse.
Perf: runtime of my test case now ~11s
Example query before (from the Perl DBI trace):
SELECT *
FROM flights f
WHERE
EXISTS (
SELECT 1
FROM runvars r
WHERE name=?
AND val=?
AND r.flight=f.flight
AND ( (CASE
WHEN (r.job) LIKE 'build-%-prev' THEN 'xprev'
WHEN ((r.job) LIKE 'build-%-freebsd'
AND 'x' = 'freebsdbuildjob') THEN 'DISCARD'
ELSE ''
END)
= '')
)
AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
AND (branch=?)
ORDER BY flight DESC
LIMIT 1
After:
SELECT *
FROM ( SELECT DISTINCT
flight, started, blessing, branch, intended
FROM flights f
JOIN runvars r USING (flight)
WHERE name=?
AND name LIKE 'revision\_%'
AND val=?
AND r.flight=f.flight
AND ( (CASE
WHEN (r.job) LIKE 'build-%-prev' THEN 'xprev'
WHEN ((r.job) LIKE 'build-%-freebsd'
AND 'x' = 'freebsdbuildjob') THEN 'DISCARD'
ELSE ''
END)
= '')
AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
AND (branch=?)
) AS sub WHERE TRUE
ORDER BY flight DESC
LIMIT 1
@@ -1,10 +1,10 @@
SELECT *
+ FROM ( SELECT DISTINCT
+ flight, started, blessing, branch, intended
FROM flights f
- WHERE
- EXISTS (
- SELECT 1
- FROM runvars r
+ JOIN runvars r USING (flight)
WHERE name=?
+ AND name LIKE 'revision\_%'
AND val=?
AND r.flight=f.flight
AND ( (CASE
@@ -14,8 +14,8 @@
ELSE ''
END)
= '')
- )
AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
AND (branch=?)
+) AS sub WHERE TRUE
ORDER BY flight DESC
LIMIT 1
Reviewed-by: George Dunlap <George.Dunlap@citrix.com> Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
---
v2: Use proper \ escaping for underscores in LIKE
projects / osstest.git / log