Long INSERT INTO..SELECT blocks slaves - sequential INSERTs better?

[Julien Walther]

Hi everyone,

I have the following issue regarding the efficency of a task we rergularly need to carry out in our replication scenario which I'd be happy to receive some comments about.

We need to copy a few thousand records (maybe 100,000-200,000) from one InnoDB table to another table (MyISAM). The source table is permanently and heavily accessed by users (read+write). So far we only used a single INSERT INTO .. SELECT statement for this purpose, but we noticed that this query (which takes about 30 minutes) is sort of blocking the SLAVES after having been executed on the MASTER, because of the sequential execution of the binlogs, leading to giant lags on the slaves.

We now recently changed the behavior to using many single INSERT statements, one after the other, and disconnecting and connecting to the DB between the statements. Is works perfectly; no lag. (Without always re-connecting, the lagging problem persists, because the INSERTs all come from one connection and seem to be handled as one huge block of statements, allowing no other statements "in between" on the slaves.)

Is this the way to go?

Thank you in advance.

Regards,
Julien Walther
Hamburg