Emergency

Incremental backup extremly slow (MariaDB 10.1.28 Galera Cluster)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Incremental backup extremly slow (MariaDB 10.1.28 Galera Cluster)

    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:
    Code:
    [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:
    Code:
    xtrabackup: The latest check point (for incremental): '379477920952'
    Code:
    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:
    Code:
    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

  • #2
    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.

    Comment


    • #3
      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.

      Comment

      Working...
      X