Pluggable Connection Pool

[yngbldjr]

We have had some bad experience int he past with C3PO - mainly leaking connections and not closing properly. I am sure it more to do with the settings used and our custom JDBC driver.

We have been using BoneCP with a lot of success!

I wanted to get your thoughts on a configurable connection pool?

One of the nice things about BoneCP is they provide a connection hook, to be able to perform specifc logic around when to terminate a connection/all connections. I am not sure how this would be supported in the module.

Thanks,
Carter

[timyates]

Hiya!

Not sure how I could make it pluggable, as despite them having a unified API for getting the connections out, the config varies quite a bit.

Do you have an example of c3p0 failing? Maybe post it as an issue on their project -- or if you have an example, I will swap c3P0 out for BoneCP or similar

PS: Not sure why you forked the project, then changed the package structure, then removed the README and LICENSE and the tests. This will make any pull request impossible (and looks like trying to erase me from history)

[yngbldjr]

I can try to get an example of it failing, but like I said, I am sure it's
probably due to our specific JDBC driver and not specifically with c3p0.
We have had luck with BoneCP, because it allows us to perform some more
specific connection termination and reconnect above c3p0. We also
experienced connection leaks with c3p0 over time.

With regards to forking and taking away your credit ;)... That was
certainly not the intent. I am a nubie when it comes to github.. And was
more messing around with stuff more than anything. I plan to rebase and
make sure that the license and readme is in place. The last thing I want
to do is stifle the open source and community effort when creating great
software. I appreciate the work you have done and want to make sure you
get your due credit!

-Carter

On Tuesday, July 23, 2013, Tim Yates wrote:

Hiya!

Not sure how I could make it pluggable, as despite them having a unified
API for getting the connections out, the config varies quite a bit.

Do you have an example of c3p0 failing? Maybe post it as an issue on
their project https://github.com/swaldman/c3p0 -- or if you have an
example, I will swap c3P0 out for BoneCP or similar

PS: Not sure why you forked the project, then changed the package
structurehttps://github.com/yngbldjr/mod-jdbc-persistor/commit/ff93148446cd54be0bcb186ec45f6ab183b3f180,
then removed the README and LICENSEhttps://github.com/yngbldjr/mod-jdbc-persistor/commit/194126ce429e393f3b8d4d8972064c54061791eeand the
testshttps://github.com/yngbldjr/mod-jdbc-persistor/commit/58d790fa15c6f896edd848b21c78eca487ec9a2b.
This will make any pull request impossible (and looks like trying to erase
me from history)

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/13#issuecomment-21409046
.

Carter Youngblood
[email protected]

[timyates]

Do you have examples of the more specific termination and reconnection calls? Even if I switched to BoneCP, I'd need to implement these across all connections as again it would be hard to make it pluggable 😦

It might be easier in this case to create your own multi-threaded worker mod with business case methods that interact with JDBC (and your own connection pool), rather than using the jdbc-persistor

[yngbldjr]

I agree that the connection pool is not easily pluggable...

I will test with the mod and see if the pool becomes an issue.

I will attach a more detained example of our datasource using BoneCP later
today.

On Tuesday, July 23, 2013, Tim Yates wrote:

Do you have examples of the more specific termination and reconnection
calls? Even if I switched to BoneCP, I'd need to implement these across all
connections as again it would be hard to make it pluggable [image:
😦]

It might be easier in this case to create your own multi-threaded worker
mod with business case methods that interact with JDBC (and your own
connection pool), rather than using the jdbc-persistor

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/13#issuecomment-21420835
.

Carter Youngblood
[email protected]

[timyates]

As for the forking thing, no worries. Had me worried for a while though ;-)

It's inadvisable to change the package structure, as it makes any bugs/fixes you find really hard to push back, and the same for getting any updates i make back into your forked repo

[yngbldjr]

Completely agree! Let's chalk it up to a newbie Github user:). I have not
had the opportunity to use github much until recently and am still trying
to get all the terminology and patterns. I have had the displeasure of
working with Perforce for the last 3 years :(. I am sure the issue is more
with our process than Perforce.

I will make sure to rebase and start fresh with the forked repo.

Another thought... Instead of pluggable... There could be 2 versions to
choose from c3p0 or BoneCP? Just a thought...

On Tuesday, July 23, 2013, Tim Yates wrote:

As for the forking thing, no worries. Had me worried for a while though ;-)

It's inadvisable to change the package structure, as it makes any
bugs/fixes you find really hard to push back, and the same for getting any
updates i make back into your forked repo

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/13#issuecomment-21421452
.

Carter Youngblood
[email protected]

[bric3]

Well I agree to make something plugable or it could be some sub-project support modules, like

• c3p0-support
• boncp-support
• hikaricp-support

On another matter if we care about performance, I strongly advises you to take a look at HikariCP jdbc pool, it shows really impressive numbers ! And actually highlight some issues in the BoneCP benchmarks.

[timyates]

Oddly, I've been thinking about this since the stats for Hikari came out (I wonder if it works with Oracle, as it shows Untested in the Status)

Not sure in my head how to implement this yet though, as there doesn't seem to be the functionality to dynamically include another module (I could have each connection pool as a system module deployed to maven)

And I'm not sure you can send a DataSource across the message bus

Any ideas?

[bric3]

I don't think it will be possible to send the DS over the message bus. Actually I wasn't thinking about vert.x modules, but more something about modules you could put in the classpath.

However I'm not sure if it works well if different datasources are needed in the app. It could be irrelevant it a verticle instance has it's own classloader, but I'm not sure how is the classloader infrastructure with vert.x

[timyates]

I was wondering whether I could pass the pool to use in the config message to the jdbc-persistor (along with pool specific config), and then have the persistor load the relevant pool implementation as a mod into it's parent classloader (much as the include param in mod.json does, but at runtime)

Otherwise, I believe I'd need to maintain 3+ separate mod-jdbc-persistors, which is something I'd rather avoid ;-)

Not sure if calling Platform methods inside a mod is advisable though... I've fired an email off to the list to see if anyone has any suggestions

[bric3]

Also it's worthwhile to say the untested for Oracle driver is relative to the instrumentation mode only, which is optional. The performance using the delegate mode already shows remarkable numbers.

[bric3]

Yeah having three different mod would be a pain to maintain. I would prefer – something like you said – to pass the config with the pool type, instantiate and configure it at runtime, meaning each handler instance has his own pool instance.

We'll see what the mailing list says.

About pools I also think tomcat-jdbc pool might be a worthwhile support as well, as the codebase is recent, and haven't shown yet issues regarding leaks, deadlocks, etc.
Again to support this issue, I've heard that C3PO might deadlock, and BoneCP cannot recover from a DB failure, so it might be interesting to offer alternatives ;)

[timyates]

Looks like runtime mod includes are on the task list for a future version of vert.x, so I'll wait until then to look into this

[bric3]

Indeed, it the meantime what do you think to be able to put a support jar in the /lib folder, one that is looked up via standard classloading / reflection features.

[timyates]

Sorry, I'm having a stupid moment... How would it then know to load the correct pool implementation, and pass pool configuration?

[bric3]

Sorry too sometimes not everything comes out of my head ;)
So I was thinking about passing the info like you do when configuring a Tomcat resource, you give the qualified name of the datasource factory, maybe we won't even need runtime modules.

About the dependencies stuff, I think compile dependencies in gradle will be copied to the /lib folder which can be used in the application without vert.x having to fetch them. At least that's my understanding of the module documentation.

And the above could easily be adapted to a json payload.

<Resource name="jdbc/IntegDB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1"
          validationInterval="30000"
          timeBetweenEvictionRunsMillis="30000"
          maxActive="100"
          minIdle="10"
          maxWait="10000"
          initialSize="10"
          removeAbandonedTimeout="60"
          removeAbandoned="true"
          logAbandoned="true"
          minEvictableIdleTimeMillis="30000"
          jmxEnabled="true"
          username="schema"
          password="password"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://databases.integ:3306/mysql"/>