Connection Pool Reconnects

[Melvin Ramos <Me...@amphorainc.com>]

Hello all,


I was wondering if you can help with my current issue.


I connect to our Database (SQL Server) via VPN tunnel. When I forcefully bounce our VPN connection and let the application to continue its process. It just hangs and do nothing.


What it the best way for the cayenne pool to detect connection failure? Is there a way to force connection when it needs it?


Please advice.


Melvin



Confidentiality Notice: This e-mail and accompanying documents contain confidential information intended for a specific individual and purpose. This e-mailed information is private and protected by law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, or distribution, or the taking of any action based on the contents of this information, is strictly prohibited.

Re: Connection Pool Reconnects

[Michael Gentry <bl...@gmail.com>]

To be a Hikari cheerleader, I recently switched to it from DBCP for local
development and it reduced application startup by about 25%.  The
applications do lots of queries (and DB faults) on startup and Hikari
really helped out with the connection pool performance (every query/fault
required a checkout/in from the connection pool).  I plan on pushing it
forward (to testing/staging/production) in the future unless we discover
issues with it.



On Wed, Nov 1, 2017 at 3:23 AM, Andrus Adamchik <an...@objectstyle.org>
wrote:

> Hi Melvin,
>
> Don't have much recent experience with SQLServer specifically, but as a
> general advise for better control of such things I would recommend using a
> third-party connection pool such as DBCP2 [1], Tomcat [2] or Hikari [3]
> (everyone's favorite as of late). All of them have manny more knobs that
> you can turn compared to Cayenne built-in DataSource. E.g. Tomcat
> DataSource has 'removeAbandoned' and 'removeAbandonedTimeout' settings that
> allow to get rid of the stuck connections, which sounds like something that
> can help here.
>
> There are a few ways you can install a custom DataSource in Cayenne. The
> easiest is via API when starting ServerRuntime:
>
>   ServerRuntime r = ServerRuntime.builder().dataSource(myDS).build();
>
> Andrus
>
> [1] https://commons.apache.org/proper/commons-dbcp/
> [2] https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html
> [3] https://github.com/brettwooldridge/HikariCP
>
> > On Oct 31, 2017, at 5:26 PM, Melvin Ramos <Me...@amphorainc.com>
> wrote:
> >
> > Hello all,
> >
> >
> > I was wondering if you can help with my current issue.
> >
> >
> > I connect to our Database (SQL Server) via VPN tunnel. When I forcefully
> bounce our VPN connection and let the application to continue its process.
> It just hangs and do nothing.
> >
> >
> > What it the best way for the cayenne pool to detect connection failure?
> Is there a way to force connection when it needs it?
> >
> >
> > Please advice.
> >
> >
> > Melvin
> >
>

Re: Connection Pool Reconnects

[Andrus Adamchik <an...@objectstyle.org>]

Hi Melvin,

Don't have much recent experience with SQLServer specifically, but as a general advise for better control of such things I would recommend using a third-party connection pool such as DBCP2 [1], Tomcat [2] or Hikari [3] (everyone's favorite as of late). All of them have manny more knobs that you can turn compared to Cayenne built-in DataSource. E.g. Tomcat DataSource has 'removeAbandoned' and 'removeAbandonedTimeout' settings that allow to get rid of the stuck connections, which sounds like something that can help here.

There are a few ways you can install a custom DataSource in Cayenne. The easiest is via API when starting ServerRuntime:

  ServerRuntime r = ServerRuntime.builder().dataSource(myDS).build();

Andrus

[1] https://commons.apache.org/proper/commons-dbcp/
[2] https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html
[3] https://github.com/brettwooldridge/HikariCP

> On Oct 31, 2017, at 5:26 PM, Melvin Ramos <Me...@amphorainc.com> wrote:
> 
> Hello all,
> 
> 
> I was wondering if you can help with my current issue.
> 
> 
> I connect to our Database (SQL Server) via VPN tunnel. When I forcefully bounce our VPN connection and let the application to continue its process. It just hangs and do nothing.
> 
> 
> What it the best way for the cayenne pool to detect connection failure? Is there a way to force connection when it needs it?
> 
> 
> Please advice.
> 
> 
> Melvin
>