1

How would I backup mysql databases without causing spike in disk I/O, so other services using the same disk are not affected by the backup. Backing up to other disk might help, but i was thinking maybe there are ways to slowdown the process so IO load is more gradual than spiked.

Alex
  • 21
  • 2
  • I suggest you to read here: https://serverfault.com/questions/94495/how-do-i-backup-a-mysql-database-but-at-low-priority – shodanshok Mar 13 '20 at 10:15
  • Does this answer your question? [How do I backup a mysql database, but at low priority?](https://serverfault.com/questions/94495/how-do-i-backup-a-mysql-database-but-at-low-priority) – shodanshok Mar 13 '20 at 10:15
  • I have never even /considered/ the idea of "throttling" backups with /any/ DBMS I've worked with. I would challenge this question, asking why the overall disk traffic on the database server is *so* high that taking a backup has any significant impact at all? Are there large number of queries table-scanning and repeatedly reading huge amounts of data? – Phill W. Mar 13 '20 at 11:29
  • Do a LVM backup. – Rick James Mar 14 '20 at 19:49

1 Answers1

2

There are two answers to your question.

  1. Direct answer.

User XtraBackup's throttle feature - https://www.percona.com/doc/percona-xtrabackup/2.3/advanced/throttling_backups.html . Give to Xtrabackup the difference between the max disk IOPS capacity minus what the database uses in peaks.

  1. Recommended answer.

Set up a replica and take backups from it. Make sure you run pt-table-chechsum periodically to ensure data consistency.

akuzminsky
  • 738
  • 4
  • 9
  • 1
    Definitely the best advice, I think ... it's also generally applicable to things like "reporting," which often generates far more disk-activity than ordinary operations do. Replicate your production database to another database that you use to run reports. Backups are the same. Note that replication operations will "pile up" until the report finishes or the backups are done. – Mike Robinson Mar 13 '20 at 19:11
  • @MikeRobinson - If you build and maintain [summary tables](http://mysql.rjweb.org/doc.php/summarytables), even "reporting" does not use much I/O. – Rick James Mar 25 '20 at 16:25