SLOW incremental backup (mysql 5.7)

MySQL & MariaDB
,
1

Hello
We running a incremental right after the full backup is completing (1hr 45 min)

FULL:
innobackupex --user=${MYSQLROOT} --password=${MYSQLPASS} --extra-lsndir=${BACKUP_PATH} --compress --open-files-limit=100000 --stream=xbstream ${BACKUP_PATH}${BACKUP_DIRNAME} > ${BACKUP_PATH}${BACKUP_DIRNAME}${DATESTAMP}.xbstream

Incremental:
innobackupex --user=${MYSQLROOT} --password=${MYSQLPASS} --extra-lsndir=${BACKUP_PATH} --compress --open-files-limit=100000 --incremental --incremental-lsn=${lsn} --stream=xbstream ${BACKUP_PATH}${BACKUP_DIRNAME} > ${BACKUP_PATH}${BACKUP_DIRNAME}${DATESTAMP}.xbstream

Incremental Backup is also taking same time as Full backup.
Any one can help?

innobackupex version
= 2.4.24 Linux (x86_64) (revision id: b4ee263)

Mysql 5.7

2

Hi, in order for incremental backup to be fast you need changed page tracking option: XtraDB changed page tracking - Percona Server for MySQL
Do you have it enabled?

1 Like
3

Disabled currently
Tried enabling it but got error
[ERROR] unknown variable 'innodb_track_changed_pages=1'

1 Like
4

@_Sumit_Debnath Are you using Percona Server MySQL 5.7? This feature is specific to Percona’s MySQL

Additionally, innobackupex is dead software. Please switch to xtrabackup. All of the same functionality exists.

1 Like
5

I’m not sure what is your question.
I have a mysql server 5.7 which is 3 years old. And we are setting up percona for backup (POC)

1 Like
6

Ok I will switch. And update

1 Like
7

Here are the commands we use in our training class

$ mkdir /var/backups

$ FULLBACKUP=/var/backups/fullbase
$ xtrabackup --backup --parallel 4 --target-dir $FULLBACKUP

$ INCBACKUP=/var/backups/inc1
$ xtrabackup --backup --parallel 4 \
    --target-dir $INCBACKUP \
    --incremental-basedir $FULLBACKUP
1 Like
8

Hi,
I have modified my script to use xtrabackup but the incremental backup is taking 42 min while the full backup time was around 45 min.

1 Like
9

Can you please provide the updated commands you are using? Did you add --incremental-basedir to your script?

1 Like
10

No I didn’t,I used --incremental-lsn. Because we remove the backup fron the directory after copying it to s3.
${PERCONA_BACKUP_COMMAND} --backup --user=${MYSQLROOT} --password=${MYSQLPASS} --parallel 4 --target-dir=${BACKUP_PATH}${BACKUP_DIRNAME} --compress --compress-threads=4 --open-files-limit=100000 --incremental-lsn=${lsn}

1 Like
11

Please re-read my previous message and check the parameter names.

1 Like
12

still same issue incremental backup is taking 45 min. (2gb incremental backup)

1 Like
13

If your incremental and full are the same size, that means you are doing the incremental wrong and you need to reexamine my two previous examples and compare to your script.

1 Like
14

My incremental backup is of 2 GB and full backup is of 19 GB

1 Like
15

Sounds like it’s working then. Have you monitored your CPU and disk utilization during the incremental process?

1 Like
16

CPU and Memory are under 30% each. I want to share the logs privately how can i do that

1 Like
17

Sumit, please review my previous comment. In order for incremental backups to be fast, you need changed page tracking option which is only available in Percona Server for MySQL.

1 Like
18

Are you backing up to local disk or to some mount point on a shared server?

1 Like
19

Ok so if wana use percona server do i have to replace the existing Mysql 5.7 server.
and if yes then how can i restore all my database and users.

1 Like
20

Hi, you can simply stop the mysql server and replace the packages with Percona’s then start back. If you use the same version, then it is a drop-in replacement.

1 Like