Getting started with Cluster/J - index search and table scans

Tweet This post follows the previous post, so set up the environment as described there.
In this example we will perform a full table scan (select * from my_data2) and an index scan (select * from my_data2 where userid=1).

The complete code is here from Severalnines.

Performing a full table scan (no search criteria)
Make sure you have records in the table my_data2.
To perform reads by primary key, we can just use Session::find(..), but here we will do a full table scan.

QueryBuilder qb = s.getQueryBuilder();

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/

List resultList = query.getResultList();
int count=0;
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}

That is it, if you run the example code, you will see the output.

Done reading X records in Y mssd

Performing a Index scan (search on part of the Primary Key)

The following snippet shows how to setup a EQUAL-filter:

/*
*  We want to compare userid=1 (select * from my_data2 where userid=2) 
*/
QueryBuilder qb = s.getQueryBuilder();
QueryDomainType dobj = qb.createQueryDefinition(MyData2.class);
/*
* Create a filter, we call this filter 'user_id_filter'
*/

PredicateOperand param = dobj.param("user_id_filter");
/*
* Set the column database column you want to filter on.
* Note: The setting dobj.get("userId") is really strange.
* In the table the column in the database is called userid, and in MyData2.
* The documentation of Cluster/J is very bad here, but
* the getter/setter are getUserId/setUserId, and you have to
* take the word after the get/set (in this case UserId) and change the first
* letter to lower case :(
*/
PredicateOperand column = dobj.get("userId");

/**
* Perform an equal compare
* we want to compare userid=1
*/

Predicate compare=column.equal(param);
dobj.where(compare);

Query query = s.createQuery(dobj);

/*
* Bind the search value to the filter
*/
query.setParameter("user_id_filter", new Long(1));

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}

That is it for this time. I hope the clusterj documention will become better.

Getting started with Cluster/J - inserts (combined PK)

Tweet This post follows the previous post, so set up the environment as described there.
In this example we will have a slightly different table with a combined PK.
The complete code is here.

Create the table

The first thing we should do is to create the table we need for this example:

CREATE TABLE `my_data2` (
`userid` bigint(20) NOT NULL DEFAULT '0',
`friendid` bigint(20) NOT NULL DEFAULT '0',
`data` varbinary(255) DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`userid`,`friendid`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (userid) */

Mapping table to Java Interface
An interfae describing each table is needed. You annotate the code with @PrimaryKey and @Column do denote if the names of columns and the column names.
The only difference here is that you need to annotate all primary key columns with @PrimaryKey.

import com.mysql.clusterj.annotation.Column;
import com.mysql.clusterj.annotation.Index;
import com.mysql.clusterj.annotation.PersistenceCapable;
import com.mysql.clusterj.annotation.PrimaryKey;

@PersistenceCapable(table="my_data2")
@Index(name="id")
public interface MyData2 {
   
      @PrimaryKey
      long getUserId();
      void setUserId(long i);
    
      @PrimaryKey
      long getFriendId();
      void setFriendId(long i);
   
      @Column(name = "data")
      byte[] getData();
      void setData(byte[] b);

      @Column(name = "last_updated")
      long getLastUpdated();
      void setLastUpdated(long ts);
}

Performing an insert

Populate the fields in the object, and make it persistent. Don't forget to set all primary key columns or you will get a ClusterJException.

MyData my_data=s.newInstance(MyData.class);
/**
* Set the data on the object
*/
my_data.setUserId(i);
my_data.setFriendId(i);
my_data.setData(data);
my_data.setLastUpdated(System.currentTimeMillis());
/**
* Persist the object */
s.makePersistent(my_data);

That is it, if you run the example code, you will see the output.

Done inserting 100 records in X ms

Getting started with Cluster/J - inserts

Tweet Cluster/J is a the Java direct API to MySQL Cluster which means it bypasses the MySQL Server and executes requests directly on the data nodes of MySQL Cluster. This gives very good performance (typically >2x faster than SQL) and low latency.

In this blog I will present how to get started and how to do simple lookups.

The complete source code (actually a bit extended to show batching) for this example can be found at Severalnines (here). The code you will see below is just snippets.

Environment

To start with you need MySQL Cluster up and running.
For development on localhost then you can get a sandbox from Severalnines.
If you want to have a test/production environment you should you the Configurator for MySQL Cluster.

First of all you have to import the following Cluster/J jars into your project.
In Eclipse, on the project, do "Properties -> Java Build Path --> Add External JARs"
The JARs you need to import are typically located in /usr/local/mysql/share/java/ (if you have installed MySQL Cluster in this location - this is the default location used by Severalnines).

You also need to set the JVM configuration in order to point out the libndbclient library :

-Djava.library.path=/usr/local/mysql/lib/ -Xms128m -Xmx512m

Create the table

The first thing we should do is to create the table we need for this example:

CREATE TABLE `my_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data` varbinary(255) DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Mapping table to Java Interface
An interfae describing each table is needed. You annotate the code with @PrimaryKey and @Column do denote if the names of columns and the column names.

public interface MyData {     

@PrimaryKey     
long getId();
void setId(long i); 

@Column(name = "data")
byte[] getData();     
void setData(byte[] b);

@Column(name = "last_updated")
long getLastUpdated();
void setLastUpdated(long ts);
}

Setting up the connection to MySQL Cluster

Properties p = new Properties();
p.setProperty("com.mysql.clusterj.connectstring", "localhost:1186");
p.setProperty("com.mysql.clusterj.database", "test");
SessionFactory sf=ClusterJHelper.getSessionFactory(p);

Creating a Session

The SessionFactory provides Sessions. On a session instance you can do a couple of things, e.g, makePersistent, updatePersistent, and find.

/**
* Sessions are not thread safe!
*/
Session s = sf.getSession();

Performing an insert

Populate the fields in the object, and make it persistent.

MyData my_data=s.newInstance(MyData.class);
/**
* Set the data on the object
*/

my_data.setId(i);
my_data.setData(data);
my_data.setLastUpdated(System.currentTimeMillis());
/**
* Persist the object */
s.makePersistent(my_data);

That is it, if you run the example code, you will see the output.

Done inserting 1000 records in 230 ms

Cluster/J - Document-oriented approach on MySQL Cluster

Tweet In a project Severalnines is engaged in, we are developing a realtime application based on Cluster/J. To start with, I must say cluster/j is fantastic and so far I am very happy with it and beaten our expectations big time. It is quite new however and we stumbled on a couple of issues, but those were fixed very fast by the Cluster/J developers. The bugs we encountered were:

• bug 59942
• bug 59941
  

Both which were worked around, and really we never did need to have a binary or a varbinary as the PK, we used a

Performance is great - we have two data nodes (nehalem, 32GB RAM, 146GB SAS 10K disk, 2x4 core 2.4GHz (E5620) ) and two application hosts (same spec, less RAM as data nodes) and we easily push through 70-80K transactions per second (reads and writes). We could easily do more, if we added more applications on the application hosts. The data nodes are using about 50-60% of their capacity (CPU).


The graph (mysql cluster statistics) is at start quite spiky. As you can see transactions/operations goes up and down. This is because we first tested with 10M, then reloaded with 20M records, 40M, and finally 80M records and let it run overnight.

The requests are primary key based, and there is a PK followed by a VARBINARY (and some other meta fields). In the VARBINARY a tree structure is stored. The tree structure represents a parent-child relationship, and we encode it in JSON. So we are using something like a document-oriented model. By doing this we avoid complex queries to navigate a parent child structure and thereby have many round-trips between application and data nodes , and instead ask for one big packet in one network roundtrip. Writes in this model will be more costly since we write back the whole parent-child structure instead of just one of the nodes in this structure.

In the Java applications we also have a REST interface to the data.

SQL is used only for one thing and that is to create the tables.

CMON Enterprise is used for monitoring and management, and the Cluster configuration comes from Severalnines Core Scripts.

Finally, we made some fine-tuning on MySQL Cluster and got another 20% - this is not shown in the graph, and another story.

The only thing we are missing (or rather waiting for) now is the multi-connection functionality (ndb_cluster_connection pooling) in cluster/j.