Re: Recording test runtimes with the buildfarm - Mailing list pgsql-hackers

On Fri, 12 Jun 2020 at 04:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Magnus Hagander <magnus@hagander.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