PXC - continue serving reads when Non-Primary?

[Justin Ryan]

Hi all,

I'm aware that when a PXC node is in a Non-Primary cluster component, it doesn't answer any queries at all, reads or writes. Consistency trumps Availability. 

Is there any way to have a Non-Primary node continue to answer reads only? I'd like to run a cluster over a flaky WAN, where clients in each location only query their local node. Stale reads and failed writes are ok during a WAN outage, so long as SELECTs still return data. 

I'm aware of pc.ignore_sb and pc.ignore_quorum, but enabling those will also allow writes to Non-Primary nodes. 

thanks!

Justin

[Justin Swanhart]

Hi,

I would suggest hanging a traditional async replication slave off of the XtraDB Cluster machine in each data center.  

--Justin

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Justin Ryan]

Hi Justin, 

These servers see very little traffic, mostly reads, so it seems like a waste to add all that complexity and hardware.

I *think* I could do the following:

wsrep_provider_options= ”pc.ignore_sb = true"

wsrep_notify_cmd = /some/script

according to http://www.codership.com/wiki/doku.php?id=notification_command, /some/script will be called on state change with --primary <yes/no>.

If the node is not primary, the script should set mysql read_only = 1, and vice versa.

but according to http://www.mysqlperformanceblog.com/2012/07/25/percona-xtradb-cluster-failure-scenarios-with-only-2-nodes/, it looks it's saying that when connection is re-established between the nodes, each will then be its own primary component, in separate clusters. 

will need to test this out. 

[Jay Janssen]

Yes, that will permit both sides of the partition to read AND write.  Then you have two databases instead of 1.

On Aug 29, 2013, at 7:24 PM, Justin Ryan <justi...@berkeley.edu> wrote:

I *think* I could do the following:

wsrep_provider_options= ”pc.ignore_sb = true"

wsrep_notify_cmd = /some/script

according to http://www.codership.com/wiki/doku.php?id=notification_command, /some/script will be called on state change with --primary <yes/no>.

If the node is not primary, the script should set mysql read_only = 1, and vice versa.

but according to http://www.mysqlperformanceblog.com/2012/07/25/percona-xtradb-cluster-failure-scenarios-with-only-2-nodes/, it looks it's saying that when connection is re-established between the nodes, each will then be its own primary component, in separate clusters. 

Jay Janssen, MySQL Consulting Lead, Percona

http://about.me/jay.janssen

[Justin Ryan]

Hi Jay, 

So what I'm looking to do (read-only on non-primary) isn't possible? 

Looking at the docs:

pc.ignore_sb (R)	false	Should we allow nodes to process updates even in the case of split brain? This is a dangerous setting in multi-master setup, but should simplify things in master-slave cluster (especially if only 2 nodes are used).
pc.ignore_quorum (R)	false	Completely ignore quorum calculations. E.g. in case master splits from several slaves it still remains operational. Use with extreme caution even in master-slave setups, because slaves won't automatically reconnect to master in this case.

and ignore_sb: "should simplify things in master-slave cluster"

ignore_quorum: "slaves won't automatically reconnect to master in this case"

those lines make me think that with ignore_sb nodes *will* reconnect and sync after communication is restored. 

[Alex Yurchenko]

Justin,

pc.ignore_sb is just a lighter option of pc.ignore_quorum. In both cases
nodes won't reconnect as each will consider itself primary. These are
not the options to allow reads.

For now to read from non-primary node you can set wsrep_on=OFF for a
given session. This however will allow writes as well. Combining this
with read-only was supposed to yield the desired effect, but it looks
like read-only functionality is broken in the recent releases from
either party.

Regards,
Alex

On 2013-08-30 18:36, Justin Ryan wrote:
> Hi Jay,
>
> So what I'm looking to do (read-only on non-primary) isn't possible?
>
> Looking at the

> docs<http://www.codership.com/wiki/doku.php?id=galera_parameters>
> :
>
> pc.ignore_sb *(R)*falseShould we allow nodes to process updates even in

> the
> case of split brain? This is a dangerous setting in multi-master setup,
> but
> should simplify things in master-slave cluster (especially if only 2
> nodes

> are used).pc.ignore_quorum *(R)*falseCompletely ignore quorum

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Justin Ryan]

Thanks Alex.

How is pc.ignore_sb desirable or even safe in a master-slave setup then, if upon partition and re-connection the slave will become its own primary component and not continue replicating from the "master"?

Is there a bug I can track for read_only being broken?

Where is the best place to submit a feature request for an option to just go read_only on Non-Primary state?

Thanks,

Justin

email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-discussion@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.

To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-discussion@googlegroups.com.

[Alex Yurchenko]

On 2013-08-30 19:53, Justin Ryan wrote:
> Thanks Alex.
>
> How is pc.ignore_sb desirable or even safe in a master-slave setup
> then, if
> upon partition and re-connection the slave will become its own primary

There will be no reconnection.

> component and not continue replicating from the "master"?

It is mostly usable in 2-node setups, where one node failure is ALWAYS a
split-brain and frequently is a node crash, so it needs to be restarted
anyways.

> Is there a bug I can track for read_only being broken?
>
> Where is the best place to submit a feature request for an option to
> just
> go read_only on Non-Primary state?

Please use Launchpad to report and look for bugs/issues.

> Thanks,
>
> Justin