Create a dashboard for database backups monitoring/reporting
Open, HighPublic
Actions

Assigned To

Authored By

	LSobanski
	May 17 2021, 3:08 PM

Description

Create a dashboard to have better understanding of the state of database backups (success/failure, delays, current status, logs, etc.). If possible, also generate a subset of that information as public metrics for prometheus, so we can integrate them with the alerting infrastructure. Provide also an inventory of objects backed up, as well as historical trends.

Original description: As a person interested in backup status, I would like to have the ability to subscribe to alerts about failed / delayed backup tasks.

Details

Subject	Repo	Branch	Lines +/-
Add the possibility of searching racks for instances, too	operations/software/pampinus	master	+2 -0
Attempt to follow Wikimedia's Design Style Guide	operations/software/pampinus	master	+172 -103
Add absolute number (bytes) changed & max staleness for backup status	operations/software/pampinus	master	+43 -10
Initial commit	operations/software/pampinus	master	+2 K -0
zarcillo: Remove access to non-primary dc prometheus hosts	operations/puppet	production	+0 -12
db_inventory: Cleanup zarcillo database grants	operations/puppet	production	+36 -101
[WIP]Add instance script with increased functionality over section	operations/software	master	+18 -0
Adapt mysql prometheus script to new zarcillo schema	operations/puppet	production	+38 -29
Add new user for dbbackups database for django dashboard	operations/puppet	production	+4 -0
[WIP]django: Create custom django module and apply it to backupmon1001	operations/puppet	production	+489 -16
dbbackups::check: Add enabled flag to have a passive host on codfw	operations/puppet	production	+63 -14
check: Split common functionality to a WMFMetrics class	operations/software/wmfbackups	master	+195 -103
django: Add dummy django secret key and mysql pass to test compilation	labs/private	master	+2 -0
alerting_host: Remove references to dbbackups monitoring	operations/puppet	production	+0 -124
ddbackups: Remove old references to the check pass on the alert hosts	labs/private	master	+0 -1
alert_host: Ensure packages and files from dbbackups check are gone	operations/puppet	production	+41 -3
dbbackups: Fix hiera key formatting for db password and django secret	labs/private	master	+2 -3
dbbackups: Add django database password and secret key for pampinus	labs/private	master	+3 -0
dbbackups: Reenable checks now that they are working as intended	operations/puppet	production	+0 -1
dbbackups: Reinstall backupmon1001 with bullseye	operations/puppet	production	+1 -0
dbbackups: Temporarely disable notifications on backupmon hosts	operations/puppet	production	+5 -4
dbbackups: Update definition to use Floats for type	operations/puppet	production	+4 -4
dbbackups: Setup backupmon1001 as a database backups monitoring service	operations/puppet	production	+158 -1

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Open		jcrespo	T283017 Create a dashboard for database backups monitoring/reporting
		Resolved		MoritzMuehlenhoff	T308643 eqiad: 1 VM for backupmon1001

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 17 2021, 3:08 PM

LSobanski triaged this task as Low priority.May 17 2021, 3:08 PM

LSobanski moved this task from Triage to Backlog on the Data-Persistence-Backup board.May 20 2021, 5:20 PM

This is not super urgent, but @LSobanski if you could think of more details of what kind of information exactly you would like to have more accessible about database backups specifically (we will also think of use cases as backup maintainers and team members- @Marostegui and me), but your unique perspective is important to us as both a manager and general SRE.

Which level of detail would be adequate to you?
What are the things you would like to understand the fastest/most clear?
What would you prefer to be notified about (push) versus reading on demand (pull/dashboard)
Do we need an api to interoperate with other systems (e.g. grafana, alert-manager, icinga, other automation, etc.?)
Are general statistics useful (not related directly to malfunction), like backup sizes, wiki sizes, table sizes, etc.?

For a list of the entire data available already on the DB (there is a lot) one could check: https://wikitech.wikimedia.org/wiki/MariaDB/Backups#Metadata but my question is- how to present that in a more "friendly way" by filtering out the not-so-important information (your take on it)- as we have already GB of existing data?

Offtopic, but something to think in the future is also how we want to present/integrate metadata about the several worklows of backups (database backups vs general backups (bacula) vs media backups and others more in the future). Of if not being integrated is a feature, not a bug.

In T283017#7114556, @jcrespo wrote:

This is not super urgent, but @LSobanski if you could think of more details of what kind of information exactly you would like to have more accessible about database backups specifically (we will also think of use cases as backup maintainers and team members- @Marostegui and me), but your unique perspective is important to us as both a manager and general SRE.

Which level of detail would be adequate to you?

What are the things you would like to understand the fastest/most clear?

What would you prefer to be notified about (push) versus reading on demand (pull/dashboard)

Do we need an api to interoperate with other systems (e.g. grafana, alert-manager, icinga, other automation, etc.?)

Are general statistics useful (not related directly to malfunction), like backup sizes, wiki sizes, table sizes, etc.?

For a list of the entire data available already on the DB (there is a lot) one could check: https://wikitech.wikimedia.org/wiki/MariaDB/Backups#Metadata but my question is- how to present that in a more "friendly way" by filtering out the not-so-important information (your take on it)- as we have already GB of existing data?

Some quick brain dump:

I think we first need to define what is acceptable and what is not and then later present that information in one way or another.
To me, the most important key information is: "are we ok backup-wise" but defining what is ok and what is not is the hard part.

In my mind, what I would love to have would be a central place to see something high level like:

dataset to backup, last time it was backed-up successfully, available number of "fresh" copies (not stored in long-term archive), available number of copies stored in bacula (or any other long-term solution), next time this backup is scheduled to run, average time that takes to finish a backup run (we'd need to define what is a backup run)

If I wanted to go low level, that would be a different place and with more specific data as you mentioned (file sizes, table sizes, backup total size) etc, but that's probably something secondary.

Along with the high level details, I believe having some documentation for the operators would be good:
ie: last available backup is too old (whatever too old means) -> follow this to get that metric back into green status.

This turned into a much bigger scope than my original intention but it's a good discussion to have. I am now thinking that my ask was not well defined and needs to be adjusted, especially given my dislike of using email for operational notification :)

The use case I was thinking of was receiving some sort of a proactive notification (ideally a Phab task, alternatively an email) when whatever we consider to be a "bad state" is breached, e.g multiple consecutive failed backups, "too old" (as Manuel said above).

LSobanski renamed this task from Backup alert email notification to Backup alert proactive notification.May 27 2021, 11:41 AM

LSobanski updated the task description. (Show Details)

As a "complicated" topic- do you mind talking in our 1:1 about the challenges of it- not just doing as requested (I don't mind spamming you with notifications if you really want them, :-D), but doing it efficiently and without getting transformed into useless spam, with little false positives (which is an open issue, here and for other cases).

For the "metadata dashboard" usage, something like https://github.com/nocodb/nocodb could be interesting to evaluate.

I think this is partially covered with the icinga and prometheus monitoring that was setup long ago + the new dbbackup dashboard we are about to setup (including limited public prometheus support).

jcrespo added a project: database-backups.May 9 2022, 9:48 AM

jcrespo added a project: Goal.

jcrespo renamed this task from Backup alert proactive notification to Create a dashboard for database backups monitoring/reporting.May 12 2022, 5:21 PM

jcrespo updated the task description. (Show Details)

Change 791414 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] dbbackups: Setup backupmon1001 as a database backups monitoring service

https://gerrit.wikimedia.org/r/791414

gerritbot added a project: Patch-For-Review.May 12 2022, 5:22 PM

Change 791560 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] alerting_host: Remove references to dbbackups monitoring

https://gerrit.wikimedia.org/r/791560

jcrespo mentioned this in rLPRIa2e9a8244698: dbbackups: Copy database check password in anticipation of role move.May 18 2022, 9:16 AM

jcrespo added a subtask: T308643: eqiad: 1 VM for backupmon1001.May 18 2022, 9:43 AM

jcrespo closed subtask T308643: eqiad: 1 VM for backupmon1001 as Resolved.May 18 2022, 10:15 AM

jcrespo mentioned this in T308643: eqiad: 1 VM for backupmon1001.

Change 791414 merged by Jcrespo:

[operations/puppet@production] dbbackups: Setup backupmon1001 as a database backups monitoring service

https://gerrit.wikimedia.org/r/791414

Change 793022 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] dbbackups: Update definition to use Floats for type

https://gerrit.wikimedia.org/r/793022

Change 793023 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] dbbackups: Temporarely disable notifications on backupmon hosts

https://gerrit.wikimedia.org/r/793023

Change 793022 merged by Jcrespo:

[operations/puppet@production] dbbackups: Update definition to use Floats for type

https://gerrit.wikimedia.org/r/793022

Change 793023 merged by Jcrespo:

[operations/puppet@production] dbbackups: Temporarely disable notifications on backupmon hosts

https://gerrit.wikimedia.org/r/793023

Change 793026 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] dbbackups: Reinstall backupmon1001 with bullseye

https://gerrit.wikimedia.org/r/793026

Change 793026 merged by Jcrespo:

[operations/puppet@production] dbbackups: Reinstall backupmon1001 with bullseye

https://gerrit.wikimedia.org/r/793026

Change 793038 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] dbbackups: Reenable checks now that they are working as intended

https://gerrit.wikimedia.org/r/793038

Change 793038 merged by Jcrespo:

[operations/puppet@production] dbbackups: Reenable checks now that they are working as intended

https://gerrit.wikimedia.org/r/793038

Change 793042 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[labs/private@master] ddbackups: Remove old references to the check pass on the alert hosts

https://gerrit.wikimedia.org/r/793042

Change 793094 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] alert_host: Ensure packages and files from dbbackups check are gone

https://gerrit.wikimedia.org/r/793094

Change 793475 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] [WIP]django: Create custom django module and apply it to backupmon1001

https://gerrit.wikimedia.org/r/793475

Change 793498 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[labs/private@master] dbbackups: Add django database password and secret key for pampinus

https://gerrit.wikimedia.org/r/793498

Change 793498 merged by Jcrespo:

[labs/private@master] dbbackups: Add django database password and secret key for pampinus

https://gerrit.wikimedia.org/r/793498

jcrespo mentioned this in rLPRIdda4fe59ed07: dbbackups: Add django database password and secret key for pampinus.May 19 2022, 3:38 PM

Change 793505 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[labs/private@master] dbbackups: Fix hiera key formatting for db password and django secret

https://gerrit.wikimedia.org/r/793505

Change 793505 merged by Jcrespo:

[labs/private@master] dbbackups: Fix hiera key formatting for db password and django secret

https://gerrit.wikimedia.org/r/793505

jcrespo mentioned this in rLPRIcccabf77e014: dbbackups: Fix hiera key formatting for db password and django secret.May 19 2022, 4:00 PM

Change 793094 merged by Jcrespo:

[operations/puppet@production] alert_host: Ensure packages and files from dbbackups check are gone

https://gerrit.wikimedia.org/r/793094

Change 793042 merged by Jcrespo:

[labs/private@master] ddbackups: Remove old references to the check pass on the alert hosts

https://gerrit.wikimedia.org/r/793042

jcrespo mentioned this in rLPRI23fc2736146a: ddbackups: Remove old references to the check pass on the alert hosts.May 20 2022, 9:01 AM

Change 791560 merged by Jcrespo:

[operations/puppet@production] alerting_host: Remove references to dbbackups monitoring

https://gerrit.wikimedia.org/r/791560

Change 793771 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[labs/private@master] django: Add dummy django secret key and mysql pass to test compilation

https://gerrit.wikimedia.org/r/793771

Change 793771 merged by Jcrespo:

[labs/private@master] django: Add dummy django secret key and mysql pass to test compilation

https://gerrit.wikimedia.org/r/793771

jcrespo mentioned this in rLPRIb3a6ba7d18e7: django: Add dummy django secret key and mysql pass to test compilation.May 20 2022, 12:50 PM

jcrespo mentioned this in dbbackups-dashboard.May 25 2022, 4:47 PM

jcrespo added a project: dbbackups-dashboard.

Change 801657 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/software/wmfbackups@master] check: Split common functionality to a WMFMetrics class

https://gerrit.wikimedia.org/r/801657

Change 801657 merged by Jcrespo:

[operations/software/wmfbackups@master] check: Split common functionality to a WMFMetrics class

https://gerrit.wikimedia.org/r/801657

Change 801741 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] dbbackups::check: Add enabled flag to have a passive host on codfw

https://gerrit.wikimedia.org/r/801741

Change 801741 merged by Jcrespo:

[operations/puppet@production] dbbackups::check: Add enabled flag to have a passive host on codfw

https://gerrit.wikimedia.org/r/801741

Change 810885 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] Add new user for dbbackups database for django dashboard

https://gerrit.wikimedia.org/r/810885

Mentioned in SAL (#wikimedia-operations) [2022-07-04T12:38:22Z] <jynus> running alter table on dbbackups db T283017

Change 810885 merged by Jcrespo:

[operations/puppet@production] Add new user for dbbackups database for django dashboard

https://gerrit.wikimedia.org/r/810885

jcrespo claimed this task.Jul 26 2022, 6:28 AM

Change 817181 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] db_inventory: Cleanup zarcillo database grants

https://gerrit.wikimedia.org/r/817181

dashboard_progress.png (581×2 px, 164 KB)

jcrespo moved this task from Backlog to In Progress on the dbbackups-dashboard board.Jul 26 2022, 9:22 AM

Change 817294 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/software/pampinus@master] Initial commit

https://gerrit.wikimedia.org/r/817294

This would be a typical warning scenario:

More status info:

Screenshot_20220726_171509.png (1×1 px, 162 KB)

Change 818088 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] Adapt mysql prometheus script to new zarcillo schema

https://gerrit.wikimedia.org/r/818088

Change 818538 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/software/pampinus@master] Add absolute number (bytes) changed & max staleness for backup status

https://gerrit.wikimedia.org/r/818538

Change 819025 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/software/pampinus@master] Attempt to follow Wikimedia's Design Style Guide

https://gerrit.wikimedia.org/r/819025

jcrespo mentioned this in T274463: Backups for GitLab.Aug 1 2022, 1:50 PM

Change 820073 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/software/pampinus@master] Add the possibility of searching racks for instances, too

https://gerrit.wikimedia.org/r/820073

Change 820074 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/software@master] [WIP]Add instance script with increased functionality over section

https://gerrit.wikimedia.org/r/820074

Screenshot_20220902_092632.png (1×2 px, 305 KB)

Screenshot_20220902_092644.png (1×2 px, 220 KB)

Very nice work Jaime!

I did some css changes and made the patch for it. Once that's deployed, I'll find a designer colleague and ask for a quick check and a bit of further improvements. I like the server view and generally think it should be split into its own service (probably fork from pampinus) but that's for later.

New CSS from Amir:

Screenshot_20220906_084752.png (1×2 px, 210 KB)

Screenshot_20220906_084809.png (1×2 px, 303 KB)

Screenshot_20220906_084830.png (1×2 px, 154 KB)

Screenshot_20220906_084526.png (1×2 px, 286 KB)

Change 817181 merged by Jcrespo:

[operations/puppet@production] db_inventory: Cleanup zarcillo database grants

https://gerrit.wikimedia.org/r/817181

Change 853950 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] zarcillo: Remove access to non-primary dc prometheus hosts

https://gerrit.wikimedia.org/r/853950

Change 853950 merged by Jcrespo:

[operations/puppet@production] zarcillo: Remove access to non-primary dc prometheus hosts

https://gerrit.wikimedia.org/r/853950

Change 817294 merged by Jcrespo:

[operations/software/pampinus@master] Initial commit

https://gerrit.wikimedia.org/r/817294

Change 818538 merged by Jcrespo:

[operations/software/pampinus@master] Add absolute number (bytes) changed & max staleness for backup status

https://gerrit.wikimedia.org/r/818538

Change 819025 merged by Jcrespo:

[operations/software/pampinus@master] Attempt to follow Wikimedia's Design Style Guide

https://gerrit.wikimedia.org/r/819025

Change 820073 merged by Jcrespo:

[operations/software/pampinus@master] Add the possibility of searching racks for instances, too

https://gerrit.wikimedia.org/r/820073

jcrespo mentioned this in rOSPMe215dfa64dcf: Add the possibility of searching racks for instances, too.Aug 7 2023, 3:59 PM

jcrespo mentioned this in rOSPMc82ca4799bcd: Attempt to follow Wikimedia's Design Style Guide.

jcrespo mentioned this in rOSPMe44183ce4405: Add absolute number (bytes) changed & max staleness for backup status.

jcrespo mentioned this in rOSPMcf20865497a6: Initial commit.

	F35510523: Screenshot_20220906_084526.png
	Sep 6 2022, 6:49 AM

	F35510525: Screenshot_20220906_084830.png
	Sep 6 2022, 6:49 AM

	F35510526: Screenshot_20220906_084809.png
	Sep 6 2022, 6:49 AM

	F35510527: Screenshot_20220906_084752.png
	Sep 6 2022, 6:49 AM

	F35506962: Screenshot_20220902_092644.png
	Sep 2 2022, 7:27 AM

	F35506963: Screenshot_20220902_092632.png
	Sep 2 2022, 7:27 AM

	F35343983: Screenshot_20220726_171509.png
	Jul 27 2022, 6:53 AM

	F35343985: Screenshot_20220727_083857.png
	Jul 27 2022, 6:53 AM

Create a dashboard for database backups monitoring/reportingOpen, HighPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

Create a dashboard for database backups monitoring/reporting
Open, HighPublic
Actions

Related Objects
Search...