MySQL clustering(NDB) vs MySQL replication (InnoDB)

MySQL InnoDB

InnoDB often comes with master/slave configuration. The drawback of this method is writes are only on master and the slaves provide only reads. Therefore concurrent writes are not supported unless you use a mechanism called shading. 

What happens when a database grows so that it cannot handle in one database and a server. Then comes the shading option. But it has to be done very carefully in order to not to lose  performance. If you want to scale out the system for write operations, using this storage mechanism, then you need to use the shading option.

shading for reads

1. when the data set do not fit into the memory and consists of may read hits from the disk rather than serving from memory.

shading for writes

1. when there are too many writes that the replication lags considerably.
2. the frequency of writes is overloading this servers disks permanently

How ever if you are going for the option of sharding, it is always good to have application level sharding. The reason is, when the application knows where the data resides, the performance becomes better.

Sharding types

1. application level sharding - put the most busy tables into separate servers and access them.
2. sharding by hash key
3. sharding using a lookup service

Why sharding is one of the last options?

1. developer has to write code to handle the shading logic
2. backup, indexing, changing schema makes it more difficult to maitain

MySQL clustering

MySQL clustering how ever supports concurrent writes. Data is partitioned among the data nodes and a copy of a node or a backup is in another node. Therefore availability is assured. 

How ever the problem is even though it provides foreign key join supports the process is slow since the data is partitioned in several nodes. If the join operation results in large volume of data this could be slow. Therefore tasks such as generating reports that takes usually several minutes are not good to be implemented using this method. Also another thing to note in this method is, it supports concurrent writes.

Setting up MySQL clustering can be more tedious than setting up InnoDB. But still it prevents the developer from using shading, since the partitioning happens among the nodes.

Below is a link which provides some hints on how to increase the performance in a mysql cluster

https://blogs.oracle.com/MySQL/entry/mysql_cluster_performance_best_practices

Scaling a relational database

Below are the highlights I saw from the valuable article given in the following link.

http://java-persistence-performance.blogspot.com/2011/05/data-partitioning-scaling-database.html

 So hats off to the author who outlined these valuable things.

You can take 5 steps to scale a database

1. optimizing the number and types of queries hitting the database, using parametrized SQL, using batch writing, using lazy, join and batch fetching, a significant load can be removed from the database.
2. ensuring your database is configured optimally, has the correct indexes, queries are using the optimal query plan, and the disk access optimally, its performance, and thus scalability can be improved
3. caching objects and data in the mid-tier, you can offload a lot of the queries hitting the database, and improve your application's performance to boot. Most JPA providers support caching, and some such as EclipseLink offer quite advanced caching functionality including invalidation, and coordinated clustered caches. JPA 2.0 defines some basic caching annotations to enable and access the cache.
4. scale the database through clustering the database across multiple machines. This could be a real clustered database, such as Oracle RAC, or just multiple regular database instances. Clustered database are good, and can improve your scalability without much work, but depending on your application you may also have to partition your data across the database nodes for optimal scalability. Without partitioning, if you write a row on one node, then access it on another, the other node must request the latest copy of the data from the other node, this can potentially make performance worse.
5. partitioning data across each of the database nodes

Data partitioning can be done in 2 major ways

• Vertical partitioning
• Horizontal partitioning

HazelCast --> what is hazel cast?

Today I looked at HazelCast and wrote some code using the library. I found this library to be extremely useful and simple. Some of the advantages found in hazelcast are,

1. It can be used as a No-SQL data store - http://www.hazelcast.org/use-cases/in-memory-nosql/
2. Useful for messaging (pub/sub, topics etc...) - http://www.hazelcast.org/use-cases/messaging/
3. Can be used as a data-grid - http://www.hazelcast.org/use-cases/data-grid/
4. Can be used for caching - http://www.hazelcast.org/use-cases/caching/
5. For application scaling - http://www.hazelcast.org/use-cases/application-scaling/
6. For clustering sessions in java web apps - http://www.hazelcast.org/use-cases/clustering/

Optimistic locking

What is optimistic locking? Imagine two users read data at the same time and both of them do updates. Then the last to update will win. that is the normal situation.

So how would you handle this situation without compromising the concurrency of the system. The below article gives one solution to this using spring read it carefully.

http://camelcase.com.au/public/jpa/optimistic-locking-with-jpa-and-spring/

Graph data bases

Have you heard about graphical representation of data. Recently I came across a technology called graph databases. The idea is to represent data and relationships in a graph. An example of an implementation of such data bases is neo4j. A simple explanation on this phenomenon is given on the following link.

http://player.vimeo.com/video/56040747#t=0m14s

Map/Reduce with mongodb

Well I am not going  write an article by myself here. Because The concept is brilliantly explained in some other article. Visit the link below to have a good understanding about mongo map/reduce feature.
http://www.mongovue.com/2010/11/03/yet-another-mongodb-map-reduce-tutorial/

The following tutorial has a simple cool tutorial on this feature as well.
http://blog.facilelogin.com/2012/02/mapreduce-with-mongodb.html

Cool mongodb admin gui panel

I was looking for a mongodb admin panel to use it. Then I found this cool looking java based mongo admin gui panel called Umong.
You can download it from https://github.com/agirbal/umongo/downloads
Just extract the content and run the .sh file using command line or directly. It will launch the admin panel

what is mongodb?

MongoDB, an implementation of c++,  belogs to NOSQL category(Non-relational operational stores) also most knows this as Not only SQL.

It doesn't have

• joins
• complex transactions

Because of the above reasons, scaling becomes easy. But the side effect is we need to have new data models (Have to stop using relational models).

Well most of you have used relational database. That is one way of modelling databases. Another way of modelling data is document oriented data modelling.

If you are unfamiliar with document oriented databases, read the following link.

http://en.wikipedia.org/wiki/Document-oriented_database

Mongodb saves data as documents.
You can try issuing mongodb commands online in the following link. It also guides you how to use mongodb.
http://try.mongodb.org/

spring jdbc template with mysql

Required Libraries are,

commons-logging
spring-jdbc
spring-beans
spring-core
spring-tx
spring-asm
mysql-connector-java
spring-context
spring-context-support
spring-expression

You can do this simply by using maven. Just google maven dependencies for spring jdbc template and put the dependencies in your pom.xml file and you are good to go.

Let's see how to use jdbc template in spring to communicate with mysql database.

Create a model class named Book.


package mad.library.form;

public class Book {

private String name;
private String author;
private String category;

public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}

}

Now create the BookDataManager class as following.