Maria DB Galera Cluster pros and crons

Things to optimize our databases [ just keep in mind :) ]

• Use good index types when it’s necessary. Too many indexes will slow down your databases.
• Set the best column data type. For example, do not use a char column data type if it stores only integers.
• Avoid duplicated keys.
• Optimize your SQL queries as much as possible.

MariaDB Galera Cluster

In order to handle increasing load and especially when that load exceeds what a single server can process, it is best practice to deploy multiple MariaDB Servers with a replication solution to maintain data consistency between them. MariaDB Galera Cluster is a multi-primary replication solution that serves as an alternative to the single-primary MariaDB Replication.

How it works?

As a multi-primary replication solution, any MariaDB Community Server can operate as a Primary Server.

This means that changes made to any node in the cluster , replicate to every other node in the cluster, using certification-based replication and global ordering of transactions for the InnoDB storage engine.

In Galera, databases shall comply with some requirements. The key ones user should follow creating tables are:

InnoDB Storage Engine. Data must be stored in the InnoDB transactional storage engine.

Primary Keys. Every table that is to be replicated must have an explicit primary key, either a single or a multi-column index.

Note:

MariaDB Galera Cluster is only available for Linux operating systems. It requires use of the InnoDB storage engine and does not support XA transactions.

Architecture

MariaDB Galera Cluster architecture involves deploying multiple instances of MariaDB Community Server. The Servers are configured to use multi-primary replication to maintain consistency between themselves.

Writes made to any node in this cluster replicate to all the other nodes of the cluster

At the same time let’s consider why we need to choose the maria DB as a solution.MariaDB Galera Cluster, high availability synchronous replication solution, that provides:

• True multi-master topology
• Automatic new node provisioning
• No data loss when nodes crash
• Data replicas remain consistent
• Automatic membership control
• No complex and time-consuming failovers
• Parallel transaction execution on all cluster nodes
• No slave lag
• No lost transactions
• Reads/writes scalability
• Smaller client latencies
• Support of multi-cloud and multi-region deployments

what really happen when maria db server starts:

• Each Server attempts to establish network connectivity with the other Servers in the cluster
• Groups of connected Servers form a component
• When a Server establishes network connectivity with the Primary Component, it synchronizes its local database with that of the cluster
• As a member of the Primary Component, the Server becomes operational — able to accept read and write queries from clients
• During startup, the Primary Component is the Server bootstrapped to run as the Primary Component. Once the cluster is online, the Primary Component is any combination of Servers which includes a minimum of more than half the total number of Servers.
• A Server or group of Servers that loses network connectivity with the majority of the cluster becomes non-operational.

In planning the number of systems to provision for MariaDB Galera Cluster, it is important to keep cluster operation in mind. Ensuring that it has enough disk space and that it is able to maintain a Primary Component in the event of outages.

• Each Server requires the minimum amount of disk space needed to store the entire database. The upper storage limit for MariaDB Enterprise Cluster is that of the smallest disk in use.
• Each switch in use should have an odd number of Servers above three.
• In a cluster that spans multiple switches, each data center in use should have an odd number of switches above three.
• In a cluster that spans multiple data centers, use an odd number of data centers above three.
• Each data center in use should have at least one Server dedicated to backup operations. This can be another cluster node or a separate Replica Server kept in sync using MariaDB Replication.

Cluster Configuration

[mariadb]

# General Configuration
bind_address             = 0.0.0.0
innodb_autoinc_lock_mode = 2

# Cluster Configuration
wsrep_cluster_name    = "accounting_cluster"
wsrep_cluster_address = "gcomm://192.0.2.1,192.0.2.2,192.0.2.3"

# wsrep Provider
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_provider_options = "evs.suspect_timeout=PT10S"

Let’s see what is the meaning of those statements:

General Configuration

The innodb_autoinc_lock_mode system variable must be set to a value of 2 to enable interleaved lock mode. MariaDB Enterprise Cluster does not support other lock modes.

Cluster Name and Address

MariaDB Galera Cluster requires that you set a name for your cluster, using the wsrep_cluster_name system variable. When nodes connect to each other, they check the cluster name to ensure that they've connected to the correct cluster before replicating data. All Servers in the cluster must have the same value for this system variable.

Using the wsrep_cluster_address system variable, you can define the back-end protocol (always gcomm) and comma-separated list of the IP addresses or domain names of the other nodes in the cluster.

deployments in the cloud, you may also need to set the wsrep_node_address system variable, so that MariaDB Community Server properly informs other Servers how to reach it.

Galera Replicator Plugin

MariaDB Community Server connects to other Servers and replicates data from the cluster through a wsrep Provider called the Galera Replicator plugin. In order to enable clustering, specify the path to the relevant .so file using the wsrep_provider system variable.

Cluster Replication

MariaDB Galera Cluster implements a multi-primary replication solution.

When we write to a table on a node, the node collects the write into a write-set transaction, which it then replicates to the other nodes in the cluster.

Your application can write to any node in the cluster. Each node certifies the replicated write-set. If the transaction has no conflicts, the nodes apply it. If the transaction does have conflicts, it is rejected and all of the nodes revert the changes.

Backups

With MariaDB Galera Cluster, each node contains a replica of all the data in the cluster. As such, you run MariaDB Backup on any node to backup the available data. The process for backing-up a node is the same as for a single MariaDB Community Server.

Encryption

MariaDB Community Server supports data-at-rest encryption to secure data on disk, and data-in-transit encryption to secure data on the network.

For data-in-transit, MariaDB Galera Cluster supports encryption the same as MariaDB Server and additionally provides data-in-transit encryption for Galera replication traffic and for State Snapshot Transfer (SST) traffic.

Failover

MariaDB Galera Cluster does not provide failover capabilities on its own. MariaDB MaxScale is used to route client connections to MariaDB Enterprise Server.

Unlike a traditional load balancer, MariaDB MaxScale is aware of changes in the node and cluster states.

MaxScale takes nodes out of the distribution that initiate a blocking SST operation or Flow Control or otherwise go down, which allows them to recover or catch up without stopping service to the rest of the cluster.

Consider the facts that mariaDB cluster over mariaDB setup locally.

1. availability
2. recovery mechanism and most of all the benefits which mentioned above