I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE records on several referring tables, which makes the deletion process very slow and time consuming, during which time some of the smaller but more frequently accessed tables are, apparently, locked (these are InnoDB tables).
Since these purges happen on regular intervals on a high-load database, I'm trying to figure out the best strategy to purge this data with minimal impact on other processes, i.e. with minimal locking of parent tables.
To give an example, I have a table structure similar to this:
+------------+ +------------+ +------------+ +-------------+
| accounts | | users | | messages | | attachments |
+------------+ +------------+ +------------+ +-------------+
| id | | id | | id | | id |
| name | | account_id | | user_id | | message_id |
| is_deleted | | name | +------------+ +-------------+
+------------+ +------------+
users.account_id REFERENCES accounts.id ON DELETE CASCADE
messages.user_id REFERENCES users.id ON DELETE CASCADE
attachments.message_id REFERENCES messages.id ON DELETE CASCADE
Assuming there are dozens of users per account, thousands of messages per user and potentially dozens attachments and other related data (e.g. tags, metadata etc.) per message - deleting an account will result in tens or hundreds of thousands of records to delete spanning multiple tables.
What would be a good strategy to take when purging data in such a case?