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 ( 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]

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.

  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);

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


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.

Higher Level View of RDMA programming and its vocabularies

Recently I have come across a pretty cool tool called RDMA. It enables direct memory access from the memory of one computer into that of another computer without involving the burden of either one’s operating system. This permits high-throughput, low-latency networking, which is especially useful in massively parallel computer clusters. In this blog I will be noting down few vocabularies that comes in handy when dealing with RDMA.

Queue Pair (QP) consists of a Send Queue (SQ) and Receive Queue (RQ). When we expect it to send data we would send it to SQ and when we expect it to receive data, we would sends it to RQ. Both of them can but put on a Completion Queue (CQ).  Completion queue (CQ) is used by network adapter to notify the status of the completed Work Request. Each entry in Completion Queue entry (CQE) holds information of completion status of one or more completed work requests.

When we want an adapter to send or receive, we need to post a request these are called work requests. In a Send Request we need to assign how much data will be sent for connected and unconnected transport and the memory buffer where data is located for connected and unconnected transport, to where the data should be send and the type of the send request and in a receive requests, the maximum data size to be received and memory buffer where data should be. Completion of a send queue and a receive queue can be assigned to same or different completion queues.

Work queue maintains order of their posted time however in different work queue does not maintain orders. Every work queue has ids own user defined id wr_id and flags, for example wr.send_flags = IBV_SEND_SIGNALED  defines generation of a completion element once the data is transmitted. it can be handled in a chain manner by assigning another work queue in

ibv_create_cq is the command that helps to create CQ. Transportations can be completed successfully or with error result is reported through a completion queue entry (CQE) polling a CQ is used to retrieve the CQE from the CQ outcome is reported in the status field of the completion entry.

We create a QP using ibv_create_qp function. In the parameter it takes a Protection Domain(PD) and a set of attributes. Protection domain is gathering resources in groups. Resource from same protection domain are allowed to communicate with each other. Eg: QP, MP. Resource from outside protection domain are not allowed to communicate. To allocate protection domain by calling ibv_alloc_pd. Attribute struct would look something like this:

struct ibv_qp_init_attr qp_init_attr;
struct ibv_cq *cq;
qp_init_attr.send_cq = cq; 
qp_init_attr.recv_cq = cq; 
qp_init_attr.qp_type = IBV_QPT_UD; 
qp_init_attr.cap.max_send_wr = 2; 
qp_init_attr.cap.max_recv_wr = 2; 
qp_init_attr.cap.max_send_sge = 1;
qp_init_attr.cap.max_recv_sge = 1;

more at:

Where max_send_wr maximum number entities that we want to allow in a send queue before completion. By the way it would be wise to note that it should be less than max cqe.
max_recv_wr maximum scatter queue that we want to allow.
At max_send_sge, max_recv_sge, sge is a short hand for Scatter Gather Entries. The maximum number of scatter gather entries can be queried using ibv_device_query.

As you can see we have set qp_type to IBV_QPT_UD which is there for Unreliable Data. In reliable context, QP is possible between two RCs, but when it is about Unreliable QP, it allows one to many Unreliable Queue Pair without requiring any previous connection setup.

Like many things in network programming, QP goes through a series of steps before it ends up processing send a receive.

RESET: By default, upon the creation of QP, it is at it’s reset state. Although it is at its ready to receive data but it can’t process any work request at this state.
INIT: After RESET it goes to INIT state after its initial configuration. When QP moves from RESET to INIT, QP starts receiving receive buffers in the receive queues using ipv_recv commands. this data won’t be used until QP is in RTR state.
RTR: After that it goes to Ready To Receive state, at this state it is configured to receive data.
RTS: After that it goes to Ready To Send, at this stage it is configured to send data. At this stage device can post using ipv_post_send commend.

After creation if you want you can modify QP using ibv_modify_qp. When modifying QP, pkey_index, port_num, qkey (for unrelieable datagram only) might be necessary. All QP that wants to communicate on unreliable datagram must share same q_key.

To make RDMA do things, it is necessary for network adapters to ask for permissions to access local data. this is done through MR (memory region). A memory region has address, size and set of permissions. that control access to the memory pointed out by the region.

To register memory region we need to call ibv_reg_mr. It takes the Protection Domain, Start Virtual Memory Address, size, access bit information like local read, local write, remote read, remote write, atomic operation. Local read access is necessary when the adapter has to access local pc to gather data when rdma operating is being processed. Local write access is necessary when adapter has to scatter data when recieving a send operation. Remote access is necessary when the adapter has to access local data from rdma operation recieved by remote process.

to open a communication we would need to call ipv_open_device, where we can assign a context and a pointer. cq_context, channel, comp_vector is necessary when dealing with completion events.

If we want to send data from a to b, we would be needing a source and a destination address or destination_gid, which is known as address vector.

We can collect our device details using ibstat command. But please note that we would need to have connect two devices, install mlnx_ofed, ibstat command, change port type ib/eth, check ports are enabled state LinkUp, if running using infiniBand opensm must be running. Also we can collect that information programmatically using ibv_get_device_list function.

Under the hood libverb handles rdma network related operations, like creating, modifying, querying, destroying resources. it handles sending receiving data from QPs, and recieving Completion Queues.

As Ipdump does not work when we are dealing with infiniBand as it bypasses OS layer we can use ibdump for debugging.

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'; 

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 -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:

MASTER_HOST = 'RDS END Point name',  
MASTER_PORT = 3306,  

Now we need to start the slave.

start slave;

Setting up (comodo) ssl for your website on aws

We have bought our ssl from comodo from as we got a better deal there. After sending them our signed key. comodo sent us following files via email, against my private key. Now I would blog about how I setted the whole thing up on AWS.

First of all, before purchasing I had to send them a key which I had generated using OpenSSL using following command:

openssl req \
       -newkey rsa:2048 -nodes -keyout domain.key \
       -out domain.csr

Which was pretty easy. And as we had bought Comodo Essential SSL Wildcard so we could buy it without verifying our company, in fairly easy in less than 5 min.

After our successful purchase comodo sent us following files as zip in my email:
domain_com.crt os our Primary Certificate, COMODORSAAddTrustCA.crt is our Intermediate Certificate, and AddTrustExternalCAROOT.crt is the The Root Certificate.

Now it gets a little bit tricky because currently our certificates are in .crt format, but we want it to be in *.pem format. So we would need to convert them in *.pem.

openssl x509 -in ./AddTrustExternalCARoot.crt -outform pem -out ./pem/AddTrustExternalCARoot.pem
openssl x509 -in ./COMODORSAAddTrustCA.crt -outform pem -out ./pem/COMODORSAAddTrustCA.pem
openssl x509 -in ./COMODORSADomainValidationSecureServerCA.crt -outform pem -out ./pem/COMODORSADomainValidationSecureServerCA.pem
openssl x509 -in ./domain_com.crt -outform pem -out ./domain.pem

We would also need to keys that was used to create these certificates by comodo.

openssl rsa -in ./domain.key -outform PEM -out domain.key.pem

Lets create the chain first:

$ cat ./COMODORSADomainValidationSecureServerCA.pem > ./CAChain.pem
$ cat ./COMODORSAAddTrustCA.pem >> ./CAChain.pem
$ cat ./AddTrustExternalCARoot.pem >> ./CAChain.pem

Now you need to login to your aws console and search for ACM (Amazon Certificate Manager). and if it is your first time you need to click on Provision certificates.

It is time to import your certificate to ACM. At the form where it says Certificate body* please paste domain.pem and domain.key.pem and at Certificate chain paste CAChain.pem.

So thats it we are done importing.

Now if you have a load balancer you can take advantages of this ssl. If you have an existing load balancer or feel free to create one, where at the place of listener add https instead of http and for certificate choose acm and your domain.

You are good to go.