In this post I want to show new possibilities which open with Percona XtraDB Cluster.
We will create 3 nodes Cluster with nodes on different continents (Europe, USA, Japan) and each node will accept write queries.
Well, you theoretically could create 3 node traditional MySQL ring replication, but this is not what you want to use day-to-day.
To show how it works I will use Amazon m1.xlarge instances, by one in Tokyo, Ireland and North California, running RedHat Entreprise 6.2 64bit.
In fact to create instances is most time consuming task. After that using my script you will have cluster running in 5 min or less.
There however some precautions needed if you run Amazon instances.
First, you need to open ports in the firewall. For the communication the nodes need, by default, 4444, 4567, 4568 ports
(see our FAQ why)
Second, please take into account that the communication is done using open channels, and you may want to establish an encrypted connection, using it in real life.
Now, as we have running instances, you can install Cluster packages from RPM repositories.
You can follow steps from documentation.
Or I prepared simple script which does all the work: http://percona.com/downloads/Percona-XtraDB-Cluster/scripts/intercont.tar.gz,
you also can follow steps from the script to adjust it for your environment.
You just need to change nodes host names and your keys in the file:
When all nodes start, we have running 3 nodes EU<->USA<->JAPAN, and each node is ready to execute both read and write queries.
Of course you may wonder what is query response time in such environment. We can check it.
CREATE TABLE t (ID INT) and simple query:
INSERT INTO t VALUES (1)
Response time on single node in EU (no cluster setup): 0.005100 sec
Response time on two-nodes (EU<->JAP) cluster: 0.275642 sec
Response time on three-nodes (EU<->JAP<->USA) cluster: 0.294754 sec
Well, one may argue that 0.27 sec for single query is kind of big, but this is the physic law, you cannot
go faster than speed of light, and the round trip between Europe and Japan takes time (or at least until scientists figure out how to attach transaction to Faster than light neutrino).
Also note, that XtraDB Cluster can apply events in parallel and throughput should be less affected by big distance.