Xtrabackup 2.4 very slow when prepare the backup

[James chen]

We upgrade Percona server 5.6 to 5.7, when try to set up new slave use Xtrabackup, we find have to upgrade it from 2.3 to 2.4 in order to backup the 5.7 database.

The problem is after the data copy finished when start to parepare the backup, the apply log records process is very slow. The xtrabackup_logfile file is about 500G, after one day it only apply around 10%.

When we tried Xtrabackup 2.3 on the same data with Percona 5.6 (we still keep one slave as 5.6 just in case), the prepare process only take about 5 hours, the xtrabackup_logfile is about 600G

[Sergei Glushchenko (sergei.glushchenko)]

Note, there is a known issue with crash recovery being slow in 5.7:
https://bugs.mysql.com/bug.php?id=80788
https://bugs.mysql.com/bug.php?id=81648

What options are you using? Have you try to change --use-memory parameter?

[James chen (baoxiao)]

I use this option --use-memory=50G, I can understand 5.7 recovery is slow
but if it is slow like this: only apply 10% of log record for one day, then
the xtrabackup tools is useless. It need more than 10 days to apply the log
file, then the slave will never catch up after that.

Thanks,

On Tue, Nov 29, 2016 at 6:47 PM, Sergei Glushchenko <
<email address hidden>> wrote:

> Note, there is a known issue with crash recovery being slow in 5.7:
> https://bugs.mysql.com/bug.php?id=80788
> https://bugs.mysql.com/bug.php?id=81648
>
> What options are you using? Have you try to change --use-memory
> parameter?
>
> ** Bug watch added: MySQL Bug System #80788
> http://bugs.mysql.com/bug.php?id=80788
>
> ** Bug watch added: MySQL Bug System #81648
> http://bugs.mysql.com/bug.php?id=81648
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1645774
>
> Title:
> Xtrabackup 2.4 very slow when prepare the backup
>
> Status in Percona XtraBackup:
> New
>
> Bug description:
> We upgrade Percona server 5.6 to 5.7, when try to set up new slave use
> Xtrabackup, we find have to upgrade it from 2.3 to 2.4 in order to
> backup the 5.7 database.
>
> The problem is after the data copy finished when start to parepare the
> backup, the apply log records process is very slow. The
> xtrabackup_logfile file is about 500G, after one day it only apply
> around 10%.
>
> When we tried Xtrabackup 2.3 on the same data with Percona 5.6 (we
> still keep one slave as 5.6 just in case), the prepare process only
> take about 5 hours, the xtrabackup_logfile is about 600G
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-xtrabackup/+bug/1645774/+subscriptions
>

[Sergei Glushchenko (sergei.glushchenko)]

In order for us to analyse the issue can you please share your my.cnf, options which you are using to take and to prepare the backup, your backup-my.cnf and the logs of backup and prepare processes.

[James chen (baoxiao)]

Backup command
innobackupex --defaults-file=/etc/my.cnf --slave-info --safe-slave-backup --user=** --password=**** --stream=xbstream --parallel=2 /data/backup/ | ssh root@10.100.16.** "xbstream -x -C /data/mysql/" &
Prepare:

innobackupex --apply-log --use-memory=50G /data/mysql &

backup-my.cnf

[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=2096103424
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=10250283

redo_log_version=1

no log file generated during the backup and prepare

[Sergei Glushchenko (sergei.glushchenko)]

You can capture backup and prepare logs by simply redirecting xtrabackup's stderr into a file.

[James chen (baoxiao)]

If you mean stderr, there is no error after 3 days, just very slow. I have
to cancel it.

Thanks

On Sun, Dec 11, 2016 at 11:42 PM, Sergei Glushchenko <
<email address hidden>> wrote:

> You can capture backup and prepare logs by simply redirecting
> xtrabackup's stderr into a file.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1645774
>
> Title:
> Xtrabackup 2.4 very slow when prepare the backup
>
> Status in Percona XtraBackup:
> New
>
> Bug description:
> We upgrade Percona server 5.6 to 5.7, when try to set up new slave use
> Xtrabackup, we find have to upgrade it from 2.3 to 2.4 in order to
> backup the 5.7 database.
>
> The problem is after the data copy finished when start to parepare the
> backup, the apply log records process is very slow. The
> xtrabackup_logfile file is about 500G, after one day it only apply
> around 10%.
>
> When we tried Xtrabackup 2.3 on the same data with Percona 5.6 (we
> still keep one slave as 5.6 just in case), the prepare process only
> take about 5 hours, the xtrabackup_logfile is about 600G
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-xtrabackup/+bug/1645774/+subscriptions
>

[Jericho Rivera (jericho-rivera)]

to anyone who manages to observe the same issue, aside from capturing logs from stderr you can also try using strace like:

strace -f -s65535 -o/tmp/xb.strace innobackupex --user=root --password=****** --tmpdir=/path/to/tmpdir /path/to/backupdir > /tmp/xb.out 2>&1

[Shahriyar Rzayev (rzayev-sehriyar)]

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

[Sai Charan (charan0304)]

This is happening to me right now,

Backup taken with

xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)

and prepare completes on the master in no less than 10 mins

Trying to restore on Slave

xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)

Prepare takes over 5 hours

I will later try to downgrade the version on the slave and try to reproduce the same issue. But looks to be an issue with xtrabackup version.

And BTW

5.6.48-88