Lag on slave when backup is in progress

[Baptiste_Mille-Mathi]

Hi there,

mysql 5.40 master with 2 slaves
backup is performed on slave.
xtrabackup 2.4.26

When the backup is running on the slave the replication starts to lag for a part of the process.

crossing log entries timestamps and replication lag metrics, the replication lag starts when I see

230207 02:54:57 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
230207 02:54:57 Executing FLUSH TABLES WITH READ LOCK...
230207 02:54:57 Starting to backup non-InnoDB tables and files

and ends with

230207 03:15:32 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '5536261657671'
xtrabackup: Stopping log copying thread.
230207 03:15:32 >> log scanned up to (5536261657680)

xtrabackup command is

xtrabackup --defaults-extra-file=/root/.xtrabackup.ini --backup --compress | xbcloud ...

.xtrabackup.ini

check_privileges=1
compress-threads=4
encrypt=AES256
encrypt-key=xxxxxxxxx
encrypt-threads=4
parallel=16
stream=xbstream

I read FLUSH TABLES WITH READ LOCK option - Percona XtraBackup
but I’d like to reduce properly the locking time by finding the culprit instead of blindly kill queries.

How can I find what is locking ?

I cannot find tables that´d be in MyISAM format using the query

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine
FROM information_schema.TABLES
WHERE ENGINE='MyISAM' 
AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

Or is there a workaround to this?

best

[Marcelo_Altmann]

Hi @Baptiste_Mille-Mathi

InnoDB is the only true non blocking engine for xtrabackup. From the snap of your log it seems that it is spending time on the non-innodb files copy.
Is it possible for you to send us a copy of a full backup so we can check which part / tables / files are taking the most time under FTWRL ?

Thanks in advance.

[Ivan_Groenewold]

Hi, as per your output the backup process takes 21 minutes to backup all non-innodb tables. This should be fast if you have only innodb tables as it only backs up internal mysql tables.
Please check the timestamps on the logs, it should give you the time taken to backup each non-InnodB table e.g.

230207 11:53:08 [01] Copying ./mysql/proc.frm to /tmp/2023-02-07_11-53-05/mysql/proc.frm
230207 11:53:08 [01]        ...done

Look for any tables taking a long time and you should find the one responsible for the delay.

[Baptiste_Mille-Mathi]

@Marcelo_Altmann

From the snap of your log it seems that it is spending time on the non-innodb files copy.

Hi @Marcelo_Altmann

as I said in the first post, I found not table being in another format that InnoDB (except system tables of course)

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine
FROM information_schema.TABLES
WHERE ENGINE!='InnoDB'
AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema', 'common_schema');
Empty set (0.77 sec)

@Marcelo_Altmann

Is it possible for you to send us a copy of a full backup so we can check which part / tables / files are taking the most time under FTWRL ?

Sorry this is not possible, the data is quite big (250GB) with private data and cyphered, but I can provide you the logs if it helps.

an information I forgot is backups are performed on a slave server where not queries are run.

[Marcelo_Altmann]

HI @Baptiste_Mille-Mathi .

Sorry, I mean full backup log.

[Baptiste_Mille-Mathi]

Thanks for looking into this

log file is WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free (password will be provided directly to you)

Here the graph with the lag of this night. it starts at 00:48 and ends at 01:11 (UTC) just before the backup finishes.

I thought about a network throttling issue but the transfer runs at full capacity of the interface (1Gbps)

actually I’m wondering if the network bottleneck could be the root cause.

Best