Mysql – 9 node percona xtradb cluster

clusteringMySQLperconareplicationxtradb-cluster

We are looking to launch a 9 node percona xtradb cluster on Amazon where each ec2 node uses 20k iops disks.

I was afraid if a 9 node cluster will work and mostly what I see is 3 node clusters.

We are write heavy on some tables and also read heavy.

Any suggestions.

Best Answer

PXC and Galera Cluster do not write scale very well.

I mentioned this in my answer to the old post Transaction speed benchmarks for mySQL v5.6 replication - seems very slow where I said the following:

Please keep in mind that Percona XtraDB Cluster does not write scale very well to begin with. Note what the Documentation says under its drawbacks (Second Drawback):

This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes.

What this means for you is this: The more nodes in the Cluster, the more network communication between nodes is required to certify that all 9 nodes can commit together or rollback together. This increased communication, which makes n(n-1) calls between nodes (where n is the number of nodes), will definitely become the bottleneck for a single INSERT, UPDATE, or DELETE. It becomes worse with multi-statement transactions. This is why most examples of PXC/Galera stress using three nodes. You could probably get away with 5 nodes but you should expect worse write performance than 3 nodes.

You could probably setup 3 different 3-node clusters and use one node from each cluster in Circular Replication (See my post MySQL 5.5 Replication to Galera/Percona XtraDB Cluster). This could easily create split brain scenarios if MySQL Replication breaks between clusters.

EPILOGUE

Please stick with 3-node cluster. Should you go with 9 nodes, you must live with the network communication across zones. If so, try to make nodes reside on the East Coast in different zones, or on the West Coast in different zones.

CHOICE #1 : Percona XtraDB Cluster

I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).

You ultimately become responsible for

sizing memory requirements and disk configuration for InnoDB
remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against mysql.user is not replicated.
adding provisioning new Cluster Nodes for Reads/Writes

CHOICE #2 : Amazon RDS

Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired Options. Then, you must do this to import the new options:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.

Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.

Mysql – Percona Cluster Failed with Haproxy : Connection Limit Exceeded

OBSERVATION #1

You have max_connect_errors to 1000000 (One Million). If you ever reach that many consecutive connection failures, you would simply run FLUSH HOSTS; to clear up blocked connections. Given the high value of that setting, you will never need to run FLUSH HOSTS;

OBSERVATION #2

I am glad you mentioned your TIME_WAITs. I wrote this post in ServerFault back on Feb 01, 2012 : MySQL lowering wait_timeout value to lower number of open connections. This is a bit of a hack but it is a necessary evil in this instance.

In short, just run this on every PXC node

SEC_TO_TIMEWAIT=1
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_recycle
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_reuse

This will make TIME_WAITs timeout in 1 second. You have to run these lines every time a node reboots.

I got this idea from this post: How to forcibly close a socket in TIME_WAIT?
The accepted answer has a pictorial representation of when a TIME_WAIT comes into existence.
The answer with the idea that I liked is the one I am now suggesting.