| Row-Level Replication in MySQL 5.0 (by the application level) [message #400] |
Wed, 22 November 2006 13:34  |
 bitrockers
Messages: 11
Registered: November 2006
Location: Germany |
Junior Member |
|
|
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
[Updated on: Wed, 22 November 2006 13:38]
|
|
|
| Re: Row-Level Replication in MySQL 5.0 (by the application level) [message #401 is a reply to message #400 ] |
Wed, 22 November 2006 13:59  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Hi,
As far as I know mysql row level replication is not any help to this problem. It still uses single thread for execution.
So yes if you need to reduce the lag you need to split your update queries to be short which is rather inconvenient.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
| Re: Row-Level Replication in MySQL 5.0 (by the application level) [message #406 is a reply to message #403 ] |
Wed, 22 November 2006 19:07 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
It is typical approach if replication lag is critical.
Regarding other databases - replication is a piece which implemented very differently in different products.
Regarding partition - I spoke mostly about horizontal partitioning. Vertical is when you split so some columns are in one table and other are in another.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
