MySQL Replication lag in slave due to Delete query - Row Based Replication

Question

I have a delete query, which delete rows by chunk (each chunk 2000)

Delete from Table1 where last_refresh_time < {time value}

Here I want to delete the rows in the table which are not refreshed for last 5days. Usually the delete will be around 10million rows. This process will be done once per-day in a little offtime.

This query executes little faster in Master, but due to ROW_BASED_REPLICATION the SLAVE is in heavy lag. As SLAVE - SQL_THREAD deletes each rows one by one from RELAY_LOG data.

We use READ_COMMITED isolation level, Is it okay to change this query transaction alone to STATEMENT_BASED replication ?

will we face any issue?

In MySql, it is mentioned like below, can someone explain this will other transaction INSERT gets affected?

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts

If other TRANSACTION INSERTS gets affected can we change ISOLATION LEVEL to REPEATABLE_READ for this DELETE QUERY TRANSACTION alone ? Is it recommended do like this?

Please share your views and Suggestions for this lag issue

Mysql - INNDOB Engine - 5.7.18

Answer 1

Don't do a single DELETE that removes 10M rows. Or 1M. Not even 100K.

Do the delete online. Yes, it is possible, and usually preferable.

Write a script that walks through the table 200 rows at a time. DELETE and COMMIT any "old" rows in that 200. Sleep for 1 second, then move on to the next 200. When it hits the end of the table, simply start over. (1K rows in a chunk may be OK.) Walk through the table via the PRIMARY KEY so that the effort to 'chunk' is minimized. Note that the 200 rows plus 1 second delay will let you get through the table in about 1 day, effectively as fast as your current code, but will much less interference.

More details: http://mysql.rjweb.org/doc.php/deletebig Note, especially, how it is careful to touch only N rows (N=200 or whatever) of the table per pass.

My suggestion helps avoid Replica lag in these ways

• Lower count (200 vs 2000). That many 'events' will be dumped into the replication stream all at once. Hence, other events are stuck behind them.
• Touch only 200 rows -- by using the PRIMARY KEY careful use of LIMIT, etc
• "Sleep" between chunks -- The Primary primes the cache with an initial SELECT that is not replicated. Hence, in Row Based Replication, the Replica is likely to be caught off guard (rows to delete have not been cached). The Sleep gives it a chance to finish the deletes and handle other replication items before the next chunk comes.

Discussion: With Row Based Replication (which is preferable), a 10M DELETE will ship 10M 1-row deletes to the Replicas. This clogs replication, delays replication, etc. By breaking it into small chunks, such overhead has a reasonable impact on replication.

Don't worry about isolation mode, etc, simply commit each small chunk. 100 rows will easily be done in less than a second. Probably 1K will be that fast. 10M will certainly not.

You said "refreshed". Does this mean that the processing updates a timestamp in the table? And this happens at 'random' times for 'random' rows? And such an update can happen multiple times for a given row? If that is what you mean, then I do not recommend PARTITIONing, which is also discussed in the link above.

Note that I do not depend on an index on that timestamp, much less suggest partitioning by that timestamp. I want to avoid the overhead of updating such an index so rapidly. Walking through the table via the PK is a very good alternative.

Answer 2

Do you really need READ_COMMITED isolation level ? It's not actually standard and ACID.

But any way. For this query you can change session isolation to REAPEATABLE_READ and use MIXED mode for binlog_format. With that you will get STATEMENT base replication only for this session.

Maybe that table usage will better fit to noSQL tool like Mongodb and TTL index.