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
Steps to reproduce:
hardware:
single sata HDD.
CPU 8x core amd, but it's not important cpu usage is small.
Generate dataset: innodb; insert into t1 values (),();" ; done > create_tables.sql
for i in `seq 1000000` ; do echo "create table t$i (id int auto_increment, primary key(id)) engine=
load database to mysql 5.7 ( start mysqld under libeatmydata to make it fast, about 15 minutes)
Insert some rows to mysql: .connect( user='root' , database='test')
import os
import random
import mysql.connector
cnx = mysql.connector
cursor = cnx.cursor()
for i in range(10000): )".format( random. randrange( 1, 1000000, 1)) execute( query)
query = "INSERT t{0} VALUES(
cursor.
cnx.commit()
cursor.close()
cnx.close()
Backup database (it's lasts forever without parallel, ) tables- compatibility- check --parallel=8
time xtrabackup --backup --skip-
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: backupfiles. tar.gz xtrabackup_ backupfiles
time tar czf xtrabackup_
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: log_buffer_ size=64M --target- dir=/root/ xtrabackup_ backupfiles
xtrabackup --prepare --use-memory=8G --innodb_
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