QA » openQA Project

My last comment was lost, here just the SQL queries I've been mentioning:

select group_id, (select concat_ws('/', (select name from job_group_parents where id = parent_id), name) from job_groups where id = group_id) as group_name, sum(result_size) as result_size from jobs group by group_id order by group_id;

select group_id, (select concat_ws('/', (select name from job_group_parents where id = parent_id), name) from job_groups where id = group_id) as group_name, (sum(result_size) / 1024 / 1024 / 1024) as result_size_gb from jobs group by group_id order by result_size_gb desc;

select id, test, (select concat_ws('/', (select name from job_group_parents where id = parent_id), name) from job_groups where id = group_id) as group_name, result_size, (result_size / 1024 / 1024) as  result_size_mb from jobs where result_size is not null order by result_size desc limit 20;

I'm currently experimenting with using Telegraf locally and have also drafted a MR for our monitoring: https://gitlab.suse.de/openqa/salt-states-openqa/-/merge_requests/287

Seems like the actual disk usage and the disk usage now available in the DB are not exactly the same. I guess one problem with my accounting is that it counts screenshots twice if the same screenshot is present in the same test and that's apparently often the case, e.g.

lrwxrwxrwx 1 martchus users       70 24. Mär 10:07 welcome-1.png -> /hdd/openqa-devel/openqa/images/cbb/22d/ea6b09799ac843a799e2f8578e.png
lrwxrwxrwx 1 martchus users       70 24. Mär 10:07 welcome-2.png -> /hdd/openqa-devel/openqa/images/cbb/22d/ea6b09799ac843a799e2f8578e.png

The reason why the figures stored in the database are inaccurate is generally that it accounts what is being uploaded and not what is being stored/linked. That means:

1. Screenshots which are already present from a previous test run are not accounted. That means the result size in the DB is smaller.
2. Text results might be uploaded twice (but are only stored once). That means the result size in the DB is bigger. With https://github.com/os-autoinst/openQA/pull/2879 in place, this shouldn't be the case anymore.

It seems that for the jobs I've tested 2. outweighs 1. and the result size in the DB is bigger than what du reports. Nevertheless I suppose the figures are accurate enough to compare them and to identify the disk space eating culprit. However, we should avoid printing them somewhere in the web UI implying that these are exact sizes.

Yet another caveat to mention for people who come from the Grafana dashboard: The accumulated result size for the groups is obviously only the size since recording the result sizes has been started and not the total result size for that group.

• The PR for the Telegraf query has been merged.
• PR for the Grafana dashboard is ongoing: https://gitlab.suse.de/openqa/salt-states-openqa/-/merge_requests/293
• PR for PostgreSQL permissions of Telegram user has been merged but maybe needs amendment: https://gitlab.suse.de/openqa/salt-states-openqa/-/merge_requests/292

As you managed to provide the permissions manually please see https://gitlab.suse.de/openqa/salt-states-openqa/-/merge_requests/292#note_206089 why salt failed to do the same.

I've update my PR so include a fix for salt (which will hopefully work).

The mentioned PR has been merged.

The retention policy for the data might need to be adjusted. It would also make sense to perform the PostgreSQL query less frequently.