MySQL Forums
Forum List  »  Replication

Re: OK, I know why it happens, but still...
Posted by: Rick James
Date: February 12, 2009 01:17AM

I find READ COMMITTED to be the "wrong" mode. But maybe you have good reason to use it. Or maybe this DELETE issue gives you a good reason not to use it.

InnoDB statements are not shipped to the slave until the COMMIT finishes on the Master. All the statements in the transaction (20K RBR rows in your case) are then suddenly shipped across the wire. And then, the slave starts executing them. Since replication is serial, it won't look at the next statement until it has finished your 20K deletes.

General rule (not just in your situation) -- Do not delete lots of rows all at once. 1000 might be a good chunk size. This means breaking up your transaction into multiple ones. If this is not acceptable to your ACID requirements, then you are caught between a rock and a hard place.

I recommend using a UNIQUE key (preferably the PRIMARY KEY) and doing it in chunks like
$leftoff = 0;
LOOP:
    $cutoff = SELECT id FROM tbl WHERE ... LIMIT 1000,1;
    BEGIN;
    DELETE FROM tbl WHERE ... AND id > $leftoff AND id <= $cutoff;
    COMMIT;
    if RowsAffected == 0 exit loop;
    $leftoff = $cutoff;
    sleep 1 second
ENDLOOP
Notes
* The LIMIT efficiently bounds your scan for stuff to delete.
* The WHERE clause is in both statements.
* Only the DELETE needs to be in the transaction; if the $cutoff record gets deleted between the SELECT and the DELETE, the code still works.
* Because of other activity, you may not delete exactly 1000 rows each time. No harm done.
* The RowsAffected check only works if you are deleting consecutive rows. Otherwise, you need to walk thru the entire table (and use some other test for exiting)
* The sleep is to give some extra breathing room for the slave. (It is not needed for non-replicating systems.)
* For MyISAM, the BEGIN/COMMIT are omitted.

Options: ReplyQuote


Subject
Views
Written By
Posted
3247
February 11, 2009 05:12AM
1905
February 11, 2009 05:17AM
1989
February 11, 2009 06:15AM
Re: OK, I know why it happens, but still...
2314
February 12, 2009 01:17AM
1993
February 12, 2009 10:13AM
2096
February 13, 2009 11:26PM
1905
February 18, 2009 04:07AM
1820
February 19, 2009 12:34AM
1842
February 19, 2009 11:21AM
1789
February 20, 2009 12:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.