"I think without a validationQuery, your pool will effectively dry-up over time."
I'm expecting some very high traffic, so it's unlikely if not impossible a connection will timeout on the mysql side with the default settings. All connections are safely returned to the pool (famous last words, I know), so I'm holding off to see if I do see the pool shrinking over time, and if I'm getting "already closed" exceptions. If that's the case the validation query and validationInterval are certainly not a great expense. It's good advice, but I'm just curious if they are absolutely necessary Cheers, Alec On Wed, Dec 4, 2013 at 1:01 PM, Christopher Schultz < ch...@christopherschultz.net> wrote: > -----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.ran...@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-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > >