Cases17 cases

15 to 17 of 17

1. IL-0319 Dropping databases on the MySQL replica mysql2 also dropped them on mysql1, as the pair actually ran master-master

   Oregon State University Open Source Lab2015

   Symptoms

   On July 30th, a few minutes after the OSU Open Source Lab started reloading the MySQL server mysql2 to fix its replication errors, the team saw that databases were being dropped on mysql1 as well. The script was stopped, and the MySQL service behind mysql1-vip stayed down through a long restore.

   Data lossService outageReplication lag

   Trigger

   On July 30th at 4:00PM PDT the team stopped slave replication on mysql2 and began dropping its databases one at a time to reload it and fix replication errors.

   Root cause

   Operator error

   Component

   DatabaseDatabase replicationDatabase backups and snapshots

   Contributing factor

   Unverified assumptionUntested backup

   Detection

   The team watching the reload noticed after a few minutes that mysql1 was also dropping databases; the delays between drops in the script gave them that time.

   Remediation

   • Stopped the script that was dropping databases
   • Restored the databases from the mysql-zrm backups
   • Pulled each database that failed to restore out of the 200GB backup file and restored it separately
   • Recovered most data with the binary logs from the 17th through the 30th

   Action items

   • Evaluate better replication strategies, including row-based replication
   • Store binlogs as a backup on a separate server
   • Take backups with Percona XtraBackup for much faster full restores
   • Use mydumper instead of mysql-zrm for faster logical backups
   • Improve documentation and training for complex systems
   • Test full restores regularly on a spare server as part of the backup process
   • Gather more accurate time estimates for the restore process
   • Audit the hosted databases regularly, since test and very large databases delayed the restore
   • Migrate to a bigger, more powerful MySQL cluster
   • Set a policy and tools to keep the community informed of outages, maintenance and resource use, with dashboards
   • Define and monitor capacity limits for core services
   • Implement redundancy and restore practices for core services, including staff drills
   • Migrate more core services to Chef
   • Review documentation, policies and performance metrics periodically
   • Find better ways to draw on community expertise
   • Raise funds to replace the oldest infrastructure and reduce technical debt

   Lessons

   The lab had stretched its resources to host as many projects as possible, and that came with tradeoffs such as heavily compressed backups to save disk and less redundancy than wanted; the heavy compression made this restore CPU-bound. The lab concluded that it needs to change how it plans, monitors and manages resources, and that the length of the outage and the lack of progress reports were not acceptable.

   View case
2. IL-0485 Dropbox went down when an OS upgrade script reinstalled active MySQL master-replica pairs and backup restores ran slow

   Dropbox2014

   Symptoms

   On Friday evening, during scheduled maintenance that started at 5:30 PM PT, the Dropbox service went down. Most functionality came back about three hours later, but core service was not fully restored until 4:40 PM PT on Sunday.

   Service outage

   Trigger

   A planned maintenance on Friday at 5:30 PM PT upgraded the operating system on some machines with a script that was meant to check that a machine held no active data before reinstalling it.

   Root cause

   Software defect

   Component

   DatabaseDatabase backups and snapshots

   Contributing factor

   No safeguard on destructive change

   Remediation

   • Restored the affected databases from backups

   Action items

   • Add a check that makes each machine verify its own state locally before running incoming commands, so machines running critical processes refuse destructive operations
   • Use a new tool that replays binary logs in parallel to recover large MySQL backups faster
   • Open source the parallel binary log replay tool

   Lessons

   Scripts that verify a machine's production state remotely before upgrading or repurposing it can be wrong, so machines need to check their own state before accepting destructive commands. Replicas give redundancy, but when every replica is lost the restore from backup decides the length of the outage, and the standard MySQL restore tool is slow on large data sets.

   View case
3. IL-0538 Sakura Cloud storage maintenance deleted 53 in-use customer disks, and the disks could not be restored from backup

   Sakura Internet2012

   Symptoms

   During emergency maintenance on 5 January 2012, a bulk deletion of unneeded file systems on the Sakura Cloud storage device also deleted 53 disks that customers were using. The deleted disks could not be restored from backup.

   Data loss

   Trigger

   In the emergency maintenance on 5 January 2012, Sakura Internet ran a batch command written only for that maintenance to delete unneeded file systems on the storage device in bulk.

   Root cause

   Operator error

   Component

   Cloud block storageDatabase backups and snapshots

   Contributing factor

   Missed in reviewUntested backup

   Action items

   • Simplify the process for deleting unneeded file systems
   • Do not run scripts that have not been reviewed and tested enough

   View case

By root cause8