> On 2012-10-21 01:49, Freejack wrote:
>> In another thread it was recommended to set binlog_format to
>> STATEMENT.
>>  When I tried this I got the following:

>> 121020 18:46:02 [ERROR] WSREP: Only binlog_format = 'ROW' is
>> currently
>> supported. Configured value: 'STATEMENT'. Please adjust your
>> configuration.
>> 121020 18:46:02 [ERROR] Aborting

>> Is there anyway to override this?

> Hi,

> 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
> generate
> 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
> obviously
> takes much less space than the binary representation of modified
> rows.
> 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.
> Since
> we're trying to maintain some appearance of actual synchrony,
> "master"
> 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;
> gc.fc_factor=1.0";

> 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.

> Regards,
> Alex