database strategies for microservices

Last blog I have talked about the problem of database lockup but how can we solve it?

Shared Tables: Shared Tables could be a easy to go and a dirty solution that is very common. But be aware that it is high maintenance.
Using mysql we can use FEDERATED ENGINE (http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html) to do this. We have to create a federated table based on the table at another remote location that we want.

CREATE TABLE remote_user (
  username varchar(20) NOT NULL,
  password varbinary(20) NOT NULL,
  PRIMARY KEY(username)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:password@someip:port/db/user’;

Database View : A database view is a comparatively better approach when the cases are simple because it allows another representation of database model which is more suitable. Most amazing thing about database view is that it supports wide range of databases. But for heavy use cases we can see performance issues. While considering database view we must ensure that both of the databases can connect with each other without any network or firewall issue. Most of the database views are read only, updating them according to need might get tricky.

CREATE TABLE federated_table (
    [column definitions go here]
)
ENGINE=FEDERATED
CONNECTION='mysql://username:password@someip:port/db/user’;

Triggers:
Database triggers might come handy where one database operation will trigger another database update. We can bind to AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers.

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END

Data Virtualization: When we are dealing with micro services possibly some of our databases are running using Mysql while other services are running other DBMS. In that case Data Virtualization strategy is necessary. One open source data virtualization platform is Teiid. But when dealing with data virtualization strategy we must know that if we are dealing with stale data or not, as it will have serious performance issue as it will add another hop as the data is not being accessed directly from database.

Event sourcing: Rather then making database operatins we can consider designing it as a stream of events that goes one after another through as message broker. So it does not matter how many users are accessing your database it will never lock up your database but it would take more time to process the data.

Change Data Capture: Another approach is to use Change Data Capture (CDC), is an integration strategy that captures the changes that are being made to a data and makes them available as a sequence of events in other databases that needs to know about these changes. It can be implemented using Apache Kafka, Debezium and so on.

Simple trick that can can help us to achieve Zero Downtime when dealing with DB migration

Currently we are dealing with quite a few deployment processes. For a company that enables DevOps culture, deployment happens many many times a day. Tiny fraction of code change goes to deployment, and as the change size is so small it gets easier to spot a bug and if the bug is crucial maybe it is time to rollback to an older version and to be able to have a database that accepts rollback, yet we have to do it with zero downtime so that the user do not understand a thing. It is often is not as easy as it sounds in principal.

Before describing about few key idea to solve this common problem lets discuss few of our most common deployment architectures.

In a blue/green deployment architecture, it consists of two different version of application running concurrently, one of them can be the production stage and another one can be development platform, but we need to note that both of the version of the app must be able to handle 100% of the requests. We need to configure the proxy to stop forwarding requests to the blue deployment and start forwarding them to the green one in a manner that it works on-the-fly so that no incoming requests will be lost between the changes from blue deployment to green.

Canary Deployment is a deployment architecture where rather than forwarding all the users to a new version, we migrate a small percentage of users or a group of users to new version. Canary Deployment is a little bit complicated to implement, because it would require smart routing Netflix’s OSS Zuul can be a tool that helps. Feature toggles can be done using FF4J and Togglz.

As we can see that most of the deployment processes requires 2 version of the application running at the same time but the problem arises when there is database involved that has migration associated with it because both of the application must be compatible with the same database.So the schema versions between consecutive releases must be mutually compatible.

Now how can we achieve zero downtime on these deployment strategies?

So we can’t do database migrations that are destructive or can potentially cause us to lose data. In this blog we will be discussing how can we approach database migrations:

One of the most common problem that we face during UPDATE TABLE is that it locks up the database. We don’t control the amount of time it will take to ALTER TABLE but most popular DBMSs available in the market, issuing an ALTER TABLE ADD COLUMN statement won’t lead to locking. For example if we want to change the type of field of database field rather than changing the field type we can add a new column.

When adding column we should not be adding a NOT NULL constraint at the very beginning of the migration even if the model requires it because this new added column will only be consumed by the new version of the application where as the new version still doesn’t provide any value for this newly added column and it breaks the INSERT/UPDATE statements from current version. We need to assure that the new version reads values from the old column but writes on both.  This is to assure that all new rows will have both columns populated with correct values. Now that new columns are being populated in a new way, it is time to deal with the old data, we need to copy the data from the old column to the new column so that all of your current rows also have both columns populated, but the locking problem arises when we try to UPDATE.

Instead of just issuing a single statement to achieve a single column rename, we’ll need to get used to breaking these big changes into multiple smaller changes. One of the solution could be taking baby steps like this:

ALTER TABLE customers ADD COLUMN correct VARCHAR(20); UPDATE customers SET correct = wrong

WHERE id BETWEEN 1 AND 100; UPDATE customers SET correct = wrong

WHERE id BETWEEN 101 AND 200;
ALTER TABLE customers DELETE COLUMN wrong;

When we are done with old column data population. Finally when we would have enough confidence that we will never need the old version, we can delete a column, as it is a destructive operation the data will be lost and no longer recoverable.

As a precaution, we should delete only after a quarantine period. After quarantined period when we are enough confident that we would no longer need our old version of schema or even a rollback that does require that version of schema then we can stop populating the old column.  If you decide to execute this step, make sure to drop any NOT NULL constraint or else you will prevent your code from inserting new rows.

Running a on premise local mysql replica with AWS RDS Aurora master

To solve our problem we are running a hybrid cloud. Few of our services are running on cloud and some of our services are running in premise locally in our country where we have our users and where AWS does not provide service. To able to do that we need a database replica that has read facility.

We need to creating replica user:

CREATE USER 'replica'@'%' IDENTIFIED BY 'slavepass'; 
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

Then create a new DB Cluster parameter group and set binlog_format to MIXED. Modify the Aurora cluster and select the custom parameter group. Restart your db to apply those changes. Now if you run following command you will be able to see the bin log file name and position.

show master status

Now we need to dump our master user data to sql dump so that we can feed our slave database.

mysqldump --single-transaction --routines --triggers --events -h XXX.azhxxxxxx2zkqxh3j.us-east-1.rds.amazonaws.com -u bhuvi –-password='xxx' my_db_name > my_db_name.sql

It can be GB to TB of data depending on your database size. So it will take time to download.

Run follwoing to know your mysql configuration file:

mysqld --help -verbose | grep my.cnf

For me it is /usr/local/etc/my.cnf

vi /usr/local/etc/my.cnf

and change server-id to:

 [mysqld] server-id = 2

now lets import these data into our mysql.

mysql -u root –-password='xxx' my_db_name < my_db_name.sql

Now we need to let our slave database know who is the master:

CHANGE MASTER TO  
MASTER_HOST = 'RDS END Point name',  
MASTER_PORT = 3306,  
MASTER_USER = '',  
MASTER_PASSWORD = '',  
MASTER_LOG_FILE='',  
MASTER_LOG_POS=;

Now we need to start the slave.

start slave;

Sample AWS CodeDeploy configuration for django

AWS has its own continuous integration tool known as CodeDeploy, using a simple command you would be able to deploy on multiple servers when you want to change something on code base.

Installing code deploy to instance

If code deploy client is not installed at your instance, you would need to do that:

sudo yum install -y ruby wget
cd /opt
wget https://aws-codedeploy-ap-south-1.s3.amazonaws.com/latest/install
chmod +x ./install
sudo ./install auto

Create CodeDeploy Application

You have to create Code Deploy application with Deployment type to Inplace deployment, and deployment Configuration set to CodeDeployDefault.OneAtATime.
Give it a name under Ec2 configuration and Amazon ec2 instance, say the name is Code deploy instance. Now you have to add the same tag to all your code deploy instances.

Set IAM Permissions

Now that we are done with installation, we would need to setup IAM rules:
First create an IAM group called CodeDeployGroup. This group needs AmazonS3FullAccess and AWSCodeDeployFullAccess permissions. Create a user and add it to this group. This user only needs programmatic access.Save key and key id to somewhere safe.

Create role that has Trusted entities and Policies are ec2.amazonaws.com and AWSCodeDeployRole AmazonS3FullAccess, respectively.

Edit trust relationship to following:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": [
            "ec2.amazonaws.com",
            "codedeploy.ap-south-1.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

Create new s3 bucket with previously created IAM rules.

CodeDeploy configuration

My codebase  structure is something like following:

- src
  - <django project>
- scripts
   install_dependencies
   start_server
   stop_server
appspec.yml
codedeploy_deploy.py
deploy.sh

appspec.yml is the file that contains our hooks and configuration for code deploy.

version: 0.0
os: linux
files:
  - source: src
    destination: /home/centos/proj_name
hooks:
  BeforeInstall:
    - location: scripts/install_dependencies
      timeout: 300
      runas: root
  ApplicationStop:
    - location: scripts/stop_server
      timeout: 300
      runas: root
  ApplicationStart:
    - location: scripts/start_server
      timeout: 300
      runas: root

for django scripts/install_dependencies may look like following:

sudo yum install -y gcc openssl-devel bzip2-devel wget
sudo yum install -y make git
cd /opt
command -v python3.6 || {
    wget https://www.python.org/ftp/python/3.6.3/Python-3.6.3.tgz
    tar xzf Python-3.6.3.tgz
    cd Python-3.6.3
    sudo ./configure --enable-optimizations
    sudo make altinstall
}
sudo yum install -y mysql-devel

for scripts/start_server I have following:

cd /home/centos/evaly
pip3.6 install -r requirements.txt
nohup uwsgi --http :80 --module evaly.wsgi > /dev/null 2>&1 &

for scripts/stop_server I have following:

pkill uwsgi

I have borrowed a python script from bitbucket team which looks like following:

# Copyright 2016 Amazon.com, Inc. or its affiliates. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"). You may not use this file
# except in compliance with the License. A copy of the License is located at
#
#     http://aws.amazon.com/apache2.0/
#
# or in the "license" file accompanying this file. This file is distributed on an "AS IS"
# BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
# License for the specific language governing permissions and limitations under the License.
"""
A BitBucket Builds template for deploying an application revision to AWS CodeDeploy
narshiva@amazon.com
v1.0.0
"""
from __future__ import print_function
import os
import sys
from time import strftime, sleep
import boto3
from botocore.exceptions import ClientError

VERSION_LABEL = strftime("%Y%m%d%H%M%S")
BUCKET_KEY = os.getenv('APPLICATION_NAME') + '/' + VERSION_LABEL + \
    '-bitbucket_builds.zip'

def upload_to_s3(artifact):
    """
    Uploads an artifact to Amazon S3
    """
    try:
        client = boto3.client('s3')
    except ClientError as err:
        print("Failed to create boto3 client.\n" + str(err))
        return False
    try:
        client.put_object(
            Body=open(artifact, 'rb'),
            Bucket=os.getenv('S3_BUCKET'),
            Key=BUCKET_KEY
        )
    except ClientError as err:
        print("Failed to upload artifact to S3.\n" + str(err))
        return False
    except IOError as err:
        print("Failed to access artifact.zip in this directory.\n" + str(err))
        return False
    return True

def deploy_new_revision():
    """
    Deploy a new application revision to AWS CodeDeploy Deployment Group
    """
    try:
        client = boto3.client('codedeploy')
    except ClientError as err:
        print("Failed to create boto3 client.\n" + str(err))
        return False

    try:
        response = client.create_deployment(
            applicationName=str(os.getenv('APPLICATION_NAME')),
            deploymentGroupName=str(os.getenv('DEPLOYMENT_GROUP_NAME')),
            revision={
                'revisionType': 'S3',
                's3Location': {
                    'bucket': os.getenv('S3_BUCKET'),
                    'key': BUCKET_KEY,
                    'bundleType': 'zip'
                }
            },
            deploymentConfigName=str(os.getenv('DEPLOYMENT_CONFIG')),
            description='New deployment from BitBucket',
            ignoreApplicationStopFailures=True
        )
    except ClientError as err:
        print("Failed to deploy application revision.\n" + str(err))
        return False     
           
    """
    Wait for deployment to complete
    """
    while 1:
        try:
            deploymentResponse = client.get_deployment(
                deploymentId=str(response['deploymentId'])
            )
            deploymentStatus=deploymentResponse['deploymentInfo']['status']
            if deploymentStatus == 'Succeeded':
                print ("Deployment Succeeded")
                return True
            elif (deploymentStatus == 'Failed') or (deploymentStatus == 'Stopped') :
                print ("Deployment Failed")
                return False
            elif (deploymentStatus == 'InProgress') or (deploymentStatus == 'Queued') or (deploymentStatus == 'Created'):
                continue
        except ClientError as err:
            print("Failed to deploy application revision.\n" + str(err))
            return False      
    return True

def main():
    if not upload_to_s3('/Users/sadafnoor/Projects/evaly/artifact.zip'):
        sys.exit(1)
    if not deploy_new_revision():
        sys.exit(1)

if __name__ == "__main__":
    main()

I have written a script to zip up my source code so that the script can upload it to s3 and eventually all my ec2 instances will be downloading that zip from s3.

export APPLICATION_NAME="CodeDeployApplicationName" 
export AWS_ACCESS_KEY_ID="IAMUserKeyId"
export AWS_DEFAULT_REGION="ap-south-1"

export AWS_SECRET_ACCESS_KEY="IAMUserSecretKey"
export DEPLOYMENT_CONFIG="CodeDeployDefault.OneAtATime"

export DEPLOYMENT_GROUP_NAME="CodeDeployDeploymentGroup"
export S3_BUCKET="S3BucketName"
zip -r ../artifact.zip src/* appspec.yml scripts/*
python codedeploy_deploy.py