I have read some articles about Proxysql with the Percona Xtra cluster. I have some concerns about this one because I don’t know how do Proxysql knows to read/write sequence when there are 2 separated Proxysql in topology.
Example: There are 2 commands from ELB to ProxySql: 1 for each
The “UPDATE 1” command is forwarded to the cluster by ProxySQL1
The “UPDATE 2” command is forwarded to the cluster by ProxySql 2 and running depend on the value of Update 1.
So if UPDATE 2 was running on the cluster before UPDATE 1 because it is processed first => UPDATE 2 won’t run anymore. Or the ProxySql1 was down when processing => Same Result
How do we solve this issue or Do I misunderstand this topology?
Thanks for reading
If update2 depends on the value of update1 then those 2 should be in a transaction. Even if you have 2 proxies, a transaction is tied to a client session that will be handled by a single proxy at a time. Makes sense?
I understood your meaning.
So how does the ELB know to push the update(1+2) to a single proxy? And how is the transaction tied to a client session? Could you explain for me or any document about this case
Thank you very much for your info.
I think you need to review how ELB works. When there is a new incoming connection request, the ELB will redirect the client to one of the proxies based on some algorithm (e.g. round robin). For the life of the connection, that specific connection will be tied to that proxy.
Hope that helps
I’m still wondering to know that what if 2 update commands don’t come from a single client session ( and load-balanced to 2 separated proxies. So what happened in that case.
Well if the updates come in different client sessions but one depends on the other then that is a design problem. They should be part of a transaction.