Not the answer you need?
Register and ask your own question!

Incremental backup extremly slow (MariaDB 10.1.28 Galera Cluster)

klausenbuskklausenbusk EntrantInactive User Role Beginner
Hello

I have been using xtrabackup for a few years, to take full backup of my 3-node MariaDB 10.1.28 Galera cluster, and it is working fine.
So today I decided that I want to optimize a bit, by taking incremental backup most of the time and full backup 1-4 times a day.

I have been taking full backup with xtrabackup > backup.xbstream and this my.cnf config:
[xtrabackup]
backup
galera-info
stream=xbstream
compress
user=xtrabackup
password=foobar
host=foo.bar
which take 1-3 minutes for a full ~ 600MB compressed backup.


I then tried taking a incremental backup, by taking the LSN number from the full backup log:
xtrabackup: The latest check point (for incremental): '379477920952'
xtrabackup --incremental-lsn 379477920952
(stream and compress was removed from the my.cnf)
But it takes forever (+40 min) and xtrabackup seems to generate "dummy" delta files for <everything> (table +??).
I can't upload the delta file, so I have uploaded it to my homeserver: http://188.228.31.139/huh/foo.ibd.delta
I have also enabled innodb_track_changed_pages, but that didn't change anything.

Some log:
xtrabackup --incremental-lsn 379477920952 --target-dir=s      
171015 18:16:56  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=foo.bar;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'xtrabackup'  (using password: YES).
171015 18:16:56  version_check Connected to MySQL server
171015 18:16:56  version_check Executing a version check against the server...
171015 18:16:56  version_check Done.
171015 18:16:56 Connecting to MySQL server host: foo.bar, user: xtrabackup, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
Using server version 10.1.28-MariaDB-1~jessie
xtrabackup version 2.3.9 based on MySQL server 5.6.24 Linux (x86_64) (revision id: fde0e3e)
incremental backup from 379477920952 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1048576
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using O_DIRECT
171015 18:16:56 >> log scanned up to (379511019919)
xtrabackup: Generating a list of tablespaces
171015 18:16:57 >> log scanned up to (379511030753)
171015 18:16:58 >> log scanned up to (379511033570)
171015 18:16:59 >> log scanned up to (379511038904)
171015 18:17:00 >> log scanned up to (379511044028)
171015 18:17:01 >> log scanned up to (379511105074)
171015 18:17:02 >> log scanned up to (379511176462)
171015 18:17:03 >> log scanned up to (379511239990)
171015 18:17:04 >> log scanned up to (379511264291)
171015 18:17:05 >> log scanned up to (379511361036)
171015 18:17:06 >> log scanned up to (379511378892)
171015 18:17:07 >> log scanned up to (379511405634)
171015 18:17:08 >> log scanned up to (379511419473)
171015 18:17:09 >> log scanned up to (379511464009)
171015 18:17:10 >> log scanned up to (379511487627)
171015 18:17:11 >> log scanned up to (379511619769)
171015 18:17:12 >> log scanned up to (379511699828)
xtrabackup: using the changed page bitmap
171015 18:17:13 [01] Copying ./ibdata1 to /s/ibdata1.delta
171015 18:17:13 [01]        ...done
171015 18:17:13 >> log scanned up to (379511702675)
171015 18:17:13 [01] Copying ./sp_bar/sp_bar_areas.ibd to /s/sp_bar/sp_bar_areas.ibd.delta
171015 18:17:13 [01]        ...done
171015 18:17:14 [01] Copying ./sp_bar/sp_players.ibd to /s/sp_bar/sp_players.ibd.delta
171015 18:17:14 [01]        ...done
171015 18:17:14 [01] Copying ./sp_bar/block_calendar.ibd to /s/sp_bar/block_calendar.ibd.delta
171015 18:17:14 [01]        ...done
[.....]

Is this a bug in MariaDB, xtrabackup or perhaps a missing feature in MariaDB?

Regards Kristian Klausen

Comments

  • sergei.glushchenkosergei.glushchenko Percona Inactive User Role Novice
    Hi klausenbusk. From your post isn't clear how many tables do you have and where xtrabackup spent its time. Can you please upload full xtrabackup log? "Dummy" files are used by xtrabackup to identify that table is still there and was not removed between full and incremental backups. Tables without deltas will be removed during incremental prepare.
  • klausenbuskklausenbusk Entrant Inactive User Role Beginner
    Hello Sergi

    I spoke with a MariaDB dev on IRC, and after a bit of debugging from my side I found the culprit. It spends around 0.02 sec zeroing out the delta_buf here: https://github.com/MariaDB/server/bl...te_filt.cc#L80

    See: https://jira.mariadb.org/browse/MDEV-14077 for more info.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.