Re: Recording test runtimes with the buildfarm

On Fri, 12 Jun 2020 at 04:32, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > On Thu, Jun 11, 2020 at 4:56 PM Andrew Dunstan <
> > It seems pretty trivial to for example get all the steps out of check.log
> > and their timing with a regexp.
>
> Yeah, I don't see why we can't scrape this data from the existing
> buildfarm output, at least for the core regression tests. New
> infrastructure could make it easier/cheaper, but I don't think we
> should invest in that until we've got a provably useful tool.

Looking at the data mentioned in the logs for install-check-C, it
seems some animals are quite stable with their timings but others are
quite unstable.

Taking the lowest half of the timings of each animal,test combination,
where the animal ran the test at least 50 times, the top 3 animals
with the most consistent timing are.

postgres=# select animal, avg(stddev) from (select animal,testname,
stddev(ms) from (select
animal,testname,unnest(ar[:array_length(ar,1)/2]) ms from (select
animal,testname,array_agg(ms order by ms) ar from run_details group by
animal,testname) a)b group by 1,2 having count(*) > 50) c group by 1
order by avg(stddev) limit 3;
animal | avg
------------+-------------------
mule | 4.750935819647279
massasauga | 5.410419286413067
eider | 6.06834118301505
(3 rows)

And the least consistent 3 are:

postgres=# select animal, avg(stddev) from (select animal,testname,
stddev(ms) from (select
animal,testname,unnest(ar[:array_length(ar,1)/2]) ms from (select
animal,testname,array_agg(ms order by ms) ar from run_details group by
animal,testname) a)b group by 1,2 having count(*) > 50) c group by 1
order by avg(stddev) desc limit 3;
animal | avg
----------+-------------------
lorikeet | 830.9292062818336
gharial | 725.9874198764217
dory | 683.5182140482121
(3 rows)

If I look at a random test from mule:

postgres=# select commit,time,result,ms from run_details where animal
= 'mule' and testname = 'join' order by time desc limit 10;
commit | time | result | ms
---------+---------------------+--------+-----
e532b1d | 2020-06-15 19:30:03 | ok | 563
7a3543c | 2020-06-15 15:30:03 | ok | 584
3baa7e3 | 2020-06-15 11:30:03 | ok | 596
47d4d0c | 2020-06-15 07:30:03 | ok | 533
decbe2b | 2020-06-14 15:30:04 | ok | 535
378badc | 2020-06-14 07:30:04 | ok | 563
23cbeda | 2020-06-13 19:30:04 | ok | 557
8f5b596 | 2020-06-13 07:30:04 | ok | 553
6472572 | 2020-06-13 03:30:04 | ok | 580
9a7fccd | 2020-06-12 23:30:04 | ok | 561
(10 rows)

and from lorikeet:

postgres=# select commit,time,result,ms from run_details where animal
= 'lorikeet' and testname = 'join' order by time desc limit 10;
commit | time | result | ms
---------+---------------------+--------+------
47d4d0c | 2020-06-15 08:28:35 | ok | 8890
decbe2b | 2020-06-14 20:28:33 | ok | 8878
378badc | 2020-06-14 08:28:35 | ok | 8854
cc07264 | 2020-06-14 05:22:59 | ok | 8883
8f5b596 | 2020-06-13 10:36:14 | ok | 8942
2f48ede | 2020-06-12 20:28:41 | ok | 8904
ffd2582 | 2020-06-12 08:29:52 | ok | 2016
7aa4fb5 | 2020-06-11 23:21:26 | ok | 1939
ad9291f | 2020-06-11 09:56:48 | ok | 1924
c2bd1fe | 2020-06-10 23:01:42 | ok | 1873
(10 rows)

I can supply the data I used for this, just send me an offlist email.
It's about 19MB using bzip2.

I didn't look at the make check data and I do see some animals use the
parallel_schedule for make installcheck, which my regex neglected to
account for, so that data is missing from the set.

David