This problem is not Galera specific. Huge transactions, including long
ranged UPDATE/INSERT/DELETE autocommit statements, is a bane of any
"bottomware" (as opposed to "middleware") replication, including the
native MySQL async replication and is a major source of slave lag there.
It is due to slave being able to start processing the transaction only
*after* master has completed it. Splitting such big events into smaller
ones is a *generally* good practice that allows slave to run more
concurrently with master.
> On 2012-10-21 01:49, Freejack wrote:
>> In another thread it was recommended to set binlog_format to
>> When I tried this I got the following:
>> 121020 18:46:02 [ERROR] WSREP: Only binlog_format = 'ROW' is
>> supported. Configured value: 'STATEMENT'. Please adjust your
>> 121020 18:46:02 [ERROR] Aborting
>> Is there anyway to override this?
> 1) You should be able to set it in runtime.
> 2) 'STATEMENT' format may help if you have memory issues with DELETE.
> It is unlikely that it will help with speed.
> Now, what is happening here:
> Ranged DELETE is a heavy operation in general. Lack of primary keys
> makes it even heavier for wsrep replication since it needs to
> row IDs on its own. What you have there is that
> - at first DELETE runs on a "master" node, which may take minutes,
> but you don't notice it much because you have other sessions running
> in parallel.
> - then the result of DELETE is replicated (copied) to "slave". This
> is where 'STATEMENT' format may help, since DELETE statement
> takes much less space than the binary representation of modified
> But it helps only if you have a very slow link or memory issues.
> - then the "slave" starts to apply the event. In my experience
> deleting 3M rows on "slave" takes considerably shorter than on
> "master" when using 'ROW' format. With 'STATEMENT' format I expect it
> to take equally long.
> Still, if it took 3 minutes on "master", it may take like 2 minutes
> on "slave". Note that at this moment all the "master" has to do is
> just commit the changes to storage, which is relatively fast, while
> slave has yet to *apply* them first - and this is a time consuming
> operation. So "master" and "slave" are in a very uneven positions
> here, with "master" ready to process new requests, while slave still
> has most of the work ahead of it.
> Now, new replication events from "master" start to pile up on "slave"
> - because its *only* slave thread is busy processing the DELETE.
> we're trying to maintain some appearance of actual synchrony,
> blocks waiting for "slave" to finish its work and catch up.
> Had you have several slave threads configured that issue could have
> been mitigated to at least some extent, although for that period the
> "slave" would be in "eventually consistent" state. But you don't have
> primary keys on that table, and it seems like deletes on tables
> without PKs don't work that well with parallel applying.
> Another option is to increase slave lag tolerance. This is done on
> slave by increasing flow control limits. E.g.
> mysql> SET GLOBAL wsrep_provider_options="gcs.fc_limit=1000000;
> This will allow slave to fall behind by 1000000 transactions - but it
> also means that most of writes to that node will fail certification.
> Another thing is that you can probably use DELETE...LIMIT... syntax
> to make DELETE less chunky.