xtrabackup --prepare is VERY slow

rlbyrd · December 30, 2020, 4:23pm

We’ve been using xtrabackup for several years with no issues at all. Recently, it became necessary for a rather large instance – 4.4TB – to begin backups. The instance is relatively busy, but not ridiculously so.

The base backup takes about 1.5 hours, which is fine. However, the ensuing apply-logs section (I always opt to do the prepare bit before deeming the full backup complete) is taking a whopping 20 hours, which seems a bit excessive to me.

I really only have one burning, painful question:

What could I do via xtrabackup or innobackupex magick to speed this part of the backup along?

Any input would be appreciated.

Kind regards,
Richard

vadimtk · December 30, 2020, 4:33pm

You should use --prepare with --use-memory option
https://www.percona.com/doc/percona-xtrabackup/2.3/xtrabackup_bin/xbk_option_reference.html#cmdoption-xtrabackup–use-memory

Try to allocate to --use-memory as much memory as possible.

rlbyrd · December 30, 2020, 4:33pm

As soon as our systems team finishes building a couple of slaves for this instance, I will absolutely utilize the --use-memory flag. However, at the moment, I’m having to take backups from the production instance, so I am reluctant to get too jiggy with it. Are there any other possible ways to speed up the log processing?

dbcarl · December 30, 2020, 4:33pm

Any luck with increasing the preparation time? I have a Zabbix instance which takes a week to prepare even with a lot of RAM. Can the number of Buffer pool instances be increased beyond 1?

oldgarey · December 30, 2020, 4:33pm

same here , 500G database restore, prepare will take few hours, thats crazy man.

philschroeder · December 30, 2020, 4:34pm

Similar issue here…Percona Server 5.6.37-82.2-log, Xtrabackup 2.3.9. 1.4TB dataset, incremental backup takes 20 minutes to complete, but apply-logs/prepare takes sometimes more than 24 hours. I give it 48GB of RAM to use, can’t give it much more (400GB dedicated to innodb-buffer-pool).