Problem and suggestion about GTID in Percona Xtradb Cluser

[周彦伟]

Dear all，

sorry, I forgot the title in the last mail.

I am using Percona Xtradb Cluster now.

I found there  have different GTID  gno in each pxc cluster node，and GTID sidno use wsrep_sidno,but not server_uuid,why not the GTID gno use the wsrep seqno.

The GTID now is:

GTID =sidno:gno (wsrep_sidno:gno)

the first section is wsrep generate,the second section is trx id on local node.

The suggested GTID is:

GTID=sidno:gno(wsrep_sidno:wsrep_seqno)

Both the sections is generated by wsrep.wsrep_seqno is xid in gelara cluster.

The benifit :

There have the same GTID on every node in the pxc cluster. When we have a slave behind the cluster,we can switch replication  among each node at will.

--

                                              致
礼！
                            
                             周彦伟
                             zhouy...@gmail.com

[krunal....@percona.com]

Sorry, I am not sure I get your email completely.

Let me put forth how the things are:

1. GTID used by PXC is different from GTID used by MySQL

2. To Query PXC GTID you would something like this.

mysql> select @@global.gtid_executed;

+------------------------------------------+

| @@global.gtid_executed                   |

+------------------------------------------+

| 2304804a-e35a-ee19-6d86-d176230fe7f3:1-3 |

+------------------------------------------+

3. Where-in initial UUID is unique cluster UID that galera assign and has to be same on all cluster

to indicate a uniformity. server_uuid is different and mysql specific. There is another unique uuid that galera assign to each node in cluster different server_uuid named as wsrep_gcomm_uuid.

wsrep_gcomm_uuid                              | dcfaf3e5-1ca5-11e6-91c8-1ad8ae1e2d30

With this background can you now re-typecast as to what would like to suggest.

Regards,

Krunal

[huai wang]

Thank you for your quick reply.

I will always follow the progress of this question.

zhufeng.wang

best wishes.

2016-05-23 14:45 GMT+08:00 Krunal Bauskar <krunal....@percona.com>:

Sorry I was bit busy last week due to PXC-5.6.29 release. May find time this week.

-----

Krunal Bauskar

C/C++ Engineer, Percona

Phone 888-401-3401 x8885,  Skype: percona.kbauskar

Pune, India (GMT+5:30)
http://www.percona.com/
http://www.mysqlperformanceblog.com/

On Mon, May 23, 2016 at 12:14 PM, huai wang <doat...@gmail.com> wrote:

hi.

I am wondering whether my suggesting is reasonable, please give reply to me.

thanks.

2016-05-18 16:01 GMT+08:00 huai wang <doat...@gmail.com>:

thanks for your reply.

With background what you said, 2304804a-e35a-ee19-6d86-d176230fe7f3 is replaced by "Where-in initial UUID", but not the  mysql specific, this uuid is same on all cluster node, but , as what in "@@global.gtid_executed", "1-3" is generated by local mysql node, not pxc xid(PXC GTID), this resulted in different SESSION.GTID_NEXT in binary logs among the cluster nodes(if not yet ,please execute reset master on any node), so SESSION.GTID_NEXT is not make sense in pxc cluster. so our suggestion is that when write the gtid event of binary logs, whether we can use the Xid value(Xid event in binary log in pxc cluster) directly, so in pxc cluster,  the transaction have same Xid and SESSION.GTID_NEXT(Behind part)。

I have modify the source code: 

file: rpl_gtid_cache.cc

function: generate_automatic_gno

before modify:

​after modify:

​
​

so like above, the Binary log like as follows:

​

picture as follows is show the gtid_next and Xid event, we cat see the Xid and the behind part of gtid_next is same, and if execute reset master, it not changed. 

​

my suggestion is all.

waiting your reply.

best wishes. 

zhufeng.wang

[huai wang]

hi.

I'am sorry to trouble you again, and I am wondering whether my suggesting is reasonable, please give reply to me.

thanks.

[Krunal Bauskar]

Hello,

Sorry for delay. Things has been bit busy lately.

Finally I got chance to analyze your proposal.

* At first, it sounds interesting given that it get things in sync.

  XID, GTID_NEXT and galera-global-seqno all goes hand in hand so it easy to grasp picture.

* Fallback is it introduces gaps in gtid.

  Now there is logical gtid which is continuous and represent successful replicated action

  and then there is xid or galera-global-seqno that represent actual execution flow including fails statement.

  This needs to be investigated from master-slave replication perspective. When galera nodes are uses as master or slave.

* When I tried to re-start node-2 it rejoined the cluster but it didn't received the action post restart.

   So there are some technical gaps those needs to understood too.

-----

Krunal Bauskar

C/C++ Engineer, Percona

Phone 888-401-3401 x8885,  Skype: percona.kbauskar

Pune, India (GMT+5:30)
http://www.percona.com/
http://www.mysqlperformanceblog.com/

[huai wang]

thank you for your reply.

The understanding of mine is that , we should to investigated this problem and then make a choice, so my suggenstiong is that, whether we can add a variable to control the gtid_next(xid or galera-global-seqno), give this choice to the users.

at least, in operations of pxc, it is better when gtid_next is the same as to galera-global-seqno.

thankyou and waiting your reply.

[huai wang]

hello Krunal:

how about this problem? I am waiting sincere sincerely.

how about my suggestion? add a variable and give the choice to user?

thank you and waiting your reply.

[krunal....@percona.com]

Hi,

As pointed earlier there are issues associated with switch those needs to be sorted out to understand what's going on.

(May be if you find some bandwidth you can try to study some of those listed issues).

Besides this I don't foresee as strong need for it but it is good to have kind of feature.

That means for sure we are not going to think about in 5.6 timeframe but can look at it as optimistic thing in 5.7 timeframe.

Regards,

Krunal

[huai wang]

thank you very much, I am very happy to known that it as optimistic thing in 5.7 timeframe, and I am expecting it come on earlier.

thanks again.

--
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.
Visit this group at https://groups.google.com/group/percona-discussion.
To view this discussion on the web visit https://groups.google.com/d/msgid/percona-discussion/302cc162-b798-452e-a92d-89ec0ced821c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[grigo...@gmail.com]

Hi

I got question that probably related to this discussion.
I am trying to add now GTID to running PXC cluster, and GTIDs are different on diffetent nodes (uuid is the same, but transaction numbers differ).
Do I understand correctly that I can not rely on GTID in PXC cluster because each node maintains own's transaction sequence number?

I am using now
percona-xtradb-cluster-server-5.7 5.7.14-26.17-1.jessie

Hope someone can give me advise on this.
Thank you

> ​after modify:

>
>
> ​
> ​
>
> so like above, the Binary log like as follows:
>
> ​
>

> picture as follows is show the gtid_next and Xid event, we cat see the Xid and the behind part of gtid_next is same, and if execute reset master, it not changed. 
>
> ​
>
>

[James Wang]

My understanding (based on my testing):

Galera takes care transaction sequence for all nodes.

GTID is needed if you need to attach a slave (for reporting, backup et al) to a PXC.

[Grigorijs Kirsteins]

Thanks for reply

Maybe you can provide your configs for gtid? Maybe I am missing something, but switching from gtid_mode=OFF to gtid_mode=ON (with intermediate OFF_PERMISSIVE/ON_PERMISSIVE also) I always end up with different seqence numbers on each node.

For example - at first moment I have 3 nodes running without gtid. Then switching on gtid on first node local transaction number start counting from 1. When I next switch on on node2 GTID sequence also starts from 1 (but not takes in account sequence number from first node which has grown up while node1 was with GTID, but node2 without). If I switch on GTID on node1 while other nodes are down, then I  get instead of this sequence number in advance on node2 when it is started (as it reads IST from node1 with both transaction without GTID and with GTID set after switching on).

Until now I have not got any method how to get GTID in sync on all PXC nodes ....

--
You received this message because you are subscribed to a topic in the Google Groups "Percona Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/percona-discussion/2GkxxN587wg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-discussion@googlegroups.com.

Visit this group at https://groups.google.com/group/percona-discussion.

To view this discussion on the web visit https://groups.google.com/d/msgid/percona-discussion/a579f42e-368c-4096-8041-2dcfa4e918b9%40googlegroups.com.

[James Wang]

May try Xid (not GTID) number in binlogs 

[Grigorijs Kirsteins]

Yes, Xid is equal in all binlogs on all nodes.

But this does not give option to use replication AUTO_POSITION as it supposed to be with GTID ...

But thanks for advice