GCP Cloudsql Mysql Replica unresponsive after mass delete on Master

Posted on

Question :

Directed here from S/O

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
  3. RESET SLAVE
  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

Thanks

Answer :

“Purge” — Do you mean a huge DELETE statement? If so, that can have many problems.

If you are “purging” most of a table, it is better to build a new table with the rows you want to keep, then use RENAME TABLE.

Details on that, plus other techniques: http://mysql.rjweb.org/doc.php/deletebig

Leave a Reply

Your email address will not be published. Required fields are marked *