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
--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011