Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (athena.apache.org: local policy includes SPF record at
 spf.trusted-forwarder.org)
Message-ID: <529F6DFA.6010906@christopherschultz.net>
Date: Wed, 04 Dec 2013 13:01:30 -0500
From: Christopher Schultz <chris@christopherschultz.net>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.1
MIME-Version: 1.0
To: Tomcat Users List <users@tomcat.apache.org>
Subject: Re: Pooled Connections Lost After 10 Minutes (600 seconds)
References: 
 <CAPhkb93FhAEBJE-C245hs=o-ZagoL5f9KdGwnFc8jHK-8X_CLw@mail.gmail.com>
 <CAPhkb904_1b+NzXHPHi-o1hWFAPwyU65jP8rEMhVzvtcw2UtPQ@mail.gmail.com>
 <CAHjMPQx61t06gavSW=c0OY3qJZiEAJN97jjEzUY_GOwYYdQ3zg@mail.gmail.com>
 <52991E6A.9020605@christopherschultz.net>
 <CAPhkb93deg+gbZsGNPvDsHZFkrTvzszHEVgfa-fTQnrT3TNaKQ@mail.gmail.com>
 <CAHjMPQyUpLaLEdWQjOO7pwufhdR6A-TPNYKXjhfCr7v2_fBQcg@mail.gmail.com>
 <CADf97R0ZoNsiWeEbKsodFs_8ynrrT4GkA02KEYUDYfE12W2wEw@mail.gmail.com>
 <CAPhkb92NgLMxzNSXAAjEu2b8MG-S9BBisJemniobVfO_CeOxYg@mail.gmail.com>
 <0EF44945-C4CD-46DF-B729-DADD9092F404@gopivotal.com>
In-Reply-To: <0EF44945-C4CD-46DF-B729-DADD9092F404@gopivotal.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Dan,

On 12/3/13, 12:32 PM, Daniel Mikusa wrote:
> On Dec 3, 2013, at 12:14 PM, Tomcat Random
> <tomcat.random@gmail.com> wrote:
> 
>> I considered using a validation query but it seemed like extra
>> overhead when the default behavior was not, um, behaving in the
>> default way.
> 
> The overhead is typically minimal.  Running "SELECT 1" or some
> other very simply query is not likely to bring your database to
> it's knees.  It might add a small amount of latency as the pool
> will need to execute the query before it give the connection to
> your application, but that's likely to be dwarfed by whatever your
> application does with the connection after it gets it.
> 
> If you are concerned you can do a couple things to make the process
> even more lightweight.
> 
> 1.) With MySQL and use "/* ping */ SELECT 1" as the validation
> query.  This is a special case with the MySQL JDBC driver that uses
> even less resources.

+1

We use this everywhere. I've never actually benchmarked it, but since
it does not execute a query on the server, it pretty much has to be
faster by any measure.

> 2.) You can use the tomcat-jdbc connection pool which has a
> validationInterval setting.  This will ensure that the validation
> query is only executed one time during the specified time interval.
> 
I haven't moved to tomcat-pool yet, but this was my initial reaction
to Alec's question about usually not needing the validation query.

> ...or you can go without a validation query, but it's not something
> I would recommend and not something I see done very often.  The
> minimal overhead is usually worth knowing that you get a valid
> connection from the pool.

+1

If you don't use a validation query, you need additional try/catch
blocks around all your "getConnection()" calls, and a loop to re-try
just in case the first connection was bad.

I think without a validationQuery, your pool will effectively dry-up
over time.

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.15 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJSn236AAoJEBzwKT+lPKRY85sQAMTss/RPDRep+yUhOxCCdCOF
8d2ZhzeDwZ/lM0d+XlH9ZJzbBdwQEpRyZf/uzQRQLK/WO/vReXN3RMptSrN+VoLE
TaqWcGva1NPvUOwrMKd1Hm8zKqtcamWTQIHa1MzQ/cN9RChmZbfNwc7CteuTd36C
zvXoj1nRhkixfE4jX2/REDtAh0QKQkCj/Dq1BpOlEzJaGwmL/fbwJreUvPiGNvaO
xQaCr92Z/Srv32oLCBCu3fs8/FN2KvLKB2YKFWB2iHrRvxiIC7tUWUp9OkZvJga2
ARs7UraFNl/Z+vi8xV2S1cavJD+jeW4ddB5QDr+1yxWRPt0QEE0UzaolATVTUak9
9cRb+87xICd5z/XoFABSPqeicyS/1/cxg/JOHrFtcM8EmXZifB4aVLpnjIqHrRC/
Y3LoCjaIBO78/0i75kC0zeS2opTXMlrvEy/0W/QA8XwGmy7yEvZhbER+TVeHsAcD
6evobH5bbSbnxXgB4o826/ihxMq3JxfZWuaGvCmgB4D4aI0SbtdNP+/SHwirGZA1
Lt/iRCFAGFtJTMygVQZzBn+gbO3mMx7vxesIFbmKRDcUWpQ7MVrs5Wq9mfLUFJp8
YnQS66MM10Nf3O0qNavBN5af4Cq1E+GvcuydchpbDHwd7czkidzLQcGbyIiBpnTq
HR5uraaRm89AZ5zUKShW
=9ync
-----END PGP SIGNATURE-----

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