DBCP and Firewall timeouts

Hello,

 We have a tomcat 5.5 server and a mysql 5.1 database sitting between
a firewall. The firewall times out connections after 60 minutes.  We
tried setting the minEvictableIdleTimeMillis to be 30 minutes but we
are still seeing these timeouts.

 Is there a recommended config of ensuring the connection pool
connections either get cleanly terminated or are sanity checked for
proper connectivity?  We are using JRuby/Tomcat/Rails and are seeing a
lot of timeouts after inactivity in the app.

Thanks
AD

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[markt-2]

> ------- Original Message -------
> From: AD <straightflush@...>
> To: Tomcat Users List <users@...>
> Sent: 01/07/09, 04:37:26
> Subject: DBCP and Firewall timeouts
>
>  Is there a recommended config of ensuring the connection pool
> connections either get cleanly terminated or are sanity checked for
> proper connectivity?  We are using JRuby/Tomcat/Rails and are seeing a
> lot of timeouts after inactivity in the app.

Use a validation query on borrow.

Mark



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

would that simply be the testOnBorrow ?  Is there a perf hit for this ?



On Wed, Jul 1, 2009 at 4:00 AM, Mark Thomas<markt@...> wrote:

>> ------- Original Message -------
>> From: AD <straightflush@...>
>> To: Tomcat Users List <users@...>
>> Sent: 01/07/09, 04:37:26
>> Subject: DBCP and Firewall timeouts
>>
>>  Is there a recommended config of ensuring the connection pool
>> connections either get cleanly terminated or are sanity checked for
>> proper connectivity?  We are using JRuby/Tomcat/Rails and are seeing a
>> lot of timeouts after inactivity in the app.
>
> Use a validation query on borrow.
>
> Mark
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[David Smith-2]

It would and the performance hit is minimal if you use the ping syntax
in your validation query.  I believe it looks like this:

validationQuery="/* ping */ SELECT 1"

-David

AD wrote:

> would that simply be the testOnBorrow ?  Is there a perf hit for this ?
>
>
>
> On Wed, Jul 1, 2009 at 4:00 AM, Mark Thomas<markt@...> wrote:
>  
>>> ------- Original Message -------
>>> From: AD <straightflush@...>
>>> To: Tomcat Users List <users@...>
>>> Sent: 01/07/09, 04:37:26
>>> Subject: DBCP and Firewall timeouts
>>>
>>>  Is there a recommended config of ensuring the connection pool
>>> connections either get cleanly terminated or are sanity checked for
>>> proper connectivity?  We are using JRuby/Tomcat/Rails and are seeing a
>>> lot of timeouts after inactivity in the app.
>>>      
>> Use a validation query on borrow.
>>
>> Mark
>>
>>
>>    

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

David,

On 7/1/2009 10:49 AM, David Smith wrote:
> It would and the performance hit is minimal if you use the ping syntax
> in your validation query.  I believe it looks like this:
>
> validationQuery="/* ping */ SELECT 1"

Note that both the PING is only applicable for MySQL when using
Connector/J, and "SELECT 1" may only be applicable on certain databases
(MySQL being one of them).

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkpLx5kACgkQ9CaO5/Lv0PCzJgCfQfuVYZtUCmJtWoWUPeVwY4hp
Z6IAn2+/5C4waC3RmiqKeJlPT11w1RpD
=w3h/
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[David Smith-2]

Christopher Schultz wrote:

> David,
>
> On 7/1/2009 10:49 AM, David Smith wrote:
> > It would and the performance hit is minimal if you use the ping syntax
> > in your validation query.  I believe it looks like this:
>
> > validationQuery="/* ping */ SELECT 1"
>
> Note that both the PING is only applicable for MySQL when using
> Connector/J, and "SELECT 1" may only be applicable on certain databases
> (MySQL being one of them).
>
> -chris

Right and I double checked the original post that started this thread.
AD described the environment as having MySQL 5.1

--David


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

David,

On 7/1/2009 5:01 PM, David Smith wrote:

> Christopher Schultz wrote:
>> David,
>>
>> On 7/1/2009 10:49 AM, David Smith wrote:
>>> It would and the performance hit is minimal if you use the ping syntax
>>> in your validation query.  I believe it looks like this:
>>> validationQuery="/* ping */ SELECT 1"
>> Note that both the PING is only applicable for MySQL when using
>> Connector/J, and "SELECT 1" may only be applicable on certain databases
>> (MySQL being one of them).
>>
> Right and I double checked the original post that started this thread.
> AD described the environment as having MySQL 5.1

Apologies... the thread was broken somewhere along the way and the
original message wasn't grouped with the others in my mail reader. The
OP definitely said they were using MySQL 5.1.

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkpMuHIACgkQ9CaO5/Lv0PADewCfVnBaTKaGS/bnDm7DZiWkTj8x
7JgAoMOO3vwM7gzLQXz8fWpGutVBclDR
=mPNr
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

unfortunately still not working.  One thing that i noticed, when i
clear my browser cookies, everything works fine again.  One of the
things we do is use cookie persistence to send a user to the same
tomcat server (we need this for file uploads,etc).  Is there anything
in the session cookie that would cause this issue?

Thanks
AD

On Thu, Jul 2, 2009 at 9:38 AM, Christopher
Schultz<chris@...> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> David,
>
> On 7/1/2009 5:01 PM, David Smith wrote:
>> Christopher Schultz wrote:
>>> David,
>>>
>>> On 7/1/2009 10:49 AM, David Smith wrote:
>>>> It would and the performance hit is minimal if you use the ping syntax
>>>> in your validation query.  I believe it looks like this:
>>>> validationQuery="/* ping */ SELECT 1"
>>> Note that both the PING is only applicable for MySQL when using
>>> Connector/J, and "SELECT 1" may only be applicable on certain databases
>>> (MySQL being one of them).
>>>
>> Right and I double checked the original post that started this thread.
>> AD described the environment as having MySQL 5.1
>
> Apologies... the thread was broken somewhere along the way and the
> original message wasn't grouped with the others in my mail reader. The
> OP definitely said they were using MySQL 5.1.
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkpMuHIACgkQ9CaO5/Lv0PADewCfVnBaTKaGS/bnDm7DZiWkTj8x
> 7JgAoMOO3vwM7gzLQXz8fWpGutVBclDR
> =mPNr
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[David Smith-2]

Do you hold on to your connections in code or do you close them when
done with each request?  If you hold on to them, they can still timeout
with or without a validationQuery set.

--David

AD wrote:

> unfortunately still not working.  One thing that i noticed, when i
> clear my browser cookies, everything works fine again.  One of the
> things we do is use cookie persistence to send a user to the same
> tomcat server (we need this for file uploads,etc).  Is there anything
> in the session cookie that would cause this issue?
>
> Thanks
> AD
>
> On Thu, Jul 2, 2009 at 9:38 AM, Christopher
> Schultz<chris@...> wrote:
> David,
>
> On 7/1/2009 5:01 PM, David Smith wrote:
> >>> Christopher Schultz wrote:
> >>>> David,
> >>>>
> >>>> On 7/1/2009 10:49 AM, David Smith wrote:
> >>>>> It would and the performance hit is minimal if you use the ping
> syntax
> >>>>> in your validation query.  I believe it looks like this:
> >>>>> validationQuery="/* ping */ SELECT 1"
> >>>> Note that both the PING is only applicable for MySQL when using
> >>>> Connector/J, and "SELECT 1" may only be applicable on certain
> databases
> >>>> (MySQL being one of them).
> >>>>
> >>> Right and I double checked the original post that started this thread.
> >>> AD described the environment as having MySQL 5.1
> Apologies... the thread was broken somewhere along the way and the
> original message wasn't grouped with the others in my mail reader. The
> OP definitely said they were using MySQL 5.1.
>
> -chris

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

we are using Jruby / Rails / Tomcat.  I think what may be happening is
that rails has its own connection pool in front of DBCP which could be
causing the issue here, not sure yet.

AD

On Tue, Jul 7, 2009 at 12:20 PM, David Smith<dns4@...> wrote:

> Do you hold on to your connections in code or do you close them when
> done with each request?  If you hold on to them, they can still timeout
> with or without a validationQuery set.
>
> --David
>
> AD wrote:
>> unfortunately still not working.  One thing that i noticed, when i
>> clear my browser cookies, everything works fine again.  One of the
>> things we do is use cookie persistence to send a user to the same
>> tomcat server (we need this for file uploads,etc).  Is there anything
>> in the session cookie that would cause this issue?
>>
>> Thanks
>> AD
>>
>> On Thu, Jul 2, 2009 at 9:38 AM, Christopher
>> Schultz<chris@...> wrote:
>> David,
>>
>> On 7/1/2009 5:01 PM, David Smith wrote:
>> >>> Christopher Schultz wrote:
>> >>>> David,
>> >>>>
>> >>>> On 7/1/2009 10:49 AM, David Smith wrote:
>> >>>>> It would and the performance hit is minimal if you use the ping
>> syntax
>> >>>>> in your validation query.  I believe it looks like this:
>> >>>>> validationQuery="/* ping */ SELECT 1"
>> >>>> Note that both the PING is only applicable for MySQL when using
>> >>>> Connector/J, and "SELECT 1" may only be applicable on certain
>> databases
>> >>>> (MySQL being one of them).
>> >>>>
>> >>> Right and I double checked the original post that started this thread.
>> >>> AD described the environment as having MySQL 5.1
>> Apologies... the thread was broken somewhere along the way and the
>> original message wasn't grouped with the others in my mail reader. The
>> OP definitely said they were using MySQL 5.1.
>>
>> -chris
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[mgainty]

i vote against 2 different connection pools
thats 2 configurations placing twice the load routing to the same DB not a good long term situation

a more viable option is to port your RAILS code to GRAILS and all others Struts/JSF/Axis to use the same DBCP which would be configured within server.xml GlobalNamingResources

the other option is to enforce all connections to be acquired thru some XA Transaction Management..XA would funnel all requests to a common connection pool via a TransactionManager..this is a big deal to configure as it can take connection requests from multiple servers..as this is oracle specific.. feel free to ping me offline for details
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adga1_xa.htm

you will also want to up the number of connections from 8 allowed by DBCP to accomodate
as seen here
http://people.apache.org/~dirkv/dbcp/configuration.html

as previously mentioned you want to place your config into GlobalNamingResources
http://tomcat.apache.org/tomcat-5.5-doc/config/globalresources.html
so all applications would be using the same DBCP configuration and same connection pool

Martin Gainty
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 Ez az
üzenet bizalmas.  Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett.  Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs.  Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Tue, 7 Jul 2009 12:31:53 -0400
> Subject: Re: DBCP and Firewall timeouts
> From: straightflush@...
> To: users@...
>
> we are using Jruby / Rails / Tomcat.  I think what may be happening is
> that rails has its own connection pool in front of DBCP which could be
> causing the issue here, not sure yet.
>
> AD
>
> On Tue, Jul 7, 2009 at 12:20 PM, David Smith<dns4@...> wrote:
> > Do you hold on to your connections in code or do you close them when
> > done with each request?  If you hold on to them, they can still timeout
> > with or without a validationQuery set.
> >
> > --David
> >
> > AD wrote:
> >> unfortunately still not working.  One thing that i noticed, when i
> >> clear my browser cookies, everything works fine again.  One of the
> >> things we do is use cookie persistence to send a user to the same
> >> tomcat server (we need this for file uploads,etc).  Is there anything
> >> in the session cookie that would cause this issue?
> >>
> >> Thanks
> >> AD
> >>
> >> On Thu, Jul 2, 2009 at 9:38 AM, Christopher
> >> Schultz<chris@...> wrote:
> >> David,
> >>
> >> On 7/1/2009 5:01 PM, David Smith wrote:
> >> >>> Christopher Schultz wrote:
> >> >>>> David,
> >> >>>>
> >> >>>> On 7/1/2009 10:49 AM, David Smith wrote:
> >> >>>>> It would and the performance hit is minimal if you use the ping
> >> syntax
> >> >>>>> in your validation query.  I believe it looks like this:
> >> >>>>> validationQuery="/* ping */ SELECT 1"
> >> >>>> Note that both the PING is only applicable for MySQL when using
> >> >>>> Connector/J, and "SELECT 1" may only be applicable on certain
> >> databases
> >> >>>> (MySQL being one of them).
> >> >>>>
> >> >>> Right and I double checked the original post that started this thread.
> >> >>> AD described the environment as having MySQL 5.1
> >> Apologies... the thread was broken somewhere along the way and the
> >> original message wasn't grouped with the others in my mail reader. The
> >> OP definitely said they were using MySQL 5.1.
> >>
> >> -chris
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: users-unsubscribe@...
> > For additional commands, e-mail: users-help@...
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>

_________________________________________________________________
Hotmail® has ever-growing storage! Don’t worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

AD,

On 7/7/2009 11:47 AM, AD wrote:
> unfortunately still not working.  One thing that i noticed, when i
> clear my browser cookies, everything works fine again.  One of the
> things we do is use cookie persistence to send a user to the same
> tomcat server (we need this for file uploads,etc).

Can your firewall use /part/ of a cookie (or URL) to map the request to
a server? If so, you can set the "route" of your <Connector> to annotate
your session id with a unique moniker for the physical server: no need
to do your own cookie management.

> Is there anything
> in the session cookie that would cause this issue?

Doubtful. Are you doing anything strange like sticking DB connections
into sessions or (intentionally) keeping connections open across requests?

If you enable the debugging options on your <Resource>, do you see any
error messages in your stdout log?

I see from a later post that you are using Rails which has its own
connection pool. You should look into the Rails DBCP setup and see if it
has similar debugging options.

Also, read my recent blog post on proper JDBC programming:
http://blog.christopherschultz.net/?p=68

Hope that helps,
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkpX51wACgkQ9CaO5/Lv0PDcdACfcIlEbaG/KVpe1dFAII3pbnxS
HoUAn0M0hEQsaJRLmYIeDX0/N3Ov6gjr
=/Q79
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...