openQA Project (public)

What mkittler, nsinger and me found is

openqa=> \timing
Timing is on.
openqa=> with mm_jobs as (select distinct id, result from jobs left join job_dependencies on (id = child_job_id or id = parent_job_id) where dependency = 2) select result, round(count(id) * 100. / (select count(id) from mm_jobs), 2)::numeric(5,2)::float as ratio from mm_jobs group by mm_jobs.result order by ratio desc;
       result       | ratio 
--------------------+-------
 obsoleted          | 44.56
 passed             | 34.02
 skipped            |  6.54
 parallel_failed    |  5.65
 parallel_restarted |  3.01
 failed             |  2.28
 softfailed         |  2.18
 incomplete         |  1.41
 none               |  0.16
 user_cancelled     |   0.1
 timeout_exceeded   |  0.09
 user_restarted     |  0.01
(12 rows)

Time: 3866.327 ms (00:03.866)

which is nice.

We can filter by a shorter time to speedup, e.g.

with mm_jobs as (select distinct id, result from jobs left join job_dependencies on (id = child_job_id or id = parent_job_id) where t_created >= (select now() at time zone 'utc' - interval '1 hour') and dependency = 2) select result, round(count(id) * 100. / (select count(id) from mm_jobs), 2)::numeric(5,2)::float as ratio from mm_jobs group by mm_jobs.result order by ratio desc;
 result  | ratio 
---------+-------
 none    | 99.14
 skipped |  0.86
(2 rows)

keep in mind that we already did select now() in other cases which causes wrong results for postgreSQL. So we should fix that to select now() at time zone 'utc'

https://gitlab.suse.de/openqa/salt-states-openqa/-/merge_requests/583 with fixes and new panels and alerts.

Next steps or ideas for the future:

• Make colors a bit more compatible to the other panels
• Add additional long-time queries for long-time statistics, e.g. all jobs, not limited to time-window and include in high-level views, e.g. on index page of grafana -> Understand general team performances
• Group by workers (and job groups and machines and archs?) and according alerts -> Find misbehaving workers (overall fail-rate as well as multi-machine specific fail-rate)

okurz wrote:

• Add additional long-time queries for long-time statistics, e.g. all jobs, not limited to time-window and include in high-level views, e.g. on index page of grafana -> Understand general team performances

Created https://gitlab.suse.de/openqa/salt-states-openqa/-/merge_requests/585 to collect the data. After acceptance needs to be included in grafana

• Group by workers (and job groups and machines and archs?) and according alerts -> Find misbehaving workers (overall fail-rate as well as multi-machine specific fail-rate)

I wonder if it's a good idea trying to do a single query including everything, e.g.

select result, job_groups.name, machine, host, round(count(jobs.id) * 100. / (select count(jobs.id) from jobs), 2)::numeric(5,2)::float as ratio_all_long_term from jobs left join job_groups on jobs.group_id = job_groups.id left join workers on jobs.assigned_worker_id = workers.id group by result, job_groups.name, machine, host;

taking roughly 5s on osd, returning 14k rows. But I don't know if we can afterwards sum up in grafana, otherwise we would have 14k different values which all round to 0.00 or something. I guess separate queries are required here?

For the long-term statistics which I configured to run only every 24h there is no data yet. Maybe the first time will only be triggered after 24h, not initially on service start. Waiting …

running out of patience. I manually changed the interval on osd from 24h to 2m, collected some samples, created grafana panels and created https://gitlab.suse.de/openqa/salt-states-openqa/-/merge_requests/587