QA » openQA Project » openQA Infrastructure

Could this alert https://stats.openqa-monitor.qa.suse.de/d/WebuiDb/webui-summary?orgId=1&from=1716147294259&to=1716175779373 be related?

Values
B0=6.840987906666666 
Labels
alertname   HTTP Response alert
grafana_folder  Salt
rule_uid    tm0h5mf4k

There is a visible decrease in CPU usage at that time and quite big increase in inactive minion jobs.

Also some interesting lines from log during that time, not sure if related.

May 20 00:27:04 openqa telegraf[1347]: 2024-05-19T22:27:04Z E! [outputs.influxdb] When writing to [http://openqa-monitor.qa.suse.de:8086]: failed doing req: Post "http://openqa-monitor.qa.suse.de:8086/write?db=telegraf": context deadline exceeded (Client.Timeout exceeded while await>
May 20 00:27:04 openqa telegraf[1347]: 2024-05-19T22:27:04Z E! [agent] Error writing to outputs.influxdb: could not write any address

Why do we have both?

openqa:/etc/cron.daily # cat /etc/cron.daily/dump-openqa
#!/bin/bash

#exit 0 # avoid further data loss
backup_dir="${backup_dir:-"/var/lib/openqa/backup"}"
date=$(date -Idate)
su - postgres -c "pg_dump -Fc openqa -f $backup_dir/$date.dump"
find $backup_dir/ -mtime +7 -print0 | xargs -0 rm -v

backup-vm:~ # grep openqa.suse.de /etc/rsnapshot.conf | grep backup_exec
backup_exec ssh root@openqa.suse.de "cd /tmp; sudo -u postgres pg_dump -Fc openqa -f /var/lib/openqa/SQL-DUMPS/$(date -I).dump"

The -j option seems to do the opposite of what we want to achieve and also it is not even compatible with the output format we're using:

       -j njobs
       --jobs=njobs
           Run the dump in parallel by dumping njobs tables
           simultaneously. This option may reduce the time needed to
           perform the dump but it also increases the load on the
           database server. You can only use this option with the
           directory output format because this is the only output
           format where multiple processes can write their data at
           the same time.

dheidler wrote in #note-21:

Why do we have both?

openqa:/etc/cron.daily # cat /etc/cron.daily/dump-openqa
#!/bin/bash

#exit 0 # avoid further data loss
backup_dir="${backup_dir:-"/var/lib/openqa/backup"}"
date=$(date -Idate)
su - postgres -c "pg_dump -Fc openqa -f $backup_dir/$date.dump"
find $backup_dir/ -mtime +7 -print0 | xargs -0 rm -v

backup-vm:~ # grep openqa.suse.de /etc/rsnapshot.conf | grep backup_exec
backup_exec   ssh root@openqa.suse.de "cd /tmp; sudo -u postgres pg_dump -Fc openqa -f /var/lib/openqa/SQL-DUMPS/$(date -I).dump"

because we want to have daily snapshots while also storing remote backups. We have even more because we also trigger a database backup in https://gitlab.suse.de/openqa/osd-deployment/-/blob/master/.gitlab-ci.yml?ref_type=heads#L216

dheidler wrote in #note-22:

The -j option seems to do the opposite of what we want to achieve and also it is not even compatible with the output format we're using

but running in parallel might make the database very busy only for a potentially much shorter time so that overall operations are impacted less. If the current output format does not support that then consider changing the output format.

The bottleneck seems to be the CPU
Backup can be speed up at the expense of size, by disabling compression (default is gzip).
Note that another backup was running at the same time:

time sudo -u postgres ionice -c3 nice -n19 pg_dump --compress=0 -Fc openqa -f /var/lib/openqa/SQL-DUMPS/test.dump

real    4m11,817s
user    0m29,894s
sys     0m44,122s

-rw-r--r-- 1 postgres postgres 21G 17. Jun 16:13 /var/lib/openqa/SQL-DUMPS/test.dump

pg_dump --compress=0 -Fc openqa | lz4 | dd status=progress of=/var/lib/openqa/SQL-DUMPS/test.dump
6163796480 bytes (6.2 GB, 5.7 GiB) copied, 308 s, 20.0 MB/s
12055096+1 records in
12055096+1 records out
6172209629 bytes (6.2 GB, 5.7 GiB) copied, 308.394 s, 20.0 MB/s

After discussing I suggest the following

1. In the daily cron script /etc/cron.daily/dump-openqa only run the backup if $name for the day does not already exist
2. Put /etc/cron.daily/dump-openqa into salt as it is currently not salt-controlled
3. In the daily cron script run the backup late at the time to give other scripts or pipelines a chance to already create the daily backup and not duplicate
4. Use a specific name for each pipeline and backup so that we don't have potential race conditions
5. Use nice
6. block on upgrade of OSD to Leap 15.6 #157981, then upgrade PostgreSQL 15->16 so that we have a fully supported PostgreSQL 16 and then use pg_dump … --compress=METHOD[:DETAIL]` to use a more modern compression, e.g. lz4
7. Consider flock /var/lib/openqa/backup/$name.lock pg_dump … in all three places cron.daily, rsnapshot and osd deployment and CI pipelines