What is Percona XtraDB Cluster
Percona develop open-source database solutions based on MySQL, PostgreSQL, MongoDB and RocksDB.
Their solution is compatible with the base open-source solution with many extras that make their solution even better.
Their MySQL cluster solution XtraDB Cluster is based on Galera Cluster with some safety belts that make their solution more stable and easier to manage.
MySQL Galera Cluster Introduction
MySQL Galera Cluster is a popular solution for achieving high availability in MySQL databases, as it provides synchronous multi-master replication. This means that multiple MySQL nodes can act as both master and replica simultaneously, allowing for better fault tolerance and automatic failover in case of node failures.
On the other hand, traditional MySQL replication, where one node acts as the master and others as slaves, operates asynchronously. This means that any failure of the master node can result in downtime and potential data loss until one of the slaves is promoted to be the new master. Additionally, due to the asynchronous nature of replication, there may be data discrepancies and staleness in the replicas until they catch up with the new master, leading to potential data loss and inconsistency.
Managing traditional MySQL replication during a failure scenario can indeed be challenging and require manual intervention, fast response, and accurate execution, as it can impact production services and result in financial loss. In contrast, MySQL Galera Cluster provides automatic and immediate failover, with all nodes being able to accept read and write operations, ensuring higher availability and data consistency.
In Galera Cluster, many of the issues associated with traditional MySQL replication are addressed out of the box. Galera Cluster uses a master-master ring topology, where all nodes can actively read and write data, and all data changes are replicated synchronously to all nodes in the cluster. This ensures that data changes are acknowledged by all nodes before they are considered committed, providing high consistency and avoiding data discrepancies.
Galera Cluster requires an odd number of nodes (such as 3, 5, 7, etc.) to avoid split-brain scenarios, where multiple nodes assume the role of the master simultaneously. In case of a node failure or unresponsiveness, the remaining healthy nodes in the cluster use a quorum-based voting mechanism to decide which node should be rejected. The decision is made by majority vote, following the principle of democracy, and the healthy nodes continue to operate as a cluster.
When a broken node is fixed and rejoins the cluster, it needs to synchronize its data with the other nodes. There are two types of synchronization that can occur:
Incremental State Transfer (IST): If the failed node has missed only a small amount of data changes, it can request an IST from one of the healthy nodes in the cluster. The healthy node streams the missing data changes to the failed node, allowing it to catch up and synchronize its data quickly.
Full State Snapshot Transfer (SST): If the failed node has been down for a longer period or has lost significant data, it may need to perform a full SST. In this case, the failed node requests a complete snapshot of the database from a healthy node, and the healthy node streams the entire database to the failed node, which then applies the snapshot and catches up with the cluster.
Both IST and SST are automated processes in Galera Cluster, helping to bring the failed node back to a consistent state and ensuring data integrity in the cluster.
State Snapshot Transfer (SST) and Incremental State Transfer (IST) are two mechanisms in Galera Cluster for synchronizing data between nodes.
State Snapshot Transfer (SST): When a new node joins the cluster or a failed node needs to catch up on a significant amount of data changes, it performs a full sync with one of the existing nodes in the cluster (known as the donor node). The donor node streams a complete snapshot of its data to the joining or recovering node, which then applies the snapshot and becomes available for new requests. SST is an automated process in Galera Cluster, but it requires copying all the data from the donor node to the joining or recovering node, which can take time and resources.
Incremental State Transfer (IST): IST is a faster mechanism for synchronizing data when a node has been down for a short period of time and has missed only a small amount of data changes. In this case, the recovering node requests only the incremental changes from the other nodes' cache, rather than copying the entire dataset from a donor node. This helps in faster synchronization and reduces the impact on resources. However, for IST to work effectively, it's important to configure the gcache.size parameter with an appropriate value, so that the cache has enough space to store the changes that occurred during the downtime.
Both SST and IST are important mechanisms in Galera Cluster for ensuring data consistency and integrity among the nodes in the cluster, and they provide options for efficient synchronization depending on the duration of the downtime and the amount of data changes missed.
Galera Cluster has specific use cases where it can be highly effective, as well as some limitations to be aware of.
Use Cases for Galera Cluster:
1. Heavy OLTP (Online Transaction Processing) systems with frequent small reads and writes: Galera Cluster is well-suited for transactional workloads with a high volume of concurrent read and write operations, making it suitable for applications that require real-time processing and frequent database updates.
2. Web and mobile applications with thousands of active users: Galera Cluster can provide high availability and data consistency for web and mobile applications with a large number of concurrent users, ensuring smooth and reliable performance.
Galera Cluster Downsides:
3. Writes can be slightly slower: Due to the synchronous replication nature of Galera Cluster, where data changes must be approved by all nodes, there may be a slight overhead in write performance compared to traditional master-slave replication setups.
Not suitable for heavy writing applications: Galera Cluster may not be the best choice for applications that write a huge amount of data constantly, such as data warehousing or analytics systems, as the synchronous replication overhead may impact performance.
Locks are not supported: Galera Cluster does not support table-level locks, as locks can only be done in the local instance and not in remote instances. This can be a limitation for applications that heavily rely on table-level locking mechanisms.
4. Primary key and InnoDB engine requirement: To ensure data consistency and integrity in Galera Cluster, every table must have a primary key defined and use the InnoDB transactional engine. This requirement may necessitate changes to the database schema or application code for some applications.
Overall, Galera Cluster is a powerful solution for high availability and data consistency in certain use cases, but it's important to consider its limitations and suitability for specific application requirements before implementing it. Proper planning, configuration, and monitoring are crucial for ensuring optimal performance and reliability in a Galera Cluster environment.
Install MySQL Percona XtraDB Cluster Step by Step
The Installation Will be on Ubuntu 20:
Update the system:
sudo apt update
Install the necessary packages:
sudo apt install -y wget gnupg2 lsb-release curl
Download the repository package:
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
Install the package with dpkg :
sudo dpkg -i percona-release_latest.generic_all.deb
Refresh the local cache to update the package information:
sudo apt update
Enable the release repository for Percona XtraDB Cluster:
sudo percona-release setup pxc80
Install the cluster:
sudo apt install -y percona-xtradb-cluster
Setup Galera Cluster parameters in my.cnf in the first node:
Cluster name
wsrep_cluster_name=xtradb-cluster
The Location of Galera extension library
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
The Nodes IP’s or server names that are part of Galera Cluster
wsrep_cluster_address=gcomm://10.10.10.1,10.10.10.2,10.10.10.3
Set Galera Cache Size to do incremental IST rather than SST
wsrep_provider_options = “gcache.size=2G”
Galera cluster support on ROW Binlog format
binlog_format=ROW
The Node IP
wsrep_node_address =10.10.10.1
Node Name
wsrep_node_name=node-01
Galera State Transfer Method – SST . Using Percona xtrabackup
wsrep_sst_method=xtrabackup-v2
Disable data encryption between nodes (default is on and should be used in prod)
pxc_encrypt_cluster_traffic = OFF
Bootstrap first node
systemctl start mysql@bootstrap.service
Connect to the database and check node and cluster status
show status like ‘wsrep%’ and check these 2 parameters:
+—————————+—————+
| Variable_name | Value |
+—————————+—————+
| wsrep_local_state_comment | Synced |
| wsrep_incoming_addresses |10.10.10.1:3306 |
+—————————+—————-+
Copy galera parameters to other nodes and replace wsrep_node_name and wsrep_node_address with the node details
start the second node. once it’s up. Connect to the database and check the cluster and node status . you should be to see that it joined to the cluster
show status like ‘wsrep%
+—————————+—————+
| Variable_name | Value |
+—————————+—————+
| wsrep_local_state_comment | Synced |
| wsrep_incoming_addresses |10.10.10.1:3306, 10.10.10.2:3306 |
+—————————+—————-+
once the second node is up. Start the third node and check its status as well.
+—————————+—————+
| Variable_name | Value |
+—————————+—————+
| wsrep_local_state_comment | Synced |
| wsrep_incoming_addresses |10.10.10.1:3306, 10.10.10.2:3306, 10.10.10.3:3306 |
+—————————+—————-+
Done.
No comments:
Post a Comment