MySQL Forums
Forum List  »  Replication

MySQL Replication lag in slave due to Delete query - Row Based Replication
Posted by: Vinieth S S
Date: August 24, 2021 04:39AM

I have a delete query

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

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.