Skip to main content

Understanding Percona XtraDB cluster

As a DevOps activist I am exploring Percona XtraDB. In a series of blogs I will share my learnings. This blog intends to capture theoretical knowledge of Percona XtraDB Cluster.

Prerequisites

  1. You should have basic knowledge of mysql. 
  2. OS - Ubuntu

What is Percona?

Percona XtraDB cluster is an open source, free MySql high availability and scalability software.
It provides:
  1. Synchronous Replication: Transaction either committed on all nodes or none.
  2. Multi-Master Replication: You can write to any node
  3. Parallel applying events on slave. Real “parallel replication”.
  4. Automatic node provisioning.
  5. Data consistency. No more unsynchronized slaves.

Introduction

  1. The cluster consists of nodes. The cluster’s recommended configuration is to have 3 nodes, however 2 nodes can be used as well.
  2. Every node is a regular Mysql / Percona server setup. You can convert your existing MySQL / Percona Server into Node and roll Cluster using it as a base or you can detach Node from Cluster and use it as a regular server.
  3. Each node will contain full copy of data.


percona.jpeg.jpg

Benefits of this approach:

  • Whenever you execute a query, it is executed locally. All data is available locally, so no remote access is required.
  • No central management. You can loose any node at any time, and cluster will continue functioning.
  • It is a good solution for scaling read workload. You can put read queries to any of the nodes.

Drawbacks:

  • Overhead of joining new node. New node will copy all data from an existing node. If it is 100 GB, it will copy 100 GB.
  • Not an effective write scaling solution. All writes have to go on all nodes.
  • Duplication of data. If you have 3 nodes, there will be 3 duplicates.

Difference between Percona XtraDB Cluster and MySQL Replication

For this we will have to look into the well known CAP theorem for distributed systems. According to this theorem, characteristics of Distributed systems are:
C - Consistency (all your data is consistent on all nodes),
A - Availability (your system is AVAILABLE to handle requests in case of failure of one or several nodes),
P - Partitioning tolerance (in case of inter-node connection failure, each node is still available to handle requests).
CAP theorem says that any Distributed system can have any two out of these three.
  • MySQL replication has: Availability and Partitioning tolerance.
  • Percona XtraDB Cluster has: Consistency and Availability.
So, MySql replication does not guarantee Consistency of data, while Percona XtraDB cluster provides consistency while it looses partitioning tolerance.

Components 

Percona XtraDb Cluster is based on:
  • Percona Server with XtraDB and includes Write Set Replication patches.
It uses:
  • Galera Library: A generic synchronous Multi-Master replication plugin for transactional applications.
  • Galera supports:
    • Incremental State Transfer (IST), useful in WAN deployments.
    • RSU, Rolling Schema Update. Schema change does not block operations against table.

Percona XtraDB cluster limitations

  • Currently replication work only with InnoDB storage engine.
That means writes to table of other types, including (mysql.*) tables, are not replicated.
DDL statements are replicated in statement level and changes to mysql.* tables will get replicated that way.
So you can issue: CREATE USER …. , this will be replicated,
but issuing: INSERT INTO mysql.user …. , will not be replicated.
You can also enable experimental MyISAM replication support with wsrep_replicate_myisam.
  • Unsupported queries:
    • LOCK/UNLOCK tables
    • lock function (GET_LOCK(), RELEASE_LOCK()....)
  • Due to cluster level concurrency control, transaction issuing COMMIT may be aborted at that stage.
There can be two transactions writing to same rows and committing in separate Percona XtraDB Cluster nodes, and only one of the them can successfully commit. The failing one will be aborted. For cluster level aborts, Percona will give back deadlock error code.
  • The write throughput of whole cluster is limited by weakest node. If one node becomes slow, whole cluster will become slow.

FEATURES


High Availability

In a basic setup with 3 nodes, the Percona XtraDB cluster will continue to function if you take any of the nodes down. Even in a situation of node crash, or if node becomes unavailable over network, the cluster will continue to work, and queries can be issued on working nodes.
In case, when there are changes in data while node was down, there are two options that Node may use when it joins the cluster:
  1. State Snapshot Transfer (SST): SST method performs full copy of data from one node to other. It’s used when a new node joins the cluster. One of the existing node will transfer data to it.
     There are three available methods of SST:
    • mysqldump
    • rsync
    • xtrabackup
Downside of “mysqldump” and “rsync” is that your cluster becomes READ-ONLY while data is copied from one node to other.
while
xtrabackup SST does not require this for entire syncing process.
  1. Incremental State Transfer (IST): If a node is down for a short period of time, and then starts up, the node is able to fetch only those changes made during the period it was down.
This is done using caching mechanism on nodes. Each node contains a cache, ring-buffer of last N changes, and the node is able to transfer part of this cache. IST can be done only if the amount of changes needed to transfer is less than N. If it exceeds N, then the joining node has to perform SST.


Multi-Master Replication

  • Multi-Master replication stands for the ability to write to any node in the cluster, and not to worry that it will get out-of-sync situation, as it regularly happens with regular MySQL replication if you imprudently write to the wrong server.
  • With Percona XtraDB Cluster you can write to any node, and the Cluster guarantees consistency of writes. That is, the write is either committed on all the nodes or not committed at all.
All queries are executed locally on the node, and there is a special handling only on COMMIT. When the COMMIT is issued, the transaction has to pass certification on all the nodes. If it does not pass, you will receive ERROR as a response on that query. After that, transaction is applied on the local node.

Let me know if you have any suggestions. You can also contact me at belwal.mohit@gmail.com.

Comments

Popular posts from this blog

EC2 Ssh Connection Refused

When ssh: connect to host ip_address port 22 Connection refused



Unable to access server???
Exactly when you see the error - “ssh: connect to host ip_address port 22: Connection refused” while connecting your AWS EC2 Instance. In order to find solution of the problem, you will go to AWS forum and other channels where you need to answers several questions first. But it's very difficult to find the actual problem. In order to get clues what the problem is, we should provide as many details as possible about what we have tried and the results we are getting. Because there are hundreds of reason why a server or service might not be accessible, also connectivity is one of the toughest issue to diagnose, especially when you are hosting something critical on your box. I've seen several topics on this problem, but none offers a solution to it.  I was not aware for what should I look at first. So I walk through from the very basics and investigated the following thing Use of verbose while ss…

jgit-flow maven plugin to Release Java Application

Introduction As a DevOps I need a smooth way to release the java application, so I compared two maven plugin that are used to release the java application and in the end I found that Jgit-flow plugin is far better than maven-release plugin on the basis of following points: Maven-release plugin creates .backup and release.properties files to your working directory which can be committed mistakenly, when they should not be. jgit-flow maven plugin doesn't create these files or any other file in your working directory.Maven-release plugin create two tags.Maven-release plugin does a build in the prepare goal and a build in the perform goal causing tests to run 2 times but jgit-flow maven plugin builds project once so tests run only once.If something goes wrong during the maven plugin execution, It become very tough to roll it back, on the other hand jgit-flow maven plugin makes all changes into the branch and if you want to roll back just delete that branch.jgit-flow maven plugin doesn…

VPC per envrionvment versus Single VPC for all environments

This blog talks about the two possible ways of hosting your infrastructure in Cloud, though it will be more close to hosting on AWS as it is a real life example but this problem can be applied to any cloud infrastructure set-up. I'm just sharing my thoughts and pros & cons of both approaches but I would love to hear from the people reading this blog about their take as well what do they think.


Before jumping right away into the real talk I would like to give a bit of background on how I come up with this blog, I was working with a client in managing his cloud infrastructure where we had 4 environments dev, QA, Pre Production and Production and each environment had close to 20 instances, apart from applications instances there were some admin instances as well such as Icinga for monitoring, logstash for consolidating logs, Graphite Server to view the logs, VPN server to manage access of people.




At this point we got into a discussion that whether the current infrastructure set-u…