PERCONA STANDALONE SERVER

As a DevOps activist I am exploring Percona XtraDB. In a series of blogs I will share my learnings. This blog intends to capture step by step details of installation of Percona XtraDB in Standalone mode

Introduction:

Percona Server is an enhanced drop-in replacement for Mysql. It offers breakthrough performance, scalability, features, and instrumentation.

Percona focus on providing a solution for the most demanding applications, empowering users to get the best performance and lowest downtime possible.

The Percona XtraDB Storage Engine:

Percona XtraDB is an enhanced version of the InnoDB storage engine, designed to better scale on modern hardware, and including a variety of other features useful in high performance environments. It is fully backwards compatible, and so can be used as a drop-in replacement for standard InnoDB.
Percona XtraDB includes all of InnoDB’s robust, reliable ACID-compliant design and advanced MVCC architecture, and builds on that solid foundation with more features, more tunability. more metrics, and more scalability.
It is designed to scale better on many cores, to use memory more efficiently, and to be more convenient and useful.

Installation on ubuntu:

STEP 1: Add Percona Software Repositories

$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

STEP 2: Add this to /etc/apt/sources.list:

deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

STEP 3: Update the local cache

$ apt-get update

STEP 4: Install the server and client packages

$ apt-get install percona-server-server-5.6 percona-server-client-5.6

STEP 5: Start Percona Server

$ service mysql start

Let me know if you have any suggestions.

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

You should have basic knowledge of mysql.
OS – Ubuntu

What is Percona?

Percona XtraDB cluster is an open source, free MySql high availability and scalability software.

It provides:

Synchronous Replication: Transaction either committed on all nodes or none.
Multi-Master Replication: You can write to any node
Parallel applying events on slave. Real “parallel replication”.
Automatic node provisioning.
Data consistency. No more unsynchronized slaves.

Introduction

The cluster consists of nodes. The cluster’s recommended configuration is to have 3 nodes, however 2 nodes can be used as well.
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.
Each node will contain full copy of data.

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:

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.

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.

Getting Started with Percona XtraDB Cluster

Percona XtraDB Cluster

Why Cluster Mode Introduction:

Percona XtraDB cluster is High Availability and Scalability solution for MySQL users which provides
Synchronous replication : Transaction either committed on all nodes or none.
Multi-master replication : You can write to any node.
Parallel applying events on slave : parallel event application on all slave nodes
Automatic node provisioning
Data consistency
Straight into the Act:Installing Percona XtraDB Cluster

Pre-requisites/Assumptions

OS – Ububtu
3 Ubuntu nodes are available

For the sake of this discussion lets name the nodes as

node 1
hostname: percona_xtradb_cluster1
IP: 192.168.1.2

node 2
hostname: percona_xtradb_cluster2
IP: 192.168.1.3

node 3
hostname: percona_xtradb_cluster3
IP: 192.168.1.4

Repeat the below steps on all nodes

STEP 1 : Add the Percona repository

$ echo "deb http://repo.percona.com/apt precise main" >> /etc/apt/sources.list.d/percona.list
$ echo "deb-src http://repo.percona.com/apt precise main" >> /etc/apt/sources.list.d/percona.list
$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

STEP 2 : After adding percona repository, Update apt cache so that new packages can be included in our apt-cache.

$ apt-get update

STEP 3 : Install Percona XtraDB Cluster :

$ apt-get install -y percona-xtradb-cluster-56 qpress xtrabackup

STEP 4 : Install additional package for editing files, downloading etc :

$ apt-get install -y python-software-properties vim wget curl netcat

With the above steps we have, installed Percona XtraDB Cluster on every node. Now we’ll configure each node, so that a cluster of three nodes can be formed.

Node Configuration:

Add/Modify file /etc/mysql/my.cnf on first node :

[MYSQLD] #This section is for mysql configuration
user = mysql                                                   
default_storage_engine = InnoDB                  
basedir = /usr
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
innodb_autoinc_lock_mode = 2
log_queries_not_using_indexes = 1
max_allowed_packet = 128M
binlog_format = ROW
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_node_address = 192.168.1.2
wsrep_cluster_name="newcluster"
wsrep_cluster_address = gcomm://192.168.1.2,192.168.1.3,192.168.1.4
wsrep_node_name = cluster1
wsrep_slave_threads = 4
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sst:secret

[sst] #This section is for sst(state snapshot transfer) configuration 
streamfmt = xbstream

[xtrabackup] #This section is defines tuning configuration for xtrabackup
compress
compact
parallel = 2
compress_threads = 2
rebuild_threads = 2

Note :
wsrep_node_address = {IP of current node}
wsrep_cluster_name= {Name of cluster}
wsrep_cluster_address = gcomm://{Comma separated IP address’s which are in cluster}
wsrep_node_name = {This is name of current node which is used to identify it in cluster}

Now as we have done node configuration. Now start first node services:
Start the node :

$ service mysql bootstrap-pxc

Make sst user for authentication of cluster nodes :

$ mysql -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' IDENTIFIED BY 'secret';"

Check cluster status :

$ mysql -e "show global status like 'wsrep%';"

Configuration file for second node:

[MYSQLD]
user = mysql
default_storage_engine = InnoDB
basedir = /usr
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
innodb_autoinc_lock_mode = 2
log_queries_not_using_indexes = 1
max_allowed_packet = 128M
binlog_format = ROW
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_node_address = 192.168.1.3
wsrep_cluster_name="newcluster"
wsrep_cluster_address = gcomm://192.168.1.2,192.168.1.3,192.168.1.4
wsrep_node_name = cluster2
wsrep_slave_threads = 4
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sst:secret

[sst]
streamfmt = xbstream

[xtrabackup]
compress
compact
parallel = 2

After doing configuration, start services of node 2.
Start node 2 :

$ service mysql start

Check cluster status :

$ mysql -e "show global status like 'wsrep%';"

Now similarly you have to configure node 3. Changes are listed below.

Changes in configuration for node 3 :
      wsrep_node_address = 192.168.1.4
      wsrep_node_name = cluster3

Start node 3 :

$ service mysql start

Test percona XtraDb cluster:

Log-in by mysql client in any node:

<code prettyprint="" style="color: black; word-wrap: no

mysql>create database opstree;
mysql>use opstree;
mysql>create table nu113r(name varchar(50));
mysql>insert into nu113r values("zukin");
mysql>select * from nu113r;

Check the database on other node by mysql client:

mysql>show databases;

Note : There should be a database named “opstree”.

mysql>use opstree;

mysql>select * from nu113r;

Note : Data will be same as in the previous node.

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-up is the right one where we are having a separate VPC per environment or the ideal setup would have been a single VPC and the environments could have been separated by subnet’s i.e a pair of subnet(public private) for each environment

Both approaches had some pros & cons associated with them

Single VPC set-up

Pros:

You only have a single VPC to manage
You can consolidate your admin app’s such as Icinga, VPN server.

Cons:

As you are separating your environments through subnets you need granular access control at your subnet level i.e instances in staging environment should not be allowed to talk to dev environment instances. Similarly you have to control access of people at granular level as well
Scope of human error is high as all the instances will be on same VPC.

VPC per environment setup

Pros:

You have a clear separation between your environments due to separate VPC’s.
You will have finer access control on your environment as the access rules for VPC will effectively be access rules for your environments.
As an admin it gives you a clear picture of your environments and you have an option to clone you complete environment very easily.

Cons:

As mentioned in pros of Single VPC setup you are at some financial loss as you would be duplicating admin application’s across environments

In my opinion the decision of choosing a specific set-up largely depends on the scale of your environment if you have a small or even medium sized environment then you can have your infrastructure set-up as “All environments in single VPC”, in case of large set-up I strongly believe that VPC per environment set-up is the way to go.

Let me know your thoughts and also the points in favour or against of both of these approaches.

Revert a patch in most awesome way

If you are a Release Engineer, System Admin or Support Engineer you have definitely come across a requirement where you have to apply patches to the target systems be it production or non-production. I’m assuming that you are using some automated system to manage the patches i.e applying them and reverting them. In this blog I would be discussing about the standard way of patch management and how you can have an out of the box solution to revert your patch in most simplistic way and without much fuss. At the end of the blog I would like to see an expression where you will say what the hell it’s so awesome yet so simple :).

People usually use some tool to apply patch to a target system which in addition to applying a patch also manage the history the patches so that it can be reverted in case the patch goes wrong. The patch history usually contains below details:

The new files that were added in the patch, while reverting the patch those files should be deleted.
The files that were deleted by the patch, while reverting the patch the deleted files should be restored back.
The files that were modified by the patch, while reverting the patch the modified files should be restored back.

You can definitely create a tool that can revert the patch for you as the use cases are not much, but do you really need to put this much effort if you can have an out of the box solution for this. What if I tell you that we use git for managing our patch history and reverting them. As git comes with a local repository concept so we created a local git repository at our app server codebase location only. Git comes with all the file level tracking we map each patch with one git commit, so at the time of reverting a specific patch you can ask git to simply revert the commit for you.

Extra steps to be done after applying patch:
To make git track the changes done in patch, you just need to perform 2 extra commands

git add . : This command will track all the files that have been modififed, added or deleted in the system.
git commit -m “Applying Patch” : This command actually adds the files information tracked by previous command with a message in the git system

Steps to be done in reverting changes done by a patch:
Once you have all the information tracked in git it will become no-brainer to revert the patches.

To view the details of all the patches: You can use git log command that will provide you the list of all the patches that you have applied or reverts that you have done

sandy@sandy:~/test/app1$ git log commit f622f1f97fc44f6897f9edc25f9c6aab8e425049 Author: sandy Date: Thu Jun 19 15:19:53 2014 +0530

Patch 1 on release2

commit 9a1dd81c7799c2f83d897eed85914eecef304bf0
Author: sandy
Date: Thu Jun 19 15:16:52 2014 +0530

Release 2

commit 135e04c00b3c3d5bc868f7774a5f284c3eb8cb29
Author: sandy
Date: Thu Jun 19 15:16:28 2014 +0530

Release 1

Now Reverting a patch is as simple as executing a simple command git revert, with the commit id of the patch

git revert f622f1f97fc44f6897f9edc25f9c6aab8e425049 [master 0ba533f] q Revert "Patch 1 on release2" 1 file changed, 1 deletion(-)

If you run git log command, you will see the patch revert history as well

sandy@sandy:~/test/app1$ git log commit 0ba533fda95ed4d7fcf0b7e6b23cd1a5589946a7 Author: sandy Date: Thu Jun 19 15:20:24 2014 +0530

Revert "Patch 1 on release2"

This reverts commit f622f1f97fc44f6897f9edc25f9c6aab8e425049.commit f622f1f97fc44f6897f9edc25f9c6aab8e425049
Author: sandy
Date: Thu Jun 19 15:19:53 2014 +0530

Patch 1 on release2

commit 9a1dd81c7799c2f83d897eed85914eecef304bf0
Author: sandy
Date: Thu Jun 19 15:16:52 2014 +0530

Release 2

commit 135e04c00b3c3d5bc868f7774a5f284c3eb8cb29
Author: sandy
Date: Thu Jun 19 15:16:28 2014 +0530

Release 1

I hope this blog has given you a very different perspective of managing the patches, let me know your thoughts about this. Also if you have such more ideas do share with me.