Vertx / JDBC client / jOOQ

[J Guedouar]

Hello guys,



Is there any example using JDBC client ( non blocking I/O ) and jOOQ ( http://www.jooq.org/ ), in fact I just want to replace the native SQL into jOOQ requests.

     connection.query("SELECT * FROM some_table", res2 -> {
      if (res2.succeeded()) {

        ResultSet rs = res2.result();
        // Do something with results
      }

Replacing this: ( JDBC query )

"SELECT * FROM some_table"

By

jOOQ DSL

create.selectFrom(BOOK)
      .where(BOOK.PUBLISHED_IN.eq(2011))
      .orderBy(BOOK.TITLE)

Of course its a simple example...



Thank you !

[Paulo Lopes]

I think there was some example posted on the forum about it not so long ago.

https://github.com/qrman/vertx-guice-jooq-ultm

[J Guedouar]

This examples shows a blocking operation JDBC, i want to have a non blocking I/O with JDBC/jOOq combo.

[Witold Szczerba]

JDBC is a blocking API and there is absolutely nothing you can do with that. It is a blocking library and spec and as such your only option is to give it worker threads and let it block over there, just like in the example (by Krzysztof Urman) mentioned here by Paulo Lopes.

Regards,

Witold Szczerba

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
Visit this group at https://groups.google.com/group/vertx.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/fc8e2af5-7249-4919-afdb-0f316f608ed2%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[J Guedouar]

Sorry but look at this:

http://vertx.io/blog/using-the-asynchronous-sql-client/

its JDBC async non blocking !

[Clement Escoffier]

Hi,

Yes this JDBC client is non-blocking, but internally it is using using the JDBC API (blocking) in different threads (worker threads). So as a user it’s non-blocking, but under the hood it’s still JDBC and make it non-blocking. 

Clement

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/162d90f7-ff89-4a09-ae3d-2067e4125ced%40googlegroups.com.

[Ivano Pagano]

As already mentioned in a similar thread, you can also take a look a this project to see how to wrap the jdbc datasource in a dedicated comsat lightweight fiber for non-blocking client calls.

https://github.com/aardelean/nonblocking-microservice

The exact piece of code is here:

https://github.com/aardelean/nonblocking-microservice/blob/master/src/main/java/home/microservice/nonblocking/JOOQConfiguration.java#L17

bye

Ivano

[J Guedouar]

So in fact this is just a wrapper for a worker verticle ( aka verticle that executes blocking I/O ) ?

[Julien Viet]

not exactly, there is no verticle deployment involved here, it uses the Context.executeBlocking() to which you provide a blocking lambda executed by the worker thread pool.

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/ee03647c-7c7c-42de-bde7-9e598249db50%40googlegroups.com.

[Alexandru Ardelean]

Hi Julien, first of all, congratz for the lead position.

I developed that piece of code to learn more about jooq, quasar and vertx. I wonder why you are saying it is executed within a blocking manner.

I mean, the code for the database connection is indeed a jdbc code, but encapsulating in that fiberWrapper it is advertised to take full advantage of the fibers...

Should work pretty much the same system as vertx-sync.

The service works as follows: spring boot starts and registers vertx with qbit and jooq.

The request hits qbit and then it uses the jooq datasource (encapsulated into fibers) pooling to obtain connection and to query.

The datasource has a 200 connection pooling. I did the very same example against jpa, with load testing. Above 200 concurrent users simulation, the requests per second started to decrease (signs for blocking threads)

Now, with jooq and fibers wrapper, at 800 concurrent users simulated still didn't show any decreasing of requests per second. I took this as the final needed proof that this solution is actually non blocking (much like jooq advertises).

Please tell me if I was using in a wrong way vertx for this example.

In general, in my developing examples, I am starting with spring boot which starts verticles. Also I plan on using solely qbit for delegating requests to other services. I was very much sure this is the proper way of using vertx for non blocking and ease of development.

[Guido Medina]

This is something not related, but you might want to re-think the connection pool sizing when configuring your DS pool, I particularly use HikariCP which is consistent and predictable and recently had to re-think my DB pooling sizing base on this:

• https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

In that article there is a sort of a presentation, skip it and go to the next paragraph that says "spoiler alert"

TBH I don't know where people get that 200 connection pool will make a different on a 8 CPU server other than more contention to just grab the connection for the sake of grabbing and then yield/surrender the CPU to something else because I/O is so busy...

Anyway enjoy the article, any operation that uses I/O must be carefully treated specially on an asynchronous toolkit/framework, JDBC IS a blocking operation because it relays on a blocking OS resource which is ... guess ... I/O

Now, if you carefully pick your datasource and configure it properly you might get away with it without any care.

HTH,

Guido.

[Alexandru Ardelean]

Hi Guido,
Thank you for your input.
I was using tomcat pool datasource... I would expect to be the same thing. I have tried with just the connection (with no pooling), and got 80% less requests served per second.

For example: without pooling, directly the datasource with the connection -> 660 requests per second.
With poolable datasource (apache tomcat implementation), 200 pool -> 3300 requests per second (same sql, same response).
I tried above 200, but the performance didn't increase, yet the memory footprint was higher. So I stayed with 200.

You are right that jdbc is blocking. I was trying out a wrapper to transform the thread block under the jdbc protocol within a fiber block (check quasar), which is 200 times lighter (both in memory and switching time) on the OS level.

It was a success(by my standards), but what Julien was saying (I think), is that I am calling the jdbc connection from a blocking context in the first place. I was not calling that service from a verticle, as I started the httpServer outside of the verticle, and could very well be that I used in a blocking manner because of that.

This is what I understood :).

[J Guedouar]

Is it possible to have the same example without Spring but with HK2 instead ?

Thx !