ADBA direction and alternatives

[sagenschneider]

Hi

I work on OfficeFloor (http://officefloor.net). Currently, I'm involved in improving performance of OfficeFloor. There are various aspects I'm looking at... multiple thread pools, thread affinity, reduced locking, etc (basically the continuation injection and thread injection concepts that OfficeFloor is built upon).... though most interested at the moment is finding a "pipelined" database driver for PostgreSql.

My understanding of the reason Vertx scores 150K per second faster than the next framework ( https://www.techempower.com/benchmarks/#section=data-r16&hw=ph&test=db ) is its use of pipelined database calls via Reactive PgClient ( https://github.com/reactiverse/reactive-pg-client ).

I'm, therefore, looking for an asynchronous PostgreSql client that is capable of pipeline (and likely sharing selector with other networking) to drive up performance of OfficeFloor in the database tests. Note: I suggest sharing the selector as it will enable a single thread to service multiple database connections (to possibly multiple database servers) - my understanding of nio to drive down number of threads to increase performance.

Ideally, I don't want to build to a "custom" API (such as ReactivePgClient or PgNio... though PgNio is looking to support ADBA but seems a dead project). The ADBA (sql2) API sounds very promising and allows us to re-use framework code on top of this API (side interest is looking to build an asynchronous reporting engine that can pipeline requests over multi-plexed connections for faster generation of reports - all requests are read-only so ideal for multi-plexed pipelining).

Anyway, I'm interested in helping out.

One thing I would like to look at is adding a Selector to the Connection.visit() so stops thread running 100% CPU in a loop. If you are happy with the help, I'm happy to get started on this :)

[cretz]

though PgNio is looking to support ADBA but seems a dead project

Most definitely not dead :-) I use it at my company to great success, I just haven't needed any more features recently. It's the ADBA support that's currently dead (sorry if that's what you meant) because I feel that until the jdbc-spec team does more (esp wrt a conformance test and more documentation) it is not worth pursuing. PgNio is definitely written to be completely extensible and give the power to the user to use/pipeline what they want when they want and every aspect, including the networking, is accessible and replaceable. Sorry to hop on this issue to talk about that project, just wanted to mention it.

[alexanderkjall]

Hi @sagenschneider, any help would be greatly appreciated :). Writing a database driver is a pretty large project and since I'm doing this as a hobby project it's far from finished.

The api/spec is still in very active debate on the jdbc-spec mailing list, so as @cretz said there is a lot more work that will happen on their end before everything settles down, so this is an early adopter project.

@cretz no problem talking about pgnio, more correct information is always better.

[sagenschneider]

Sorry, @cretz . Did not mean to imply PgNio was dead. Only that last commits in the project were more than 4 months ago, so that given ADBA is active and in some flux... that, as you say, the ADBA effort on PgNio project is on hold.

@cretz Is there a time frame on when PgNio might implement the ADBA spec?

Note: my interest here is building on a "portable" solution that I can use on top of other database implementations (and not be tied to a particular driver API). Just happens Tech Empower get best efforts with PostgreSql, so starting my focus on this (plus its much cheaper to run up PostgreSql than Oracle Database :) ).

If there is nothing available in this space, I'm happy to help build it out. From what I can see is that pgsql2 is further along in the ADBA spec implementation. But this is only my last few days of research in this area... so very happy to be corrected on my little bit of experience in the ADBA driver space :)

[sagenschneider]

@alexanderkjall sounds like pgsql2 is the way to go for ADBA for PostgreSql. I'll get started on adding a Selector to the Connection.visit() thread so that the thread waits for events rather than polling at 100% CPU.

I'll also keep in focus that the Selector can be provided to the DataSource so that a single thread can service many Connections and even many DataSources (plus for web frameworks the HTTP socket also) allowing "true non-blocking". However, will also assume many threads can be used so needs some level of thread safety :)

[cretz]

Is there a time frame on when PgNio might implement the ADBA spec?

No. I suspended work on it. To not clutter this issue up, PgNio ADBA support can be discussed at cretz/pgnio#8. Yes, this project would be your best choice if you need ADBA support.

[davecramer]

And to further clutter up the issue YAPGRD https://github.com/r2dbc/r2dbc-client Seems that everyone is scratching the same itch here. I'm not entirely convinced that ADBA is the solution, it seems to be evolving as we speak. https://github.com/reactiverse/reactive-pg-client Cheers, Dave

…

On 31 July 2018 at 10:01, Chad Retz ***@***.***> wrote: Is there a time frame on when PgNio might implement the ADBA spec? No. I suspended work on it. To not clutter this issue up, PgNio ADBA support can be discussed at cretz/pgnio#8 <cretz/pgnio#8>. Yes, this project would be your best choice if you need ADBA support. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#17 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAYz9oRHUEPT_gjDUhuj1UAQoqaX7JbHks5uMGNKgaJpZM4VnlW3> .

[davecramer]

On 31 July 2018 at 10:11, Dave Cramer ***@***.***> wrote: And to further clutter up the issue YAPGRD https://github.com/ r2dbc/r2dbc-client Seems that everyone is scratching the same itch here. I'm not entirely convinced that ADBA is the solution, it seems to be evolving as we speak. https://github.com/reactiverse/reactive-pg-client Cheers, Dave

Hmm that went out a bit too quickly. What I was going to say was it would be useful to collaborate on a single solution. I can add that to the pgjdbc github project if that is of interest. The other thing that would be useful is to enumerate all of the various reactive or async projects. So far I am aware of: https://github.com/reactiverse/reactive-pg-client https://github.com/r2dbc/r2dbc-client https://github.com/cretz/pgnio https://github.com/alaisi/postgres-async-driver Please add to this list if you are aware of others.

…

On 31 July 2018 at 10:01, Chad Retz ***@***.***> wrote: > Is there a time frame on when PgNio might implement the ADBA spec? > > No. I suspended work on it. To not clutter this issue up, PgNio ADBA > support can be discussed at cretz/pgnio#8 > <cretz/pgnio#8>. Yes, this project would be > your best choice if you need ADBA support. > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <#17 (comment)>, or mute > the thread > <https://github.com/notifications/unsubscribe-auth/AAYz9oRHUEPT_gjDUhuj1UAQoqaX7JbHks5uMGNKgaJpZM4VnlW3> > . >

[cretz]

In addition to https://github.com/reactiverse/reactive-pg-client and https://github.com/alaisi/postgres-async-driver, I also used https://github.com/mauricio/postgresql-async (basically Scala only) before building my own. Ideally, places like https://github.com/dhamaniasad/awesome-postgres#language-bindings and/or https://github.com/akullpp/awesome-java#database would have these lists.

[alexanderkjall]

As async database access is an area that is still quite new I think it's very natural that there exists many different projects that explore different API styles, determining what abstraction model that is best suited isn't an easy task.

My gut feeling is that the ADBA standard as it looks today isn't something that I would like to have directly in my business logic at work. I would probably want to have at least a hand full of helpers that abstract stuff away, and also something that could pool the database connections.

But regardless of that I still think that it's important that there is a standardization process, so that it will be possible to build reusable components, and ADBA feels like it will cover all use cases.

[sssinghsyr]

@sagenschneider
I had the same suggestion, to reduce/block the continuous CPU usage of the DataSource thread.
Issue #12

I had started working on this, have to make more changes and code re-work
forked source - io-multiplexing

You can have a lookup and we can both work on this.

[sagenschneider]

@sssinghsyr thanks. I did some work last night. I'll continue discussion of Selectors to #12 (correction, as #12 closed opened #18 to continue work) and get my code in so you can see my approach (basically want to inject the Selector so that it can be re-used accross DataSources and potentially even by HTTP servicing when used in web application).

I'll rename this issue to aligning to ADBA direction and learning from alternatives.

[sagenschneider]

@sssinghsyr discussion for Selector continued in issue #18 (as #12 closed)