March 06, 2013

Roman Stoffel

ADBCJ: Some Peformance Numbers

This time we look at some performance numbers, comparing JDBC with ADBCJ (github, blog-posts). Particular the performance of small web applications, once implemented with JDBC, once with ADBCJ. Each application is implemented with the Play Framework, which supports well asynchronous operations. I don’t want to go into all the gritty details. If you really want to read tons of details, go ahead.

The setup looked like this:

- Amazon EC2: Mysql Extra Large instance
- Two Amazon EC2: M1 Large instance as web tiers. Running the Play Application.
- Two Amazon EC2:M1 Medium instance as load tiers. Using Grinder to run scripts which use the web app.

We take a look at latency and throughput.

Test Setup

Test Setup

Blog-App

First let’s look a small blog app. The app serves posts, which have tags and authors. So the application gets the latest posts and fetches the tags for each post. Also it shows the most popular tags.

A blog

A blog


We take a look at the latency, with a web tier which is far away from its max capacity (around 50% CPU utilization):

Latency in Blog Application

Latency in Blog Application

We can see that with ADBCJ requests are completed faster. For example with JDBC, it takes around 50ms on average to return the result; the ADBCJ version can do it in about 38ms.

Let’s also look at the throughput. In a small, focused test, where we access the index page in JSON format, ADBCJ performs way better. However, in a more realistic scenario, where we access different pages, the throughput of ADBCJ was about the same.

Blog: Latency vs Throughput

Blog: Latency vs Throughput

So you are now might tempted to use ADBCJ for a blog like application. Think again, a blog is fairly static application. So we can easily use caching to store the rendered pages. A good cache beats JDBCs and ADBCJs asses! Adding a cache boosts throughput and latency.

Blog: Latency vs Throughput

Blog: Latency vs Throughput

Blog: Latency With Caching

Blog: Latency With Caching

Cache is King

User Content App

Let’s look at a more dynamic application, a “Facebook” clone. The main page lists posts from the people a person follows. Each post has ‘likes’ and comments. Also the site suggests users to follow. Furthermore it also contains messages, which user can send to each other.
About 50% of the requests require complex read operations, the other 50% are simple writes, like inserting a new comment.

First, let’s look again at the latency. We can see that the complex read operation suddenly is three times faster. However, all writes requests are slowed down. This is maybe a tradeoff which you can make. Reading requests are the ones where users have to wait, while write requests can be done in the background via AJAX.

Latency User Content App

Latency User Content App

So, I wondered if there a fundamental issue with ADBCJ for writes, or if it’s an interaction effect. I ran test with separate read and write requests only. Then ADBCJ has always better latency. So, it seems to be an unfortunate interaction.

Reads / Writes Only

Reads / Writes Only

Finally, let’s take a throughput. ADBCJ does way better. Even more, when the system is under pressure, also ADBCJ’s latency for writes start to get better compared to JDBC.

Throughput User Content

Throughput User Content

Latency under large Load

Latency under large Load

Stats Collection App

The last application is a ‘stats collection app’, which collections information about the user’s actions. It is very write heavy, with only some rare, long running reads. Here JDBC and ADBCJ latency was about the same. However, the throughput of ADBCJ was much better.

Stats Collecting

Stats Collecting

Stats App Throughput

Stats App Throughput

Conclusion

So, ADBCJ can speed up your web applications. Of course, only the data serving parts. Keep in mind caching, avoiding requests etc). Of course you need to do your own experiment for your use case. Next time I’m going to explain why ADBCJ can support better latency and throughput, with code examples. Stay tuned!

by gamlerhart at March 06, 2013 21:41

February 28, 2013

Roman Stoffel

ADBCJ in Scala: Getting Started

What, again asynchronous database access? Yes, because I’m currently knee deep in this stuff. (^^) This post series will show how to use ADBCJ in Scala 2.10, share a few tips, performance insights etc.

ADBCJ and Scala, new buddies

ADBCJ and Scala, new buddies

Grabbing the Bits

Of course, first we need to grab the bits. We can get it via SBT. Currently there are only snapshots available on my snapshot repository. We pull the Scala ADBCJ API, the ADBCJ connection pool and the MySQL driver.

name := "Scala ADBCJ Demo"

version := "1.0"

scalaVersion := "2.10.0"

libraryDependencies += "info.gamlor.adbcj" %% "scala-adbcj" % "0.6-SNAPSHOT"

libraryDependencies += "org.adbcj" % "adbcj-connection-pool" % "0.6-SNAPSHOT"

libraryDependencies += "org.adbcj" % "mysql-async-driver" % "0.6-SNAPSHOT"

resolvers += "Gamlor-Repo" at "https://raw.github.com/gamlerhart/gamlor-mvn/master/snapshots"

Getting Access to the Database

The next step is to get access the the database. We do this with the Database object, by passing in the connection URL, username and password. This create a instance which is used to access this database. Usually you should have one instance only per database in your application.

  // Note the "pooled" prefix, for using the connection pool
  val database: DatabaseAccess = Database("adbcj:pooled:mysql://localhost/adbcj-demo",
    "adbcj",
    "adbcj-pwd")

Then we can create new connections. The most basic way is to create a connection directly. However, you are then responsible for closing the connection.

// connect() will return a future, which contains the connection
// You need to close the connection yourself.
val simpleConnectionFuture = database.connect()

// We can use the Scala for construct to deal nicely with futures
val futureForWholeOperation = for {
 connection <-simpleConnectionFuture
 closeDone <- connection.close()
} yield "Done"

Closing Connections Automatically

Now closing the connection manually can be error prone and tedious. Therefore helper methods exists. One for using a connection and one for using a connection with a new transaction.
The closure has to return a future. Since everything is asynchronously, we cannot close the connection when we leave the future. Instead we need to close it when everything is done. So, the connection will be close, when the future returned by the closure is completed.

val doneWithConnection:Future[DBResultList] = database.withConnection{
connection =>
  // do something with the connection
  // you need to return a future, because everything is asynchrous
  // so the connection can only be closed when everything is done
  connection.executeQuery("SELECT 1")
}

val doneWithTransactionAndConnection:Future[DBResultList] = database.withTransaction{
connection =>
  // Same goes for transactions
  connection.executeQuery("SELECT 1")
}

Some Real Work

Let’s do some real work. We create the schema if it doesn’t exist and then insert a few items. In general, I highly recommend to read the Akka documentation about futures, which gives an idea how to deal with tem.

val txDoneFuture = database.withTransaction{
connection =>
  // Same goes for transactions
  val schemaCreation = connection.executeQuery("""CREATE TABLE IF NOT EXISTS posts(\n
  						id int NOT NULL AUTO_INCREMENT,\n
							title varchar(255) NOT NULL,\n
							ontent TEXT NOT NULL,\n
							PRIMARY KEY (id)\n
							) ENGINE = INNODB;""")

  val firstPost =connection.executeUpdate("INSERT INTO posts(title,content) VALUES('The Title','TheContent')")
  val secondPost =connection.executeUpdate("INSERT INTO posts(title,content) VALUES('Second Title','More Content')")
  val thirdPost =connection.executeUpdate("INSERT INTO posts(title,content) VALUES('Third Title','Even More Content')")

  // we want to await for all operations at the end
  for {
  	schemaDone <-schemaCreation
  	postOneDone <-firstPost
  	postTwoDone <-secondPost
  	postThreeDone <-thirdPost
  } yield "All Done"

}

Query

Let’s also query for some data. When running queries, your future will contain a immutable result set, which supports the standard collection operations.

    val postsDataFuture = database.withTransaction{
      connection =>
        val postsFuture =connection.executeQuery("SELECT * FROM posts")

        postsFuture onSuccess {
          case rs:DBResultList => {
            for (row <- rs){
              System.out.println("ID: "+row("ID").getLong()+" with title "+row("title").getString());
            }
          }
        }

        postsFuture
    }

Prepared Statements

Of course, there prepared statement support:

val postsDataFuture= database.withTransaction{
  connection =>
  val preparingStmt =connection.prepareQuery("SELECT * FROM posts WHERE title LIKE ?")

	val postsFuture = preparingStmt.flatMap(stmt=>stmt.execute("Third Title"))

	postsFuture onSuccess {
	  case rs:DBResultList => {
		for (row <- rs){
		  System.out.println("ID: "+row("ID").getLong()+" with title "+row("title").getString());
		}
	  }
	}

	postsFuture
}

One Last Hint, Debugging

So, this was a small tour through the API. However I want to share one last important thing, which helps you dealing with failures.

Let’s assume you ran a back query:

org.adbcj.mysql.codec.MysqlException: 42S02Table 'adbcj-demo.thistabledoesnotexist' doesn't exis 
  at org.adbcj.mysql.codec.packets.ErrorResponse.toException(ErrorResponse.java:49)
	at org.adbcj.mysql.codec.decoding.ExpectQueryResult.handleError(ExpectQueryResult.java:31)
	at org.adbcj.mysql.codec.decoding.ResponseStart.parse(ResponseStart.java:33)
	at org.adbcj.mysql.codec.decoding.AcceptNextResponse.parse(AcceptNextResponse.java:26)
	at org.adbcj.mysql.codec.MySqlClientDecoder.doDecode(MySqlClientDecoder.java:96)
	at org.adbcj.mysql.codec.MySqlClientDecoder.decode(MySqlClientDecoder.java:67)
	at org.adbcj.mysql.netty.Decoder.decode(MysqlConnectionManager.java:177)
	at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:113)
	...

Then you get a future which contain the error. However, the stack trace of the error does not contain any hint where in the code the query was issues. It just contains a stack trace with the internal event loop:

You can use the debug flat to get a stack trace which points at the location which sent the request. Either add -Dorg.adbcj.debug=true to the JVM flags. Or set the appropriate property when crating the database access. Note that this feature is quite expensive, so I recommend to use it while developing or during an investigation.

org.adbcj.DbException: 42S02Table 'adbcj-demo.thistabledoesnotexist' doesn't exis 
  at org.adbcj.support.stacktracing.StackTraceCapturing$CaputureByDefault.capture(StackTraceCapturing.java:20)
	at org.adbcj.support.stacktracing.StackTraceCapturing.defaultCapture(StackTraceCapturing.java:11)
	at org.adbcj.support.stacktracing.StackTracingOptions$1.captureStacktraceAtEntryPoint(StackTracingOptions.java:12)
	at org.adbcj.support.DefaultDbFuture.<init>(DefaultDbFuture.java:45)
	at org.adbcj.support.DefaultDbSessionFuture.<init>(DefaultDbSessionFuture.java:50)
	at org.adbcj.mysql.codec.MySqlRequests.executeQuery(MySqlRequests.java:28)
	at org.adbcj.mysql.codec.MySqlConnection.executeQuery(MySqlConnection.java:108)
	at org.adbcj.mysql.codec.MySqlConnection.executeQuery(MySqlConnection.java:102)
	at org.adbcj.connectionpool.PooledConnection.executeQuery(PooledConnection.java:78)
	at info.gamlor.db.DBConnection$$anonfun$executeQuery$1.apply(DBConnection.scala:109)
	at info.gamlor.db.DBConnection$$anonfun$executeQuery$1.apply(DBConnection.scala:109)
	at info.gamlor.db.FutureConversions$class.completeWithAkkaFuture(FutureConversions.scala:20)
	at info.gamlor.db.DBConnection.completeWithAkkaFuture(DBConnection.scala:31)
	at info.gamlor.db.DBConnection.executeQuery(DBConnection.scala:109)
--->at info.gamlor.adbcj.scalademo.MainDemo$$anonfun$8.apply(MainDemo.scala:99)
    ... // RIGHT Location
Caused by: org.adbcj.mysql.codec.MysqlException: 42S02Table 'adbcj-demo.thistabledoesnotexist' doesn't exist 
	at org.adbcj.mysql.codec.packets.ErrorResponse.toException(ErrorResponse.java:49)
	at org.adbcj.mysql.codec.decoding.ExpectQueryResult.handleError(ExpectQueryResult.java:31)
	at org.adbcj.mysql.codec.decoding.ResponseStart.parse(ResponseStart.java:33)
	at org.adbcj.mysql.codec.decoding.AcceptNextResponse.parse(AcceptNextResponse.java:26)
	at org.adbcj.mysql.codec.MySqlClientDecoder.doDecode(MySqlClientDecoder.java:96)
	at org.adbcj.mysql.codec.MySqlClientDecoder.decode(MySqlClientDecoder.java:67)
	at org.adbcj.mysql.netty.Decoder.decode(MysqlConnectionManager.java:177)
	at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:113)

So, that’s it for now. Future posts will go into more aspects of ADBCJ the interaction with Scala.

by gamlerhart at February 28, 2013 22:59