3 ms·
Deleting lots of rows is perhaps the biggest weakness you are running into. What I do for some cases is "LIMIT 4000", sleep for 1 second, and repeat until no ro
by ploxiln 7y ago
Deleting lots of rows is perhaps the biggest weakness you are running into. What I do for some cases is "LIMIT 4000", sleep for 1 second, and repeat until no rows affected (tweak the numbers for your situation obviously) (usually this is for a periodic expiry/cleanup task).
- _vertigo 7y agoIt’s probably worth mentioning that at least in MySQL, this is dangerous if using statement based replication. https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.... https://dev.mysql.com/doc/refman/8.0/en/replication-features-limit.html https://dev.mysql.com/doc/refman/8.0/en/replication-features...
- ploxiln 7y agoGreat point. I happen to use row-format mixed (default on RDS), so that can make it OK. Also, often I have some other related stuff to cleanup, so the pattern is more like: SELECT id, stuff FROM table WHERE expired LIMIT 1000; do cleanup; if all successful: DELETE FROM table WHERE id IN (id, id, ...); so the delete is deterministic for statement-based replication.