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...

  1. Increasing the size of the replica, we've given it 104 GB RAM but the lag doesn't go...
  2. Restarting replica
  4. 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'...
  5. 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:

  1. explanation
  2. mysql configuration
  3. method




Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.