TomcatExpert

Using the Tomcat 7 JDBC Connection Pool in Production

posted by dmikusa on January 23, 2012 03:14 PM

One of the new features with Tomcat 7 is a replacement to the commons-dbcp connection pool. While the commons-dbcp connection pool works fine for small or low traffic applications, it is known to have problems in highly concurrent environments (think multi-core/multi-cpu).

Fortunately, this is where the JDBC Connection Pool excels. It is a completely new connection pool which has been written from the ground up, with a focus on highly concurrent environments and performance.

Given its focus on high concurrency and performance, many users are finding that the JDBC Connection Pool can be great for use in a production environment. This article will discuss the features and options which make using the JDBC Connection Pool a great choice.

The Basics

Getting started with the JDBC Connection Pool is very simple. It was purposefully designed as a drop-in replacement for commons-dbcp and as such, if you've ever used commons-dbcp you'll be immediately familiar with the configuration for the JDBC Connection Pool.

This means the most existing commons-dbcp users can switch to the JDBC Connection Pool by simply adding the following property to their configuration factory=”org.apache.tomcat.jdbc.pool.DataSourceFactory”.

What about the other commons-dbcp configuration options? You don't need to change them at all. Virtually all of the commons-dbcp configuration options are supported by the JDBC Connection Pool. Including but not limited to: testWhileIdle, testOnBorrow, testOnReturn, maxActive, minIdle, maxWait, initialSize, removeAbandoned, removeAbandonedTimeout and logAbandoned.

Connection Validation

How many users have seen this scenario before? You deploy a new application to production and it runs great all day, however the next morning you find that the application is dead. Upon examining the logs, you find that they are full of connection errors. What happened?

A common scenario at many companies is for a database to be briefly shutdown at night for maintenance (or backup). Unfortunately, when this occurs all of the connections in your application's connection pool are lost. Furthermore, because the connection was broken by the server, the connection pool does not know that the connection has been lost. As a result, the connection pool still thinks that the connection is valid, and will continue to distribute the connection to your application. Not until your application actually attempts to use the connection will it find out that the connection is bad.

Fortunately, there is an easy way to resolve this issue. You need the connection pool test each connection before it lends the connection to your application. With the JDBC Connection Pool, just like with commons-dbcp, this feature can be enabled by setting testOnBorrow to true and validationQuery to a simple test query like SELECT 1 or SELECT 1 FROM DUAL. Alternatively, the validatorClassName property can be used to indicate the full name of the class that will be used to validate connections. The class specified by validatorClassName will need to implement org.apache.tomcat.jdbc.pool.Validator, and can be used to implement any custom validation logic.

Unlike commons-dbcp, the JDBC Connection Pool provides an additional parameter called validationInterval. This parameter allows you to control how frequently a given validation query is executed. By default it is set to 30 seconds; therefore, the given validation query will only run once per connection every 30 seconds.

To illustrate the power of this feature, let's take a look at an example. You have a connection pool configured to validate connections and that pool hands out 10 connections per second to your application. Given this, the pool will execute 10 validation queries per second for a total of 300 queries over a 30 second period. However, if the validation interval is enabled and set to 30 seconds, that means only 10 queries will be run for the same 30 second time period, offering a reduction of 290 queries. Despite the fact that a typical validation query executes quickly, it is still easy to see how this can be a big savings for your application.

When deciding the value to use for the validationInterval property, the following rule can be helpful. Larger values offer better performance, while smaller values decrease the chance of a stale connection being presented to your application.

For example, a value of 60 seconds means that a validation query only needs to run once every 60 seconds for each connection. However, that also means that once a connection is validated it will not be revalidated for another 60 seconds. If a database connection were to be lost one second after it was validated then there would be a period of 59 seconds where the connection would still be considered valid by the connection pool. If a connection were to be borrowed while in this state, use of the connection would fail and the application would get a connection error.

As you can see from the example, picking a value for the validationInterval property is a trade-off and ultimately depends on what is acceptable for your application.

JDBC Interceptors

Another feature specific to the JDBC Connection Pool is the concept of a JDBC Interceptor. From the documentation, “An interceptor is a powerful way to enable, disable or modify functionality on a specific connection or its sub components”. At first glance this definition may seem a bit vague, but what this essentially means is that functionality can be added or removed from the connection pool with only a simple configuration adjustment. Even better, the code does not have to live inside the JDBC Connection Pool JAR. You can create and add custom JDBC Interceptors in the same manner as you would add an interceptor that is bundled with the connection pool.

Enabling an interceptor is easy; add the jdbcInterceptors property to your connection pool configuration and specify a semi-colon separated list containing the names of the interceptors to be used. For example, jdbcInterceptors=”ConnectionState;StatementFinalizer”.

If the interceptor that you are adding resides in the package org.apache.tomcat.jdbc.pool.interceptor specify the short name, as in the previous example. However, if you are adding an interceptor that resides in a difference package, like a custom interceptor, you would just reference the full class name of your interceptor. For example, com.mycompany.interceptors.MyCustomInterceptor.

With that in mind, let's take a look at some of the interceptors that come with the JDBC Connection Pool.

ConnectionState Interceptor

The ConnectionState interceptor is a simple interceptor which caches the properties autoCommit, readOnly, transationIsolation and catalog.

Normally when one of these properties is accessed, the connection must talk to the server and retrieve the requested value. Since this action results in communication over the network, access to these properties can be slow.

The benefit of the ConnectionState interceptor is that the values will be cached locally, allowing faster access and limiting network chatter.

StatementFinalizer Interceptor

The StatementFinalizer interceptor watches for any statement that is created by a connection with the createStatement, prepareStatement or prepareCall methods. It monitors the statements; when the connection is eventually returned to the pool, the interceptor ensures that any statements that were created are properly closed.

This interceptor comes in handy when dealing with legacy or third party applications where statement handling is problematic, but it is not possible for you to fix the code itself.

SlowQueryReport and SlowQueryReportJmx Interceptors

The SlowQueryReport and SlowQueryReportJmx interceptors monitor the amount of time that a query takes to execute, and flag any query that exceed a predefined threshold. The SlowQueryReport interceptor will log any slow queries at the WARN level, while the SlowQueryReportJmx interceptor will produce a JMX notification in addition to the WARN log statement.

I hope that the benefits to these interceptors are obvious. If you have an application in which performance is slow, you can enable this interceptor and automatically get profiling information about the SQL queries being executed by your application.

ResetAbandonedTimer Interceptor

The ResetAbandonedTimer interceptor works in conjunction with the removeAbandoned property. When the removeAbandoned property is enabled, the connection pool will attempt to reclaim connections which have been checked out of the pool for a long period of time. The rationale here is that connections checked out for an extended period of time are typically connections which have not been properly closed by a deployed application. The removeAbandoned setting will thus allow you to reclaim connections from badly behaving applications.

Most of the time this works just fine, however the abandoned timer can be tricked by a long running process. Let's say that you have a batch process that takes 20 minutes to execute. What happens when it is still running, but the removeAbandoned timeout elapses? Unfortunately, the connection will still be reclaimed by the pool.

This is the benefit of the ResetAbandonedTimer interceptor. With it enabled, the abandoned timeout will be reset every time an SQL operation is executed on the connection. Thus enabling the removeAbandoned process to be more accurate, particularly for legitimate long running processes.

Summary

The JDBC Connection Pool is a fantastic addition to Tomcat 7. Not only does it provide an alternative connection pool, but one with superior performance and features. I would encourage any current users of commons-dbcp to give it a try and see how it can benefit their applications.

More information on the JDBC Connection Pool can be obtained from the Tomcat 7 documentation at the following link.

https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

Daniel Mikusa is a Software Support Engineer at Pivotal.  Daniel has been a user of Apache Tomcat for the past six years and is currently focused on providing enterprise-class support for Pivotal's Tomcat and tc Server customers. Daniel attended college at Kent State University and holds a bachelor's degree in Computer Science.

Comments

distribute trasactions

Thanks for write up.

I have a basic question on tomcat.

can tomcat 7 supports distribute transactions or we have to use dbcp connection pool mechansim for this

Learn java,tomcat,hadoop,linux with examples

Out-of-the-box Tomcat does

Out-of-the-box Tomcat does not have support for distributed transactions. If you require distributed transactions for your application then you'll need to use a third-party transaction manager like Bitronix or Atomikos.

Comparison to C3P0

How does Tomcat 7 JDBC Connection Pool compare with C3P0?

Regards,
Franklin

This article is a bit old,

This article is a bit old, but provides some comparison data. Additionally, the source code for the tests is available, so you can download and run the tests for yourself.

http://www.tomcatexpert.com/blog/2010/03/22/understanding-jdbc-pool-performance-improvements

A common situation at many

A common situation at many companies is for a data source to be temporarily shut down at night for servicing (or backup). Unfortunately link, when this happens all of the relationships in your application's relationship share are missing. Furthermore, because the text was damaged by the server,

adfas

Thanks for showing this awesome post APP download and click and click and click and click and click

How CP recovers from a database failover?

I found your blog while Googling for a problem I'm facing. This is very informative.

And I tell you the problem I'm facing. Please shed some light if you know what could possibly go wrong here. I have tomcat connection pool and AWS RDS MySQL configured with my webapp. I use RDS DNS host in my webapp. In case RDS is restarted with failover, it gets a new IP. But my app can't connect to RDS anymore. I hope it's because stale connections in pool. But I have enabled <testOnBorrow>. DO you have any idea why I'm getting this? Thanks.

Out-of-the-container Tomcat

Out-of-the-container Tomcat doesn't have assistance for transactions listen to this podcast. If you need purchases that are distributed for the software you then'll have to make use of a third party exchange manager like Bitronix.

And I tell you the problem

And I tell you the problem I'm suffering from. Please decrease some light if you know what could probably don't succeed here. I have tomcat connection discuss and AWS RDS MySQL created with my webapp. I use RDS DNS wide range in my webapp. In scenario RDS is re-booted with failover read review, it gets a new IP. But my app can't connect to RDS any more time. Hopefully it's because boring connections in discuss. But I have permitted . DO you have any idea why I'm getting this

And I tell you the issue I'm

And I tell you the issue I'm affected by top article. Please reduce some mild if you know what could probably crash here. I have tomcat relationship talk about and AWS RDS MySQL designed with my webapp. I use RDS DNS extensive variety in my webapp.

And I tell you the problem

And I tell you the problem I'm suffering from. Please decrease some light if you know what could probably don't succeed here. I have tomcat connection discuss and AWS RDS MySQL created with my webapp. I use RDS DNS wide range in my webapp. In scenario RDS is re-booted with failover published here, it gets a new IP. But my app can't connect to RDS any more time. Hopefully it's because boring connections in discuss. But I have permitted . DO you have any idea why I'm getting this?

And I tell you the problem

And I tell you the problem I'm suffering from the lost ways review. Please decrease some light if you know what could probably don't succeed here. I have tomcat connection discuss and AWS RDS MySQL created with my webapp. I use RDS DNS wide range in my webapp.

News Greek

It is very good, but look at the information at this address.
news today

This page certainly has all

This page certainly has all of the information I wanted about this subject and didn’t know who to ask.
Mortgage Broker Calgary

obat herbal alternatif

obat kanker payudara Kanker payudara adalah penyakit yang terdapat pada bagian payudara.
Sembuhkan Penyakit Stroke menggunakan obat stroke.
obat kanker serviksyakni pilihan yang paling tepat dalam mengobati sakit kanker serviks hingga sembuh.
Obat Kista Penyakit Kista memiliki bentuk yang bermacam-macam.

Besides the designs, oakley

Besides the designs, oakley Cheap Sunglasses Online accept been able-bodied accepted for their angry quality. The frames fabricated of carbon cilia or titanium accomplish them able and flexible. The cast aswell action designs with polarized lenses which assure your eyes from the blaze of the sun. So if you go out to acquirement sunglasses for yourself, attending no added than Ray Ban to be abiding of what you are acrimonious for yourself.There are abounding humans who are addicted of cutting sunglasses, spectacles and acquaintance lenses. They like to abrasion all these because cutting these things are actual acclaimed and acceptable for them. The deals 4 optical is the latest cast which provides actual admirable and anticipated all kinds of eye- wear.The affiliation of the Ray Ban cast with top performers is a part of the affidavit for the brand's recognition. Of advance top chic athletes accept Ray Ban due to the backbone of the articles as able-bodied as their assurance record. The Ray Ban sunglasses Outlet sunglasses are created to abide the appulse of abundant altar and can abide complete even afterwards the fall.

http://www.rfshoeoutlets.co.uk

I think this is one of the

I think this is one of the best options using the Tomcat 7 JDBC connection pool in production. It will be very much helpful for the production process. I am trying to explore more new details regarding this. smoke cheese

GAME HACK

pou descargar gratis On this subject internet page, you'll see my best information, be sure to look over this level of detail. clash of clans gemmes hack

Nice post. Can you please

Nice post. Can you please tell me that where we can see the logs for the query given in the 'validationQuery'?

The frames fabricated of

The frames fabricated of carbon cilia or titanium accomplish them able and flexible. The cast aswell GTA 5 Free Download action designs with polarized lenses which assure your eyes from the blaze of the sun. So if you go out to acquirement sunglasses for yourself, attending no added than Ray Ban to be abiding Rayman Adventures Hack of what you are acrimonious for yourself.There are abounding humans who are addicted of cutting sunglasses, spectacles and acquaintance lenses. They like to abrasion all these because cutting these things are actual acclaimed and acceptable for them. The deals 4 optical is the latest cast which provides actual admirable and anticipated all kinds of eye- wear.The affiliation of the Ray Ban Mobile Strike Hack cast with top performers is a part of the affidavit for the brand's recognition.

Hey there! I just want to

Hey there! I just want to offer you a huge thumbs up for the great information you have got right here on this post. I am returning to your website for more soon.
Edmonton Mortgage Broker
Life Insurance Vancouver
Life Insurance Calgary

This sort of is an excellent

This sort of is an excellent publish Our companion and i observed of examine that consequently click here to investigate. It's precisely what I wished to discover wish inside forthcoming on regarding exposing this sort of exceptional publish you might keep.

Jual Laptop Notebook PC HP Lenovo Printer Tinta Toner Epson Mura

You should keep on this kind of fantastic perform and also My partner and i enjoy a lot more of one's great web sites. jual PC Murah

OC Housing News

Hmm… I interpret blogs on a analogous issue, however i never visited your blog. I added it to populars also i’ll be your faithful primer.
Housing Market News

Watch Latest Pakistani Talk Shows and Dramas Online HD at ViiTV

Excellent read, Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work.
Watch Latest pakistani talk shows

Wimpernverlängerung Berlin, Wimpernverdichtung, Sugaring Haarent

Cool you write, the information is very good and interesting, I'll give you a link to my site.
sugaring

TRUTH About PhenQ: Reviews, Ingredients, Side Effects

It is fine, nonetheless evaluate the information and facts around this correct.
Best Diet Pills In 2016 That Work Fast

Artemisinin Cure of Cancer - Artemisinin

I love the blog. Great post. It is very true, people must learn how to learn before they can learn. lol, i know it sounds funny but its very true. . .
artemisinin

Amazing app

i must say you don a great job i like your effort.
Snaptube Apk Android

dsdsds

happy navratri images
dandiya songs
navratri wishes 2016

nice

I would like to say that I am researcher manifestation miracle destiny tuning and I am doing research on heavy vehicle as well as I am offering program pdf http://p-tweets.com/srivija download manifestation miracle by heahter Matthews

good job

I found this post very helpful 7 Steps to Health diabetes Thanks for Sharing! The Big Diabetes Lie

http://www.fabrikasikonstruksi.com/

toko besi beton toko besi beton
toko besi beton ais toko besi beton ais
toko besi beton Sni Ulir Polos toko besi beton Sni Ulir Polos
toko besi beton bjku toko besi beton bjku
toko besi beton cakra steel cs toko besi beton cakra steel cs
toko besi beton delcoprima toko besi beton delcoprima
toko besi beton gunung garuda toko besi beton gunung garuda
toko besi beton interworld steel is toko besi beton interworld steel is
toko besi beton jcac toko besi beton jcac
toko besi beton krakatau steel toko besi beton krakatau steel
toko besi beton ksty toko besi beton ksty
toko besi beton master steel ms toko besi beton master steel ms
toko besi beton PAS toko besi beton PAS
toko besi beton perwira toko besi beton perwira
toko besi beton psi Ulir Polos toko besi beton psi Ulir Polos
toko besi beton sii toko besi beton sii
toko besi beton Sni Ulir Polos polos ulir toko besi beton Sni Ulir Polos polos ulir
www.tokobesibajamurah.com www.tokobesibajamurah.com
www.pusatbesibaja.com www.pusatbesibaja.com
www.pusatbesibaja.co.id www.pusatbesibaja.co.id
jasa konstruksi baja jasa konstruksi baja
undangan pernikahan undangan pernikahan
jasa konstruksi jual laptop asus
jasa konstruksi baja jasa konstruksi baja wf
jasa konstruksi gudang jasa konstruksi jembatan
jasa konstruksi gedung jasa konstruksi bangunan
www.husnialbardani.com www.husnialbardani.com
jual laptop asus murah jual laptop asus murah

www.sentrabesibaja.com Daftar toko Besi Hollow Dari toko toko
toko Besi Beton Daftar toko Besi Pipa Baja Schedule 40 Dari toko toko
toko Besi H beam Daftar toko Besi H beam Yang Ditoko Dari toko Ke Agen
toko Besi Wf Info toko Welded Beam T beam King Queen Cross Castellated Beam
toko Pipa Besi Hitam Medium Daftar toko Bondek Cor Ter Murah toko toko
toko Besi Pipa Baja Schedule 40 sentra besi baja
toko Besi Pipa Baja Schedule 80 Daftar toko Besi Unp Baja Profil Kanal toko toko
toko Wiremesh Daftar toko Besi Pipa Baja Schedule 80 Dari toko toko
toko Besi Hollow Daftar toko Plat Kapal Dari toko Ke toko
toko Besi Cnp Profil Baja Gording Daftar toko Atap Spandek Sni Dari Supplier toko
toko Besi Unp Baja Profil Kanal Daftar toko Plat Besi Hitam Dari Supplier toko
toko toko Besi Siku Baja Daftar toko toko Besi Siku Baja Dari Supplier toko
toko Plat Kapal Bki Krakatau Steel Daftar toko Pipa Besi Hitam Medium Dari Agen toko
toko Plat Besi Hitam Daftar toko Wiremesh Yang Ditoko Dari toko Ke Supplier
toko Plat Bordes Kembang Daftar toko Besi Wf Ditoko Dari toko Lewat Supplier
toko Atap Spandek Sni Daftar toko Plat Bordes Kembang Dari toko toko
toko Bondek Cor Daftar toko Besi Beton Yang Di toko Dari toko Ke toko
toko Welded Beam T beam King Queen Cross Castellated Beam Daftar toko Besi Cnp Profil Baja Gording Supplier toko
jasa konstruksi jual laptop asus
jasa konstruksi baja jasa konstruksi baja wf
jasa konstruksi gudang jasa konstruksi jembatan
jasa konstruksi gedung jasa konstruksi bangunan
www.husnialbardani.com www.husnialbardani.com
www.mrosidin.com www.cerahcollection.com
jual laptop asus murah jual laptop asus murah

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.