Backup per database overhead

[bmmalc]

Hello there,

because of the issue I described at https://forums.percona.com/t/unable-to-restore-a-database-from-a-full-backup-using-xbcloud-with-encryption-and-compression and it seems it won’t be fixed any time soon, I need to rethink backing up all my databases in one time to be able to restore faster.

The reason I choose to backup all databases in one shot instead of separately is the latter adds an overhead because in each database backup xtrabackup backs up same files like ibdata so the storage at the end for the backup is 40% bigger.

is there some way to reduce this overhead ?

[matthewb]

ibdata is innodb’s core dictionary and must be part of each backup. You can attempt to shrink the ibdata file by doing a LOGICAL dump, wipe, restore. Then your ibdata will be much smaller going forward.

Your only other option would be to convert to LOGICAL backups. I recommend mydumper which can do parallel, compressed backups. The nice thing here, is that mydumper creates a file for each table. So restoring a single table is as simple as downloading 1 file from your Swift. Downsides are all those normally associated with logical-style backups.

[bmmalc]

@matthewb

I recommend mydumper which can do parallel, compressed backups

Thanks for the suggestion, for now I’ll do the maximum to stick with my first solution, but I keep that in mind.

@matthewb

Downsides are all those normally associated with logical-style backups.

Can you elaborate or point me to a document that would explain the downsides you’re thinking about because I have some in mind but certainly not as much as you

Thanks.

[matthewb]

Logical backups are slowest to create/dump (requiring full-table scans, more CPU, etc) and slowest to import (highest disk IO usage, high CPU usage, etc)

[bmmalc]

That matches more or less what I had in mind
Thanks