Backup causes replication lag

MySQL & MariaDB
1

During my backup using mariabackup - - backup(option) I found out that replication breaks at a point where mariabackup is taking the backup of NON innodb tables and all files.

The replication error I got is “waiting for room in worker thread event queue” meaning there is no memory allocated for SQL THREADS if I’m correct ?
During this backup stage it’s taking the backup of some files like said (they are log-bin) Lots of them and each one is 1GB. My question is I’m not sure why this is causing replication lag. NOTE it causes replication lag of about 32 seconds

Thank you

2

Hello, usually for non innodb tables there is a global lock taken, as otherwise there is no way to get tables in a consistent manner. Replication will be blocked while that happens, so it is normal you see lag.

2 Likes
3

Thank you for the response, but this normally happens at a particular point during the backup process, won’t happen during the initial process but towards the end.
Also, we have been doing this backup for a while and no problem no lagging. Until recently we patched box and switched slave to master

1 Like
4

@Dba1,
Non-innodb tables are always backed up last in the overall process; “towards the end”. Also, this is when binlogs are backed up as well. You can take some preliminary steps by purging out any unnecessary binlogs on the source. SHOW BINARY LOGS on the source. If the replica is caught up, then you can purge all binlogs up to the current one PURGE BINARY LOGS TO 'binlog.file.name'; Then run your backup. This will copy less binlogs.

1 Like
5

Thank you Matt, I later found that mariabackup tool provided has a bug. I think it is 10.35. What it does is that after completing backup of innodb tables it start backing up all the binary logs files. And they are huge and at this point it breaks replication because the replication is complaining of no memory For the SQL THREADS. In addition I’m using parallel replication set to conservative and sql_slave_ workers set high. After downgrading the mariabackup utilities everything went fine. I think I can always purge the binary logs and make it smaller

1 Like