We have a Master/Replica configuration for Mysql innodb(5.7.32) databases in Cloud SQL... We have a single Table (let's call it Master table) partitioned on Two keys having both primary and non-clustered indexes... It's a row-based replication with automatic disk increase on both instances... It's not a HA configuration so it's not a failover replica...
What we're trying to do... We're trying to purge the master table back to N number of days... This is done for multiple reasons so let's say this is a client requirement...
What's the issue... Whenever we're purging the master table it just stalls the replica, it deletes a certain number of rows on the replica and then just passes out... The number of records in a single purge is around 5 million rows... The time the purge starts on the master, the lag starts... It's a totally repeatable issue... we know it's caused because it's a row-based, sequential replication so
What we've tried so far...
- Increasing the size of the replica, we've given it 104 GB RAM but the lag doesn't go...
- Restarting replica
- RESET SLAVE
- Trying enabling parallel replication https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#configuring-parallel-replication... every single time I tried this it failed with an 'Unknown error occurred'...
- Trying setting it to a Statement-based replication by the
SET binlog_format="STATEMENT"command but the "root" user doesn't have the privilege and gets an'access denied'error...
Now the question... what am I missing in my:
- explanation
- mysql configuration
- method
Thanks
