Comment 1 for bug 1705383

Revision history for this message
Nickolay Ihalainen (ihanick) wrote : Re: xtrabackup 2.4.7 prepare slow at log_reserve_and_open => log_buffer_sync_in_background

Steps to reproduce:
hardware:
single sata HDD.
CPU 8x core amd, but it's not important cpu usage is small.

Generate dataset:
for i in `seq 1000000` ; do echo "create table t$i (id int auto_increment, primary key(id)) engine=innodb;insert into t1 values (),();" ; done > create_tables.sql

load database to mysql 5.7 ( start mysqld under libeatmydata to make it fast, about 15 minutes)

Insert some rows to mysql:
import os
import random
import mysql.connector
cnx = mysql.connector.connect(user='root', database='test')
cursor = cnx.cursor()

for i in range(10000):
  query = "INSERT t{0} VALUES()".format(random.randrange(1, 1000000, 1))
  cursor.execute(query)
  cnx.commit()

cursor.close()
cnx.close()

Backup database (it's lasts forever without parallel, )
time xtrabackup --backup --skip-tables-compatibility-check --parallel=8
xtrabackup: Transaction log of lsn (7804802029) to (11939776928) was copied.
170831 00:26:42 completed OK!

real 8498m53.610s
user 18m42.898s
sys 34m25.394s

Unprepared backup requires 100GB on disk (2GB compressed to tar.gz I can share unprepared backup).

It's interesting that offline backup 144X times faster than xtrabackup without prepare:
time tar czf xtrabackup_backupfiles.tar.gz xtrabackup_backupfiles
real 58m51.506s
user 24m5.608s
sys 8m1.729s

xtrabackup --prepare --use-memory=8G --target-dir=/root/xtrabackup_backupfiles
hangs for at least 8 hours after:
InnoDB: Doing recovery: scanned up to log sequence number 11939776928 (88%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.

Killed xtrabackup restarted with and in one hour passed that point:
xtrabackup --prepare --use-memory=8G --innodb_log_buffer_size=64M --target-dir=/root/xtrabackup_backupfiles

InnoDB: Doing recovery: scanned up to log sequence number 11939776928 (88%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8