The filesize of incremental backupset is bigger than the base full backupset

MySQL & MariaDB Percona XtraBackup
1

We use percona xtrabackup 2.3.6([COLOR=#FF0000]percona-xtrabackup-2.3.6-1.el7.x86_64) to backup mariadb cluster. We found sometimes the filesize of incremental backupset is bigger than the base full backupset. And found the [COLOR=#FF0000]ibdata1.delta size is huge.

The full backup script
innobackupex --no-timestamp ${mysql_bak_dir}/full_backup/2019-10-25_20-00

The incremental backupset script
innobackupex --no-timestamp --incremental-basedir=${mysql_bak_dir}/full_backup/2019-10-25_20-00 --incremental ${mysql_bak_dir}/incremental_backup/2019-10-31_08-00

The full backupset filesize
[COLOR=#0000FF]# du -sh /mysql_bak/full_backup/2019-10-25_20-00/
27G /mysql_bak/full_backup/2019-10-25_20-00/
[COLOR=#0000FF]# ll /mysql_bak/full_backup/2019-10-25_20-00/
total 27613032
-rw-r----- 1 root root 388 Oct 25 20:05 backup-my.cnf
drwx------ 2 root root 4096 Oct 25 20:05 barbican
drwx------ 2 root root 4096 Oct 25 20:05 billing_proxy
drwx------ 2 root root 4096 Oct 25 20:05 ceph_om
drwx------ 2 root root 4096 Oct 25 20:05 cinder
drwx------ 2 root root 4096 Oct 25 20:05 cloudguard
drwx------ 2 root root 71 Oct 25 20:05 cloudguard@002dext
drwx------ 2 root root 50 Oct 25 20:05 ec2
drwx------ 2 root root 4096 Oct 25 20:05 glance
drwx------ 2 root root 4096 Oct 25 20:05 gnocchi
drwx------ 2 root root 4096 Oct 25 20:05 heat
drwx------ 2 root root 4096 Oct 25 20:05 horizon
[COLOR=#FF0000]-rw-r----- 1 root root 28271706112 Oct 25 20:05 ibdata1
drwx------ 2 root root 4096 Oct 25 20:05 keystone
drwx------ 2 root root 4096 Oct 25 20:05 manila
drwx------ 2 root root 4096 Oct 25 20:05 mysql
drwx------ 2 root root 12288 Oct 25 20:05 neutron
drwx------ 2 root root 8192 Oct 25 20:05 nova
drwx------ 2 root root 4096 Oct 25 20:05 performance_schema
drwx------ 2 root root 36 Oct 25 20:05 test
drwx------ 2 root root 4096 Oct 25 20:05 trove
drwx------ 2 root root 4096 Oct 25 20:05 trovetmpdb
-rw-r----- 1 root root 28 Oct 25 20:05 xtrabackup_binlog_info
-rw-r----- 1 root root 123 Oct 25 20:05 xtrabackup_checkpoints
-rw-r----- 1 root root 509 Oct 25 20:05 xtrabackup_info
-rw-r----- 1 root root 3921408 Oct 25 20:05 xtrabackup_logfile
[COLOR=#0000FF]# cat full_backup/2019-10-25_20-00/xtrabackup_info
uuid = b4a8a557-f71f-11e9-8c7e-90b11c392314
name =
tool_name = innobackupex
[COLOR=#FF0000]tool_command = --no-timestamp /mysql_bak/full_backup/2019-10-25_20-00
tool_version = 2.3.6
ibbackup_version = 2.3.6
server_version = 5.5.40-MariaDB-wsrep-log
start_time = 2019-10-25 20:00:22
end_time = 2019-10-25 20:05:15
lock_time = 0
binlog_pos = filename ‘mariadb-bin.006551’, position ‘57207367’
[COLOR=#FF0000]innodb_from_lsn = 0
innodb_to_lsn = 645240760421
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

The incremental backupset filesize
[COLOR=#0000FF]# du -sh incremental_backup/*
318M incremental_backup/2019-10-26_08-00
609M incremental_backup/2019-10-26_20-00
901M incremental_backup/2019-10-27_08-00
1.2G incremental_backup/2019-10-27_20-00
1.5G incremental_backup/2019-10-28_08-00
1.8G incremental_backup/2019-10-28_20-00
2.1G incremental_backup/2019-10-29_08-00
2.4G incremental_backup/2019-10-29_20-00
29G incremental_backup/2019-10-30_08-00 # ibdata1.delta size is huge and bigger than ibdata1 of full backup
29G incremental_backup/2019-10-30_20-00 # ibdata1.delta size is huge and bigger than ibdata1 of full backup
29G incremental_backup/2019-10-31_08-00 # ibdata1.delta size is huge and bigger than ibdata1 of full backup
[COLOR=#0000FF]# ll incremental_backup/2019-10-31_08-00
total 29480208
-rw-r----- 1 root root 388 Oct 31 08:08 backup-my.cnf
drwx------ 2 root root 4096 Oct 31 08:08 barbican
drwx------ 2 root root 4096 Oct 31 08:08 billing_proxy
drwx------ 2 root root 4096 Oct 31 08:08 ceph_om
drwx------ 2 root root 4096 Oct 31 08:08 cinder
drwx------ 2 root root 4096 Oct 31 08:08 cloudguard
drwx------ 2 root root 71 Oct 31 08:08 cloudguard@002dext
drwx------ 2 root root 50 Oct 31 08:08 ec2
drwx------ 2 root root 4096 Oct 31 08:08 glance
drwx------ 2 root root 4096 Oct 31 08:08 gnocchi
drwx------ 2 root root 4096 Oct 31 08:08 heat
drwx------ 2 root root 4096 Oct 31 08:08 horizon
[COLOR=#FF0000]-rw-r----- 1 root root 30180589568 Oct 31 08:08 ibdata1.delta
-rw-r----- 1 root root 44 Oct 31 08:00 ibdata1.meta
drwx------ 2 root root 4096 Oct 31 08:08 keystone
drwx------ 2 root root 4096 Oct 31 08:08 manila
drwx------ 2 root root 4096 Oct 31 08:08 mysql
drwx------ 2 root root 12288 Oct 31 08:08 neutron
drwx------ 2 root root 8192 Oct 31 08:08 nova
drwx------ 2 root root 4096 Oct 31 08:08 performance_schema
drwx------ 2 root root 36 Oct 31 08:08 test
drwx------ 2 root root 4096 Oct 31 08:08 trove
drwx------ 2 root root 4096 Oct 31 08:08 trovetmpdb
-rw-r----- 1 root root 28 Oct 31 08:08 xtrabackup_binlog_info
-rw-r----- 1 root root 132 Oct 31 08:08 xtrabackup_checkpoints
-rw-r----- 1 root root 603 Oct 31 08:08 xtrabackup_info
-rw-r----- 1 root root 7021056 Oct 31 08:08 xtrabackup_logfile
[COLOR=#0000FF]# cat incremental_backup/2019-10-31_08-00/xtrabackup_info
uuid = 973efcc2-fb72-11e9-a8a7-90b11c392314
name =
tool_name = innobackupex
[COLOR=#FF0000]tool_command = --no-timestamp --incremental-basedir=/mysql_bak/full_backup/2019-10-25_20-00 --incremental /mysql_bak/incremental_backup/2019-10-31_08-00
tool_version = 2.3.6
ibbackup_version = 2.3.6
server_version = 5.5.40-MariaDB-wsrep-log
start_time = 2019-10-31 08:00:01
end_time = 2019-10-31 08:08:39
lock_time = 0
binlog_pos = filename ‘mariadb-bin.006603’, position ‘49316443’
[COLOR=#FF0000]innodb_from_lsn = 645240760421
innodb_to_lsn = 651800266120
partial = N
[COLOR=#FF0000]incremental = Y
format = file
compact = N
compressed = N
encrypted = N
[COLOR=#0000FF]# cat incremental_backup/2019-10-29_20-00/xtrabackup_info
uuid = 244332d9-fa44-11e9-8c7e-90b11c392314
name =
tool_name = innobackupex
[COLOR=#FF0000]tool_command = --no-timestamp --incremental-basedir=/mysql_bak/full_backup/2019-10-25_20-00 --incremental /mysql_bak/incremental_backup/2019-10-29_20-00
tool_version = 2.3.6
ibbackup_version = 2.3.6
server_version = 5.5.40-MariaDB-wsrep-log
start_time = 2019-10-29 20:00:02
end_time = 2019-10-29 20:03:38
lock_time = 0
binlog_pos = filename ‘mariadb-bin.006584’, position ‘50461173’
[COLOR=#FF0000]innodb_from_lsn = 645240760421
innodb_to_lsn = 649673535199
partial = N
[COLOR=#FF0000]incremental = Y
format = file
compact = N
compressed = N
encrypted = N

We search a lot of documents and blogs to check what’s the root cause. But it is not lucky and there is no idea to fix. Please give us some ideas, Thanks!

2

Hi popgo can I just check in with you on what versions of MariaDB and Galera you are running? And if you are seeing any error logs being written?

3

Hi Lorraine,

Thanks for your quick reply. Our Maraidb version is mariadb-galera-server-5.5.40-2.el7.x86_64 and Galera version is galera-25.3.5-7.el7.x86_64. There are no errors in Mysql error log when xtrabackup script processing. Thanks

4

Thanks for that update. OK, so those are versions that we’d support. Later versions of MariaDB are incompatible and you’ll need to move to Maria’s back up solution which I believe is a fork of PXB.

I checked in with the PXB team, there’s not a known issue. They suggested:
[LIST]
[]What about other ibd files? Does ibdata1 file grow in the datadir too?
[]It could be that their ibdata1 grew between backups.
[*]ibdata1 contains undo log and change buffer. It might be that it has fully refreshed between backups
[/LIST] These are the types of things that we could foresee possibly impacting the size of the incremental backup.

5

Thanks for your reply. Yes, we have checkd the version compatibility with Mariadb and xtrabackup that it’s supported. About these suggestions’ answers:
[LIST]
[]Other tables’ ibd filesize are OK and ibdata1 file does not grow obviously
[]ibdata1 did not grow between backups
[/LIST]

By the way, after we did a new full backup, all of new incremental backups based this new full backup are ok. So can u guide us some trobleshooting ways to analyse. Thanks.

6

Hi again

We’ve a good number of free webinars on troubleshooting, if you go to here you’ll see them: [URL]Percona Webinars: Open Source Database Learning

Those and some of our blog posts might well provide you with some methods to track down the issue that you’ve encountered.

I did also find this page on the MariaDB site, you probably already saw this, but if not please do check that none of the conditions known to MariaDB are affecting you in this instance. [URL]Percona XtraBackup Overview - MariaDB Knowledge Base

I’ll also ask the team if they have other methods to troubleshoot on PXB but I suspect they are covered by those resources shared above.

7

This could depend on your workload. I believe there are some things that can lead to a delta which is bigger than the original ibdata:
[LIST]
[]Many updates on the same set of rows (you will store all transactions, even if some of them are theoretically useless because the changes have been overwritten)
[]Many deletes followed by more inserts (deletes take some space)
[*]Using many REPLACE commands, because it’s the same as DELETE + INSERT (except when REPLACE only inserts new rows)
[/LIST] From the top of my mind, I can’t find a way to check if I’m correct.

8

Thanks again. We try to use mariadb xtrabackup tool to backup. If have any results, we will share here. Thanks