Continuent Blog: Three Database Nodes vs. Two Plus a Witness in a MySQL HA Cluster

Blog

Our team of MySQL database experts regularly blogs on topics that range from MySQL availability, MySQL replication, multi-master MySQL, and MySQL-aware proxies, all the way through to ‘how to’ content for our solutions: Tungsten Clustering, Tungsten Replicator and Tungsten Proxy.

This blog was written in response to a question about the differences between a three node MySQL cluster and a two-node-plus-a-witness MySQL cluster. In summary, the two-plus-witness configuration reduces MySQL high availability (HA) capabilities; but let me explain in a little more detail…

Firstly, quorum is important in a cluster, in fact in any HA scenario - this isn’t unique to MySQL database clustering. The principle being that in the event of a failure, the nodes “vote” on what action to take. With an even number of nodes you could end up in a situation where you do not get a majority result, so you need an odd number of nodes to ensure the voting ALWAYS produces a majority result. If you’re interested to learn more, check out this blog: Why Does a MySQL / MariaDB Cluster Require an Odd Number of Nodes?

Having an odd number of nodes is important, but we recognise that for some customers the costs/overheads of having three full database nodes doesn’t work for them, or perhaps their applications can cope with the slightly raised risk, or the application isn’t read heavy and therefore doesn’t require horizontal scaling capacity (at least not right now). So let’s dive into the pros and cons of each scenario:

	Pros	Cons
Three (3) full database nodes	In the event of the primary node failing, you have 2 viable nodes, 1 will become the new primary, this still leaves you with two nodes so that reads can be load balanced across the cluster, reducing pressure on the primary. If the new primary then also fails, you still have the 3rd node to failover too. 3 nodes also give you greater horizontal capacity for distributing your read traffic. Backups taken on a replica node will still leave you with the write primary and a viable read replica, able to serve your business.	There really are none from a technical perspective. There is a greater $cost due to increased CPU/Disk Storage that will be required.
Two (2) database nodes + witness	The witness node still needs to be a server in it’s own right (VM or Physical) however there is no database installed, therefore disk storage can be minimal. CPU/Memory overhead would be lower since the host is only running the manager process. The manager process provide the quorum discussed above.	Your horizontal scaling capacity is reduced - you only have 1 viable replica node for read only traffic. In the event of a failure of the primary node, you only have a single node to handle all of the business traffic - reads/writes. If this node subsequently fails, your cluster goes down. Backups could impact performance as they would need to be taken from a viable node, since you only have 1 (the only replica) this would put additional pressure on the primary during backup and/or impact read performance and replication latency from the node being backed up.

We have a few customers who started out with 2+1 because it suited their hardware/server capacity budgets better at the time, but as their applications grew, they needed to expand. Converting a witness node into a full database node at a later date is VERY easy and can be achieved without downtime. With my DBA/Site Availability hat on, I would always opt for 3 full nodes from the outset to give me peace of mind that we have full HA, but equally, that isn’t always possible for everyone.

Hope the above helps to explain the differences between the two MySQL cluster configurations. If you have any questions please feel free to ask!

Published In

Series:

MySQL High Availability (HA) & Disaster Recovery (DR)

Tags:

syadmin/DBA, MySQL, MariaDB, Architecture, training & resources

Author

Chris Parker

VP of Customer Success, EMEA

Chris is based in the UK, and has over 20 years of experience working as a database administrator. Prior to joining Continuent, Chris managed large-scale Oracle and MySQL deployments at Warner Bros., BBC, and prior to joining the Continuent Team, he worked at the online fashion company, Net-A-Porter.

View All Chris’s Posts

Three Database Nodes vs. Two Plus a Witness in a MySQL HA Cluster

Published In

Author

Comments

Add new comment

Filtered HTML