[MDEV-14077] Incremental backup extremly slow - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.28
Fix Version/s: 10.1.30, 10.2.12
Component/s: Backup
Labels:
- upstream
Environment:
OS: ArchLinux
mariabackup: Patched version with extra debug, based on 10.1 (98cd0ec536915b25a841ffc227285b15f35acef7)
MariaDB: 10.1.28

Sprint:
10.1.30

Description

Hello

Taking incremental backup like: `mariabackup --backup --incremental-lsn=xx` is way slower than taking a full backup, something like 20x+ as slow.

I did a bit of debugging, and noticed that it spent way longer in xtrabackup_copy_datafile, when doing inc backup (~ 0.02 vs ~ 0.001 (full backup)).

After a bit more trail-and-error, I found that it spent most of the time in a `memset` line. This line: https://github.com/MariaDB/server/blob/da05d0276a0569341c8bb41365dc7b05f9c4ddb7/extra/mariabackup/write_filt.cc#L80

I found that by adding the following print statement to the file: https://github.com/klausenbusk/server/commit/afcdb9128927e714ee0e3bcf5c14fea2f56855e0
Which gave me:
inc: 0.000001
inc: 0.000004
inc: 0.027387
inc: 0.027435

Ideally the code should only write delta file, when something has changed. At the moment it write a lot of "dummy" delta files, which only contain the header.

Regards Kristian Klausen

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

foo.patch
5 kB
2017-10-17 22:12
xtrabackup.cc.patch
0.9 kB
2017-10-18 00:15
xtrabackup.cc.patch
0.7 kB
2017-10-17 12:26
xtrabackup.cc.patch
0.6 kB
2017-10-16 23:35

Activity

Ascending order - Click to sort in descending order

Kristian Klausen created issue - 2017-10-16 18:43

Marko Mäkelä made changes - 2017-10-16 18:44

Field	Original Value	New Value
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]

Marko Mäkelä made changes - 2017-10-16 19:34

Assignee

Vladislav Vaintroub [ wlad ]

Marko Mäkelä added a comment - 2017-10-16 19:35

This affects also xtrabackup.

Marko Mäkelä added a comment - 2017-10-16 19:35 This affects also xtrabackup.

Kristian Klausen made changes - 2017-10-16 23:35

Attachment

xtrabackup.cc.patch [ 44345 ]

Kristian Klausen added a comment - 2017-10-16 23:39 - edited

I have attached a patch, which skip all the not changed "table" instead of creating dummy files.

Now most of the time is spent copying *.frm files, maybe we can improve that?

Kristian Klausen added a comment - 2017-10-16 23:39 - edited I have attached a patch, which skip all the not changed "table" instead of creating dummy files. Now most of the time is spent copying *.frm files, maybe we can improve that?

Kristian Klausen made changes - 2017-10-17 12:26

Attachment

xtrabackup.cc.patch [ 44349 ]

Kristian Klausen added a comment - 2017-10-17 12:27

If forgot a `if`, to ensure the logic only run when doing incremental backup. See updated patch.

Kristian Klausen added a comment - 2017-10-17 12:27 If forgot a `if`, to ensure the logic only run when doing incremental backup. See updated patch.

Kristian Klausen made changes - 2017-10-17 22:12

Attachment

foo.patch [ 44351 ]

Kristian Klausen added a comment - 2017-10-17 22:17

So I did a experiment.
The `foo.patch` add a new flag (--incremental-foo) and field to xtrabackup_checkpoints (foo), which contain a unix timestamp.
It then skip all "non-InnoDB tables and files" which hasn't changed since `incremental-foo`.

I'm not sure it worth the effort, and there is also the clock screw concern.

Kristian Klausen added a comment - 2017-10-17 22:17 So I did a experiment. The `foo.patch` add a new flag (--incremental-foo) and field to xtrabackup_checkpoints (foo), which contain a unix timestamp. It then skip all "non-InnoDB tables and files" which hasn't changed since `incremental-foo`. I'm not sure it worth the effort, and there is also the clock screw concern.

Kristian Klausen made changes - 2017-10-18 00:15

Attachment

xtrabackup.cc.patch [ 44352 ]

Kristian Klausen added a comment - 2017-10-18 00:16

New patch again, now the skipping logic should work with with and without page tracking support (bitmap).

Kristian Klausen added a comment - 2017-10-18 00:16 New patch again, now the skipping logic should work with with and without page tracking support (bitmap).

Kristian Klausen added a comment - 2017-10-18 16:22

Hmm, the "dummy" delta files is needed for the prepare phase:

"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.

https://www.percona.com/forums/questions-discussions/percona-xtrabackup/49859-incremental-backup-extremly-slow-mariadb-10-1-28-galera-cluster?p=49874#post49874

Maybe we would write a list of table at the end? Seems more effective than creating a lot of small files.

Kristian Klausen added a comment - 2017-10-18 16:22 Hmm, the "dummy" delta files is needed for the prepare phase: "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. https://www.percona.com/forums/questions-discussions/percona-xtrabackup/49859-incremental-backup-extremly-slow-mariadb-10-1-28-galera-cluster?p=49874#post49874 Maybe we would write a list of table at the end? Seems more effective than creating a lot of small files.

Vladislav Vaintroub added a comment - 2017-10-25 12:37

If making a full backup is not a big deal for you, why not use a binary diff tool, e.g rdiff, instead of this incremental backup

Vladislav Vaintroub added a comment - 2017-10-25 12:37 If making a full backup is not a big deal for you, why not use a binary diff tool, e.g rdiff, instead of this incremental backup

Kristian Klausen added a comment - 2017-10-27 06:32

> If making a full backup is not a big deal for you, why not use a binary diff tool, e.g rdiff, instead of this incremental backup
It adds unneeded complexity. I store the backup on object storage services (Backblaze B2 & Amazon S3), so I can't use complex tool without copying a lot of files back and forth.

With mariabackup I only need to push files, with rdiff I would need to pull the files from the last backup <somehow>. It would be much easier if mariabackup could just be fixed.

Kristian Klausen added a comment - 2017-10-27 06:32 > If making a full backup is not a big deal for you, why not use a binary diff tool, e.g rdiff, instead of this incremental backup It adds unneeded complexity. I store the backup on object storage services (Backblaze B2 & Amazon S3), so I can't use complex tool without copying a lot of files back and forth. With mariabackup I only need to push files, with rdiff I would need to pull the files from the last backup <somehow>. It would be much easier if mariabackup could just be fixed.

Sergei Golubchik made changes - 2017-11-01 09:34

Priority

Critical [ 2 ]

Major [ 3 ]

Sergei Golubchik made changes - 2017-11-01 09:35

Labels

upstream

Kristian Klausen added a comment - 2017-11-09 12:25

Reported upstream: https://bugs.launchpad.net/percona-xtrabackup/+bug/1731249

Kristian Klausen added a comment - 2017-11-09 12:25 Reported upstream: https://bugs.launchpad.net/percona-xtrabackup/+bug/1731249

Sergei Golubchik made changes - 2017-12-12 20:17

Sprint

10.1.30 [ 215 ]

Vladislav Vaintroub added a comment - 2017-12-12 22:36

klausenbusk, did you have a chance to look at how it performs for you in 10.1.29, since it already contains a patch that I think would speed up your backup considerably.

Vladislav Vaintroub added a comment - 2017-12-12 22:36 klausenbusk , did you have a chance to look at how it performs for you in 10.1.29, since it already contains a patch that I think would speed up your backup considerably.

Vladislav Vaintroub made changes - 2017-12-12 22:36

Labels

upstream

need_feedback upstream

Vladislav Vaintroub added a comment - 2017-12-19 14:10

Closing due to no feedback. Please feel free to comment, if it did not solve it for you

Vladislav Vaintroub added a comment - 2017-12-19 14:10 Closing due to no feedback. Please feel free to comment, if it did not solve it for you

Vladislav Vaintroub made changes - 2017-12-19 14:10

Fix Version/s		10.1.30 [ 22637 ]
Fix Version/s		10.2.12 [ 22810 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Kristian Klausen added a comment - 2018-04-14 20:17

wlad sorry for the late respond. The fix [1] does indeed fix it, full backup takes around ~ 3 min now and inc backup around ~ 1,5 min.

Many thanks
I did stumble on a packing issue, when changing my script though: https://jira.mariadb.org/browse/MDEV-15869

[1] https://github.com/MariaDB/server/commit/53c7aaf332d31d0441a533fd9a91b380169ab611

Kristian Klausen added a comment - 2018-04-14 20:17 wlad sorry for the late respond. The fix [1] does indeed fix it, full backup takes around ~ 3 min now and inc backup around ~ 1,5 min. Many thanks I did stumble on a packing issue, when changing my script though: https://jira.mariadb.org/browse/MDEV-15869 [1] https://github.com/MariaDB/server/commit/53c7aaf332d31d0441a533fd9a91b380169ab611

Julien Fritsch made changes - 2020-12-08 15:38

Labels

need_feedback upstream

upstream

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 83054 ]

MariaDB v4 [ 152994 ]

People

Assignee:: Vladislav Vaintroub

Reporter:: Kristian Klausen

Votes:: 0 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2017-10-16 18:43

Updated:: 2020-12-08 15:38

Resolved:: 2017-12-19 14:10

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration