Database clustering as a concept has become more complicated with the advent of high-volume database operations.
Clustering allows one or more servers or database instances to connect to a single database. It provides fault tolerance and load balancing in high-volume environments, and is also used for replication, particularly in cloud-based environments.
Technopedia explains :
Clustering takes different forms, depending on how the data is stored and allocated resources. The first type is known as the shared-nothing architecture.…Shared-nothing is also known as “database sharding.”…Contrast this with shared-disk architecture, in which all data is stored centrally and then accessed via instances stored on different servers or nodes.
There are several possible clustering options available to database administrators. Some alternatives for use with MySQL are worth investigating, including Galera Cluster for MySQL, while other database environments, such as MongoDB, have a specific means of addressing clustering or replication.
Master/slave is a straightforward option with failover. The master is the primary database or master copy of the database. The secondary, or slave, databases synchronize with it when any updates are made. This is a very reliable, stable approach to clustering. The slave is available for promotion if there is a problem with the master, providing little to no down time if done automatically.
Among the merits of the master/slave approach is that it allows for read/write splits using either a load balancer or a third-party tool. It also enables a quick and easy promotion of a slave to a master if a load balancer is in use.
Clustering Beyond Master/Slave
Another option is using MySQL Cluster, the official database clustering solution from Oracle. Although it provides users with benefits such as high availability, redundancy, and no downtime, it also suffers from performance-related problems. In our experience, some companies that have migrated to MySQL Cluster revert to a master/slave configuration to achieve their specific, high-availability database targets. More real-world experiences can be found in the new Datavail whitepaper, The Trade-Offs Between MySQL Cluster and Master/Slave.
Master/slave and MySQL Cluster are only two of the many database high availability options available to users today. Another is Galera Cluster for MySQL. Released in May 2007, Galera Cluster provides users with a clustering solution that is easy to use and offers high availability. Although this particular technology is not as mature as master/slave, it does appear to be far more stable than MySQL Cluster. It is a good option for those clients who really need a clustered environment.
However, using Galera doesn’t resolve all the challenges a user might have when attempting to address database clustering issues. As Patrik Sallner, the chief executive officer for MariaDB (developer of the open-source fork of MySQL) told V3:
Clustering is pretty complex, so while Galera cluster offers better performance and scaling it also brings more variables that meant it took longer to optimize.
It is important to note that Galera Cluster for MySQL is but one of the so-called flavors of Galera available today. You may also read or have heard about Percona XtraDB Cluster or MariaDB Cluster, each of which also use the Galera write set replication (WSREP) libraries.
If you are using MongoDB, there is an option known as replica set you can employ for this purpose. This allows users to keep identical copies of their data on various servers. A replica set consists of a group of servers. One is designated as the primary. This primary server takes client requests. The secondary servers maintain copies of the primary server data. In the event the primary cannot function, perhaps due to a crash, then the secondary servers select a functioning secondary to step in and act as the primary.
There are a few challenges associated with creating replica sets, including the downtime associated with initially creating the replica set on a server. But with the new versions you can use MongoDB’s cloud manager GUI to manage and configure replica set.
Download Datavail’s Whitepaper on Clustering
These are just a few of the options available and we’ve not covered all the possible pros and cons associated with each. Many of these are discussed in our white paper The Trade-Offs Between MySQL Cluster and Master/Slave available for download.
If you are debating a change in how your organization structures its databases, please contact Datavail to discuss the merits of each of these clustering options in the context of your unique operations. We can provide a custom solution designed for your enterprise. Stay tuned to the Datavail Blog for up-to-the-moment guidance with all your database issues.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.