Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
Message from discussion Delete_rows_log_event::find_ro w() on node2 preventing writes on node1
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Alex Yurchenko  
View profile  
 More options Oct 20 2012, 11:30 pm
From: Alex Yurchenko <alexey.yurche...@codership.com>
Date: Sun, 21 Oct 2012 06:30:11 +0300
Local: Sat, Oct 20 2012 11:30 pm
Subject: Re: [codership-team] Delete_rows_log_event::find_row() on node2 preventing writes on node1
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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.