Page MenuHomePhabricator
Create Task
Maniphest T138562

Improve regular production database backups handling
Open, MediumPublic
Actions

Description

  • Documentation, documentation, documentation
  • More flexibility: if possible per-table CSVs
  • More flexibility: Physical backups
  • Better recovery documentation: "one line to recover" (there is now a recover_section.py)
  • Faster point in time recovery/premade tools
  • Better compression
  • Prepare based on name of the backup, not just the section
  • Option optimization (e.g. double the use_memory)
  • Identify failures after X amount of timeout/time passed and easy cleanup of file leftover (probably on T205627)
  • Purge old metadata and make sure logs are rotated T205627
  • Review and improve logging (beyond metadata)
  • 1 retry after initial failure
  • More optimization of certain database tables
  • Maybe some kind of locking of backups and/or transfer.py to prevent concurrent actions on the same source or target servers
  • Differential backups
  • More detailed health checks of backups (size, failures, objects, ...). E.g. check size is within a percentage of the previous backup.
  • Document the last edit time (and potentially alert on) of some sample tables (e.g. recentchanges or revision) to verify the source databases are up to date (e.g. if its master, or intermediate master have replication stopped, or some other issue causing recent backups of stale data)
  • Have a quick way to see which backup sources belong to each section (tendril, dashboard)
  • Document and/or automate best server configuration for fast dump load (e.g. disable checksums, innodb transactionality, etc.)
  • Enable the possibility of editing per-table options such as the engine and compression
  • Workaround the "myloader doesn't import empty dbs" bug

Details

Related Gerrit Patches:
operations/puppet : productionCorrect invalid cron definition; add gtid to backups

Related Objects
Search...

StatusSubtypeAssignedTask
 ResolvedRobHT216175 HP Gen9 onboard controller review
 ResolvedfgiunchediT220787 Fix RAID handler alert and puppet facter to work with Gen10 hosts and ssacli tool
 OpenNoneT138562 Improve regular production database backups handling
 ResolvedjcrespoT131705 dbstore1001 low available space
 OpenNoneT111929 Puppetize grants for mysql hosts that are the source of recovery (dbstore, passive misc)
 ResolvedjcrespoT151999 Create script to monitor db dumps for backups are successful
 OpenjcrespoT79922 Set up backup strategy for es clusters
 DeclinedNoneT157668 Automate dataset backup recovery
 ResolvedjcrespoT162789 Create less overhead on bacula jobs when dumping production databases
 ResolvedjcrespoT169516 Implement cron-based mydumper backups on the dbstore role
 OpenNoneT151583 Use tls for dump backup generation
 OpenNoneT157702 Followup for TLS MariaDB server roll-out
 OpenNoneT152427 Create a check/calendar alert for MariaDB TLS certs
 ResolvedjcrespoT183469 Setup newer machines and replace all old misc (m*) and x1 eqiad machines
 ResolvedCmjohnsonT184054 Decommission db1029 and db1031
 ResolvedCmjohnsonT187542 Decommission db1043
 ResolvedCmjohnsonT184703 Decommission db1011
 ResolvedjcrespoT184704 Setup tendril database monitoring on 2 new hosts, one on eqiad and one on codfw
Unknown Object (Task)
 ResolvedMarosteguiT186123 rack/setup/install db2093 (WAS: rack/setup/install tendril2001)
 ResolvedRobHT186172 switch port configuration for db2093
 ResolvedAndrewT189005 Failover m5 master from db1009 to db1073
 ResolvedCmjohnsonT189216 Decommission db1009
 ResolvedjcrespoT189655 Switchover m1 master from db1016 to db1063
 ResolvedCmjohnsonT190179 Decommission db1016
 ResolvedCmjohnsonT189773 Decommission db1020
 ResolvedjcrespoT189656 Switchover m2 master from db1020 to db1051
 ResolvedCmjohnsonT190262 Decommission db1001
 ResolvedjcrespoT183470 Setup newer machines and replace all old misc (m*) and x1 codfw machines
 ResolvedPapaulT187543 Decommission db2012
 ResolvedMarosteguiT187722 Replace db2030 from m5 with another host (WAS: Degraded RAID on db2030)
 ResolvedRobHT187768 Decommission db2030
 ResolvedRobHT187886 Decommission db2011
 ResolvedjcrespoT169517 Research backup storage options and prepare a design document
 ResolvedjcrespoT184699 Generate consistent logical database backups in CODFW
 ResolvedNoneT183735 Check data consistency across production shards
 ResolvedMarosteguiT163190 Checksum data on s7
 ResolvedMarosteguiT165743 frwiktionary on s7 still needs fixing on the revision table
 ResolvedMarosteguiT162807 Run pt-table-checksum on s1 (enwiki)
 DeclinedMarosteguiT186503 Rebuild user_newtalk on db1052
 ResolvedmarkT184697 Failover existing eqiad database backup system to the new codfw database logical backup system
 ResolvedjcrespoT184696 Finish the database backups generation script to create consistent logical backups in CODFW
 ResolvedjcrespoT189383 Faster backups by archiving and unarchiving databases in parallel
 ResolvedjcrespoT189384 Faster backups by starting mydumper execution of different sections in parallel when they are about to finish and are running on a limited number of threads
 OpenRduranT156462 Framework to transfer files over the LAN
 ResolvedjcrespoT192358 Setup database logical backups on eqiad
 ResolvedjcrespoT198447 Monitor backup generation for failure or incorrect generation
 ResolvedjcrespoT198937 Setup database on tendril hosts to gather backup statistics
 ResolvedjcrespoT198987 Gather statistics about the backups on a database
 ResolvedjcrespoT203969 Create Icinga alerts on backup generation failure
 ResolvedjcrespoT201392 Finish eqiad metadata database backup setup (s1-s8, x1)
 DuplicateNoneT205626 Document clearly the mariadb backup and recovery setup
 OpenNoneT200398 Document clearly the mariadb backup and recovery setup, specially how to recover a backup
 OpenNoneT205627 Purge and monitor old metadata for the mariadb backups database
 OpenNoneT205628 Handle object metadata backups and compare it with stored database object inventory
 OpenNoneT104459 Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and slaves
 OpenNoneT57455 Provide dynamic report of differences between replica databases and production databases
 ResolvedMarosteguiT51199 Add index log_type_action
 ResolvedMarosteguiT217397 logging.log_title_time and logging.log_title_type_time indexes are not on tables.sql but they exist on most of the wikis
 ResolvedjcrespoT206203 Implement database binary backups into the production infrastructure
 ResolvedjcrespoT206204 Research options for producing binary backups (lvm snapshots, cold backups, mariabackup)
 ResolvedjcrespoT210292 Implement a proof of concept of a snapshot cycle automation for a mediawiki section database
 ResolvedjcrespoT213406 Purchase and setup remaining hosts for database backups
 ResolvedjcrespoT213404 Design the final architecture for the database binary backups
Unknown Object (Task)
Unknown Object (Task)
Unknown Object (Task)
 ResolvedjcrespoT220572 Productionize eqiad and codfw source backup hosts & codfw backup test host
 ResolvedPapaulT219461 rack/setup/install db2102.codfw.wmnet as a testing host for codfw backups
 DeclinedNoneT218188 Import issue (bug?) on Python 3.4/3.5 + multiprocessing affecting Cumin
 OpenNoneT218189 wmfmariadbpy/CuminExecution must capture Exception cumin.transports.WorkerError
 ResolvedjcrespoT219631 Create a recovery/provisioning script for database binary backups
 ResolvedPapaulT220002 Decommission dbstore2001.codfw.wmnet and dbstore2002.codfw.wmnet
 ResolvedjcrespoT219399 rack/setup/deploy eqiad dedicated backup recovery/provisioning hosts
 ResolvedPapaulT219463 rack/setup/install (5) codfw dedicated dump slaves
 ResolvedCmjohnsonT218985 rack/setup/install db1139|db1140.eqiad.wmnet (2 dump slaves)
Unknown Object (Task)
 ResolvedPapaulT218336 rack/setup/deploy codfw dedicated backup recovery/provisioning hosts
Unknown Object (Task)
 ResolvedjcrespoT225378 db2097 (codfw s1&s6 source backups) mariadb@s6 *process* (10.1.39) crashed on 2019-06-08
 ResolvedjcrespoT236924 dbprov2002 slower to generate snapshots

Event Timeline

jcrespo created this task.Jun 24 2016, 9:00 AM
Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptJun 24 2016, 9:00 AM
jcrespo added a comment.Jun 24 2016, 10:06 AM

The is to improve handling of cases such as T138516

jcrespo moved this task from Triage to Backlog on the DBA board.Jun 24 2016, 2:49 PM
gerritbot added a subscriber: gerritbot.Jun 30 2016, 10:32 AM

Change 296706 had a related patch set uploaded (by Jcrespo):
Correct invalid cron definition; add gtid to backups

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

gerritbot added a comment.Jun 30 2016, 10:35 AM

Change 296706 merged by Jcrespo:
Correct invalid cron definition; add gtid to backups

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

greg moved this task from On-going to Follow-up on the Wikimedia-Incident board.Jul 27 2016, 10:45 PM
greg added a subscriber: greg.Sep 29 2016, 7:40 PM

This follow-up task from an incident report has not been updated recently. If it is no longer valid, please add a comment explaining why. If it is still valid, please prioritize it appropriately relative to your other work. If you have any questions, feel free to ask me (Greg Grossmeier).

jcrespo triaged this task as Low priority.Sep 30 2016, 8:24 AM
jcrespo renamed this task from Improve db backup handling, specially of misc hosts to Improve db backup handling.Apr 12 2017, 10:21 AM
jcrespo removed a project: Patch-For-Review.
jcrespo renamed this task from Improve db backup handling to Improve regular production database backups handling.Apr 12 2017, 10:26 AM
jcrespo raised the priority of this task from Low to Medium.
jcrespo added subtasks: T111929: Puppetize grants for mysql hosts that are the source of recovery (dbstore, passive misc), T151999: Create script to monitor db dumps for backups are successful, T79922: Set up backup strategy for es clusters, T157668: Automate dataset backup recovery.
jcrespo moved this task from Backlog to Meta/Epic on the DBA board.
jcrespo updated the task description. (Show Details)
jcrespo updated the task description. (Show Details)Mar 9 2018, 3:16 PM
greg removed a subscriber: greg.Apr 11 2019, 9:20 PM
jcrespo updated the task description. (Show Details)Jun 25 2019, 8:58 AM
jcrespo updated the task description. (Show Details)Dec 2 2019, 10:03 AM
jcrespo updated the task description. (Show Details)Dec 10 2019, 3:13 PM
jcrespo updated the task description. (Show Details)
Content licensed under Creative Commons Attribution-ShareAlike 3.0 (CC-BY-SA) unless otherwise noted; code licensed under GNU General Public License (GPL) or 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