Developian

ZeroMQ support in Drizzle

2011-08-10T09:36:00.000+02:00

My little zeromq plugin just hit drizzle trunk, it is simply a replication plugin that publishes a multi-frame message on a ZeroMQ endpoint. The first frame contains the name of the schema the transaction was executed against and the second frame is the actual protobuf-serialized transaction.

Getting started

Install zeromq
Build & install Drizzle (it will pick up the zeromq libs automatically)
Start drizzled with --plugin-add=zeromq (this binds a zeromq socket on tcp://*:9999 - change this by setting --zeromq.endpoint=[whatever])
Profit

Try it out by running this small python script:


import zmq

ctx = zmq.Context()
s = ctx.socket(zmq.SUB)
s.setsockopt(zmq.SUBSCRIBE, '')
s.connect('tcp://localhost:9999')
i = 0
while True:
    i = i+1
    print s.recv_multipart()
    print i

You should see output on the form ['the_schema', *blob*].

Drizzle JDBC 1.1 - SSL support and multi queries

2011-04-24T08:58:00.000+02:00

Just pushed up Drizzle JDBC 1.1 with 2 quite big new features:

SSL support - add ?useSSL=true in the connection string (and do the usual ssl-in-java magic to make it work. Check the MySQL documentation for more information, it is set up exactly the same way here)
Multi queries - add ?allowMultiQueries=true to your connection string to be able to send several queries in one round trip to the server.

Note that these features are only supported when using the driver against a MySQL server - Drizzle does not have these features (yet).

Download it from the central maven repo (should be synced within an hour or so)

Drizzle JDBC 1.0

2011-04-11T09:12:00.000+02:00

Finally got around to making a 1.0-release of Drizzle JDBC, after a while of not getting any bug reports (not at all claiming that it is bug free, but it is stable enough for several companies to use it in production).

Far those new to Drizzle JDBC, it is a BSD-licensed JDBC driver for Drizzle and MySQL.

Changelog

DatabaseMetaData fix - return column_type instead of data_type, contains more information (by Stephane Giron)
Detect if a blob is actually a CLOB by checking charset number (by Christian Peter)
Connect with UTF-8 charset default (bug reported by Marc Isambart)
Support for connection timeouts (by Marc Isambart) - add ?connectTimeout=X to have an X second connection timeout.
Properly support TIME and BOOLEAN datatypes in Drizzle
Move query timeout thread to the Connection class instead of having it in the statement class (duh) - now one extra thread per Connection instead of one per Statement. Is safe since only one statement can run in one connection at a time.
Generally shape up UTF8 handling throughout the driver (by Marc Isambart and me).
Update copyright headers, now includes the standard Simplified BSD license header.
Add an AUTHORS file

Whats next?

So, the general plan for the future is:

Make the driver more modular - it should be possible for users to change the behavior of almost every aspect of the driver
Support the optional javax.sql packages - XA handling etc. This will be built as a separate maven-published jar to keep size of the basic driver small
Support the SQL escape syntax (never used it, but i guess it should be there...)
...

If any of the things above are more important for anyone, please let me know so that i know where to begin.

Visualizing data in real time using drizzle and rabbitmq, DEMO!

2011-04-07T12:03:00.000+02:00

A couple of weeks ago, when Mozilla released firefox 4, i watched glow.mozilla.org - a site made to visualize the firefox downloads in real time. I figured this could be quite a neat use case for my old drizzle to web socket replicator.

So, the compontents involved:

Drizzle with the rabbitmq plugin, see this blog post for more information on how to get it up and running.
Drizzle-JDBC to generate the data in the database
RabbitReplication
RabbitMQ to transport the transactions from drizzle to the applier
Your browser, both Chrome and Firefox support web sockets, but for firefox you need to enable them first

The basic flow is this:

10 inserts per second are executed in the drizzle database using drizzle-jdbc, these inserts contain a random IP address
Drizzle does its magic and packages up each insert as a google protobuf transaction message
The rabbitmq plugin picks up the message and publishes it to a RabbitMQ server
RabbitReplication consumes the message from RabbitMQ and maps it onto an annotated java object. This gives alot of flexibility to the rabbitreplication user since you can manipulate the information any way you want, in this example i extract a longitude and latitude value from the IP address using MaxMind GeoLite City. Have a look at row the "setSomething()" method in the ExampleRepl.java file. This file is something a rabbitreplication end user would implement and provide
The java object is serialized to json
The json string is published to all connected clients
A javascript on the page takes the lat/lng-values and maps these onto a google map
The same javascript appends some of the raw data to a table

So, I'm hoping the little demo i'm linking below shows some of the extreme flexibility of the Drizzle replication. This, for me, is the unique selling point for Drizzle. Also, if anyone has an actual production use case for this, i'd be extremely happy to help implement it!

http://demo.rabbitreplication.org

(Thanks to Rackspace for hosting!)

Publish transactions to RabbitMQ in drizzled7

2011-03-28T21:11:00.000+02:00

Since Drizzle went GA a couple of weeks ago, i figured i should update rabbitreplication to support it, quite a few changes have been done to the structure of the protobuf messages etc. First hurdle was to get drizzle to actually publish messages to rabbitmq, so here is a step-by-step tutorial to get started with that.

Install a recent version of rabbitmq

Get the latest version of librabbitmq:

$ hg clone http://hg.rabbitmq.com/rabbitmq-codegen/
$ hg clone http://hg.rabbitmq.com/rabbitmq-c/

Build it (in the rabbitmq-c directory):

$ autoreconf -f -i
$ ./configure
$ make
$ make install

Install Drizzle, follow these instructions:
http://docs.drizzle.org/#compiling-and-installing. (During ./configure you should see that librabbitmq is found and that the plugin is getting built)

Start Drizzle like this:

$ sbin/drizzled7 --plugin-add rabbitmq,default-replicator --rabbitmq.use-replicator default

To verify that it works, you can run a rabbitmq consumer from rabbitmq-c/examples, like this:

./amqp_listen localhost 5672 ReplicationExchange ReplicationRoutingKey

Then execute an insert (or something, just not a select :) ) on your drizzle server, and you should see something like this in your console:

Result 0
Frame type 1, channel 1
Method AMQP_BASIC_DELIVER_METHOD
Delivery 1, exchange ReplicationExchange routingkey ReplicationRoutingKey
----
00000000: 0A 17 08 01 10 87 36 18 : F0 FA D9 99 FA F1 A7 02  ......6.........
00000010: 20 99 81 DA 99 FA F1 A7 : 02 12 40 08 01 10 F2 FA   .........@.....
00000020: D9 99 FA F1 A7 02 18 FC : FA D9 99 FA F1 A7 02 2A  ...............*
00000030: 17 0A 06 0A 01 62 12 01 : 61 12 06 08 04 12 02 69  .....b..a......i
00000040: 64 12 05 08 01 12 01 74 : 32 11 08 01 10 01 1A 0B  d......t2.......
00000050: 0A 01 32 0A 02 61 61 10 : 00 10 00 20 01 28 01     ..2..aa.... .(.
0000005F:

In a couple of days I'll make a new release of rabbitreplication that supports the latest version of drizzle, stay tuned for that! I'll also update the drizzle rabbitmq plugin so that it is actually usable (support reconnect etc)

Drizzle JDBC 0.8

2011-02-14T19:58:00.000+01:00

Just pushed up Drizzle-JDBC 0.8 to the central maven repository, list of changes is below. To use it, update your poms to include this:

<dependency>
   <groupId>org.drizzle.jdbc</groupId>
   <artifactId>drizzle-jdbc</artifactId>
   <version>0.8</version>
</dependency>

Or simply download the jar file and add it to the classpath. Further instructions on how to us it is available on the Drizzle JDBC Wiki

Drizzle JDBC is a BSD Licensed JDBC driver for MySQL and Drizzle.

Changlog for 0.8

Allow queries bigger than 16M (Stephane Giron)
Allow prepared statements with large parameters
Allow reading back big result sets
Implement cancel() on statements
Implement query timeouts
Java 5 support (Christian Peter)
Add option to create database if it does not exist (Stephan Giron) - add ?createDB=true to the connection string
Fixes to database metadata queries after drizzle got its data dictionary
Better UTF8 support and BLOB/CLOB fixes (Christian Peter)
Support for MySQL old passwords (Stephane Giron)
LOAD DATA LOCAL INFILE support (Stephane Giron)
Properly close socket (Gilles Rayrat)
Support BigDecimal in setObject
Move maven repositories to the Sonatype OSS repositories
Initial blob streaming (PBMS) support (http://developian.blogspot.com/2010/08/blob-streaming-pbms-support-in-drizzle.html)

Report any issues/feature requests to me, krummas@gmail.com or on http://launchpad.net/drizzle-jdbc

Blob Streaming (PBMS) support in drizzle jdbc

2010-08-11T21:18:00.000+02:00

I just pushed up initial support for PBMS, blob streaming for drizzle jdbc. It is not yet a complete solution for blob streaming, but you can use setBinaryStream and getBinaryStream to stream data to/from Drizzle and MySQL.

To use it, grab a snapshot from hudson (the reason the tests fail is that i have not had time to rewrite INFORMATION_SCHEMA queries to support the new I_S in Drizzle). Then follow this example to get started, important stuff is in bold

Connection connection = 
    DriverManager.getConnection(
           "jdbc:drizzle://localhost:3307/test_units_jdbc?enableBlobStreaming=true");
// inserting a blob:
Statement stmt = connection.createStatement();
stmt.execute("create table bstreaming1 (id int not null primary key auto_increment, test longblob)");
PreparedStatement ps = connection.prepareStatement("insert into bstreaming1 values (null, ?)");
// fake an inputstream with data:
ByteArrayInputStream bais = new ByteArrayInputStream("HEJHEJHEJ".getBytes());
ps.setBinaryStream(1, bais);
ps.executeUpdate();
        
stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from bstreaming1");
assertEquals(rs.next(), true);
byte[] b = new byte[100];
int l = rs.getBinaryStream("test").read(b);
assertEquals("HEJHEJHEJ",new String(b,0,l));

To get PBMS support in drizzle you need to have a fairly recent build, and start the daemon with --plugin_add=pbms. InnoDB tables are streaming enabled, which means that any LONGBLOB column will be streamed.

Note that in the current version you will get the blob reference and not a String representation of the blob if you do a getString on the column for example, this will change when I get time to do it.

I have not yet tested against MySQL, if you have a PBMS enabled MySQL server, please let me know if it works!

Help me test my BSD licensed JDBC driver for Drizzle and MySQL

2010-07-05T20:00:00.002+02:00

I'm developing Drizzle-JDBC which is a BSD licensed JDBC driver for both MySQL and Drizzle. The goal is to build a maintainable, "light weight" JDBC driver with a permissive license (it is hard to be truly light weight when implementing the massive JDBC interfaces).

Now what I really need is beta testers. I don't expect anyone to use it in production, but it would be great if you could try it with any existing application by just placing the .jar on the class path, changing the connection string, and running your test suites/load tests etc. Report any issues to me at launchpad.

Also, I need some inspiration, are there any features you have wanted to see in a JDBC driver? Cloud aware (if anyone wants this, please define it for me :) )? Plug-in system for query optimization etc?

How to help:

Download Drizzle-JDBC 0.7 and place it on your classpath, or, if you use maven, add this dependency to your pom.xml:

<dependency>
  <groupId>org.drizzle.jdbc</groupId>
  <artifactId>drizzle-jdbc</artifactId>
  <version>0.7</version>
</dependency>

Change the connection string to the form:
```
jdbc:mysql:thin://<username>:<password>@<hostname>:<port>/<schema>
```
(if your tooling needs to do a Class.forName, the driver classname is org.drizzle.jdbc.DrizzleDriver)
Run your test suite
When anything unexpected happens, post a bug report at launchpad, preferably with a stack trace and the test case that triggered the error (or send me an email at krummas@gmail.com if you don't have a launchpad account and don't want one).

Or...

Tell me about a feature you would really like to see in a JDBC driver! (well, better performance and bug free would of course be nice features, but that is what I want to achieve from the previous section)

Drizzle JDBC 0.7

2010-07-04T19:39:00.002+02:00

Just pushed Drizzle JDBC 0.7 to the maven repository, it contains bug fixes and features both for the MySQL and Drizzle support;

Support user name and password in Properties when connecting.
A lot of datatype fixes thanks to the guys at Continuent and their Connector test suite.
Initial implementation of ParameterMetadata.
Fix umlaut bug reported by Christian Peter.
Fix getTables to work properly after bug report from Christian Peter.
Allow zero-length passwords, bug reported by Christian Peter.
Throw proper exception when connection to server times out.
Implementation of setObject with type conversions.

The binaries are in the central maven repository, http://repo1.maven.org/maven2/org/drizzle/jdbc/drizzle-jdbc/0.7/. Or, if you use maven, add the following to your pom.xml:

<dependency>
<groupId>org.drizzle.jdbc</groupId>
<artifactId>drizzle-jdbc</artifactId>
<version>0.7</version>
</dependency>

The code is in launchpad.

Using Jetty and Jersey with Scala

2010-06-14T20:46:00.000+02:00

Lately I've been playing around with Scala again and I wanted to build a small RESTful web app using Jersey running in Jetty and I couldn't find any working examples online.

First you need to download and install Scala and SBT, follow the instructions for SBT on the site. Then you need to create the SBT project (bold stuff is what i typed in);

$ sbt
Project does not exist, create new project? (y/N/s) y
Name: RestWebApp
Organization: none
Version [1.0]: 
Scala version [2.7.7]: 2.8.0.RC5
sbt version [0.7.4]: 
Getting Scala 2.7.7 ...
:: retrieving :: org.scala-tools.sbt#boot-scala
 confs: [default]
 2 artifacts copied, 0 already retrieved (9911kB/288ms)
Getting org.scala-tools.sbt sbt_2.7.7 0.7.4 ...
:: retrieving :: org.scala-tools.sbt#boot-app
 confs: [default]
 15 artifacts copied, 0 already retrieved (4096kB/149ms)
[success] Successfully initialized directory structure.
Getting Scala 2.8.0.RC5 ...
downloading http://repo1.maven.org/maven2/org/scala-lang/scala-compiler/2.8.0.RC5/scala-compiler-2.8.0.RC5.jar ...
 [SUCCESSFUL ] org.scala-lang#scala-compiler;2.8.0.RC5!scala-compiler.jar (92356ms)
downloading http://repo1.maven.org/maven2/org/scala-lang/scala-library/2.8.0.RC5/scala-library-2.8.0.RC5.jar ...
 [SUCCESSFUL ] org.scala-lang#scala-library;2.8.0.RC5!scala-library.jar (61762ms)
:: retrieving :: org.scala-tools.sbt#boot-scala
 confs: [default]
 2 artifacts copied, 0 already retrieved (14497kB/49ms)
[info] Building project RestWebApp 1.0 against Scala 2.8.0.RC5
[info]    using sbt.DefaultProject with sbt 0.7.4 and Scala 2.7.7

Now, you need to set up the dependencies for jersey and jetty, in the file project/build/RestWebApp.scala like this;

import sbt._

class RestWebAppProject(info: ProjectInfo) extends DefaultProject(info)
{
  val jersey = "com.sun.jersey" % "jersey-server" % "1.2"
  val jerseyJson = "com.sun.jersey" % "jersey-json" % "1.2"
  val jetty = "org.eclipse.jetty" % "jetty-server" % "8.0.0.M0"
  val jettyServlet = "org.eclipse.jetty" % "jetty-servlet" % "8.0.0.M0"

  val javanetDeps = "javanetDeps" at "http://download.java.net/maven/2/"
}

Now, in your sbt console;


> reload
[info] Recompiling project definition...
[info]    Source analysis: 1 new/modified, 0 indirectly invalidated, 0 removed.
[info] Building project RestWebApp 1.0 against Scala 2.8.0.RC5
[info]    using RestWebAppProject with sbt 0.7.4 and Scala 2.7.7
> update
[info] 
[info] == update ==
[info] :: retrieving :: none#restwebapp_2.8.0.RC5 [sync]
[info]  confs: [compile, runtime, test, provided, system, optional, sources, javadoc]
[info]  20 artifacts copied, 0 already retrieved (3745kB/97ms)
[info] == update ==
[success] Successful.
[info] 
[info] Total time: 1 s, completed Jun 14, 2010 8:31:40 PM

Now you are good to go, create a file in src/main/scala/something.scala:

package marcus.test

import javax.servlet.http.{HttpServletRequest, HttpServletResponse}
import javax.servlet.ServletException
 
import java.io.IOException
 
import org.eclipse.jetty.server.{Server, Request}
import org.eclipse.jetty.server.handler.AbstractHandler
import org.eclipse.jetty.server.nio.SelectChannelConnector
import org.eclipse.jetty.servlet.{ServletHolder, ServletContextHandler}
import com.sun.jersey.spi.container.servlet.ServletContainer

import javax.ws.rs.{GET, Produces, Path}


object WebRunner {
  def main(args: Array[String]) {

    val server = new Server(8080)
    val connector = new SelectChannelConnector()
    server.addConnector(connector)
    
    val holder:ServletHolder = new ServletHolder(classOf[ServletContainer])
    holder.setInitParameter("com.sun.jersey.config.property.resourceConfigClass",
                            "com.sun.jersey.api.core.PackagesResourceConfig")
    holder.setInitParameter("com.sun.jersey.config.property.packages",
                            "marcus.test")
    val context = new ServletContextHandler(server, "/", ServletContextHandler.SESSIONS)
    context.addServlet(holder, "/*")
    server.start
    server.join
  }
}


@Path("/helloworld")
class TestResource {
  @GET
  def hello() = {
    "HELLO!!"
  }
}

and in your sbt console:


> compile
...
> run
...

and visit http://localhost:8080/helloworld and you should see "HELLO!!". Note that in a real project you might want to separate the classes in different files etc.

Drizzle JDBC 0.6

2010-04-05T20:28:00.001+02:00

I just pushed up Drizzle JDBC 0.6 to the maven repo, should be in the central repository real soon.

Biggest changes:

Fix setObject, handle null properly
Fix bug 501443
Fix bug 501452
Default to MySQL protocol when connecting to Drizzle
Remove unused code
Default to single-threaded operation
Rename org.drizzle.jdbc.Driver to org.drizzle.jdbc.DrizzleDriver
Lots of protocol performance improvements - both faster and uses less memory (YourKit rules!)
Prepared statement performance increased alot - uses less memory and is faster

Drizzle Hibernate Dialect

2010-03-16T11:22:00.001+01:00

I just built a hibernate dialect for drizzle:


bzr branch lp:~krummas/+junk/drizzledialect

Drop the file in your project and set;


hibernate.dialect=org.drizzle.hibernate.dialect.DrizzleDialect
hibernate.connection.url=jdbc:drizzle://10.100.100.50:3307/somedb
hibernate.connection.driver_class=org.drizzle.jdbc.DrizzleDriver

And you are ready to go. Preferably use it with the latest drizzle-jdbc snapshot. Please try it out and let me know how it works.

Replicating transactions directly to RabbitMQ

2010-01-25T15:43:00.000+01:00

Previously RabbitReplication tailed the transaction log provided by Drizzle and then the Java application sent the protobuf serialized transaction to RabbitMQ. Now it is possible to skip the transaction log file and send the transaction directly to the RabbitMQ server without the extra step of storing it in a file first.

The code is available at https://code.launchpad.net/~krummas/drizzle/rabbitmq_log and to build it with rabbitmq support you need to install librabbitmq which is a bit tricky;

Installing librabbitmq

Install mercurial
Branch the librabbitmq code: hg clone http://hg.rabbitmq.com/rabbitmq-c/
Branch the rabbitmq codegen repo into a subdirectory called codegen in the rabbitmq-c directory:

cd rabbitmq-c
hg clone http://hg.rabbitmq.com/rabbitmq-codegen/ codegen

Run autoconf like this: autoreconf -i
Run the configure script
make
make install

Build Drizzle
When librabbitmq is installed, build drizzle like this:

bzr branch lp:~krummas/drizzle/rabbitmq-log
config/autorun.sh
./configure --with-rabbitmq-log-plugin
make
make install

and it is done!

Start Drizzle with RabbitMQ support

First, you can run drizzled with a --help flag to see the options available, they are all prefixed with --rabbitmq-log-XYZ.

The default values for the parameters makes drizzle connect to localhost as "guest" and replicate to an exchange called ReplicationExchange. Start it like this to replicate changes to a rabbitmq on localhost:

$ sbin/drizzled --default-replicator-enable --rabbitmq-log-enable

The other available options are described in --help

Drizzle to Infinispan replication and a small code walkthrough

2010-01-16T20:37:00.003+01:00

This post aims to explain how to build your own key/value-store applier in RabbitReplication by walking through the new Infinispan support as an example.

Infinispan
Infinispan is a "distributed, highly available data grid platform" and it exposes a REST interface where it is possible to manipulate the data in infinispan. For example it is a simple HTTP PUT method to store new data and a HTTP DELETE does exactly what you expect. The data is stored under resources, for RabbitReplication the data is stored under /<schema.table>/<key>. This means you can view the data in infinispan using a browser.

When I implemented the Infinispan support, I simply dropped the .war file in the webapps directory of a jetty installation and started it. Since Infinispan has a REST interface, the client library can be any HTTP client, I picked the Jersey REST client since it is incredibly easy to use.

Customizing RabbitReplication
RabbitReplication uses Guice internally for dependency injection, so, to use another KeyValue store you need to create your own Module for configuration. I'll show an example below.

To add support for a new key/value store, you need to implement an interface, org.drizzle.replication.transformer.objectstores.KeyValueStoreClient (here). It is a quite straight-forward add/get/remove interface. Look at the infinispan implementation (here) for an example. Note the @Inject on the constructor, it tells guice that the WebResource parameter should be injected when it constructs the object. You will need to put the rabbitreplication.jar file on your classpath when building your stuff.

Guice is configured in modules where you bind() an interface to an implementation, so, to configure guice to use a new KeyValueStore, we need to bind() the KeyValueStoreClient interface to the new implementation. Look at the infinispan module (here) for an example how to do it, the method annotated with @Provides is called by guice when it needs to create a KeyValueStoreClient.

To tell RabbitReplication to use your new module, you simply edit your configuration file, and set where the new Module is located, see this example. If you need to configure your new client, simply add the properties in the config file, guice will bind every property in the file to @Named(...) strings, check the Infinispan module provider method for an example how to use it.

Now, build your code into a jar, drop the jar in the lib/ directory of rabbitreplication and start RabbitReplication like this; bin/start.sh config/someconf.properties - the config file should have the custom_module set to the name of your new module.

Downloading and installing
Best way to use rabbitreplication is still to branch the code from launchpad (lp:rabbitreplication) and then write ant in the base directory, this will create a .zip and a .tgz in the dist directory. You can also download the binaries here.

Unpack the distribution file
Copy a config file from .sample to .properties in the config directory and edit the file to your liking. objectslave.properties.sample is the sample you want to look at if you want to try out Infinispan.
Start rabbitreplication by executing bin/start.sh config/yourconf.properties

Look at the previous posts on rabbitreplication to find out how to start a master etc.

Multi threaded replication appliers

2010-01-08T20:54:00.001+01:00

Lately I've been working on a transaction reducer to be able to multi thread the applier. Basic idea is to reduce the transaction to only affect one row with one statement, when that is the case, we can have a thread pool doing the actual persisting of the statements (of course it has some drawbacks as well, more about those later). This approach is particularly interesting for NoSQL appliers.

Reducing transactions
The transaction log in drizzle contains a list of statements. Each statement contains a list of records, where each record contains information about what changed on one row in the master. One example transaction could look like something like this:

transaction_context {
  server_id: 1
  transaction_id: 10
  start_timestamp: 1262812100381445
  end_timestamp: 1262812153799963
}
statement {
  type: INSERT
  start_timestamp: 1262812100381446
  end_timestamp: 1262812134317464
  insert_header {
   table_metadata {
   schema_name: "unittests"
   table_name: "test1"
   }
   field_metadata {
   type: INTEGER
   name: "id"
   }
   field_metadata {
   type: VARCHAR
   name: "test"
   }
   field_metadata {
   type: VARCHAR
   name: "ignored"
   }
  }
  insert_data {
   segment_id: 1
   end_segment: true
   record {
   insert_value: "78"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "79"
   insert_value: "a"
   insert_value: "b"
   }
...
   record {
   insert_value: "87"
   insert_value: "a"
   insert_value: "b"
   }
  }
}
statement {
  type: UPDATE
  start_timestamp: 1262812134317466
  end_timestamp: 1262812151669387
  update_header {
   table_metadata {
   schema_name: "unittests"
   table_name: "test1"
   }
   key_field_metadata {
   type: INTEGER
   name: "id"
   }
   set_field_metadata {
   type: VARCHAR
   name: "test"
   }
  }
  update_data {
   segment_id: 1
   end_segment: true
   record {
   key_value: "85"
   after_value: "test"
   }
  }
}
statement {
  type: DELETE
  start_timestamp: 1262812151669389
  end_timestamp: 1262812153799963
  delete_header {
   table_metadata {
   schema_name: "unittests"
   table_name: "test1"
   }
   key_field_metadata {
   type: INTEGER
   name: "id"
   }
  }
  delete_data {
   segment_id: 1
   end_segment: true
   record {
   key_value: "81"
   }
  }
}

Or in SQL:

BEGIN;

INSERT INTO unittests.table1 (id, test, ignored) VALUES (78, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (79, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (80, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (81, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (82, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (83, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (84, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (85, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (86, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (87, "a","b");

UPDATE unittests.table1 set test = "test" WHERE id = 85;

DELETE FROM unittests.table1 WHERE id = 81;

COMMIT;

I.e. a number of inserted rows, one updated and one deleted. If we could exploit this and make sure that every record in the transaction only affects one row then each row is totally independent from all other records in the transaction and we could then have a pool of threads applying the transaction.

So, if i reduce the above transaction like this:

TransactionReducer reducer = new DrizzleTransactionReducer();
TransactionMessage.Transaction txn = getNextTransaction();
TransactionMessage.Transaction t = reducer.reduce(txn);

I get this transaction:

transaction_context {
  server_id: 1
  transaction_id: 10
  start_timestamp: 1262812100381445
  end_timestamp: 1262812153799963
}
statement {
  type: INSERT
  start_timestamp: 1262812100381446
  end_timestamp: 1262812134317464
  insert_header {
   table_metadata {
   schema_name: "unittests"
   table_name: "test1"
   }
   field_metadata {
   type: INTEGER
   name: "id"
   }
   field_metadata {
   type: VARCHAR
   name: "test"
   }
   field_metadata {
   type: VARCHAR
   name: "ignored"
   }
  }
  insert_data {
   segment_id: 1
   end_segment: true
   record {
   insert_value: "78"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "79"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "80"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "82"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "83"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "84"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "85"
   insert_value: "test"
   insert_value: "b"
   }
   record {
   insert_value: "86"
   insert_value: "a"
   insert_value: "b"
   }
   record {
   insert_value: "87"
   insert_value: "a"
   insert_value: "b"
   }
  }
}

Or, in SQL:

BEGIN;

INSERT INTO unittests.table1 (id, test, ignored) VALUES (78, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (79, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (80, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (82, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (83, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (84, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (85, "test","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (86, "a","b");

INSERT INTO unittests.table1 (id, test, ignored) VALUES (87, "a","b");

COMMIT;

I.e. a list of only inserts. This means we can apply the transaction using several threads. Another benefit is that we reduce the total number of records to insert meaning better performance.

Updates and deletes that affect rows outside the transaction are also reduced, for example if, within one transaction, one external row is updated twice, then deleted, only the delete statement will be executed.

Drawbacks

There are, of course some drawbacks, for example, if this is used when applying to a database, the applier will not be able to apply the transaction as a transaction since there is no way of sharing transaction context between several client threads.

Using it

To try it out, branch this repository: lp:~krummas/+junk/transactionreducer and look at the tests file. Should be straight forward to add more tests to see how it handles them.

It will also be available in RabbitReplication as a configuration option on the slave. The size of the thread pool and number of client connections will also be configurable.

Performance

In theory it should be faster to apply fewer statements using more threads, and the time spent reducing the transaction should easily be less then the time spent doing network I/O etc. I've done a few non-scientific benchmarks using a multi threaded cassandra applier and it takes approximately half the time applying reduced transactions using the multi threaded applier. I will make some proper benchmarks when everything is in place in RabbitReplication.

2009 and looking forward to 2010

2010-01-01T13:30:00.000+01:00

So, this is yet another 2009 retrospect with some goals for 2010, I'll do it in list form so someone might actually browse it;

2009:

Main event of 2009 was that i got kid nr 2, Teo.
Ran 1000K despite injuries
Started Drizzle JDBC.
Got excited about programming again and realized I need a new job
Got Drizzle JDBC to version 0.6, not many users yet, so can't say much about the quality (or, one could look at it from another angle, it is bug free! *cough*)
Went to JavaOne, great stuff, probably the last one.
Started a very long parental leave!
Started working a lot on replication-related stuff:

Wrote an extractor and applier Drizzle for Tungsten Replicator - submitted the code and waiting for it to be published
Started my own replication project, RabbitReplication - uses RabbitMQ as a transport and can replicate into Drizzle, Memcached, Project Voldemort, Cassandra and directly to user browsers using websockets.

"Learned" Haskell and started looking at Erlang.
Read some great books; Java Concurrency In Practice, Real World Haskell, Effective java (like every year) ...

2010 goals:

Continue my long, sweet parental leave (living in Sweden has its benefits)
Run 1500K
Get myself a new job, main requirements:

Most importantly, has a high paced startup-feel to it, I want to build stuff, not have meetings about the stuff we could build
Uses open source products
Contributes stuff back to open source communities (or, best of all, has some open source products of their own)

Make RabbitReplication into a proper product

Create web page
Make regular releases with good documentation
Build support for more column/key-value stores

Document how to extend it
Clean up the code

Get Drizzle-JDBC to "1.0" with someone using it in production (given drizzle makes it there). I'm guessing that when Drizzle itself is production ready, the user (and bug-) count will increase.
Learn at least one new programming language (Erlang, I'm looking at you), and build something with it.
Go to at least one conference
Blog more, November and December frequencies have been ok, mainly because I actually had time to build stuff worth blogging about.
Read more books (current reading queue: Erlang programming, SICP, Reread Distributed Systems - Concepts and design, Java Generics and Collections)
Invent a clock with 30 hrs a day to actually manage all the above goals

Drizzle replication to WebSockets

2009-12-28T21:58:00.001+01:00

I just pushed a WebSocket applier to RabbitReplication, and yes, it is as crazy as it sounds. It works pretty much like all appliers - it consumes drizzle transactions from RabbitMQ, converts them into objects by inspecting annotations, marshalls the object to JSON, and then stores the JSON string. In this case it stores it to a set of websockets. RabbitReplication is deployed as a war file to Jetty 7.01 which supports websockets.

DEMO

I set the demo up on my server at home (in Sweden) on a DSL line, so it might be slow, but it should show the idea, all operations are instant when latency is low (if anyone wants to host it at a better place, please let me know). Of course, it requires a WebSocket capable browser and the only one I know of is Google Chrome.

It works like this:

INSERT is executed from the "drizzle client" webapp - totally separate webapp that uses drizzle jdbc to insert/update/delete data.
Drizzle stores the transaction in the database and in the transaction log.
Master extractor extracts the transaction and publishes it to RabbitMQ
Slave applier consumes the transaction from RabbitMQ
Applier transforms the transaction to JSON
Applier writes the JSON to a set of websockets
Javascript voodoo is performed to make it visible

Possible real usecases

The demo app just shows what is possible, but a real use case could be that someone has a drizzle backed forum and want to add some real time post-updates to some front page somewhere. This would be real easy, simply start a new slave configured for WebSocket application (of course RabbitReplication is already used for other replication needs :) ), convert the JSON to something that makes sense and they are set! If someone has a cool usecase, please let me know and i'll build a more realistic demo app!

Better replication from drizzle to cassandra

2009-12-16T18:58:00.008+01:00

Introduction
This article describes how one of the replication appliers work in rabbitreplication, namely the HashTransformer which transforms each INSERT/UPDATE into a hashmap which is then stored in a column-family based storage, currently Cassandra. For a better overview of RabbitReplication, go check out earlier posts on the subject here: http://developian.blogspot.com

Configuration

This example replicates changes done to a table called test1 in the schema called unittests. RabbitReplication is configured to only replicate the columns id and test (yes, good example, I know...). The column id is used as a key. The following slave configuration is used for this use case:

replication_role = hashslave

rabbitmq.host = 10.100.100.50

rabbitmq.queuename = ReplicationQueue

rabbitmq.exchangename = ReplicationExchange

rabbitmq.routingkey = ReplicationRoutingKey

rabbitmq.password =

rabbitmq.username =

rabbitmq.virtualhost =

hashstore.host = localhost:9160

hashstore.type = cassandra

hashreplicator.replicate.unittests.test1 = id,test

hashreplicator.key.unittests.test1 = id

hashreplicator.keycolseparator.unittests.test1 = .

The hashreplicator rows are the interresting ones, they describe what columns to replicate, what columns are the primary key and what separator to use between the columns when the key is multi column.

Example

Replicating an insert:

drizzle> use unittests

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

drizzle> desc test1;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

+---------+-------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

drizzle> insert into test1 (id, test) values (300, "firstins");

Query OK, 1 row affected (0 sec)

Results in the following on the Cassandra side:

cassandra> get unittests.test1['300']

(column=test, value=firstins; timestamp=1260985298391)

(column=id, value=300; timestamp=1260985298385)

Returned 2 rows.

Update:

drizzle> update test1 set test = "updated" where id = 300;

Query OK, 1 row affected (0 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Gives this in cassandra:

cassandra> get unittests.test1['300']

(column=test, value=updated; timestamp=1260985526210)

(column=id, value=300; timestamp=1260985298385)

Returned 2 rows.

Note that the timestamp for the id column is not updated (only changes are updated, not entire rows).

Delete:

drizzle> delete from test1 where id = 300;

Query OK, 1 row affected (0 sec)

And in Cassandra:

cassandra> get unittests.test1['300']

Returned 0 rows.

That is it, go to http://launchpad.net/rabbitreplication to check out the code, report bugs or suggest features!

Cassandra support in rabbitreplication

2009-12-10T20:55:00.000+01:00

Just pushed support for replicating into cassandra to http://launchpad.net/rabbitreplication

The following format is used:
KeySpace = schema name from the transaction
ColumnFamily = table name from the transaction
Column name = "object" since we only store objects
Key = the key generated from the object to store, either by using the @Id annotation or by implementing the KeyAware interface

In the CLI you would type something like this to get the data (drizzle schema name is unittests and table is test1):

cassandra> get unittests.test1['1']['object']
==> (name=object, value={"name":"updated","ssn":1}; timestamp=1260472768425)

Drizzle persistence in Project Voldemort

2009-12-07T20:56:00.000+01:00

I just built drizzle support into Project Voldemort:
http://github.com/krummas/voldemort - just add drizzle in your stores.xml and it should work

Basically just a cutnpaste of the mysql code, uses drizzle-jdbc (which is included in the git repo).

To try it out, check out the code from github, execute "ant release" in the base dir and you get binaries in the dist directory.

Replication from drizzle to memcached / project voldemort

2009-12-06T22:15:00.002+01:00

The last few days I've been working on a way to replicate changes from drizzle into a key value store, currently project voldemort and memcached. It is built in my rabbit replication project which means that the transactions are transfered over a message bus (rabbitmq currently). The picture below describes an example of how the involved components could be set up (not likely that you want both memcached and project voldemort though):

Current feature list of rabbitreplication:

Replication from drizzle into drizzle (or any database with a JDBC driver) / memcached / project voldemort.
Map inserts and updates onto java objects using annotated classes (see below for example).
Two different ways of marshalling objects, JSON ond Java object serialization
Full control over how the key is generated (just implement the KeyAware interface in your target object)
Simple interface to build new marshallers.
Simple interface to build new object stores.
Simple interface to build new transports. (Will blog these extension points later)

Example:

The class below will catch any statements on the table unittests.test1 and take the column "id" and set it on the ssn field, and it will take the "test" column and set it on the name field. It will use the field annotated with @Id as key in the store and use the JSONMarshaller to marshal the object.

@Entity(schema = "unittests", table = "test1", marshaller = JSONMarshaller.class)
public class ExampleRepl {
   @Id
   @Column("id")
   private int ssn;

   @Column("test")
   private String name;
/*...*/

}

Add this to your config to use it:
managed_classes = org.drizzle.managedclasses.ExampleRepl, ...

Then you just start your slave like this:
java -jar replication.jar objectslave.properties

You need to put your managed classes on the classpath (drop them in the lib dir)

(See earlier posts about rabbitreplication on how to get started)

Todo:
Clean up configuration, quite messy right now
Write blogposts about how to roll your own transport/marshalling/key-value store implementations
Increase test suite and set up hudson for continuous integration
Write proper usage documentation
Build more backends, marshallers and transports, evolve apis
Write a MySQL binlog master (needs to transform mysql binlog into drizzle's protobuf based log, not even sure it is possible)
Create a way to not have to write code on the slave (pin tables to a hash and store it)
...
Getting involved
Get the code, bzr branch lp:rabbitreplication
Use it, give me feedback (krummas@gmail.com) <- most important!
Download
http://marcus.no-ip.biz/rabbitrepl.zip (yes, i will soon set up a proper download page).

Update of RabbitMQ Replicator

2009-11-30T21:52:00.000+01:00

The little Drizzle RabbitMQ replicator I built last week got quite popular (and even users!) so I decided to work a bit more on it. These are the changes:

Moved it to a real project on launchpad (http://launchpad.net/rabbitreplication)
Store transaction log position on master so that a transaction is never sent twice over the wire.
Major internal refactorings, start using google guice for IoC
Make the transport pluggable, it should now be easy to write your own transport and swap out rabbitmq, JMS anyone?
Alot more configurations for rabbitmq, see example config

User Guide:

Download binaries here (or check out code: bzr branch lp:rabbitreplication)
Unzip on master
Edit master.properties to reflect your environment (hope the config vars are self-explanatory, let me know if there are any problems)
Start the master: java -jar replication.jar master.properties
Unzip on slave
Edit slave.properties
Start the slave: java -jar replication.jar slave.properties
Watch changes replicate, report issues to me (krummas@gmail.com)

Todo/Ideas:

I'm thinking about using this code to build a framework for replicating changes from Drizzle into other storage forms, for example Hadoop/hbase or Cassandra. My thinking is that it could be useful for moving data that is very hot into a faster storage without changing your application too much. This might need business logic implemented on the "slave" (saying for example that this column should be stored and this should be ignored), and that is what I'm thinking could be built quite nicely using a framework (a DSL could be handy).

Please let me know if this has been done somewhere or if it is a stupid idea!

Drizzle Replication using RabbitMQ as a transport

2009-11-23T21:38:00.000+01:00

Having spent a bit of time learning how the transaction log in drizzle works (and it is incredibly easy to work with), I got an idea to use RabbitMQ as a transport. RabbitMQ is an implementation of the AMQP standard in Erlang, so it must be awesome.

It works like this; on the master, a java app is simply tailing the transaction log, sending all new transactions, in raw format, to a rabbitmq server.

The slave(s) are connected to the messaging server and are guaranteed to get the raw messages. When the slave gets the message, it transforms it to a JDBC prepared statement and executes every statement as a batch operation. The reason I use prepared statements and batch operations is that I get a lot for free from the JDBC driver, for example correct escaping of strings etc, and I can also enable the rewrite batch handler feature to get a great performance boost.

Another great thing we get for free by using rabbitmq is the fail-safety, if an exception is thrown in the slave, the message is not acknowledged and it will be retried later.

Being able to write something like this in a few hours really shows how powerful the drizzle replication system is. It will be one of the killer features.

There is one issue i really need to fix, namely that the master does not keep track of which transactions it has sent over the wire, so it will resend all transactions in the log every time it is restarted. Of course, since this was written in a short time, there are probably lots of other issues as well. If you want a real replication solution, go check out Tungsten Replicator.

If anyone wants to contribute, the code is on launchpad: https://code.launchpad.net/~krummas/+junk/rabbitmq-replication

If you simply want to try it out, get the binaries here: http://marcus.no-ip.biz/rabbitrepl.zip To start it, you do java -jar replication.jar master.properties - just make sure you edit the .properties files before starting.

Tungsten Replicator and Drizzle howto

2009-11-17T21:08:00.000+01:00

Last week I got a few hours to spend on making an extractor for Tungsten Replicator which works against the Drizzle transaction log. This post aims to explain how you use it to replicate changes between drizzle instances.

To get a better understanding of the drizzle replication system, please go read the article on Jay Pipes blog, here.

Get the code
First, you need to check the code out from the tungsten sourceforge repo, like this:
svn co https://tungsten.svn.sourceforge.net/svnroot/tungsten/trunk

Then you need to download my patch, here. Unzip it in the trunk/replicator directory and apply it like this:
patch -p0 < drizzle_support.patch

This patch includes all dependencies and the applier I wrote about in the last post.

Build it
Now you need to build tungsten, change working dir to the replicator directory and write
ant

If you got test errors, you can run ant allExceptJunit to skip the tests (there are some environment configurations needed to get the test suite running).

The artifacts end up in the build/ directory.

Get and build Drizzle
Follow the instructions on the drizzle wiki, http://drizzle.org/wiki/Compiling, just make sure that you pass --with-transaction-log-plugin when you ./configure drizzle.

Start Drizzle
Follow the instructions on the drizzle wiki: http://drizzle.org/wiki/Starting_drizzled and add the parameters --transaction-log-enable --default-replicator-enable when starting drizzled, otherwise you wont get a transaction log.

Set up Tungsten Replicator
Using the binaries built before, read the instructions for MySQL on the Continuent page: http://www.continuent.com/community/tungsten-replicator/documentation

Then we need to do some changes to the configuration files, for the master (extractor), use this configuration file as a template. Note that you must change the path to the transaction log. On the slave (applier), use this config file

Now you should be good to go, start your drizzle and tungsten instances and watch the changes replicate.

Note that almost all the involved components (drizzle, drizzle-jdbc, the drizzle extractor and applier) are not recommended for production use yet.

As always, if you have any questions, shoot me an email (krummas@gmail.com) or ping me on #drizzle @ freenode

Replication from MySQL to Drizzle using Tungsten Replicator

2009-10-29T08:29:00.001+01:00

Last week I sat down and created a Drizzle Applier for Tungsten Replicator. This makes it possible to replicate changes from MySQL (for example) to Drizzle. An applier is used to apply changes on a slave. This blog post aims to explain how you could try it out.

Download this patch

Check out the latest tungsten code:

svn co https://tungsten.svn.sourceforge.net/svnroot/tungsten/trunk/

Apply the patch:
```
patch -p0 < drizzle_applier.diff
```
in the replicator directory
Build it:
```
ant
```
in the replicator directory. This creates binaries in the replicator/build/ directory.
Follow the instructions in the Tungsten Replicator documentation to set up the master and the slave (extractor and applier).
Use this properties file as an example for your properties file on the slave (applier).
Download this version of drizzle jdbc, and put it in the lib/ directory (of the extracted distribution)
Start it up!

Until there is a mysqldump-converter (or similar) that can create mysql dumps that can be consumed by drizzle, it will be difficult to get a consistent starting point.

Note that this is a very early version of both the applier and the jdbc driver, so please report any bugs to me, krummas@gmail.com, attach the log file from the log directory.

The code has been submitted to Continuent and will hopefully end up in a future official version.

I'm also currently working on an extractor for drizzle - i.e. making it possible to use Drizzle as a master. Stay tuned for that within a week or so.

Drizzle JDBC 0.5

2009-09-28T07:18:00.001+02:00

I just pushed Drizzle-JDBC 0.5 to the maven repos, changelog:

fixes

setBytes in Blob not working
getTypeName in ResultSetMetaData
create timestamp based on a date field
isBeforeFirst() in ResultSets should return false if it is an empty result set
setObject should support java.util.Date
timestamp bug
if asking for generated keys and no query was executed - return empty result set

Support "ON DUPLICATE KEY UPDATE" in rewrite insert batch handler
Handle prepared statement placeholders within quotes and comments
Automatically run test suite for mysql as well as drizzle
Strip queries from comments in prepared statements
Use java.nio.ByteBuffer - better performance and cleaner code
More reports in Hudson (history lost though, apparently no way to convert a maven-project to a freestyle-project): http://marcus.no-ip.biz/hudson/job/drizzle-jdbc-freestyle/

It is available in the public maven repository: http://repo2.maven.org/maven2/org/drizzle/jdbc/drizzle-jdbc/0.5/

Drizzle JDBC 0.4 - Rewrite batch handler

2009-07-14T22:11:00.005+02:00

I just pushed up Drizzle JDBC 0.4, which, most importantly, contains a rewrite batch handler (I guess much like the one Mark Matthews announced for Connector/J a couple of months ago). It gives the expected 10x+ batch insert improvements, on my laptop I can insert 1M "simple" rows in under 20 seconds.

It is written as a plugin, so you need to do this to your Connection to enable it:

if(connection.isWrapperFor(DrizzleConnection.class)) {
DrizzleConnection dc = connection.unwrap(DrizzleConnection.class);
dc.setBatchQueryHandlerFactory(new RewriteParameterizedBatchHandlerFactory());
}

(Yes, it will be possible to configure plugins via the connection string and by setting properties). Please, try it out and report any bugs you find!

I also wrote a Scala version of the plugin, it is not bundled with the 0.4 release though - if you are interrested, it is on Launchpad. Scala is awesome, hoping to do some more Scala development in the near future.

This is the changelog for 0.4:

Findbug fixes
Internal refactorings
Make sure all tests pass against both MySQL and Drizzle
Batch handler API cleanups, details here.
Fix read of 16bit word (by Trond Norbye)
A query rewrite batch handler

Download 0.4 here.

Pluggable Batch Update Handlers

2009-06-04T22:04:00.003+02:00

Reading about the awesome batch insert performance blog post by Mark Matthews last week (http://www.jroller.com/mmatthews/entry/speeding_up_batch_inserts_for) got me thinking, why has this not been done before? Connector/J must be the most deployed JDBC driver in the world and batch inserts are a common use case, why hasn't the community stepped up and implemented the query rewrite feature before? Most likely because it is a complex issue that requires deep knowledge of the rest of the driver. I have been a happy Connector/J user myself for several years and never considered doing something like this.

To handle this complexity in drizzle-jdbc the batch query functionality is pluggable, i.e. you can implement a small interface and tell the connection to use that implementation. So, if anyone out there has some crazy ideas about how to improve performance of batch inserts/updates, it should be fairly easy.

First you need to implement the ParameterizedBatchHandler interface, it has two methods:


void addToBatch(ParameterizedQuery query);
int [] executeBatch(Protocol protocol) throws QueryException;

addToBatch is called when addBatch() is called on the PreparedStatement. I.e. when someone wants to add the current set of parameters in a prepared statement to the current batch - the query parameter contains all the information you need to make something smart.

executeBatch is called when executeBatch() is called on the PreparedStatement. The protocol sent to this method should be used to send the query to the server (though, you could make new connections to the server, fork up a few threads and send queries to the server in parallel).

Then, to make the connection use your handler:


Connection connection = DriverManager.getConnection("jdbc:drizzle://localhost:4427/test_units_jdbc");
if(connection.isWrapperFor(DrizzleConnection.class)) {
   DrizzleConnection dc = connection.unwrap(DrizzleConnection.class);
   dc.setBatchQueryHandler(VerrrryFastBatchHandler.class);
}
PreparedStatement ps = connection.prepareStatement("insert into asdf (somecol) values (?)");
ps.setString(1,"aa");
ps.addBatch();
ps.executeBatch();

The current implementation in drizzle-jdbc simply stores all queries in a list and when doing executeBatch, the queries are sent, one-by-one, to the server. I'm planning on doing a rewrite handler in the near future.

Look at these files for more information:

ParameterizedBatchHandler.java

Drizzle-JDBC 0.3

2009-06-01T08:53:00.004+02:00

I just pushed up 0.3 of drizzle-jdbc to the maven repository, go here to download. It will soon be synced to the official maven repository.

Changes from 0.2 to 0.3:

Add the services file to make the driver autoload.

Throw proper JDBC4 SQLExceptions.

Make blobs + getObject() work against new versions of drizzled.

Fix bug with generated keys and prepared statements.

Fix bug with prepared statements and adding parameters several times (fix by Trond Norbye).

Name the packet fetcher thread.

Fix bug with getSchemas() - returned columns in wrong order.

Rework of the packet fetching by Trond.

Pluggable batch query handlers (I'll blog about this soon)

JavaOne sessions

2009-05-29T14:12:00.002+02:00

I'm attending JavaOne this year, it starts on Tuesday. These are the sessions i look forward the most to (in no particular order):

Return of the Puzzlers: Schlock and Awe (TS-5186)
Josh Bloch and Neal Gafter present a couple of interresting programming puzzles, will be fun.

Effective Java: Still effective after all these years (TS-5217)
Again, Joshua Bloch. His book "Effective Java" is one of the best programming books i've read.

Java Platform Concurrency Gotchas (TS-4863)
Concurrency is a difficult issue, this session presents issues with code examples.

Ghost in the virtual machine: A reference to references (TS-5245)
Bob is the author of google guice (see below) and from what I've heard, he is also a great speaker.

Defective Java Code: Mistakes that matter (TS-5335)
About bugs in java code, static analysis etc.

Drizzle: A new database for the cloud (TS-5410)
Drizzle is a great project, positive, helpful people.

JDBC? We don't need no stinkin' JDBC: How LinkedIn scaled with memcached, SOA and a bit of SQL. (TS-4696)
Always interesting to hear how big companies handle scalability issues really job related for me.

Introduction to Google Guice: The Java Programming language is fun again (TS-5434)
Google Guice really opened my eyes to what you can do with java, the subject of the talk is so true, it shows what java is capable of and that java can be fun. This is how modern java development should be done.

Drizzle-JDBC in central maven repository

2009-05-10T07:14:00.004+02:00

Now releases of Drizzle-JDBC are in the central maven repository which makes it very easy to use if you are building your projects with maven (or ant + ivy).

Just add the following dependency to your pom.xml:

<dependency>                                                                        
 <groupid>org.drizzle.jdbc</groupid>                                          
 <artifactid>drizzle-jdbc</artifactid>                                        
 <version>0.1</version>                                                       
</dependency>

And you are good to go, maven will download the dependencies for you!

Note, drizzle-jdbc uses SLF4J for logging, so if you don't use that you'll need to add a dependency to one of the logging implementations as well:

<dependency>                                                                        
 <groupid>org.slf4j</groupid>                                          
 <artifactid>slf4j-nop</artifactid>                                        
 <version>1.5.6</version>                                                       
</dependency>

jHeidi drizzle support

2009-04-27T07:01:00.000+02:00

Bill Culp over at jheidi.com just released a version of jHeidi that supports Drizzle, using drizzle-jdbc, cool seeing the driver in action! Go try it out.

Initial MySQL support and the JDBC4 wrapper interface

2009-04-21T20:28:00.000+02:00

It is now possible to use drizzle-jdbc to execute queries against a MySQL database. The changes needed to support this were very small, the only difference (right now) in the protocol is that the number of commands has been reduced in drizzle, and the server capabilities enum has been modified to mirror the capabilities in drizzle. In the near future drizzle will have a brand new protocol though, so I made the decision to separate the main protocol classes, and share whatever can be shared outside that class.

To use drizzle-jdbc against MySQL, check this example:


        try {
            Class.forName("org.drizzle.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new SQLException("Could not load driver");
        }
 // lets hope the big O doesnt sue me for using "thin" in the connection string:
        Connection connection = 
           DriverManager.getConnection("jdbc:mysql:thin://slaveusr@localhost:3306/test_units_jdbc"); 
 Statement statement = connection.createStatement();
 statement.execute("INSERT INTO a VALUES (1)");

JDBC4 introduced the wrapper interface, so that a user of the JDBC driver can access vendor-specific functionality in the driver. To test it, I implemented the COM_BINLOG_DUMP command against MySQL, it works like this:


 if(connection.isWrapperFor(ReplicationConnection.class))) {
    ReplicationConnection rc = connection.unwrap(ReplicationConnection.class);
           List<RawPacket> rpList = rc.startBinlogDump(891,"mysqld-bin.000001");
       for(RawPacket rp : rpList) {
              for(byte b:rp.getRawBytes()) {
                 System.out.printf("0x%x ",b);
              }
              System.out.printf("\n");
           }
        }

Of course, this is just a proof-of-concept, it gives you a list of the raw events in the binlog on the server. This is not something really usable, but it shows how to build vendor-specific stuff and access them through the JDBC4 wrapper interface. My plan is to parse the events from the binlog and make them available on a per-request basis so we don't need to store all events from the server binlog in-memory.

Note about performance in drizzle-jdbc

2009-04-01T20:55:00.000+02:00

Ok, i don't really like to optimize early, but this made me learn using blocking queues and executor services. This blog post is very unscientific and does not provide any raw numbers, you can fetch them yourselves, look at the LoadTest test case in drizzle-jdbc. The MySQL stuff is commented out since I want the tests to pass in hudson

First, I ran a few comparisons between drizzle-jdbc against drizzle and Connector/J on MySQL, using the same test case, insert 1000 rows, read them back 1000 times. Connector/J was 5-10 times faster, then i remembered that tables in mysql have myisam as the default storage engine (doh). So, when i created the tables with InnoDB, the performance was still alot better with connector/j, but the difference was not as big (C/J was approx 50% faster).

The first optimization i did was to start fetching packets (communication between drizzle and client is done using packets, for example row packets, ok packets, error packets etc) in a background thread and putting them on a BlockingQueue. To manage the background thread I use an ExecutorService. So, whenever a packet is available on the stream, it is read back and put on the queue. The packet is a raw packet, i.e. the executor simply reads up length and stores the raw bytes in a "RawPacket" on the queue. The gain from this is that while the protocol figures out what kind of packet it is, the next packet is prepared on the queue. Look here for the code of the AsyncPacketFetcher. I also built a SyncPacketFetcher which does not use a background thread or queue, it simply creates the RawPackets directly when called.

By pushing the packet fetching to a background thread, another natural optimization would be to return the result set from the protocol with a reference to the packet fetcher before starting to read RowPackets, and only consume the queue when the user of drizzle-jdbc wants to read a row. Then the user could start processing result sets before all rows are read back from the server.

The second optimization was to start prefetching data when it is available on the inputstream. I.e. read as much as possible whenever reading, and use the local buffered bytes when read():ing. A similar technique is used in connector/j. Look here for the code.

All in all, these optimizations take drizzle-jdbc close to the performance of connector/j (not all the way though, something like 5-10% off). But I want to stay away from any more optimizing until the new protocol is implemented in drizzle. It is also likely that my configuration of my local drizzled is not as good as the configuration of the mysql server I use. All these changes takes the design of drizzle-jdbc closer to what I want when the async stuff is available in drizzle.

Using EnumSet when communicating over the network

2009-03-21T14:15:00.000+01:00

Many applications use bitmasks to send flags over the wire since it is a very efficient way of representing settings etc, problem is that the code to handle them is quite hard to read and maintain, on both the server and client side. To overcome this, EnumSet in Java can be used. If the flags that are masked in the value sent are represented as enums, the bitmask value can be seen as a set of enums, this concept is used in drizzle-jdbc.

For example, to represent server capabilities the following enum is used:

public enum ServerCapabilities {
   LONG_PASSWORD((short)1),       /* new more secure passwords */
   FOUND_ROWS((short)2),       /* Found instead of affected rows */
   LONG_FLAG((short)4),       /* Get all column flags */
   CONNECT_WITH_DB((short)8),       /* One can specify db on connect */
   NO_SCHEMA((short)16),      /* Don't allow database.table.column */
   COMPRESS((short)32),      /* Can use compression protocol */
   ODBC((short)64),      /* Odbc client */
   LOCAL_FILES((short)128),     /* Can use LOAD DATA LOCAL */
   IGNORE_SPACE((short)256),     /* Ignore spaces before '(' */
   CLIENT_PROTOCOL_41((short)512),     /* New 4.1 protocol */
   INTERACTIVE((short)1024),    /* This is an interactive client */
   SSL((short)2048),    /* Switch to SSL after handshake */
   IGNORE_SIGPIPE((short)4096),    /* IGNORE sigpipes */
   TRANSACTIONS((short)8192),    /* Client knows about transactions */
   RESERVED((short)16384),   /* Old flag for 4.1 protocol  */
   SECURE_CONNECTION((short)32768),  /* New 4.1 authentication */
...
   private final short bitmapFlag; //holds the value
   ServerCapabilities(short i) {
       this.bitmapFlag = i;
   }

   public static short fromSet(Set<ServerCapabilities> capabilities) {
       short retVal = 0;
       for(ServerCapabilities cap : capabilities) {
           retVal = (short) (retVal | cap.getBitmapFlag());
       }
       return retVal;
   }
  

   public static Set<ServerCapabilities> getServerCapabilitiesSet(short i) {
       Set<ServerCapabilities> statusSet = EnumSet.noneOf(ServerCapabilities.class);
       for(ServerCapabilities value : ServerCapabilities.values())
           if((i & value.getBitmapFlag()) == value.getBitmapFlag())
               statusSet.add(value);
       return statusSet;
   }
}

This enum contains all capabilities of the drizzle server we are talking to. The value in the enum constructor is the bit position in the the value sent over the network. So, two convenience methods are implemented in the enum, one for taking the actual bit-masked value and creating an enum set, and one for taking an enum set and creating a bitmasked value. fromSet creates a short which contains the flags set, and getServerCapabilitiesSet creates an EnumSet which contains the enums represented by i.

Now it is possible to handle bitmasked values like this:

    short bitmask = readShortFromNetwork(); // yeah not really, but hey
 Set<ServerCapabilities> serverCapabilities = ServerCapabilities.getServerCapabilitiesSet(bitmask);
 if(serverCapabilities.containsAll(EnumSet.of(ServerCapabilities.TRANSACTIONS, ServerCapabilities.SSL)) {
     doSomethingSecure();
 }

Also, when sending something to the server:


 Set<ServerCapabilities> capabilities = EnumSet.of(ServerCapabilities.TRANSACTIONS, ServerCapabilities.SSL);
 writeShortToNetwork(ServerCapabilities.fromSet(capabilities));

So, what we've got is a type safe way of sending/receiving flags to/from a server. Internally, the enum set is stored in a long, so performance-wise it is close to equivalent of handling the bit-operations yourself.

Look at these files for usage examples:

DrizzleProtocol.java

ServerCapabilities.java

Introducing Drizzle JDBC

2009-03-19T21:32:00.000+01:00

The last couple of months I've been working on a JDBC driver for Drizzle, the driver is available here. The aim for the driver was to be lightweight and easy to develop. It is likely that Connector/J will support drizzle in the future, so the aim for this driver is to be a lightweight BSD-licensed alternative.

To build the driver, do the following (java 6 is required):

$ bzr branch lp:drizzle-jdbc
$ mvn compile

If you want to package the driver into something usable, you need to have a drizzle server on your localhost with a database called test_units_jdbc, then you can do:

$ mvn package

And you will get a .jar file in the target directory. There are also nightly builds available in hudson.

The only current dependency is slf4j, so you need to put the api-jar and one of the implementations on the classpath, check here for more help. The reason I use slf4j is that i don't want to impose a logging mechanism on the users, and I dont want to build something that scans the classpath for known logging implementations etc.

Below is an example of how to use it:

Connection connection = DriverManager.getConnection("jdbc:drizzle://localhost:4427/test_units_jdbc");
Statement statement = connection.createStatement();
statement.executeUpdate("INSERT INTO t1 (id) values (1)");

The connection string is on the form:
jdbc:drizzle://<username>:<password>@<host>:<port>/<database>

Notes about using this driver:

It is under heavy development and many features expected from a JDBC driver are not yet implemented. Of course, since it is very early in the development, there will be bugs!
Currently only Java6 is supported (JDBC4 was released with java6)
The internal protocols and API's will change when the new asynchronous server-API is available. This improve performance in the driver alot
Authentication is not supported, this is due to the fact that drizzle does not do this out-of-the-box and the way of doing the authentication is likely to change when the new protocol is in place
The driver reads back the full result set into memory and there is currently no way of not doing this.
Prepared statements are
It does work with hibernate and hibernate-jpa
It can be set up as a datasource in JBoss
It could, in theory, be used against MySQL as well
Bug reports / feature requests are needed! Also, if anyone is interrested in contributing, please do so, contact me on IRC for example, freenode, #drizzle, my nick is marcuse

JAX-WS RI, JBoss and Guice

2008-12-12T13:16:00.000+01:00

So, I needed to use the guicemanaged annotation on some webservices at work, this was the way I got it working, if anyone has a better way, please let me know:

Download the jaxws-ri lib, unpack it
Download guice
Download guicemanaged-0.2.jar
Copy the following libs to
/server/
/lib:
- aopalliance.jar
- guicemanaged-0.2.jar
- guice-snapshot20081016.jar
- jaxws-rt.jar
- jaxb-xjc.jar
- streambuffer.jar
- stax-ex.jar

This advice is probably good even if you don't need guice. When i copied all the jars from the jaxws RI package, jboss freaked out.

Google Guice and JAX-WS RI revisited

2008-11-06T07:57:00.000+01:00

I've been coding a bit more with the guice-extension for jax-ws, and the result is actually quite nice, see below for how your web service endpoint would look when it is handled by guice:

@GuiceManaged(module = WebServiceModule.class)
@WebService
public class AddNumbersImpl implements AddNumbers {

    private Calculator calculator;

    @Inject
    public void setCalculator(Calculator calc)
    {
        this.calculator=calc;
    }
    @WebMethod
    public int addNumbers(int num1, int num2)
    {
        return this.calculator.calc(num1,num2);
    }
}

And WebServiceModule looks like this:

public class WebServiceModule extends AbstractModule {
    protected void configure() {
        bind(Calculator.class).to(CalculatorImpl.class).in(Singleton.class);
    }
}

It can handle endpoints in any scope you want, if you want just one instance of the endpoint, you either annotate the class with @Singleton or bind it in Singleton scope in your module.

Head over to the jax-ws commons project on dev.java.net to get the code, binaries and some information on how to use it.

Google Guice and jax-ws

2008-11-04T09:15:00.000+01:00

To integrate google guice with jax-ws, you need to use the InstanceResolver extension point. this way your endpoints will look like this;

@GuiceManaged

@WebService(targetNamespace = "http://marcus", name="AddNumbers")

public class AddNumbersImpl {

@Inject

public void setCalculator(Calculator calc)

{

this.calculator=calc;

}

...

To achieve this you need to create the annotation GuiceManaged;

@Retention(RUNTIME)

@Target(TYPE)

@Documented

@WebServiceFeatureAnnotation(id=GuiceManagedFeature.ID, bean=GuiceManagedFeature.class)

@InstanceResolverAnnotation(GuiceManagedInstanceResolver.class)

public @interface GuiceManaged {

}

Then you need to implement the GuiceManagedFeature:

public class GuiceManagedFeature extends WebServiceFeature {

public static final String ID="some_nice_id";

@FeatureConstructor

public GuiceManagedFeature()

{

this.enabled=true;

}

public String getID() {

return ID;

}

And finally the actual InstanceResolver:

public class GuiceManagedInstanceResolver extends AbstractMultiInstanceResolver {

private T instance=null;

public GuiceManagedInstanceResolver(@NotNull Class clazz)

{

super(clazz);

}

public T resolve(@NotNull Packet packet) {

if(instance==null)

{

instance=create();

Injector injector= Guice.createInjector(new WebServiceModule());

injector.injectMembers(instance);

}

return instance;

}

That should do it, I'll update this post soon with a URL to the actual code (with documentation on how to use it. My plan is to make it more extensible (i.e. no hard dependency on the WebServiceModule) and possible to use in a non-singleton scope soon.