Page MenuHomePhabricator
Create Task
Maniphest T384810

MariaDB lifetime management system
Open, In Progress, MediumPublic
Actions

Description

Implement automation to depool/repool replicas as needed, manage role changes between master / candidate-master / replicas, and ultimately automate master failover.

Requirements:

  • Track where/when ground truth is coming from
  • Hard/soft distributed locks on section / dc to prevent clashing failover / maintenance activities
  • Support multiple candidates and select based on priority
  • Support topology-aware host selection (rack, TOR switch etc)
  • Automated failover/depooling with velocity checks
    • Critical path cannot depend on lower tier services

Initial design doc: https://docs.google.com/document/d/17ApZIOSyGP2kmMbhOgXT-9--_OrRaNCuSpThFPGW6eo/edit?tab=t.0

Roadmap:

Details

Related Changes in Gerrit:
Show related patches Customize query in gerrit
Related Changes in GitLab:
TitleReferenceAuthorSource BranchDest Branch
Add puppet helper and basic runbook examplerepos/sre/wmfmariadbpy!13fcerattopuppethelpermain
Add initial locking mechanismrepos/data_persistence/dbtools/auto_schema!8fcerattolockingmain
Customize query in GitLab

Related Objects
Search...

StatusSubtypeAssignedTask
 OpenNoneT327300 Cookbook for rack depool
 In ProgressFCeratto-WMFT384810 MariaDB lifetime management system
 OpenFCeratto-WMFT384212 Create a dashboard to show depooled hosts
 In ProgressFCeratto-WMFT196366 Implement (or refactor) a script to move slaves when the master is not available
 ResolvedFCeratto-WMFT399580 Automate read-only es* section OS updates
 OpenFCeratto-WMFT239814 Automate DB upgrades
 ResolvedNoneT374191 upgrade clone cookbook
 DeclinedABran-WMFT376712 mysql_legacy: SQL query quote escape
 ResolvedABran-WMFT376701 mysql_legacy data_directory getter
 ResolvedABran-WMFT377129 mariadb: systemctl status accessor in mysql_legacy
 DeclinedFCeratto-WMFT375460 mariadb: create a cookbook to convert a replica in primary candidate
 ResolvedMarosteguiT374951 find a viable replication source on codfw
ResolvedFCeratto-WMFT384909 sre.mysql.pool check for lag
 OpenNoneT374026 Allow operator to repool a set of nodes in parallel
 ResolvedFCeratto-WMFT383674 Abstract away different database depooling mechanisms into a cookbook
 DuplicateFCeratto-WMFT411573 sre.mysql.parsercache: make it work with msX sections
 OpenNoneT388127 Generate and parse DB-related puppet conf
 OpenNoneT257814 Use zarcillo as an authoritative inventory of db instances/roles
 Resolved• KormatT257816 Set up replication for zarcillo
 DeclinedNoneT257819 Add haproxy config for zarcillo
 OpenNoneT257821 Add monitoring to ensure consistency between puppet and zarcillo
 Resolved• KormatT257822 Add monitoring to ensure consistency between tendril and zarcillo
 Resolved• KormatT258566 Refactor tendril+zarcillo roles/profiles
 OpenNoneT389663 Fetch DB-related data from puppet
 ResolvedFCeratto-WMFT395304 Enabling CAS for https://zarcillo.wikimedia.org/
 DeclinedNoneT377451 zarcillo: Take inventory of all active use cases
 OpenFCeratto-WMFT396816 single DB server replag / downtime should not page us anymore
 ResolvedFCeratto-WMFT400056 Core DB testbed on VMs
 OpenNoneT390087 eqiad: VMs requested for Data Persistence automation and testbeds
 DuplicateFCeratto-WMFT408774 Add db-test* hosts to zarcillo and test-s4
 ResolvedMarosteguiT409159 Clean test-s4 cluster data
 OpenFCeratto-WMFT376370 mariadb: create a synthetic monitoring indicator for dc switchover readiness
 OpenNoneT375589 mariadb monitoring: buffer pool usage
 ResolvedMarosteguiT254738 Create a monitoring check for event_scheduler and for installed events
 ResolvedFCeratto-WMFT417608 Create a dashboard for sre.mysql.clone cookbook runs
 OpenNoneT420563 Automate Pre-DC switchover checks in Zarcillo

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
FCeratto-WMF claimed this task.Jan 27 2025, 9:13 AM
FCeratto-WMF triaged this task as Medium priority.
FCeratto-WMF added a subtask: T384212: Create a dashboard to show depooled hosts.
FCeratto-WMF added a subtask: T196366: Implement (or refactor) a script to move slaves when the master is not available.
FCeratto-WMF added a subtask: T239814: Automate DB upgrades.
FCeratto-WMF added a subtask: T375460: mariadb: create a cookbook to convert a replica in primary candidate.
FCeratto-WMF updated the task description. (Show Details)Jan 27 2025, 9:33 AM
Volans subscribed.Jan 27 2025, 9:40 AM
FCeratto-WMF updated the task description. (Show Details)Jan 27 2025, 10:38 AM
FCeratto-WMF updated the task description. (Show Details)Jan 27 2025, 11:23 AM
FCeratto-WMF updated the task description. (Show Details)Jan 27 2025, 1:57 PM
Marostegui moved this task from Triage to In progress on the DBA board.Jan 28 2025, 11:19 AM
FCeratto-WMF updated the task description. (Show Details)Jan 28 2025, 4:55 PM
FCeratto-WMF added a subtask: T374026: Allow operator to repool a set of nodes in parallel.Jan 30 2025, 8:36 AM
FCeratto-WMF added a subtask: T383674: Abstract away different database depooling mechanisms into a cookbook.
• joanna_borun subscribed.Jan 30 2025, 10:58 AM
FCeratto-WMF updated the task description. (Show Details)Jan 30 2025, 11:15 AM
FCeratto-WMF added a subtask: T388127: Generate and parse DB-related puppet conf.Mar 6 2025, 2:14 PM
FCeratto-WMF added a subtask: T257814: Use zarcillo as an authoritative inventory of db instances/roles.Mar 11 2025, 4:49 PM
FCeratto-WMF changed the status of subtask T383674: Abstract away different database depooling mechanisms into a cookbook from Open to In Progress.Mar 12 2025, 5:47 PM
gerritbot added a comment.Mar 20 2025, 6:05 PM

Change #1129904 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/cookbooks@master] Add switchover cookbook

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

gerritbot added a project: Patch-For-Review.Mar 20 2025, 6:05 PM
FCeratto-WMF added a subtask: T390087: eqiad: VMs requested for Data Persistence automation and testbeds.Mar 26 2025, 5:13 PM
FCeratto-WMF changed the status of subtask T239814: Automate DB upgrades from Open to In Progress.Mar 27 2025, 11:30 AM
FCeratto-WMF added a subscriber: Joe.EditedApr 3 2025, 2:37 PM

I'm sharing here an updated diagram of the components based on the ongoing work and a recent discussion with @Joe.
The arrows indicate data flows.

Screenshot 2025-04-03 at 16-17-00 Graphviz Online.png (783×1 px, 102 KB)

White ellipses represent data, rectangles represent processes or functions.

  • status fetcher: extracts replication and general health status from databases at high frequency, directly and/or from Orchestrator
  • dbconfig ingestion: fetches DB-related data from e.g. https://noc.wikimedia.org/dbconfig/eqiad.json
  • daemon: performs database pooling/depooling, master/candidate flips and other activities requiring changing dbcontrol value automatically or on SRE input
  • API: a read only HTTP API primarily providing T384212.
  • CLI tool: used by SREs to set desired states; could be a runbook.
  • puppet ingestion: fetches DB-related data from Puppet primarily to spot inconsistencies or new/unused hosts, see T389663 T388127 T389932

All processes are running as daemons, except for the CLI tool. Most processes should be stateless and able to run in the aux k8s cluster.
(Processes could be unix processes or different functions in the same daemon)

The Puppet ingestion process is optional and/or could be implemented as a manual tool instead.

The API could also serve ancillary data e.g. generate configuration snippets.

The status fetcher also exposes replication delay metrics as Prometheus metrics
addressing T141968 with minimal effort.

(editable diagram at https://is.gd/qtV5ZJ)

Marostegui subscribed.Apr 4 2025, 7:37 AM

This is in the very long term, but we need to keep in mind that if I am successful with T324965: Clean up old gtid_domain_id we may start using orchestrator for master switchovers (both emergency and planned ones). In either case, this (either coding this tool or fixing GTID and trusting orchestrator) is a very long term thing, but just wanted to leave this here.

CodeReviewBot added a comment.May 7 2025, 1:01 PM

fceratto opened https://gitlab.wikimedia.org/repos/data_persistence/dbtools/auto_schema/-/merge_requests/8

Add initial locking mechanism

gerritbot added a comment.May 13 2025, 10:13 AM

Change #1145127 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: values.yaml: Fix typo, remove comment

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

gerritbot added a comment.May 13 2025, 11:15 AM

Change #1145127 merged by Clément Goubert:

[operations/deployment-charts@master] zarcillo: values.yaml: Fix typo, remove comment

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

gerritbot added a comment.May 14 2025, 4:23 PM

Change #1146018 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: values.yaml: Add FQDN for SNI

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

gerritbot added a comment.May 14 2025, 4:41 PM

Change #1146018 merged by jenkins-bot:

[operations/deployment-charts@master] zarcillo: values.yaml: Add FQDN for SNI

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

Marostegui closed subtask T375460: mariadb: create a cookbook to convert a replica in primary candidate as Declined.May 23 2025, 6:42 AM
FCeratto-WMF added a subtask: T395304: Enabling CAS for https://zarcillo.wikimedia.org/.May 27 2025, 9:12 AM
FCeratto-WMF closed subtask T395304: Enabling CAS for https://zarcillo.wikimedia.org/ as Resolved.Jun 3 2025, 7:28 AM
gerritbot added a comment.Jun 12 2025, 4:39 PM

Change #1156401 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: Allow egress to idp.wikimedia.org

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

gerritbot added a comment.Jun 13 2025, 9:57 AM

Change #1156401 merged by Federico Ceratto:

[operations/deployment-charts@master] zarcillo: Allow egress to idp.wikimedia.org

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

gerritbot added a comment.Jun 25 2025, 11:55 AM

Change #1129904 abandoned by Federico Ceratto:

[operations/cookbooks@master] Add switchover cookbook

Reason:

Discussed on IRC: the automation workflow is to be rediscussed

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

gerritbot added a comment.Jun 25 2025, 12:07 PM

Change #1129904 restored by Federico Ceratto:

[operations/cookbooks@master] Add switchover cookbook

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

gerritbot added a comment.Jul 3 2025, 4:07 PM

Change #1166227 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: Update egress to idp.wikimedia.org

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

gerritbot added a comment.Jul 7 2025, 8:36 AM

Change #1166227 merged by jenkins-bot:

[operations/deployment-charts@master] zarcillo: Update egress to idp.wikimedia.org

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

FCeratto-WMF added a subtask: T377451: zarcillo: Take inventory of all active use cases.Jul 7 2025, 8:52 AM
FCeratto-WMF added a subtask: T396816: single DB server replag / downtime should not page us anymore.Jul 21 2025, 7:24 AM
FCeratto-WMF mentioned this in T396816: single DB server replag / downtime should not page us anymore.
FCeratto-WMF added a subtask: T400056: Core DB testbed on VMs.Jul 22 2025, 10:36 AM
FCeratto-WMF removed a subtask: T390087: eqiad: VMs requested for Data Persistence automation and testbeds.
CodeReviewBot added a comment.Jul 23 2025, 1:10 PM

fceratto closed https://gitlab.wikimedia.org/repos/data_persistence/dbtools/auto_schema/-/merge_requests/8

Add initial locking mechanism

gerritbot added a comment.Jul 24 2025, 2:44 PM

Change #1172334 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: Enable egress for Alertmanager

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

gerritbot added a comment.Jul 25 2025, 7:47 AM

Change #1172334 merged by Federico Ceratto:

[operations/deployment-charts@master] zarcillo: Enable egress for Alertmanager

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

fgiunchedi mentioned this in T400443: Allow wider Alertmanager API RO access.Jul 25 2025, 8:07 AM
gerritbot added a comment.Jul 25 2025, 1:38 PM

Change #1172635 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: Add egress to Netbox

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

Marostegui closed subtask T377451: zarcillo: Take inventory of all active use cases as Declined.Jul 29 2025, 6:53 AM
Marostegui mentioned this in T377451: zarcillo: Take inventory of all active use cases.
gerritbot added a comment.Jul 29 2025, 10:49 AM

Change #1172635 merged by Federico Ceratto:

[operations/deployment-charts@master] zarcillo: Add egress to Netbox and config-master

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

Ladsgroup moved this task from In progress to Meta/Epic on the DBA board.Sep 3 2025, 12:01 PM
FCeratto-WMF updated the task description. (Show Details)Sep 23 2025, 1:23 PM
FCeratto-WMF added a subtask: T376370: mariadb: create a synthetic monitoring indicator for dc switchover readiness.Oct 6 2025, 9:20 AM
gerritbot added a comment.Oct 15 2025, 1:26 PM

Change #1196437 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: update egress after IDP ipaddr changes

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

gerritbot added a comment.Oct 15 2025, 2:16 PM

Change #1196437 merged by Federico Ceratto:

[operations/deployment-charts@master] zarcillo: update egress after IDP ipaddr changes

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

FCeratto-WMF mentioned this in rOSMD40ab45b75334: Add puppet helper and basic runbook example.Oct 15 2025, 2:31 PM
CodeReviewBot added a comment.Oct 15 2025, 2:51 PM

fceratto opened https://gitlab.wikimedia.org/repos/sre/wmfmariadbpy/-/merge_requests/13

Add puppet helper and basic runbook example

FCeratto-WMF mentioned this in rOSMD096d14eb02c3: Add puppet helper and basic runbook example.Oct 15 2025, 2:51 PM
FCeratto-WMF mentioned this in rOSMD631a3ed587c0: Add puppet helper and basic runbook example.Oct 16 2025, 8:54 AM
FCeratto-WMF mentioned this in rOSMD72dd60d6fea3: Add puppet helper and basic runbook example.Oct 16 2025, 9:56 AM
FCeratto-WMF mentioned this in rOSMD54786d249f61: Add puppet helper and basic runbook example.Oct 16 2025, 3:49 PM
FCeratto-WMF mentioned this in rOSMD8acd0d09cc27: Add puppet helper and basic runbook example.Oct 21 2025, 10:40 AM
FCeratto-WMF mentioned this in rOSMD42b7b45bd645: Add puppet helper and basic runbook example.Oct 23 2025, 9:48 AM
FCeratto-WMF mentioned this in rOSMD304189dfb752: Add puppet helper and basic runbook example.Oct 23 2025, 4:50 PM
FCeratto-WMF added a comment.Oct 27 2025, 9:37 AM

Related: https://gerrit.wikimedia.org/r/c/operations/deployment-charts/+/1198912

gerritbot added a comment.Oct 27 2025, 10:14 AM

Change #1198924 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: remove obsoleted IDP egress ipaddrs

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

gerritbot added a comment.Oct 27 2025, 4:00 PM

Change #1198924 merged by Federico Ceratto:

[operations/deployment-charts@master] zarcillo: remove obsoleted IDP egress ipaddrs

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

FCeratto-WMF mentioned this in T409926: Primary-replica switchover automation.Nov 12 2025, 12:58 PM
FCeratto-WMF closed subtask T400056: Core DB testbed on VMs as Resolved.Nov 25 2025, 11:27 AM
FCeratto-WMF changed the status of subtask T196366: Implement (or refactor) a script to move slaves when the master is not available from Open to In Progress.Nov 25 2025, 11:38 AM
gerritbot added a comment.Nov 25 2025, 4:14 PM

Change #1211165 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/deployment-charts@master] zarcillo: Allow egress to etcd to fetch dbctl values

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

gerritbot added a comment.Nov 26 2025, 3:39 PM

Change #1211165 merged by Federico Ceratto:

[operations/deployment-charts@master] zarcillo: Allow egress to etcd to fetch dbctl values

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

FCeratto-WMF reopened subtask T400056: Core DB testbed on VMs as In Progress.Nov 27 2025, 8:45 AM
Marostegui closed subtask T400056: Core DB testbed on VMs as Resolved.Dec 1 2025, 2:44 PM
CodeReviewBot added a comment.Dec 2 2025, 10:03 AM

fceratto merged https://gitlab.wikimedia.org/repos/sre/wmfmariadbpy/-/merge_requests/13

Add puppet helper and basic runbook example

gerritbot added a comment.Dec 11 2025, 11:51 AM

Change #1217492 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):

[operations/puppet@production] prometheus-mariadb-replication-lag.py: mysql_heartbeat_lag_seconds metric

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

gerritbot added a comment.Dec 15 2025, 8:40 AM

Change #1129904 abandoned by Federico Ceratto:

[operations/cookbooks@master] Add switchover cookbook

Reason:

The tool has been moved to https://gitlab.wikimedia.org/repos/sre/wmfmariadbpy/-/merge_requests/17

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

gerritbot added a comment.Jan 12 2026, 1:09 PM

Change #1217492 merged by Federico Ceratto:

[operations/puppet@production] prometheus-mariadb-replication-lag.py: mysql_heartbeat_lag_seconds metric

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

FCeratto-WMF changed the status of subtask T376370: mariadb: create a synthetic monitoring indicator for dc switchover readiness from Stalled to Open.Jan 12 2026, 1:24 PM
FCeratto-WMF changed the status of subtask T383674: Abstract away different database depooling mechanisms into a cookbook from In Progress to Open.Jan 16 2026, 3:10 PM
FCeratto-WMF changed the status of subtask T239814: Automate DB upgrades from In Progress to Open.Jan 19 2026, 2:57 PM
FCeratto-WMF closed subtask T384909: sre.mysql.pool check for lag as Resolved.Feb 3 2026, 4:11 PM
FCeratto-WMF closed subtask T383674: Abstract away different database depooling mechanisms into a cookbook as Resolved.Feb 9 2026, 10:22 AM
FCeratto-WMF added a subtask: T417608: Create a dashboard for sre.mysql.clone cookbook runs.Feb 25 2026, 12:01 PM
FCeratto-WMF added a subtask: T420563: Automate Pre-DC switchover checks in Zarcillo.Thu, Mar 19, 9:54 AM
FCeratto-WMF closed subtask T417608: Create a dashboard for sre.mysql.clone cookbook runs as Resolved.Tue, Apr 14, 7:29 AM
Marostegui added a parent task: T327300: Cookbook for rack depool.Tue, Apr 14, 7:59 AM
FCeratto-WMF updated the task description. (Show Details)Tue, Apr 14, 8:05 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits