XtraBackup reports >512G Transaction Logs but In Fact its xtrabackup_logfile

[Jervin R]

We don't know if these are related yet and how we ended up with 1007G size of xtrabackup_logfile size but innodb_log_file_size is configured only with 5G.

Here's a snippet from the backup indicating we should've only gotten 200M logfile:

xtrabackup: Transaction log of lsn (12726759490889) to (12726978282329) was copied.

And here's during prepare of the delta:

131226 06:18:53 innobackupex: Starting ibbackup with command: /mysql/bin/xtrabackup --defaults-file="/shared/backup_20131221/main_prepared/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/shared/backup_20131221/main_prepared --apply-log-only --incremental-dir=/shared/backup_20131221/delta_20131226_025136/ --tmpdir=/var/tmp/

/mysql/bin/xtrabackup version 2.1.6 for MySQL server 5.6.11 solaris11 (x86_64) (revision id: undefined)
incremental backup from 12726750252577 is enabled.
xtrabackup: cd to /shared/backup_20131221/main_prepared
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=1054181310464, start_lsn=(12726759490889)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup: innodb_log_group_home_dir = /shared/backup_20131221/delta_20131226_025136/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 1054181310464
InnoDB: Allocated tablespace 3182, old maximum was 0
xtrabackup: page size for /shared/backup_20131221/delta_20131226_025136//ibdata1.delta is 16384 bytes
...
...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup: innodb_log_group_home_dir = /shared/backup_20131221/delta_20131226_025136/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 1054181310464
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Combined size of log files must be < 512 GB
xtrabackup: innodb_init(): Error occured.
innobackupex: Error:
innobackupex: ibbackup failed at /mysql/bin/innobackupex line 2569.

[Jervin R (revin)]

Full backup and prepare logs available on internal issue #37934

[Jervin R (revin)]

my.cnf from full and erring delta:

[root@xxxxxxx /shared/backup_20131221] # cat ../backup_20131221_bad2/main_prepared/backup-my.cnf
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:100M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5368709120
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_tablespaces=0
[root@xxxxxxx /shared/backup_20131221] # cat ../backup_20131221_bad2/delta_20131226_025136/backup-my.cnf
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:100M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5368709120
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_tablespaces=0

[Jervin R (revin)]

Now we know how we ended up with 1007G of xtrabackup_logfile https://bugs.launchpad.net/percona-xtrabackup/+bug/1264432

[Alexey Kopytov (akopytov)]

Not a bug per comment #3.

[Alexey Kopytov (akopytov)]

The size of xtrabackup_logfile is increased by 1/8 on prepare. Which is a heuristic constant to leave room for redo log writes generated by recovery (e.g. when merging change buffer entries). A better way to handle this would be to disable redo log writes on recovery.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-1266