xtrabackup 2.2->2.4 Updates and backup performance have been reduced.
Description
Environment
Attachments
Smart Checklist
Activity
Lalit Choudhary April 22, 2021 at 10:20 AM
So far I'm not able to reproduce this case.
Lalit Choudhary April 22, 2021 at 10:17 AM
Thank you for the details and test.
Repeated question, is there any difference in the logic or architecture between xtrabackup 2.2 and 2.4?
I do not see any explicit change mentioned in xtrabackup release but there are lot of changes in latest version.
There is no difference when using a single command. xtrabackup_history proves it.or Shouldn't I not use the stream option or the pv os command?
If there is no difference when using xtrabackup out of script then when you are running this as part of the script along with other commands they might be contributing to the total time difference.
Also if you are running this on production with the active load then many other factors impact on backup speed.
jungheelee January 14, 2021 at 9:47 AM
for example,
In xtrabackup version 2.2, the black hole engine does not follow the redo change (= log scanned up).
but, In version 2.4, such doubts as to follow.
It's a guess.
jungheelee January 14, 2021 at 9:35 AM
Hi @.
Thanks for the reply.
1. There is no difference when using a single command. xtrabackup_history proves it.
or Shouldn't I not use the stream option or the pv os command?
2. As advised, I am using xtrabackup while upgrading to xtrabackup 2.4 version. (Do not use innobackupex.)
However, only the DB server in question maintains xtrabackup 2.2 version, innobackupex.
So, In version 2.4, both xtrabackup and innobackupex had the same performance drop.
The records at that time are once again attached
(* in version 2.2, innobackupex is also used as it is because there is no problem.)
name | start_time | end_time | execute_time | ibbackup_version | server_version | tool_name | tool_command |
realdb2_full_2020-06-02 | 2020-06-02 08:15:03 | 2020-06-02 20:51:19 | 12:36:16 | xtrabackup version 2.2.13 | 5.6.45-log | innobackupex | --ibbackup=xtrabackup --user= --password=... --history=realdb2_full_2020-06-02 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp |
realdb2_full_2020-06-01 | 2020-06-01 08:15:04 | 2020-06-01 20:47:09 | 12:32:05 | xtrabackup version 2.2.13 | 5.6.45-log | innobackupex | --ibbackup=xtrabackup --user= --password=... --history=realdb2_full_2020-06-01 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp |
realdb2_full_2020-05-31 | 2020-05-31 08:15:03 | 2020-05-31 20:40:44 | 12:25:41 | xtrabackup version 2.2.13 | 5.6.45-log | innobackupex | --ibbackup=xtrabackup --user= --password=... --history=realdb2_full_2020-05-31 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp |
realdb2_full_2020-05-30 | 2020-05-30 08:15:03 | 2020-05-31 01:33:06 | 17:18:03 | 2.4.20 | 5.6.45-log | xtrabackup | --defaults-file=/etc/mysql/my.cnf --backup --target-dir=dir --tmpdir=/var/lib/mysql/tmp --login-path=user --history=realdb2_full_2020-05-30 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar |
realdb2_full_2020-05-30 | 2020-05-30 08:15:03 | 2020-05-31 01:33:06 | 17:18:03 | 2.4.20 | 5.6.45-log | xtrabackup | --defaults-file=/etc/mysql/my.cnf --backup --target-dir=dir --tmpdir=/var/lib/mysql/tmp --login-path=user --history=realdb2_full_2020-05-30 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar |
realdb2_full_2020-05-29 | 2020-05-29 08:15:04 | 2020-05-30 00:49:59 | 16:34:55 | 2.4.20 | 5.6.45-log | innobackupex | --defaults-file=/etc/mysql/my.cnf --ibbackup=xtrabackup --user= --password=... --history=realdb2_full_2020-05-29 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp |
realdb2_full_2020-05-28 | 2020-05-28 08:15:03 | 2020-05-28 19:26:02 | 11:10:59 | xtrabackup version 2.2.13 | 5.6.45-log | innobackupex | --ibbackup=xtrabackup --user= --password=... --history=realdb2_full_2020-05-28 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp |
3. The DB where the problem occurs has a very large data size. (12 Terabyte)
Unfortunately, It does not seem easy to reproduce the phenomenon when testing with a small data size DB.
4. If you have any specifics,
There are many bloackhole engine tables only in this server, which has a problem with backup performance after upgrade.
(Other DBs have very few or no blackhole engine tables))
This part is the most suspicious.
Repeated question, is there any difference in the logic or architecture between xtrabackup 2.2 and 2.4?
Lalit Choudhary January 14, 2021 at 8:36 AM
If you are using a script for backup then it might be calculating the time of the actions other than xrabackup run.
Use single command for test and compare backup time. In my tests, I don't see much difference.
also innobackex deprecated and I would suggest using xtrabackup
Details
Details
Smart Checklist
Open Smart Checklist
Smart Checklist
hi.
I recently updated my xtrabackup version from 2.2 to 2.4. (For 100 servers)
However, after the update,
some servers found that the backup performance was very slow.
I found that the backup utility was not changed from innobackupex to xtrabackup, and I confirmed that the performance problem was caused by the update.
The problem is that not all servers are relevant, but only a few servers have symptoms. Presumably, backup performance seems to be lower.
I searched the official documentation for a major change that was carried out internally from 2.2 to 2.4, but did not find it.
It seems to be a fatal issue in system operation.
The following is the backup record for PERCONA_SCHEMA.xtrabackup_history.
(+ xtrabackup 2.4 lock_time issue link : https://jira.percona.com/browse/PXB-1982)
realdb(slave)
NAME
server_version
tool_name
ibbackup_version
start_time
end_time
exec_time
lock_time
tool_command
realdb2_full_2019-12-22
5.6.45-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-22 0:15
2019-12-22 9:43
9:28:38
0
--ibbackup=xtrabackup --user=tmondba --password=... --history=realdb2_full_2019-12-22 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
realdb2_full_2019-12-21
5.6.45-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-21 0:15
2019-12-21 9:41
9:26:05
0
--ibbackup=xtrabackup --user=tmondba --password=... --history=realdb2_full_2019-12-21 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
realdb2_full_2019-12-20
5.6.45-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-20 0:15
2019-12-20 9:36
9:21:17
0
--ibbackup=xtrabackup --user=tmondba --password=... --history=realdb2_full_2019-12-20 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
realdb2_full_2019-12-19
5.6.45-log
innobackupex
2.4.16
2019-12-19 0:15
2019-12-19 14:31
14:16:44
1576733507
--defaults-file=/etc/mysql/my.cnf --ibbackup=xtrabackup --user=tmondba --password=... --history=realdb2_full_2019-12-19 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
realdb2_full_2019-12-18
5.6.45-log
xtrabackup
2.4.16
2019-12-18 0:15
2019-12-18 13:57
13:42:09
1576645032
--defaults-file=/etc/mysql/my.cnf --backup --target-dir=/mnt/Data/.backup/full --tmpdir=/var/lib/mysql/tmp --login-path=db_backup --history=realdb2_full_2019-12-18 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar
realdb2_full_2019-12-17
5.6.45-log
xtrabackup
2.4.16
2019-12-17 0:15
2019-12-17 14:01
13:46:43
1576558901
--defaults-file=/etc/mysql/my.cnf --backup --target-dir=/mnt/Data/.backup/full --tmpdir=/var/lib/mysql/tmp --login-path=db_backup --history=realdb2_full_2019-12-17 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar
realdb2_full_2019-12-16
5.6.45-log
xtrabackup
2.4.16
2019-12-16 0:15
2019-12-16 14:02
13:47:54
1576472578
--defaults-file=/etc/mysql/my.cnf --backup --target-dir=/mnt/Data/.backup/full --tmpdir=/var/lib/mysql/tmp --login-path=db_backup --history=realdb2_full_2019-12-16 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar
realdb2_full_2019-12-15
5.6.45-log
xtrabackup
2.4.16
2019-12-15 0:15
2019-12-15 14:11
13:56:02
1576386666
--defaults-file=/etc/mysql/my.cnf --backup --target-dir=/mnt/Data/.backup/full --tmpdir=/var/lib/mysql/tmp --login-path=db_backup --history=realdb2_full_2019-12-15 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar
realdb2_full_2019-12-14
5.6.45-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-13 15:30
2019-12-14 0:00
8:30:44
0
--ibbackup=xtrabackup --user=tmondba --password=... --history=realdb2_full_2019-12-14 --defaults-file=/etc/mysql/my.cnf --parallel=4 --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --compress --compress-threads=4 --stream=xbstre
realdb2_full_2019-12-13
5.6.45-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-13 0:15
2019-12-13 9:55
9:40:29
0
--ibbackup=xtrabackup --user=tmondba --password=... --history=realdb2_full_2019-12-13 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
realdb2_full_2019-12-12
5.6.45-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-12 0:15
2019-12-12 9:51
9:36:23
0
--ibbackup=xtrabackup --user=tmondba --password=... --history=realdb2_full_2019-12-12 --defaults-file=/etc/mysql/my.cnf --no-lock --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
devdb (slave)
NAME
server_version
tool_name
ibbackup_version
start_time
end_time
exec_time
lock_time
tool_command
devdb2_full_2019-12-07
5.6.45-enterprise-commercial-advanced-log
xtrabackup
2.4.16
2019-12-07 3:20
2019-12-07 14:08
10:48:14
0
--defaults-file=/etc/mysql/my.cnf --backup --target-dir=/mnt/Data/.backup/full --tmpdir=/var/lib/mysql/tmp --login-path=db_backup --history=devdb2_full_2019-12-07 --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar
devdb2_full_2019-12-06
5.6.45-enterprise-commercial-advanced-log
xtrabackup
2.4.16
2019-12-06 3:20
2019-12-06 14:06
10:46:28
0
--defaults-file=/etc/mysql/my.cnf --backup --target-dir=/mnt/Data/.backup/full --tmpdir=/var/lib/mysql/tmp --login-path=db_backup --history=devdb2_full_2019-12-06 --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar
devdb2_full_2019-12-05
5.6.45-enterprise-commercial-advanced-log
xtrabackup
2.4.16
2019-12-05 3:20
2019-12-05 14:09
10:49:31
0
--defaults-file=/etc/mysql/my.cnf --backup --target-dir=/mnt/Data/.backup/full --tmpdir=/var/lib/mysql/tmp --login-path=db_backup --history=devdb2_full_2019-12-05 --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar
devdb2_full_2019-12-04
5.6.45-enterprise-commercial-advanced-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-04 3:20
2019-12-04 9:52
6:32:37
108
--ibbackup=xtrabackup --user=tmondba --password=... --history=devdb2_full_2019-12-04 --defaults-file=/etc/mysql/my.cnf --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
devdb2_full_2019-12-03
5.6.45-enterprise-commercial-advanced-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-03 3:20
2019-12-03 9:44
6:24:23
107
--ibbackup=xtrabackup --user=tmondba --password=... --history=devdb2_full_2019-12-03 --defaults-file=/etc/mysql/my.cnf --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
devdb2_full_2019-12-02
5.6.45-enterprise-commercial-advanced-log
innobackupex
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64)
2019-12-02 3:20
2019-12-02 9:42
6:22:00
106
--ibbackup=xtrabackup --user=tmondba --password=... --history=devdb2_full_2019-12-02 --defaults-file=/etc/mysql/my.cnf --slave-info --safe-slave-backup --safe-slave-backup-timeout=1800 --stream=tar /var/lib/mysql/tmp
If you need any information or materials, please ask.
We monitoring is using PMM.
Thank you.