0

We have our operational as well as decision making databases both running on Percona MySQL 5.7.10. The OS is Ubuntu 16.04. On these various database instances we have been using Percona Innobackupex (Xtrabackup) for daily and incremental backups. Both of these backup types take the same amount of time to finish. For our Data Warehouse, running on an AWS ec2 m4.2xlarge instance type, which is around 3 TB in size both the full and incremental take around 5-6 hours to finish. Shouldn't the incremental finish faster?

Based on the full backup taken a day before, I've tried testing the incremental backup numerous times and each time I found it to be taking the same time as the full backup.

Any help or pointers to this would of be of great help.

2

It's kind of expected assuming how incremental backups work.

  • Xtrabackup knows the last LSN (a point when a full copy was taken).
  • Xtrabackup reads all pages. Every page has an LSN in its header of the latest modification:
    • if a page is newer than the last LSN - save it.
    • if a page is older than the last LSN - skip it.

So, in the end of the day xtrabackup needs to read same amount of pages, whether it's a full or incremental copy.

But. There is an option in Percona Server can track modified pages. In that enabled, Xtrabackup doesn't have to scan whole tablespace and thus incremental backup time can be greatly reduced.

3
  • Thanks for pointing this out. Is the option to track modified pages specific to Percona Server 8.0? – Deb May 20 '20 at 18:49
  • This option has been around for a while. I think I tested it with 5.6 – akuzminsky May 21 '20 at 5:05
  • Thanks again. Looks promising and exactly what I was looking for. I'll make this change on one of servers and test further. – Deb May 22 '20 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.