Hi
I have been troubleshooting replication lag on a mysql8 server… setting sync_binlog=0 HELPED… at lot… but i am kinda scared of doing this…
i have found a ton of articles talking about why not to do this on a stand-alone or master but i haven’t found anything good talking about how bad of an idea this in on a slave…
my setup is real simple – 1 master, 1 slave… the main purpose of the setup is to be able to run backups (using xtrabackup) off the slave instead of the master… the best case scenario is i would like my script to stop the slave, run the backup then restart the slave – but at the moment the replication is not fast enough… the backups takes about 10 hours due to the size of the db (7 TB) –
thanks
larry
2 Likes
Hi @larryschul thanks for posting to the Percona forums!
on a replica you are more able to relax durability concerns. So you are safe to configure sync_binlog=0 on your replica.
Tho you didn’t ask, if you use Percona XtraBackup on the replica you should not need to stop replication as it is designed to operate in a non-blocking fashion. So that should help simplify your backup procedure.
1 Like
Hi @larryschul , my apologies for too aggressively closing the topic as solved. I’m including your comments here and will reply below
the problem with xtrabackup on prod is the fact that i cannot issue a create table command. my app created a lot of temp tables and also tables for routine use. i cannot stop the app from doing this, and from my experience and my reading everything seems to point to the fact that createe table cannot be issued while a backup is occurringg — any way around this?
What happens during both XtraBackup and CREATE TABLE is that they need a metadata lock on the database aka all queries to be finished so that these operations can begin. In practice what happens is that one event will stall the next. So if you have a lot of CREATE TABLE running, your XtraBackup process will wait in a queue until the CREATE TABLE statements complete, then will get the lock it needs and start the backup. Likely your subsequent CREATE TABLE statements will simply start and not finish, they will be waiting until either the app gives up or a timeout is reached, or your backup concludes.
If you can’t change the app behaviour then you should consider setting up a replica where these CREATE TABLE statements are only executed via replication, and then take your XtraBackup off the replica. This way you don’t block the app, at the cost of probably making the replica lag in replication until the backup completes, because of the stalled CREATE TABLE queries as they are serially processed on the replica and can’t apply during backup window.
If you do this also then consider using [pt-table-checksum](https://docs.percona.com/percona-toolkit/pt-table-checksum.html) to ensure your replica has a consistent copy of the data.
1 Like
so in your opinion … when i backup the replica with xtrabackup – i should issue a stop replica. lets the backup run… then start replica… and let it catchup – is the safest way of doing the backup?
thanks
larry
1 Like
Hi @larryschul
With XtraBackup you should NOT need to do any STOP REPLICA command. XtraBackup will work seamlessly with your replication configuration
1 Like
so if the master is issuing create tables during the backup period — the replica will just sorta hang while xtrabackup does its thing… but all should be seamless and not conflict— correct?
1 Like
That’s correct - there are two threads involved in replication, the IO thread which will continue fetching binary logs from the Primary during backup / during CREATE TABLE events, and the SQL thread which applies the binary logs locally to the replica, it will stall when it sees a CREATE TABLE and then when the backup finishes it will be lagged behind (visible in the Seconds_behind_master variable from SHOW REPLICA STATUS\G) and give it time to catch up.
1 Like