Hi all,
eveyone who uses replication might at some point noticed the “replication lag” that may occur if a table on the slave should be updated at a time, where the table is locked by another ‘slow’ (but sometimes unavoidable) query that is still at work.
Sometimes even the replication lag occurs because of slow update/delete queries, as each master-query is executed in sequential order. So, of course we always try to avoid complex update/delete queries in general.
Since MySQL 5.1 there is this new “row level” replication. Did anyone of you used that? Do you know if there is more than one “replication thread” that executes the master queries? I think they could do that because the ‘order’ of the queries is not important anymore, or am I wrong at this point?
I’m asking, because actually we’re re-developing our existing application, and we’re thinking about coding in a way that helps MySQL (5.0) to keep the replication lag as small a possible.
Example: Instead of sending a short (but heavy) UPDATE/DELETE query to the master, e.g.
UPDATE tableX
SET A = B
WHERE insertDate <= ‘…’
AND status = ‘OK’
Assume that UPDATE requires a slow table scan, which lasts about 30 seconds. If the slave has to execute that query, the whole replication will hang for at least 30 seconds, because the slave also has to do the table scan.
We thought about splitting ALL updates/deletes on a per-application basis in that way:
SELECT primaryKey FROM tableX
WHERE insertDate <= ‘…’
AND status = ‘OK’
And than sending single UPDATES to the master
foreach($result){
UPDATE tabelX
SET A = B
WHERE primaryKey = $resultsPrimaryKey
}
This should help (and actually does in our production enviroment on specific, heavy update queries) avoiding the replication lag.
What do you think about that? Is that a good idea to generally split every update on the application level? Or should we hope that MySQL 5.1 is getting stable soon? And does row-level-replication mean that each slave has multplie replication threads so that even a slow UPDATE query will not cause the replication to hang ?
Hoping for your ideas and comments. )
Greetings
Christian