I’m happy to announce that Google has added Click-to-deploy functionality for Percona XtraDB Cluster (PXC) on Google Cloud Compute nodes. This gives you the ability to rapidly spin up a cluster for experimentation, performance testing, or even production use on Google Compute Engine virtual machines.
What is Percona XtraDB Cluster?
Percona XtraDB Cluster is a virtually synchronous cluster of MySQL Innodb nodes. Unlike conventional MySQL asynchronous replication which has a specific network topology of master and slaves, PXC’s nodes have no specific topology. Functionally, this means that there are no masters and slaves, so you can read and write on any node.
Further, any failure in the cluster does not require any re-arranging of the replication topology. Instead, clients just reconnect to another node and continue reading and writing.
We have a ton of material about Percona XtraDB Cluster in previous posts, in the PXC manual, and in various webinars. If you want a concentrated hour overview of Percona XtraDB Cluster, I’d recommend watching this webinar.
How do I use Click-to-deploy?
Simply visit Google Cloud’s solutions page here: https://cloud.google.com/solutions/percona to get started. You are given a simple setup wizard that allows you choose the size and quantity of nodes you want, disk storage type and volume, etc. Once you ‘Deploy Cluster’, your instances will launch and form a cluster automatically with sane default tunings. After that, it’s all up to you what you want to do.
Seeing it in action
Once your instances launch, you can add an SSH key to access (you can also install the Google Cloud SDK). This, handily, creates a new account based on the username in the SSH public key text. Once I add the key, I can easily just ssh to the public IP of the instance and I have my own account:
1 2 3 4 5 6 7 8 9 10 11 | jayj@~ [500]$ ssh [public ip] Linux percona-mysql-niyr 3.2.0-4-amd64 #1 SMP Debian 3.2.60-1+deb7u3 x86_64 The programs included with the Debian GNU/Linux system are free software; the exact distribution terms for each program are described in the individual files in /usr/share/doc/*/copyright. Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent permitted by applicable law. jayj@percona-mysql-niyr:~$ |
Once there I installed sysbench 0.5 from the Percona apt repo:
1 2 3 4 5 6 7 8 | jayj@percona-mysql-niyr:~$ sudo -i root@percona-mysql-niyr:~# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A root@percona-mysql-niyr:~# echo "deb http://repo.percona.com/apt wheezy main" > /etc/apt/sources.list.d/percona.list root@percona-mysql-niyr:~# apt-get update; apt-get install sysbench -y ... Setting up sysbench (0.5-3.wheezy) ... root@percona-mysql-niyr:~# logout jayj@percona-mysql-niyr:~$ |
Now we can load some data and run a quick test:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | jayj@percona-mysql-niyr:~$ sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=16 --oltp-table-size=1000000 --oltp-auto-inc=off --num-threads=8 --mysql-user=root --mysql-password=[the admin password you set in the wizard] --mysql-db=test run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 8 Random number generator seed is 0 and will be ignored Threads started! thread prepare4 Creating table 'sbtest5'... thread prepare2 Creating table 'sbtest3'... thread prepare6 Creating table 'sbtest7'... thread prepare0 Creating table 'sbtest1'... thread prepare7 Creating table 'sbtest8'... thread prepare1 Creating table 'sbtest2'... thread prepare5 Creating table 'sbtest6'... thread prepare3 Creating table 'sbtest4'... Inserting 100000 records into 'sbtest5' Inserting 100000 records into 'sbtest3' Inserting 100000 records into 'sbtest7' Inserting 100000 records into 'sbtest1' Inserting 100000 records into 'sbtest8' Inserting 100000 records into 'sbtest2' Inserting 100000 records into 'sbtest6' Inserting 100000 records into 'sbtest4' Creating table 'sbtest13'... Creating table 'sbtest15'... Inserting 100000 records into 'sbtest13' Inserting 100000 records into 'sbtest15' Creating table 'sbtest11'... Creating table 'sbtest12'... Inserting 100000 records into 'sbtest11' Creating table 'sbtest16'... Inserting 100000 records into 'sbtest12' Creating table 'sbtest10'... Creating table 'sbtest14'... Creating table 'sbtest9'... Inserting 100000 records into 'sbtest16' Inserting 100000 records into 'sbtest9' Inserting 100000 records into 'sbtest10' Inserting 100000 records into 'sbtest14' OLTP test statistics: queries performed: read: 0 write: 608 other: 32 total: 640 transactions: 0 (0.00 per sec.) read/write requests: 608 (11.33 per sec.) other operations: 32 (0.60 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 53.6613s total number of events: 10000 total time taken by event execution: 0.0042s response time: min: 0.00ms avg: 0.00ms max: 0.02ms approx. 95 percentile: 0.00ms Threads fairness: events (avg/stddev): 1250.0000/3307.19 execution time (avg/stddev): 0.0005/0.00 jayj@percona-mysql-niyr:~$ sysbench --test=/usr/share/doc/sysbench/tests/db/update_index.lua --oltp-tables-count=16 --oltp-table-size=1000000 --num-threads=8 --mysql-user=root --mysql-password=[the admin password you set in the wizard]--mysql-db=test --max-requests=0 --max-time=10 --report-interval=1 --oltp-auto-inc=off --rand-init=on --rand-type=uniform run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 8 Report intermediate results every 1 second(s) Initializing random number generator from timer. Random number generator seed is 0 and will be ignored Threads started! [ 1s] threads: 8, tps: 0.00, reads: 0.00, writes: 1396.43, response time: 10.85ms (95%), errors: 0.00, reconnects: 0.00 [ 2s] threads: 8, tps: 0.00, reads: 0.00, writes: 1314.13, response time: 14.91ms (95%), errors: 0.00, reconnects: 0.00 [ 3s] threads: 8, tps: 0.00, reads: 0.00, writes: 1382.87, response time: 12.34ms (95%), errors: 0.00, reconnects: 0.00 [ 4s] threads: 8, tps: 0.00, reads: 0.00, writes: 949.09, response time: 12.88ms (95%), errors: 0.00, reconnects: 0.00 [ 5s] threads: 8, tps: 0.00, reads: 0.00, writes: 1312.01, response time: 11.27ms (95%), errors: 0.00, reconnects: 0.00 [ 6s] threads: 8, tps: 0.00, reads: 0.00, writes: 898.92, response time: 11.64ms (95%), errors: 0.00, reconnects: 0.00 [ 7s] threads: 8, tps: 0.00, reads: 0.00, writes: 1541.71, response time: 10.59ms (95%), errors: 0.00, reconnects: 0.00 [ 8s] threads: 8, tps: 0.00, reads: 0.00, writes: 1551.35, response time: 11.48ms (95%), errors: 0.00, reconnects: 0.00 [ 9s] threads: 8, tps: 0.00, reads: 0.00, writes: 923.07, response time: 10.40ms (95%), errors: 0.00, reconnects: 0.00 [ 10s] threads: 8, tps: 0.00, reads: 0.00, writes: 1273.99, response time: 11.01ms (95%), errors: 0.00, reconnects: 0.00 OLTP test statistics: queries performed: read: 0 write: 12551 other: 0 total: 12551 transactions: 0 (0.00 per sec.) read/write requests: 12551 (1254.65 per sec.) other operations: 0 (0.00 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 10.0036s total number of events: 12551 total time taken by event execution: 79.9602s response time: min: 1.13ms avg: 6.37ms max: 389.52ms approx. 95 percentile: 11.68ms Threads fairness: events (avg/stddev): 1568.8750/4.81 execution time (avg/stddev): 9.9950/0.00 |
So, we see ~1200 tps on an update test in our little cluster, not too bad!
What it is not
This wizard is a handy way to get a cluster setup for some experimentation or testing. However, it is not a managed service: after you launch it, you’re responsible for maintaining, tuning, etc. You could use it for production, but you may want some further fine tuning, operational procedures, etc. All of this is absolutely something Percona can help you with.
Hi,
Is it possible to add external node(s) to this cluster? I mean for example I have a working server somewhere and want to keep it, but also want to set up an XtraDB cluster on Google Compute Engine and want the old server to be the part of the new cluster.
Many thanks,
Jozsef Toth
@Jozsef,
There’s nothing magical about the cluster this tool creates, it’s a normal cluster and you can spin up other machines for them to join the cluster. Once the cluster is spun up initially by this tool, then the management (adding/removing nodes, etc.) is up to you.
The main limitation in what you are asking here (and in any PXC cluster) would be that all the nodes must be able to communicate with each other. So, providing you can work out the networking/firewall details between all these nodes, then you can connect them.
One caveat would be commit latency would increase on the GCE cluster by adding an external node. How much depends on how far away the extra node is.
A second caveat would be that this setup tool will create a new cluster with the nodes in GCE. You’d have to add the other node later, which would copy the data from the new cluster to the older node. If you want the data sync to go in the opposite direction, you’d need to ensure the old node was a cluster first, and then connect the GCE nodes to it.
A third caveat would be what will happen when the GCE nodes and your remote node lose network connectivity for whatever reason. What do you want to happen in terms of node failure? I’d consider this carefully based on how Galera handles node failure and network partitions before putting something like this into production.