MySQL Forums
Forum List  »  Replication

Re: is it possible replicate with mysql5..5
Posted by: Rick James
Date: July 02, 2011 09:12AM

Here's the sequence of events:

1. The complete write happens on Master. (Note: With InnoDB, 'complete' means that the COMMIT has happened.)

2. Master sends the write command (or entire transaction). With "Statement Based Replication (SBR), actual queries are sent; with Row Based Replication (RBR), changes are sent.

3. Master writes the same stuff (Step 2) to the "binlog" (on Master's disk).

4. Slave receives what was sent in step 2. (This will be milliseconds after step 2, possibly before step 3 flushes to disk.)

5. Slave's "I/O thread" writes it to "relay log" (on Slave's disk).

6. If you have a new version of MySQL, and you have enabled "Semi-sync Replication", the Slave tells the Master that it has finished step 5.

7. Meanwhile, Slave's "SQL thread" is busy reading the relay log and executing the queries; eventually (usually very soon) it gets to the statement in question, and executes it.

"Asynchronous" == There is no communication back to the Master to say "I'm finished". Even with semi-sync, the write has not been committed to the database, only to the relay-log.

Normally all those steps happen in much less than a second, even if the Slave is on the other side of the world. However, several things can slow this down...

* Resource contention (disk, CPU, locks on tables, etc) can slow down the SQL thread (step 7).

* A long running query (eg ALTER a big table) can cause a traffic jam in step 7, leading to the statements queued up in the relay log to not be started until after the long query. Note that there is only one "SQL thread". (There is talk about eventually having one thread per database, but this is risky.)

* RBR with a huge UPDATE could delay Step 2 and 4.

* Network hiccups could cause delays in step 2 and 4. (This is rare, at least in 'developed' countries.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: is it possible replicate with mysql5..5
836
July 02, 2011 09:12AM


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.