Alec, Dan, and Chris, 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. > +1 interesting topic and responses. Thanks! Since I'm using TomEE, tomcat jdbc pool is default, and below is the config. <Resource id="jdbc/dbJta" type="javax.sql.DataSource"> JdbcDriver org.apache.derby.jdbc.EmbeddedDriver JdbcUrl jdbc:derby:X:/myPathToMyDB;create=true UserName .... Password .... JtaManaged true jmxEnabled true InitialSize 10 MaxActive 30 MaxIdle 20 MaxWait 10000 minIdle 10 suspectTimeout 60 removeAbandoned true removeAbandonedTimeout 180 timeBetweenEvictionRunsMillis 30000 jdbcInterceptors=StatementCache(max=1024) </Resource> As you can see, I am one of those rare cases that Dan mentioned...not using validationQuery. Not so much intentional, but I'm still somewhat novice as tomcat user. With that said, I have not had the need to add try/catch to ensure I get a good connection from the pool. I don't have high traffic coming to my web app, but there are times when multiple users are using the app, and I see absolutely 'no' connection issues (ever), and performance is quite good/sound as well. So, I do hear the recommendations, in this thread, about validation query, but my app has not told me yet...that it needs the validation query. :)
