On 9/11/2007 4:13 AM, Cyril SCETBON wrote:

> OK.
> You don't think that it alter the performance doing 100 updates for a 
> table with 100 atributes versus one update on 2 columns ? We certainly 
> have to accept poor performance when applying log if we use log shipping 
> in this case ?

Certainly does statement based replication offer better performance when 
used for mass-updates or mass-deletes. But how on earth do you replicate 
something like

     UPDATE foo SET bar = random();

with a statement based replication system. And under MVCC, how do you 
ensure that the order and logical content of all transaction visibility 
snapshots is consistent while replicating the data? Please note that 
something as simple as

     DELETE FROM foo WHERE bar < 20;

executed by a session in READ COMMITTED transaction isolation level will 
delete different sets of rows if a concurrent transaction, setting a 
rows bar from 30 to 10 committed before or after it. This MVCC 
visibility crap will also be in the way if your answer to the above 
random() problem was "setting the random seed ...".

And finally, consider 20 concurrent sessions, each doing all sorts of 
things using temp tables, then doing

     INSERT INTO real_table SELECT nextval('some_seq'), a, b, c
         FROM temp_table;

Lets ignore for a moment the fact that actually using those 20 temp 
tables would require to replay the updates on the replica in 20 separate 
and concurrent sessions, which ultimately will lead to a replication 
design that requires each and every single master session to be 
replicated in its own slave session (if your master has 200 clients, 
your slave will eventually have 200 replication DB connections to serve).

All those 20 sessions have concurrent access to real_table. They will 
run in parallel. It is totally impossible to foresee which session will 
allocate which sequence numbers. So what's your idea to coordinate that 
mess within a statement based replication system? And don't tell me you 
want to serialize those transactions, because the point of doing 
something like that in the first place is probably performance problems, 
so serializing all application access isn't going to be the answer.

Does anyone know what MySQL using InnoDB tables would do in this case?


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #