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:
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:
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:
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.
The post Google Compute Engine adds Percona XtraDB Cluster to click-to-deploy process appeared first on MySQL Performance Blog.