You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomee.apache.org by Witold Szczerba <pl...@gmail.com> on 2013/02/28 00:37:15 UTC
JDBC Connection Pool reconnects on each connection usage
Hi,
I am facing something very strange. I am not 100% sure if this is true, but
everything looks like the connection pool actually reconnects to database,
I mean it closes the TCP connection and creates new one, over and over,
instead of reusing connections.
To make sure it is not an issue with my application, I have created a new
project with only one class:
/**
*
* @author witoldsz
*/
@Path("/resource")
@Stateless
public class Endpoint {
@Resource(name = "jdbc/resource")
private DataSource ds;
@GET
@Produces(MediaType.TEXT_PLAIN)
public String hello() throws Exception {
try (Connection conn = ds.getConnection()) {
//do nothing, just fetch and close the connection
}
return "Hello!";
}
}
The JDBC resource is actually an Oracle 11g.
Now hear this: each time I do a HTTP GET:
http://localhost:8080/app/resource
I can see a new connection being established and the previous one closed,
used iptraf to monitor.
Also, the:
--
SELECT username, sid, serial#, logon_time, machine, port
FROM v$session
WHERE username = 'MY_USER_NAME'
ORDER BY logon_time;
--
result is showing new value in "serial#" column after each HTTP GET.
What is worth noticing, the already used connection does not get destroyed
immediately after use. It waits in the pool and is destroyed when new one
is created. And the new one waits there until yet another one is created
and it goes like this over and over.
I am sure this is the reason my application works really, I mean really
slow, when I am developing from home with remote, over VPN, database.
My environment:
tomee.xml:
<Resource id="jdbc/resource" type="DataSource">
JdbcDriver oracle.jdbc.driver.OracleDriver
JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
UserName test
Password test
JtaManaged true
</Resource>
OS:
Ubuntu 12.10 64 bit,
Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
TomEE 1.5.1 JAX-RS
tested also using:
TomEE 1.6.0-20130226.041154-13-jaxrs
I am attaching source code of the webapp (3.6kB), just configure a
"jdbc/resource" datasource in tomee.xml and deploy it to run it on your own.
Thanks,
Witold Szczerba
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Witold Szczerba <pl...@gmail.com>.
Thanks for the hint with another connection pool. I wanted to check it out
some time ago and forgot about that option.
Will try with my application to see the difference with end-to-end test
runner to compare total execution time.
Regards,
Witold Szczerba
---
Sent from my mobile phone.
On Feb 28, 2013 2:39 AM, "John D. Ament" <jo...@gmail.com> wrote:
> When I run your example on my local machine, using Tomee 1.5.1 w/ jax-rs
> and mysql, I get the same results. However, switching to dbcp pooling
> changes that.
>
> <Resource id="jdbc/resource" type="DataSource">
> JdbcDriver com.mysql.jdbc.Driver
> JdbcUrl jdbc:mysql://localhost:3306/ex
> UserName ex
> Password abc123
> JtaManaged true
> DataSourceCreator dbcp
> </Resource>
>
> And sorry, missing the brackets around your try.
>
> John
>
>
> On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <pljosh.mail@gmail.com
> >wrote:
>
> > Well, all the properties you have mentioned have sane defaults, that's
> why
> > I did not specified them.
> > Also, the try (...) statement (called try-with-resources), introduced in
> > Java7, is calling #close method, so the connection is handled correctly.
> >
> > Regards,
> > Witold Szczerba
> > ---
> > Sent from my mobile phone.
> > On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com> wrote:
> >
> > > It seems like you don't have initialSize, maxIdle, maxActive, minIdle
> or
> > > maxWait set.
> > >
> > > Also, I don't think you're returning the connection to the pool.
> > >
> > >
> > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> pljosh.mail@gmail.com
> > > >wrote:
> > >
> > > > Hi,
> > > > I am facing something very strange. I am not 100% sure if this is
> true,
> > > > but everything looks like the connection pool actually reconnects to
> > > > database, I mean it closes the TCP connection and creates new one,
> over
> > > and
> > > > over, instead of reusing connections.
> > > >
> > > > To make sure it is not an issue with my application, I have created a
> > new
> > > > project with only one class:
> > > >
> > > > /**
> > > > *
> > > > * @author witoldsz
> > > > */
> > > > @Path("/resource")
> > > > @Stateless
> > > > public class Endpoint {
> > > >
> > > > @Resource(name = "jdbc/resource")
> > > > private DataSource ds;
> > > >
> > > > @GET
> > > > @Produces(MediaType.TEXT_PLAIN)
> > > > public String hello() throws Exception {
> > > > try (Connection conn = ds.getConnection()) {
> > > > //do nothing, just fetch and close the connection
> > > > }
> > > > return "Hello!";
> > > > }
> > > > }
> > > >
> > > > The JDBC resource is actually an Oracle 11g.
> > > >
> > > > Now hear this: each time I do a HTTP GET:
> > > > http://localhost:8080/app/resource
> > > > I can see a new connection being established and the previous one
> > closed,
> > > > used iptraf to monitor.
> > > >
> > > > Also, the:
> > > > --
> > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > FROM v$session
> > > > WHERE username = 'MY_USER_NAME'
> > > > ORDER BY logon_time;
> > > > --
> > > > result is showing new value in "serial#" column after each HTTP GET.
> > > >
> > > > What is worth noticing, the already used connection does not get
> > > destroyed
> > > > immediately after use. It waits in the pool and is destroyed when new
> > one
> > > > is created. And the new one waits there until yet another one is
> > created
> > > > and it goes like this over and over.
> > > >
> > > > I am sure this is the reason my application works really, I mean
> really
> > > > slow, when I am developing from home with remote, over VPN, database.
> > > >
> > > > My environment:
> > > > tomee.xml:
> > > >
> > > > <Resource id="jdbc/resource" type="DataSource">
> > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > UserName test
> > > > Password test
> > > > JtaManaged true
> > > > </Resource>
> > > >
> > > > OS:
> > > > Ubuntu 12.10 64 bit,
> > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > TomEE 1.5.1 JAX-RS
> > > > tested also using:
> > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > >
> > > >
> > > > I am attaching source code of the webapp (3.6kB), just configure a
> > > > "jdbc/resource" datasource in tomee.xml and deploy it to run it on
> your
> > > own.
> > > >
> > > > Thanks,
> > > > Witold Szczerba
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Romain Manni-Bucau <rm...@gmail.com>.
Tomee is tomcat and tomcat jdbc doc explains it very well
Le 28 févr. 2013 12:11, "Witold Szczerba" <pl...@gmail.com> a écrit :
> One thing I can say just now is that setting DataSourceCreator to DBCP
> makes a HUGE difference in performance. That connection pool works as a one
> would expect any connection pool to work.
> Have not tried the extra interceptors on top of Tomcat Connection Pool, but
> as long as I can keep far away from it, I am fine.
>
> The only thing I would suggest for TomEE project for now would be to
> supplement the documentation, so every one, picking TomEE as their app
> server of choice, would be warned about the issue.
>
> Thanks,
> Witold Szczerba
>
>
> On 28 February 2013 02:39, John D. Ament <jo...@gmail.com> wrote:
>
> > When I run your example on my local machine, using Tomee 1.5.1 w/ jax-rs
> > and mysql, I get the same results. However, switching to dbcp pooling
> > changes that.
> >
> > <Resource id="jdbc/resource" type="DataSource">
> > JdbcDriver com.mysql.jdbc.Driver
> > JdbcUrl jdbc:mysql://localhost:3306/ex
> > UserName ex
> > Password abc123
> > JtaManaged true
> > DataSourceCreator dbcp
> > </Resource>
> >
> > And sorry, missing the brackets around your try.
> >
> > John
> >
> >
> > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <pljosh.mail@gmail.com
> > >wrote:
> >
> > > Well, all the properties you have mentioned have sane defaults, that's
> > why
> > > I did not specified them.
> > > Also, the try (...) statement (called try-with-resources), introduced
> in
> > > Java7, is calling #close method, so the connection is handled
> correctly.
> > >
> > > Regards,
> > > Witold Szczerba
> > > ---
> > > Sent from my mobile phone.
> > > On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com>
> wrote:
> > >
> > > > It seems like you don't have initialSize, maxIdle, maxActive, minIdle
> > or
> > > > maxWait set.
> > > >
> > > > Also, I don't think you're returning the connection to the pool.
> > > >
> > > >
> > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > pljosh.mail@gmail.com
> > > > >wrote:
> > > >
> > > > > Hi,
> > > > > I am facing something very strange. I am not 100% sure if this is
> > true,
> > > > > but everything looks like the connection pool actually reconnects
> to
> > > > > database, I mean it closes the TCP connection and creates new one,
> > over
> > > > and
> > > > > over, instead of reusing connections.
> > > > >
> > > > > To make sure it is not an issue with my application, I have
> created a
> > > new
> > > > > project with only one class:
> > > > >
> > > > > /**
> > > > > *
> > > > > * @author witoldsz
> > > > > */
> > > > > @Path("/resource")
> > > > > @Stateless
> > > > > public class Endpoint {
> > > > >
> > > > > @Resource(name = "jdbc/resource")
> > > > > private DataSource ds;
> > > > >
> > > > > @GET
> > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > public String hello() throws Exception {
> > > > > try (Connection conn = ds.getConnection()) {
> > > > > //do nothing, just fetch and close the connection
> > > > > }
> > > > > return "Hello!";
> > > > > }
> > > > > }
> > > > >
> > > > > The JDBC resource is actually an Oracle 11g.
> > > > >
> > > > > Now hear this: each time I do a HTTP GET:
> > > > > http://localhost:8080/app/resource
> > > > > I can see a new connection being established and the previous one
> > > closed,
> > > > > used iptraf to monitor.
> > > > >
> > > > > Also, the:
> > > > > --
> > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > FROM v$session
> > > > > WHERE username = 'MY_USER_NAME'
> > > > > ORDER BY logon_time;
> > > > > --
> > > > > result is showing new value in "serial#" column after each HTTP
> GET.
> > > > >
> > > > > What is worth noticing, the already used connection does not get
> > > > destroyed
> > > > > immediately after use. It waits in the pool and is destroyed when
> new
> > > one
> > > > > is created. And the new one waits there until yet another one is
> > > created
> > > > > and it goes like this over and over.
> > > > >
> > > > > I am sure this is the reason my application works really, I mean
> > really
> > > > > slow, when I am developing from home with remote, over VPN,
> database.
> > > > >
> > > > > My environment:
> > > > > tomee.xml:
> > > > >
> > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > UserName test
> > > > > Password test
> > > > > JtaManaged true
> > > > > </Resource>
> > > > >
> > > > > OS:
> > > > > Ubuntu 12.10 64 bit,
> > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > TomEE 1.5.1 JAX-RS
> > > > > tested also using:
> > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > >
> > > > >
> > > > > I am attaching source code of the webapp (3.6kB), just configure a
> > > > > "jdbc/resource" datasource in tomee.xml and deploy it to run it on
> > your
> > > > own.
> > > > >
> > > > > Thanks,
> > > > > Witold Szczerba
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Romain Manni-Bucau <rm...@gmail.com>.
Yes and no. Pooling should be on by default but not sure about last
release...maybe check our trunk. We had issues with defaults
Le 28 févr. 2013 16:44, "Howard W. Smith, Jr." <sm...@gmail.com> a
écrit :
> IMO, I think you should ask your questions on the tomcat user list.
>
>
> On Thu, Feb 28, 2013 at 10:21 AM, Witold Szczerba <pljosh.mail@gmail.com
> >wrote:
>
> > Well yes, I am. Configuring JDBC resource means configuring a JDBC
> > connection pool, doesn't it? It is explicitly called in JNDI Datasource
> > HOW-TO: Database Connection Pool (DBCP) Configurations. Default values:
> > MaxActive, MaxIdle, etc... that is all about pool. Also, if the pooling
> > feature of JDBC resources were disabled, then I would expect connections
> to
> > be closed after use, yet they do stay in pool, but as I said, they will
> > never be used again but replaced.
> >
> > Prove me wrong, but Apache Tomcat has a broken connection pool, not a
> JDBC
> > resource without a pool.
> >
> > Regards,
> > Witold Szczerba
> >
> >
> > On 28 February 2013 14:22, John D. Ament <jo...@gmail.com> wrote:
> >
> > > Witold,
> > >
> > > Maybe we have bad assumptions. Are you assuming that connection
> pooling
> > is
> > > enabled by default in datasources?
> > >
> > > John
> > >
> > >
> > > On Thu, Feb 28, 2013 at 7:33 AM, Witold Szczerba <
> pljosh.mail@gmail.com
> > > >wrote:
> > >
> > > > OK, maybe I am blind or maybe I just do not get it at all. I was
> > reading
> > > > entire JDBC connection pool documentation of TomEE and Tomcat before
> > > > posting to this thread and, even with your explicit links provided,
> > there
> > > > is nothing (at least I cannot find nothing) about the issue I have
> > > > described (not how to handle connection leaks which I do not have).
> > > >
> > > > Can you, please, tell me exactly, I mean exactly where in the docs it
> > is
> > > > described (or other kind of hint), that the configuration like I have
> > > > provided (which is exactly the same as in
> > > > common-datasource-configurations.html) makes broken Tomcat connection
> > > pool?
> > > > I mean totally broken (application works now like 100 times faster).
> > > >
> > > > Regards,
> > > > Witold Szczerba
> > > >
> > > > On 28 February 2013 13:00, John D. Ament <jo...@gmail.com>
> > wrote:
> > > >
> > > > > Witold,
> > > > >
> > > > > The entry I added is based on this page in the Tomcat docs:
> > > > >
> > > > >
> > > >
> > >
> >
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
> > > > > In addition, at the bottom of the TomEE datasource page you can see
> > it
> > > > > noted (though it's somewhat hidden)
> > > > > http://tomee.apache.org/common-datasource-configurations.html
> > > > >
> > > > > Though I do +1 your sentiment with the configuration. TomEE ends
> up
> > > > > reliant on the upstream project's docs.
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > >
> > > > > On Thu, Feb 28, 2013 at 6:10 AM, Witold Szczerba <
> > > pljosh.mail@gmail.com
> > > > > >wrote:
> > > > >
> > > > > > One thing I can say just now is that setting DataSourceCreator to
> > > DBCP
> > > > > > makes a HUGE difference in performance. That connection pool
> works
> > > as a
> > > > > one
> > > > > > would expect any connection pool to work.
> > > > > > Have not tried the extra interceptors on top of Tomcat Connection
> > > Pool,
> > > > > but
> > > > > > as long as I can keep far away from it, I am fine.
> > > > > >
> > > > > > The only thing I would suggest for TomEE project for now would be
> > to
> > > > > > supplement the documentation, so every one, picking TomEE as
> their
> > > app
> > > > > > server of choice, would be warned about the issue.
> > > > > >
> > > > > > Thanks,
> > > > > > Witold Szczerba
> > > > > >
> > > > > >
> > > > > > On 28 February 2013 02:39, John D. Ament <john.d.ament@gmail.com
> >
> > > > wrote:
> > > > > >
> > > > > > > When I run your example on my local machine, using Tomee 1.5.1
> w/
> > > > > jax-rs
> > > > > > > and mysql, I get the same results. However, switching to dbcp
> > > > pooling
> > > > > > > changes that.
> > > > > > >
> > > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > > JdbcDriver com.mysql.jdbc.Driver
> > > > > > > JdbcUrl jdbc:mysql://localhost:3306/ex
> > > > > > > UserName ex
> > > > > > > Password abc123
> > > > > > > JtaManaged true
> > > > > > > DataSourceCreator dbcp
> > > > > > > </Resource>
> > > > > > >
> > > > > > > And sorry, missing the brackets around your try.
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > >
> > > > > > > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <
> > > > > pljosh.mail@gmail.com
> > > > > > > >wrote:
> > > > > > >
> > > > > > > > Well, all the properties you have mentioned have sane
> defaults,
> > > > > that's
> > > > > > > why
> > > > > > > > I did not specified them.
> > > > > > > > Also, the try (...) statement (called try-with-resources),
> > > > introduced
> > > > > > in
> > > > > > > > Java7, is calling #close method, so the connection is handled
> > > > > > correctly.
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Witold Szczerba
> > > > > > > > ---
> > > > > > > > Sent from my mobile phone.
> > > > > > > > On Feb 28, 2013 1:12 AM, "John D. Ament" <
> > john.d.ament@gmail.com
> > > >
> > > > > > wrote:
> > > > > > > >
> > > > > > > > > It seems like you don't have initialSize, maxIdle,
> maxActive,
> > > > > minIdle
> > > > > > > or
> > > > > > > > > maxWait set.
> > > > > > > > >
> > > > > > > > > Also, I don't think you're returning the connection to the
> > > pool.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > > > > > > pljosh.mail@gmail.com
> > > > > > > > > >wrote:
> > > > > > > > >
> > > > > > > > > > Hi,
> > > > > > > > > > I am facing something very strange. I am not 100% sure if
> > > this
> > > > is
> > > > > > > true,
> > > > > > > > > > but everything looks like the connection pool actually
> > > > reconnects
> > > > > > to
> > > > > > > > > > database, I mean it closes the TCP connection and creates
> > new
> > > > > one,
> > > > > > > over
> > > > > > > > > and
> > > > > > > > > > over, instead of reusing connections.
> > > > > > > > > >
> > > > > > > > > > To make sure it is not an issue with my application, I
> have
> > > > > > created a
> > > > > > > > new
> > > > > > > > > > project with only one class:
> > > > > > > > > >
> > > > > > > > > > /**
> > > > > > > > > > *
> > > > > > > > > > * @author witoldsz
> > > > > > > > > > */
> > > > > > > > > > @Path("/resource")
> > > > > > > > > > @Stateless
> > > > > > > > > > public class Endpoint {
> > > > > > > > > >
> > > > > > > > > > @Resource(name = "jdbc/resource")
> > > > > > > > > > private DataSource ds;
> > > > > > > > > >
> > > > > > > > > > @GET
> > > > > > > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > > > > > > public String hello() throws Exception {
> > > > > > > > > > try (Connection conn = ds.getConnection()) {
> > > > > > > > > > //do nothing, just fetch and close the
> > connection
> > > > > > > > > > }
> > > > > > > > > > return "Hello!";
> > > > > > > > > > }
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > > > The JDBC resource is actually an Oracle 11g.
> > > > > > > > > >
> > > > > > > > > > Now hear this: each time I do a HTTP GET:
> > > > > > > > > > http://localhost:8080/app/resource
> > > > > > > > > > I can see a new connection being established and the
> > previous
> > > > one
> > > > > > > > closed,
> > > > > > > > > > used iptraf to monitor.
> > > > > > > > > >
> > > > > > > > > > Also, the:
> > > > > > > > > > --
> > > > > > > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > > > > > > FROM v$session
> > > > > > > > > > WHERE username = 'MY_USER_NAME'
> > > > > > > > > > ORDER BY logon_time;
> > > > > > > > > > --
> > > > > > > > > > result is showing new value in "serial#" column after
> each
> > > HTTP
> > > > > > GET.
> > > > > > > > > >
> > > > > > > > > > What is worth noticing, the already used connection does
> > not
> > > > get
> > > > > > > > > destroyed
> > > > > > > > > > immediately after use. It waits in the pool and is
> > destroyed
> > > > when
> > > > > > new
> > > > > > > > one
> > > > > > > > > > is created. And the new one waits there until yet another
> > one
> > > > is
> > > > > > > > created
> > > > > > > > > > and it goes like this over and over.
> > > > > > > > > >
> > > > > > > > > > I am sure this is the reason my application works
> really, I
> > > > mean
> > > > > > > really
> > > > > > > > > > slow, when I am developing from home with remote, over
> VPN,
> > > > > > database.
> > > > > > > > > >
> > > > > > > > > > My environment:
> > > > > > > > > > tomee.xml:
> > > > > > > > > >
> > > > > > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > > > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > > > > > > UserName test
> > > > > > > > > > Password test
> > > > > > > > > > JtaManaged true
> > > > > > > > > > </Resource>
> > > > > > > > > >
> > > > > > > > > > OS:
> > > > > > > > > > Ubuntu 12.10 64 bit,
> > > > > > > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > > > > > > TomEE 1.5.1 JAX-RS
> > > > > > > > > > tested also using:
> > > > > > > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > I am attaching source code of the webapp (3.6kB), just
> > > > configure
> > > > > a
> > > > > > > > > > "jdbc/resource" datasource in tomee.xml and deploy it to
> > run
> > > it
> > > > > on
> > > > > > > your
> > > > > > > > > own.
> > > > > > > > > >
> > > > > > > > > > Thanks,
> > > > > > > > > > Witold Szczerba
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by "Howard W. Smith, Jr." <sm...@gmail.com>.
IMO, I think you should ask your questions on the tomcat user list.
On Thu, Feb 28, 2013 at 10:21 AM, Witold Szczerba <pl...@gmail.com>wrote:
> Well yes, I am. Configuring JDBC resource means configuring a JDBC
> connection pool, doesn't it? It is explicitly called in JNDI Datasource
> HOW-TO: Database Connection Pool (DBCP) Configurations. Default values:
> MaxActive, MaxIdle, etc... that is all about pool. Also, if the pooling
> feature of JDBC resources were disabled, then I would expect connections to
> be closed after use, yet they do stay in pool, but as I said, they will
> never be used again but replaced.
>
> Prove me wrong, but Apache Tomcat has a broken connection pool, not a JDBC
> resource without a pool.
>
> Regards,
> Witold Szczerba
>
>
> On 28 February 2013 14:22, John D. Ament <jo...@gmail.com> wrote:
>
> > Witold,
> >
> > Maybe we have bad assumptions. Are you assuming that connection pooling
> is
> > enabled by default in datasources?
> >
> > John
> >
> >
> > On Thu, Feb 28, 2013 at 7:33 AM, Witold Szczerba <pljosh.mail@gmail.com
> > >wrote:
> >
> > > OK, maybe I am blind or maybe I just do not get it at all. I was
> reading
> > > entire JDBC connection pool documentation of TomEE and Tomcat before
> > > posting to this thread and, even with your explicit links provided,
> there
> > > is nothing (at least I cannot find nothing) about the issue I have
> > > described (not how to handle connection leaks which I do not have).
> > >
> > > Can you, please, tell me exactly, I mean exactly where in the docs it
> is
> > > described (or other kind of hint), that the configuration like I have
> > > provided (which is exactly the same as in
> > > common-datasource-configurations.html) makes broken Tomcat connection
> > pool?
> > > I mean totally broken (application works now like 100 times faster).
> > >
> > > Regards,
> > > Witold Szczerba
> > >
> > > On 28 February 2013 13:00, John D. Ament <jo...@gmail.com>
> wrote:
> > >
> > > > Witold,
> > > >
> > > > The entry I added is based on this page in the Tomcat docs:
> > > >
> > > >
> > >
> >
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
> > > > In addition, at the bottom of the TomEE datasource page you can see
> it
> > > > noted (though it's somewhat hidden)
> > > > http://tomee.apache.org/common-datasource-configurations.html
> > > >
> > > > Though I do +1 your sentiment with the configuration. TomEE ends up
> > > > reliant on the upstream project's docs.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > On Thu, Feb 28, 2013 at 6:10 AM, Witold Szczerba <
> > pljosh.mail@gmail.com
> > > > >wrote:
> > > >
> > > > > One thing I can say just now is that setting DataSourceCreator to
> > DBCP
> > > > > makes a HUGE difference in performance. That connection pool works
> > as a
> > > > one
> > > > > would expect any connection pool to work.
> > > > > Have not tried the extra interceptors on top of Tomcat Connection
> > Pool,
> > > > but
> > > > > as long as I can keep far away from it, I am fine.
> > > > >
> > > > > The only thing I would suggest for TomEE project for now would be
> to
> > > > > supplement the documentation, so every one, picking TomEE as their
> > app
> > > > > server of choice, would be warned about the issue.
> > > > >
> > > > > Thanks,
> > > > > Witold Szczerba
> > > > >
> > > > >
> > > > > On 28 February 2013 02:39, John D. Ament <jo...@gmail.com>
> > > wrote:
> > > > >
> > > > > > When I run your example on my local machine, using Tomee 1.5.1 w/
> > > > jax-rs
> > > > > > and mysql, I get the same results. However, switching to dbcp
> > > pooling
> > > > > > changes that.
> > > > > >
> > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > JdbcDriver com.mysql.jdbc.Driver
> > > > > > JdbcUrl jdbc:mysql://localhost:3306/ex
> > > > > > UserName ex
> > > > > > Password abc123
> > > > > > JtaManaged true
> > > > > > DataSourceCreator dbcp
> > > > > > </Resource>
> > > > > >
> > > > > > And sorry, missing the brackets around your try.
> > > > > >
> > > > > > John
> > > > > >
> > > > > >
> > > > > > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <
> > > > pljosh.mail@gmail.com
> > > > > > >wrote:
> > > > > >
> > > > > > > Well, all the properties you have mentioned have sane defaults,
> > > > that's
> > > > > > why
> > > > > > > I did not specified them.
> > > > > > > Also, the try (...) statement (called try-with-resources),
> > > introduced
> > > > > in
> > > > > > > Java7, is calling #close method, so the connection is handled
> > > > > correctly.
> > > > > > >
> > > > > > > Regards,
> > > > > > > Witold Szczerba
> > > > > > > ---
> > > > > > > Sent from my mobile phone.
> > > > > > > On Feb 28, 2013 1:12 AM, "John D. Ament" <
> john.d.ament@gmail.com
> > >
> > > > > wrote:
> > > > > > >
> > > > > > > > It seems like you don't have initialSize, maxIdle, maxActive,
> > > > minIdle
> > > > > > or
> > > > > > > > maxWait set.
> > > > > > > >
> > > > > > > > Also, I don't think you're returning the connection to the
> > pool.
> > > > > > > >
> > > > > > > >
> > > > > > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > > > > > pljosh.mail@gmail.com
> > > > > > > > >wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > > I am facing something very strange. I am not 100% sure if
> > this
> > > is
> > > > > > true,
> > > > > > > > > but everything looks like the connection pool actually
> > > reconnects
> > > > > to
> > > > > > > > > database, I mean it closes the TCP connection and creates
> new
> > > > one,
> > > > > > over
> > > > > > > > and
> > > > > > > > > over, instead of reusing connections.
> > > > > > > > >
> > > > > > > > > To make sure it is not an issue with my application, I have
> > > > > created a
> > > > > > > new
> > > > > > > > > project with only one class:
> > > > > > > > >
> > > > > > > > > /**
> > > > > > > > > *
> > > > > > > > > * @author witoldsz
> > > > > > > > > */
> > > > > > > > > @Path("/resource")
> > > > > > > > > @Stateless
> > > > > > > > > public class Endpoint {
> > > > > > > > >
> > > > > > > > > @Resource(name = "jdbc/resource")
> > > > > > > > > private DataSource ds;
> > > > > > > > >
> > > > > > > > > @GET
> > > > > > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > > > > > public String hello() throws Exception {
> > > > > > > > > try (Connection conn = ds.getConnection()) {
> > > > > > > > > //do nothing, just fetch and close the
> connection
> > > > > > > > > }
> > > > > > > > > return "Hello!";
> > > > > > > > > }
> > > > > > > > > }
> > > > > > > > >
> > > > > > > > > The JDBC resource is actually an Oracle 11g.
> > > > > > > > >
> > > > > > > > > Now hear this: each time I do a HTTP GET:
> > > > > > > > > http://localhost:8080/app/resource
> > > > > > > > > I can see a new connection being established and the
> previous
> > > one
> > > > > > > closed,
> > > > > > > > > used iptraf to monitor.
> > > > > > > > >
> > > > > > > > > Also, the:
> > > > > > > > > --
> > > > > > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > > > > > FROM v$session
> > > > > > > > > WHERE username = 'MY_USER_NAME'
> > > > > > > > > ORDER BY logon_time;
> > > > > > > > > --
> > > > > > > > > result is showing new value in "serial#" column after each
> > HTTP
> > > > > GET.
> > > > > > > > >
> > > > > > > > > What is worth noticing, the already used connection does
> not
> > > get
> > > > > > > > destroyed
> > > > > > > > > immediately after use. It waits in the pool and is
> destroyed
> > > when
> > > > > new
> > > > > > > one
> > > > > > > > > is created. And the new one waits there until yet another
> one
> > > is
> > > > > > > created
> > > > > > > > > and it goes like this over and over.
> > > > > > > > >
> > > > > > > > > I am sure this is the reason my application works really, I
> > > mean
> > > > > > really
> > > > > > > > > slow, when I am developing from home with remote, over VPN,
> > > > > database.
> > > > > > > > >
> > > > > > > > > My environment:
> > > > > > > > > tomee.xml:
> > > > > > > > >
> > > > > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > > > > > UserName test
> > > > > > > > > Password test
> > > > > > > > > JtaManaged true
> > > > > > > > > </Resource>
> > > > > > > > >
> > > > > > > > > OS:
> > > > > > > > > Ubuntu 12.10 64 bit,
> > > > > > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > > > > > TomEE 1.5.1 JAX-RS
> > > > > > > > > tested also using:
> > > > > > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > I am attaching source code of the webapp (3.6kB), just
> > > configure
> > > > a
> > > > > > > > > "jdbc/resource" datasource in tomee.xml and deploy it to
> run
> > it
> > > > on
> > > > > > your
> > > > > > > > own.
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > > Witold Szczerba
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Witold Szczerba <pl...@gmail.com>.
Well yes, I am. Configuring JDBC resource means configuring a JDBC
connection pool, doesn't it? It is explicitly called in JNDI Datasource
HOW-TO: Database Connection Pool (DBCP) Configurations. Default values:
MaxActive, MaxIdle, etc... that is all about pool. Also, if the pooling
feature of JDBC resources were disabled, then I would expect connections to
be closed after use, yet they do stay in pool, but as I said, they will
never be used again but replaced.
Prove me wrong, but Apache Tomcat has a broken connection pool, not a JDBC
resource without a pool.
Regards,
Witold Szczerba
On 28 February 2013 14:22, John D. Ament <jo...@gmail.com> wrote:
> Witold,
>
> Maybe we have bad assumptions. Are you assuming that connection pooling is
> enabled by default in datasources?
>
> John
>
>
> On Thu, Feb 28, 2013 at 7:33 AM, Witold Szczerba <pljosh.mail@gmail.com
> >wrote:
>
> > OK, maybe I am blind or maybe I just do not get it at all. I was reading
> > entire JDBC connection pool documentation of TomEE and Tomcat before
> > posting to this thread and, even with your explicit links provided, there
> > is nothing (at least I cannot find nothing) about the issue I have
> > described (not how to handle connection leaks which I do not have).
> >
> > Can you, please, tell me exactly, I mean exactly where in the docs it is
> > described (or other kind of hint), that the configuration like I have
> > provided (which is exactly the same as in
> > common-datasource-configurations.html) makes broken Tomcat connection
> pool?
> > I mean totally broken (application works now like 100 times faster).
> >
> > Regards,
> > Witold Szczerba
> >
> > On 28 February 2013 13:00, John D. Ament <jo...@gmail.com> wrote:
> >
> > > Witold,
> > >
> > > The entry I added is based on this page in the Tomcat docs:
> > >
> > >
> >
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
> > > In addition, at the bottom of the TomEE datasource page you can see it
> > > noted (though it's somewhat hidden)
> > > http://tomee.apache.org/common-datasource-configurations.html
> > >
> > > Though I do +1 your sentiment with the configuration. TomEE ends up
> > > reliant on the upstream project's docs.
> > >
> > > John
> > >
> > >
> > >
> > > On Thu, Feb 28, 2013 at 6:10 AM, Witold Szczerba <
> pljosh.mail@gmail.com
> > > >wrote:
> > >
> > > > One thing I can say just now is that setting DataSourceCreator to
> DBCP
> > > > makes a HUGE difference in performance. That connection pool works
> as a
> > > one
> > > > would expect any connection pool to work.
> > > > Have not tried the extra interceptors on top of Tomcat Connection
> Pool,
> > > but
> > > > as long as I can keep far away from it, I am fine.
> > > >
> > > > The only thing I would suggest for TomEE project for now would be to
> > > > supplement the documentation, so every one, picking TomEE as their
> app
> > > > server of choice, would be warned about the issue.
> > > >
> > > > Thanks,
> > > > Witold Szczerba
> > > >
> > > >
> > > > On 28 February 2013 02:39, John D. Ament <jo...@gmail.com>
> > wrote:
> > > >
> > > > > When I run your example on my local machine, using Tomee 1.5.1 w/
> > > jax-rs
> > > > > and mysql, I get the same results. However, switching to dbcp
> > pooling
> > > > > changes that.
> > > > >
> > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > JdbcDriver com.mysql.jdbc.Driver
> > > > > JdbcUrl jdbc:mysql://localhost:3306/ex
> > > > > UserName ex
> > > > > Password abc123
> > > > > JtaManaged true
> > > > > DataSourceCreator dbcp
> > > > > </Resource>
> > > > >
> > > > > And sorry, missing the brackets around your try.
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <
> > > pljosh.mail@gmail.com
> > > > > >wrote:
> > > > >
> > > > > > Well, all the properties you have mentioned have sane defaults,
> > > that's
> > > > > why
> > > > > > I did not specified them.
> > > > > > Also, the try (...) statement (called try-with-resources),
> > introduced
> > > > in
> > > > > > Java7, is calling #close method, so the connection is handled
> > > > correctly.
> > > > > >
> > > > > > Regards,
> > > > > > Witold Szczerba
> > > > > > ---
> > > > > > Sent from my mobile phone.
> > > > > > On Feb 28, 2013 1:12 AM, "John D. Ament" <john.d.ament@gmail.com
> >
> > > > wrote:
> > > > > >
> > > > > > > It seems like you don't have initialSize, maxIdle, maxActive,
> > > minIdle
> > > > > or
> > > > > > > maxWait set.
> > > > > > >
> > > > > > > Also, I don't think you're returning the connection to the
> pool.
> > > > > > >
> > > > > > >
> > > > > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > > > > pljosh.mail@gmail.com
> > > > > > > >wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > > I am facing something very strange. I am not 100% sure if
> this
> > is
> > > > > true,
> > > > > > > > but everything looks like the connection pool actually
> > reconnects
> > > > to
> > > > > > > > database, I mean it closes the TCP connection and creates new
> > > one,
> > > > > over
> > > > > > > and
> > > > > > > > over, instead of reusing connections.
> > > > > > > >
> > > > > > > > To make sure it is not an issue with my application, I have
> > > > created a
> > > > > > new
> > > > > > > > project with only one class:
> > > > > > > >
> > > > > > > > /**
> > > > > > > > *
> > > > > > > > * @author witoldsz
> > > > > > > > */
> > > > > > > > @Path("/resource")
> > > > > > > > @Stateless
> > > > > > > > public class Endpoint {
> > > > > > > >
> > > > > > > > @Resource(name = "jdbc/resource")
> > > > > > > > private DataSource ds;
> > > > > > > >
> > > > > > > > @GET
> > > > > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > > > > public String hello() throws Exception {
> > > > > > > > try (Connection conn = ds.getConnection()) {
> > > > > > > > //do nothing, just fetch and close the connection
> > > > > > > > }
> > > > > > > > return "Hello!";
> > > > > > > > }
> > > > > > > > }
> > > > > > > >
> > > > > > > > The JDBC resource is actually an Oracle 11g.
> > > > > > > >
> > > > > > > > Now hear this: each time I do a HTTP GET:
> > > > > > > > http://localhost:8080/app/resource
> > > > > > > > I can see a new connection being established and the previous
> > one
> > > > > > closed,
> > > > > > > > used iptraf to monitor.
> > > > > > > >
> > > > > > > > Also, the:
> > > > > > > > --
> > > > > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > > > > FROM v$session
> > > > > > > > WHERE username = 'MY_USER_NAME'
> > > > > > > > ORDER BY logon_time;
> > > > > > > > --
> > > > > > > > result is showing new value in "serial#" column after each
> HTTP
> > > > GET.
> > > > > > > >
> > > > > > > > What is worth noticing, the already used connection does not
> > get
> > > > > > > destroyed
> > > > > > > > immediately after use. It waits in the pool and is destroyed
> > when
> > > > new
> > > > > > one
> > > > > > > > is created. And the new one waits there until yet another one
> > is
> > > > > > created
> > > > > > > > and it goes like this over and over.
> > > > > > > >
> > > > > > > > I am sure this is the reason my application works really, I
> > mean
> > > > > really
> > > > > > > > slow, when I am developing from home with remote, over VPN,
> > > > database.
> > > > > > > >
> > > > > > > > My environment:
> > > > > > > > tomee.xml:
> > > > > > > >
> > > > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > > > > UserName test
> > > > > > > > Password test
> > > > > > > > JtaManaged true
> > > > > > > > </Resource>
> > > > > > > >
> > > > > > > > OS:
> > > > > > > > Ubuntu 12.10 64 bit,
> > > > > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > > > > TomEE 1.5.1 JAX-RS
> > > > > > > > tested also using:
> > > > > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > > > > >
> > > > > > > >
> > > > > > > > I am attaching source code of the webapp (3.6kB), just
> > configure
> > > a
> > > > > > > > "jdbc/resource" datasource in tomee.xml and deploy it to run
> it
> > > on
> > > > > your
> > > > > > > own.
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > > Witold Szczerba
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by "John D. Ament" <jo...@gmail.com>.
Witold,
Maybe we have bad assumptions. Are you assuming that connection pooling is
enabled by default in datasources?
John
On Thu, Feb 28, 2013 at 7:33 AM, Witold Szczerba <pl...@gmail.com>wrote:
> OK, maybe I am blind or maybe I just do not get it at all. I was reading
> entire JDBC connection pool documentation of TomEE and Tomcat before
> posting to this thread and, even with your explicit links provided, there
> is nothing (at least I cannot find nothing) about the issue I have
> described (not how to handle connection leaks which I do not have).
>
> Can you, please, tell me exactly, I mean exactly where in the docs it is
> described (or other kind of hint), that the configuration like I have
> provided (which is exactly the same as in
> common-datasource-configurations.html) makes broken Tomcat connection pool?
> I mean totally broken (application works now like 100 times faster).
>
> Regards,
> Witold Szczerba
>
> On 28 February 2013 13:00, John D. Ament <jo...@gmail.com> wrote:
>
> > Witold,
> >
> > The entry I added is based on this page in the Tomcat docs:
> >
> >
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
> > In addition, at the bottom of the TomEE datasource page you can see it
> > noted (though it's somewhat hidden)
> > http://tomee.apache.org/common-datasource-configurations.html
> >
> > Though I do +1 your sentiment with the configuration. TomEE ends up
> > reliant on the upstream project's docs.
> >
> > John
> >
> >
> >
> > On Thu, Feb 28, 2013 at 6:10 AM, Witold Szczerba <pljosh.mail@gmail.com
> > >wrote:
> >
> > > One thing I can say just now is that setting DataSourceCreator to DBCP
> > > makes a HUGE difference in performance. That connection pool works as a
> > one
> > > would expect any connection pool to work.
> > > Have not tried the extra interceptors on top of Tomcat Connection Pool,
> > but
> > > as long as I can keep far away from it, I am fine.
> > >
> > > The only thing I would suggest for TomEE project for now would be to
> > > supplement the documentation, so every one, picking TomEE as their app
> > > server of choice, would be warned about the issue.
> > >
> > > Thanks,
> > > Witold Szczerba
> > >
> > >
> > > On 28 February 2013 02:39, John D. Ament <jo...@gmail.com>
> wrote:
> > >
> > > > When I run your example on my local machine, using Tomee 1.5.1 w/
> > jax-rs
> > > > and mysql, I get the same results. However, switching to dbcp
> pooling
> > > > changes that.
> > > >
> > > > <Resource id="jdbc/resource" type="DataSource">
> > > > JdbcDriver com.mysql.jdbc.Driver
> > > > JdbcUrl jdbc:mysql://localhost:3306/ex
> > > > UserName ex
> > > > Password abc123
> > > > JtaManaged true
> > > > DataSourceCreator dbcp
> > > > </Resource>
> > > >
> > > > And sorry, missing the brackets around your try.
> > > >
> > > > John
> > > >
> > > >
> > > > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <
> > pljosh.mail@gmail.com
> > > > >wrote:
> > > >
> > > > > Well, all the properties you have mentioned have sane defaults,
> > that's
> > > > why
> > > > > I did not specified them.
> > > > > Also, the try (...) statement (called try-with-resources),
> introduced
> > > in
> > > > > Java7, is calling #close method, so the connection is handled
> > > correctly.
> > > > >
> > > > > Regards,
> > > > > Witold Szczerba
> > > > > ---
> > > > > Sent from my mobile phone.
> > > > > On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com>
> > > wrote:
> > > > >
> > > > > > It seems like you don't have initialSize, maxIdle, maxActive,
> > minIdle
> > > > or
> > > > > > maxWait set.
> > > > > >
> > > > > > Also, I don't think you're returning the connection to the pool.
> > > > > >
> > > > > >
> > > > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > > > pljosh.mail@gmail.com
> > > > > > >wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > > I am facing something very strange. I am not 100% sure if this
> is
> > > > true,
> > > > > > > but everything looks like the connection pool actually
> reconnects
> > > to
> > > > > > > database, I mean it closes the TCP connection and creates new
> > one,
> > > > over
> > > > > > and
> > > > > > > over, instead of reusing connections.
> > > > > > >
> > > > > > > To make sure it is not an issue with my application, I have
> > > created a
> > > > > new
> > > > > > > project with only one class:
> > > > > > >
> > > > > > > /**
> > > > > > > *
> > > > > > > * @author witoldsz
> > > > > > > */
> > > > > > > @Path("/resource")
> > > > > > > @Stateless
> > > > > > > public class Endpoint {
> > > > > > >
> > > > > > > @Resource(name = "jdbc/resource")
> > > > > > > private DataSource ds;
> > > > > > >
> > > > > > > @GET
> > > > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > > > public String hello() throws Exception {
> > > > > > > try (Connection conn = ds.getConnection()) {
> > > > > > > //do nothing, just fetch and close the connection
> > > > > > > }
> > > > > > > return "Hello!";
> > > > > > > }
> > > > > > > }
> > > > > > >
> > > > > > > The JDBC resource is actually an Oracle 11g.
> > > > > > >
> > > > > > > Now hear this: each time I do a HTTP GET:
> > > > > > > http://localhost:8080/app/resource
> > > > > > > I can see a new connection being established and the previous
> one
> > > > > closed,
> > > > > > > used iptraf to monitor.
> > > > > > >
> > > > > > > Also, the:
> > > > > > > --
> > > > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > > > FROM v$session
> > > > > > > WHERE username = 'MY_USER_NAME'
> > > > > > > ORDER BY logon_time;
> > > > > > > --
> > > > > > > result is showing new value in "serial#" column after each HTTP
> > > GET.
> > > > > > >
> > > > > > > What is worth noticing, the already used connection does not
> get
> > > > > > destroyed
> > > > > > > immediately after use. It waits in the pool and is destroyed
> when
> > > new
> > > > > one
> > > > > > > is created. And the new one waits there until yet another one
> is
> > > > > created
> > > > > > > and it goes like this over and over.
> > > > > > >
> > > > > > > I am sure this is the reason my application works really, I
> mean
> > > > really
> > > > > > > slow, when I am developing from home with remote, over VPN,
> > > database.
> > > > > > >
> > > > > > > My environment:
> > > > > > > tomee.xml:
> > > > > > >
> > > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > > > UserName test
> > > > > > > Password test
> > > > > > > JtaManaged true
> > > > > > > </Resource>
> > > > > > >
> > > > > > > OS:
> > > > > > > Ubuntu 12.10 64 bit,
> > > > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > > > TomEE 1.5.1 JAX-RS
> > > > > > > tested also using:
> > > > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > > > >
> > > > > > >
> > > > > > > I am attaching source code of the webapp (3.6kB), just
> configure
> > a
> > > > > > > "jdbc/resource" datasource in tomee.xml and deploy it to run it
> > on
> > > > your
> > > > > > own.
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Witold Szczerba
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Witold Szczerba <pl...@gmail.com>.
OK, maybe I am blind or maybe I just do not get it at all. I was reading
entire JDBC connection pool documentation of TomEE and Tomcat before
posting to this thread and, even with your explicit links provided, there
is nothing (at least I cannot find nothing) about the issue I have
described (not how to handle connection leaks which I do not have).
Can you, please, tell me exactly, I mean exactly where in the docs it is
described (or other kind of hint), that the configuration like I have
provided (which is exactly the same as in
common-datasource-configurations.html) makes broken Tomcat connection pool?
I mean totally broken (application works now like 100 times faster).
Regards,
Witold Szczerba
On 28 February 2013 13:00, John D. Ament <jo...@gmail.com> wrote:
> Witold,
>
> The entry I added is based on this page in the Tomcat docs:
>
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
> In addition, at the bottom of the TomEE datasource page you can see it
> noted (though it's somewhat hidden)
> http://tomee.apache.org/common-datasource-configurations.html
>
> Though I do +1 your sentiment with the configuration. TomEE ends up
> reliant on the upstream project's docs.
>
> John
>
>
>
> On Thu, Feb 28, 2013 at 6:10 AM, Witold Szczerba <pljosh.mail@gmail.com
> >wrote:
>
> > One thing I can say just now is that setting DataSourceCreator to DBCP
> > makes a HUGE difference in performance. That connection pool works as a
> one
> > would expect any connection pool to work.
> > Have not tried the extra interceptors on top of Tomcat Connection Pool,
> but
> > as long as I can keep far away from it, I am fine.
> >
> > The only thing I would suggest for TomEE project for now would be to
> > supplement the documentation, so every one, picking TomEE as their app
> > server of choice, would be warned about the issue.
> >
> > Thanks,
> > Witold Szczerba
> >
> >
> > On 28 February 2013 02:39, John D. Ament <jo...@gmail.com> wrote:
> >
> > > When I run your example on my local machine, using Tomee 1.5.1 w/
> jax-rs
> > > and mysql, I get the same results. However, switching to dbcp pooling
> > > changes that.
> > >
> > > <Resource id="jdbc/resource" type="DataSource">
> > > JdbcDriver com.mysql.jdbc.Driver
> > > JdbcUrl jdbc:mysql://localhost:3306/ex
> > > UserName ex
> > > Password abc123
> > > JtaManaged true
> > > DataSourceCreator dbcp
> > > </Resource>
> > >
> > > And sorry, missing the brackets around your try.
> > >
> > > John
> > >
> > >
> > > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <
> pljosh.mail@gmail.com
> > > >wrote:
> > >
> > > > Well, all the properties you have mentioned have sane defaults,
> that's
> > > why
> > > > I did not specified them.
> > > > Also, the try (...) statement (called try-with-resources), introduced
> > in
> > > > Java7, is calling #close method, so the connection is handled
> > correctly.
> > > >
> > > > Regards,
> > > > Witold Szczerba
> > > > ---
> > > > Sent from my mobile phone.
> > > > On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com>
> > wrote:
> > > >
> > > > > It seems like you don't have initialSize, maxIdle, maxActive,
> minIdle
> > > or
> > > > > maxWait set.
> > > > >
> > > > > Also, I don't think you're returning the connection to the pool.
> > > > >
> > > > >
> > > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > > pljosh.mail@gmail.com
> > > > > >wrote:
> > > > >
> > > > > > Hi,
> > > > > > I am facing something very strange. I am not 100% sure if this is
> > > true,
> > > > > > but everything looks like the connection pool actually reconnects
> > to
> > > > > > database, I mean it closes the TCP connection and creates new
> one,
> > > over
> > > > > and
> > > > > > over, instead of reusing connections.
> > > > > >
> > > > > > To make sure it is not an issue with my application, I have
> > created a
> > > > new
> > > > > > project with only one class:
> > > > > >
> > > > > > /**
> > > > > > *
> > > > > > * @author witoldsz
> > > > > > */
> > > > > > @Path("/resource")
> > > > > > @Stateless
> > > > > > public class Endpoint {
> > > > > >
> > > > > > @Resource(name = "jdbc/resource")
> > > > > > private DataSource ds;
> > > > > >
> > > > > > @GET
> > > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > > public String hello() throws Exception {
> > > > > > try (Connection conn = ds.getConnection()) {
> > > > > > //do nothing, just fetch and close the connection
> > > > > > }
> > > > > > return "Hello!";
> > > > > > }
> > > > > > }
> > > > > >
> > > > > > The JDBC resource is actually an Oracle 11g.
> > > > > >
> > > > > > Now hear this: each time I do a HTTP GET:
> > > > > > http://localhost:8080/app/resource
> > > > > > I can see a new connection being established and the previous one
> > > > closed,
> > > > > > used iptraf to monitor.
> > > > > >
> > > > > > Also, the:
> > > > > > --
> > > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > > FROM v$session
> > > > > > WHERE username = 'MY_USER_NAME'
> > > > > > ORDER BY logon_time;
> > > > > > --
> > > > > > result is showing new value in "serial#" column after each HTTP
> > GET.
> > > > > >
> > > > > > What is worth noticing, the already used connection does not get
> > > > > destroyed
> > > > > > immediately after use. It waits in the pool and is destroyed when
> > new
> > > > one
> > > > > > is created. And the new one waits there until yet another one is
> > > > created
> > > > > > and it goes like this over and over.
> > > > > >
> > > > > > I am sure this is the reason my application works really, I mean
> > > really
> > > > > > slow, when I am developing from home with remote, over VPN,
> > database.
> > > > > >
> > > > > > My environment:
> > > > > > tomee.xml:
> > > > > >
> > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > > UserName test
> > > > > > Password test
> > > > > > JtaManaged true
> > > > > > </Resource>
> > > > > >
> > > > > > OS:
> > > > > > Ubuntu 12.10 64 bit,
> > > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > > TomEE 1.5.1 JAX-RS
> > > > > > tested also using:
> > > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > > >
> > > > > >
> > > > > > I am attaching source code of the webapp (3.6kB), just configure
> a
> > > > > > "jdbc/resource" datasource in tomee.xml and deploy it to run it
> on
> > > your
> > > > > own.
> > > > > >
> > > > > > Thanks,
> > > > > > Witold Szczerba
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Romain Manni-Bucau <rm...@gmail.com>.
a side note about: you can use bonecp with tomee (with some jar addition)
or even your own system
we can't describe all systems so we do our best with the default ones
(dbcp/tomcat). Since tomcat doc is good we thought it was not mandatory to
copy it over tomee website.
If you think it should be done feel free to send us patch on the doc (using
online editing system for instance)
PS: tomcat system is a bit faster than dbcp once configured but was
originally designed for high load
*Romain Manni-Bucau*
*Twitter: @rmannibucau <https://twitter.com/rmannibucau>*
*Blog: **http://rmannibucau.wordpress.com/*<http://rmannibucau.wordpress.com/>
*LinkedIn: **http://fr.linkedin.com/in/rmannibucau*
*Github: https://github.com/rmannibucau*
2013/2/28 John D. Ament <jo...@gmail.com>
> Witold,
>
> The entry I added is based on this page in the Tomcat docs:
>
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
> In addition, at the bottom of the TomEE datasource page you can see it
> noted (though it's somewhat hidden)
> http://tomee.apache.org/common-datasource-configurations.html
>
> Though I do +1 your sentiment with the configuration. TomEE ends up
> reliant on the upstream project's docs.
>
> John
>
>
>
> On Thu, Feb 28, 2013 at 6:10 AM, Witold Szczerba <pljosh.mail@gmail.com
> >wrote:
>
> > One thing I can say just now is that setting DataSourceCreator to DBCP
> > makes a HUGE difference in performance. That connection pool works as a
> one
> > would expect any connection pool to work.
> > Have not tried the extra interceptors on top of Tomcat Connection Pool,
> but
> > as long as I can keep far away from it, I am fine.
> >
> > The only thing I would suggest for TomEE project for now would be to
> > supplement the documentation, so every one, picking TomEE as their app
> > server of choice, would be warned about the issue.
> >
> > Thanks,
> > Witold Szczerba
> >
> >
> > On 28 February 2013 02:39, John D. Ament <jo...@gmail.com> wrote:
> >
> > > When I run your example on my local machine, using Tomee 1.5.1 w/
> jax-rs
> > > and mysql, I get the same results. However, switching to dbcp pooling
> > > changes that.
> > >
> > > <Resource id="jdbc/resource" type="DataSource">
> > > JdbcDriver com.mysql.jdbc.Driver
> > > JdbcUrl jdbc:mysql://localhost:3306/ex
> > > UserName ex
> > > Password abc123
> > > JtaManaged true
> > > DataSourceCreator dbcp
> > > </Resource>
> > >
> > > And sorry, missing the brackets around your try.
> > >
> > > John
> > >
> > >
> > > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <
> pljosh.mail@gmail.com
> > > >wrote:
> > >
> > > > Well, all the properties you have mentioned have sane defaults,
> that's
> > > why
> > > > I did not specified them.
> > > > Also, the try (...) statement (called try-with-resources), introduced
> > in
> > > > Java7, is calling #close method, so the connection is handled
> > correctly.
> > > >
> > > > Regards,
> > > > Witold Szczerba
> > > > ---
> > > > Sent from my mobile phone.
> > > > On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com>
> > wrote:
> > > >
> > > > > It seems like you don't have initialSize, maxIdle, maxActive,
> minIdle
> > > or
> > > > > maxWait set.
> > > > >
> > > > > Also, I don't think you're returning the connection to the pool.
> > > > >
> > > > >
> > > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > > pljosh.mail@gmail.com
> > > > > >wrote:
> > > > >
> > > > > > Hi,
> > > > > > I am facing something very strange. I am not 100% sure if this is
> > > true,
> > > > > > but everything looks like the connection pool actually reconnects
> > to
> > > > > > database, I mean it closes the TCP connection and creates new
> one,
> > > over
> > > > > and
> > > > > > over, instead of reusing connections.
> > > > > >
> > > > > > To make sure it is not an issue with my application, I have
> > created a
> > > > new
> > > > > > project with only one class:
> > > > > >
> > > > > > /**
> > > > > > *
> > > > > > * @author witoldsz
> > > > > > */
> > > > > > @Path("/resource")
> > > > > > @Stateless
> > > > > > public class Endpoint {
> > > > > >
> > > > > > @Resource(name = "jdbc/resource")
> > > > > > private DataSource ds;
> > > > > >
> > > > > > @GET
> > > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > > public String hello() throws Exception {
> > > > > > try (Connection conn = ds.getConnection()) {
> > > > > > //do nothing, just fetch and close the connection
> > > > > > }
> > > > > > return "Hello!";
> > > > > > }
> > > > > > }
> > > > > >
> > > > > > The JDBC resource is actually an Oracle 11g.
> > > > > >
> > > > > > Now hear this: each time I do a HTTP GET:
> > > > > > http://localhost:8080/app/resource
> > > > > > I can see a new connection being established and the previous one
> > > > closed,
> > > > > > used iptraf to monitor.
> > > > > >
> > > > > > Also, the:
> > > > > > --
> > > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > > FROM v$session
> > > > > > WHERE username = 'MY_USER_NAME'
> > > > > > ORDER BY logon_time;
> > > > > > --
> > > > > > result is showing new value in "serial#" column after each HTTP
> > GET.
> > > > > >
> > > > > > What is worth noticing, the already used connection does not get
> > > > > destroyed
> > > > > > immediately after use. It waits in the pool and is destroyed when
> > new
> > > > one
> > > > > > is created. And the new one waits there until yet another one is
> > > > created
> > > > > > and it goes like this over and over.
> > > > > >
> > > > > > I am sure this is the reason my application works really, I mean
> > > really
> > > > > > slow, when I am developing from home with remote, over VPN,
> > database.
> > > > > >
> > > > > > My environment:
> > > > > > tomee.xml:
> > > > > >
> > > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > > UserName test
> > > > > > Password test
> > > > > > JtaManaged true
> > > > > > </Resource>
> > > > > >
> > > > > > OS:
> > > > > > Ubuntu 12.10 64 bit,
> > > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > > TomEE 1.5.1 JAX-RS
> > > > > > tested also using:
> > > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > > >
> > > > > >
> > > > > > I am attaching source code of the webapp (3.6kB), just configure
> a
> > > > > > "jdbc/resource" datasource in tomee.xml and deploy it to run it
> on
> > > your
> > > > > own.
> > > > > >
> > > > > > Thanks,
> > > > > > Witold Szczerba
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by "John D. Ament" <jo...@gmail.com>.
Witold,
The entry I added is based on this page in the Tomcat docs:
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
In addition, at the bottom of the TomEE datasource page you can see it
noted (though it's somewhat hidden)
http://tomee.apache.org/common-datasource-configurations.html
Though I do +1 your sentiment with the configuration. TomEE ends up
reliant on the upstream project's docs.
John
On Thu, Feb 28, 2013 at 6:10 AM, Witold Szczerba <pl...@gmail.com>wrote:
> One thing I can say just now is that setting DataSourceCreator to DBCP
> makes a HUGE difference in performance. That connection pool works as a one
> would expect any connection pool to work.
> Have not tried the extra interceptors on top of Tomcat Connection Pool, but
> as long as I can keep far away from it, I am fine.
>
> The only thing I would suggest for TomEE project for now would be to
> supplement the documentation, so every one, picking TomEE as their app
> server of choice, would be warned about the issue.
>
> Thanks,
> Witold Szczerba
>
>
> On 28 February 2013 02:39, John D. Ament <jo...@gmail.com> wrote:
>
> > When I run your example on my local machine, using Tomee 1.5.1 w/ jax-rs
> > and mysql, I get the same results. However, switching to dbcp pooling
> > changes that.
> >
> > <Resource id="jdbc/resource" type="DataSource">
> > JdbcDriver com.mysql.jdbc.Driver
> > JdbcUrl jdbc:mysql://localhost:3306/ex
> > UserName ex
> > Password abc123
> > JtaManaged true
> > DataSourceCreator dbcp
> > </Resource>
> >
> > And sorry, missing the brackets around your try.
> >
> > John
> >
> >
> > On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <pljosh.mail@gmail.com
> > >wrote:
> >
> > > Well, all the properties you have mentioned have sane defaults, that's
> > why
> > > I did not specified them.
> > > Also, the try (...) statement (called try-with-resources), introduced
> in
> > > Java7, is calling #close method, so the connection is handled
> correctly.
> > >
> > > Regards,
> > > Witold Szczerba
> > > ---
> > > Sent from my mobile phone.
> > > On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com>
> wrote:
> > >
> > > > It seems like you don't have initialSize, maxIdle, maxActive, minIdle
> > or
> > > > maxWait set.
> > > >
> > > > Also, I don't think you're returning the connection to the pool.
> > > >
> > > >
> > > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> > pljosh.mail@gmail.com
> > > > >wrote:
> > > >
> > > > > Hi,
> > > > > I am facing something very strange. I am not 100% sure if this is
> > true,
> > > > > but everything looks like the connection pool actually reconnects
> to
> > > > > database, I mean it closes the TCP connection and creates new one,
> > over
> > > > and
> > > > > over, instead of reusing connections.
> > > > >
> > > > > To make sure it is not an issue with my application, I have
> created a
> > > new
> > > > > project with only one class:
> > > > >
> > > > > /**
> > > > > *
> > > > > * @author witoldsz
> > > > > */
> > > > > @Path("/resource")
> > > > > @Stateless
> > > > > public class Endpoint {
> > > > >
> > > > > @Resource(name = "jdbc/resource")
> > > > > private DataSource ds;
> > > > >
> > > > > @GET
> > > > > @Produces(MediaType.TEXT_PLAIN)
> > > > > public String hello() throws Exception {
> > > > > try (Connection conn = ds.getConnection()) {
> > > > > //do nothing, just fetch and close the connection
> > > > > }
> > > > > return "Hello!";
> > > > > }
> > > > > }
> > > > >
> > > > > The JDBC resource is actually an Oracle 11g.
> > > > >
> > > > > Now hear this: each time I do a HTTP GET:
> > > > > http://localhost:8080/app/resource
> > > > > I can see a new connection being established and the previous one
> > > closed,
> > > > > used iptraf to monitor.
> > > > >
> > > > > Also, the:
> > > > > --
> > > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > > FROM v$session
> > > > > WHERE username = 'MY_USER_NAME'
> > > > > ORDER BY logon_time;
> > > > > --
> > > > > result is showing new value in "serial#" column after each HTTP
> GET.
> > > > >
> > > > > What is worth noticing, the already used connection does not get
> > > > destroyed
> > > > > immediately after use. It waits in the pool and is destroyed when
> new
> > > one
> > > > > is created. And the new one waits there until yet another one is
> > > created
> > > > > and it goes like this over and over.
> > > > >
> > > > > I am sure this is the reason my application works really, I mean
> > really
> > > > > slow, when I am developing from home with remote, over VPN,
> database.
> > > > >
> > > > > My environment:
> > > > > tomee.xml:
> > > > >
> > > > > <Resource id="jdbc/resource" type="DataSource">
> > > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > > UserName test
> > > > > Password test
> > > > > JtaManaged true
> > > > > </Resource>
> > > > >
> > > > > OS:
> > > > > Ubuntu 12.10 64 bit,
> > > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > > TomEE 1.5.1 JAX-RS
> > > > > tested also using:
> > > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > > >
> > > > >
> > > > > I am attaching source code of the webapp (3.6kB), just configure a
> > > > > "jdbc/resource" datasource in tomee.xml and deploy it to run it on
> > your
> > > > own.
> > > > >
> > > > > Thanks,
> > > > > Witold Szczerba
> > > > >
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Witold Szczerba <pl...@gmail.com>.
One thing I can say just now is that setting DataSourceCreator to DBCP
makes a HUGE difference in performance. That connection pool works as a one
would expect any connection pool to work.
Have not tried the extra interceptors on top of Tomcat Connection Pool, but
as long as I can keep far away from it, I am fine.
The only thing I would suggest for TomEE project for now would be to
supplement the documentation, so every one, picking TomEE as their app
server of choice, would be warned about the issue.
Thanks,
Witold Szczerba
On 28 February 2013 02:39, John D. Ament <jo...@gmail.com> wrote:
> When I run your example on my local machine, using Tomee 1.5.1 w/ jax-rs
> and mysql, I get the same results. However, switching to dbcp pooling
> changes that.
>
> <Resource id="jdbc/resource" type="DataSource">
> JdbcDriver com.mysql.jdbc.Driver
> JdbcUrl jdbc:mysql://localhost:3306/ex
> UserName ex
> Password abc123
> JtaManaged true
> DataSourceCreator dbcp
> </Resource>
>
> And sorry, missing the brackets around your try.
>
> John
>
>
> On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <pljosh.mail@gmail.com
> >wrote:
>
> > Well, all the properties you have mentioned have sane defaults, that's
> why
> > I did not specified them.
> > Also, the try (...) statement (called try-with-resources), introduced in
> > Java7, is calling #close method, so the connection is handled correctly.
> >
> > Regards,
> > Witold Szczerba
> > ---
> > Sent from my mobile phone.
> > On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com> wrote:
> >
> > > It seems like you don't have initialSize, maxIdle, maxActive, minIdle
> or
> > > maxWait set.
> > >
> > > Also, I don't think you're returning the connection to the pool.
> > >
> > >
> > > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <
> pljosh.mail@gmail.com
> > > >wrote:
> > >
> > > > Hi,
> > > > I am facing something very strange. I am not 100% sure if this is
> true,
> > > > but everything looks like the connection pool actually reconnects to
> > > > database, I mean it closes the TCP connection and creates new one,
> over
> > > and
> > > > over, instead of reusing connections.
> > > >
> > > > To make sure it is not an issue with my application, I have created a
> > new
> > > > project with only one class:
> > > >
> > > > /**
> > > > *
> > > > * @author witoldsz
> > > > */
> > > > @Path("/resource")
> > > > @Stateless
> > > > public class Endpoint {
> > > >
> > > > @Resource(name = "jdbc/resource")
> > > > private DataSource ds;
> > > >
> > > > @GET
> > > > @Produces(MediaType.TEXT_PLAIN)
> > > > public String hello() throws Exception {
> > > > try (Connection conn = ds.getConnection()) {
> > > > //do nothing, just fetch and close the connection
> > > > }
> > > > return "Hello!";
> > > > }
> > > > }
> > > >
> > > > The JDBC resource is actually an Oracle 11g.
> > > >
> > > > Now hear this: each time I do a HTTP GET:
> > > > http://localhost:8080/app/resource
> > > > I can see a new connection being established and the previous one
> > closed,
> > > > used iptraf to monitor.
> > > >
> > > > Also, the:
> > > > --
> > > > SELECT username, sid, serial#, logon_time, machine, port
> > > > FROM v$session
> > > > WHERE username = 'MY_USER_NAME'
> > > > ORDER BY logon_time;
> > > > --
> > > > result is showing new value in "serial#" column after each HTTP GET.
> > > >
> > > > What is worth noticing, the already used connection does not get
> > > destroyed
> > > > immediately after use. It waits in the pool and is destroyed when new
> > one
> > > > is created. And the new one waits there until yet another one is
> > created
> > > > and it goes like this over and over.
> > > >
> > > > I am sure this is the reason my application works really, I mean
> really
> > > > slow, when I am developing from home with remote, over VPN, database.
> > > >
> > > > My environment:
> > > > tomee.xml:
> > > >
> > > > <Resource id="jdbc/resource" type="DataSource">
> > > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > > UserName test
> > > > Password test
> > > > JtaManaged true
> > > > </Resource>
> > > >
> > > > OS:
> > > > Ubuntu 12.10 64 bit,
> > > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > > TomEE 1.5.1 JAX-RS
> > > > tested also using:
> > > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > > >
> > > >
> > > > I am attaching source code of the webapp (3.6kB), just configure a
> > > > "jdbc/resource" datasource in tomee.xml and deploy it to run it on
> your
> > > own.
> > > >
> > > > Thanks,
> > > > Witold Szczerba
> > > >
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by "John D. Ament" <jo...@gmail.com>.
When I run your example on my local machine, using Tomee 1.5.1 w/ jax-rs
and mysql, I get the same results. However, switching to dbcp pooling
changes that.
<Resource id="jdbc/resource" type="DataSource">
JdbcDriver com.mysql.jdbc.Driver
JdbcUrl jdbc:mysql://localhost:3306/ex
UserName ex
Password abc123
JtaManaged true
DataSourceCreator dbcp
</Resource>
And sorry, missing the brackets around your try.
John
On Wed, Feb 27, 2013 at 7:58 PM, Witold Szczerba <pl...@gmail.com>wrote:
> Well, all the properties you have mentioned have sane defaults, that's why
> I did not specified them.
> Also, the try (...) statement (called try-with-resources), introduced in
> Java7, is calling #close method, so the connection is handled correctly.
>
> Regards,
> Witold Szczerba
> ---
> Sent from my mobile phone.
> On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com> wrote:
>
> > It seems like you don't have initialSize, maxIdle, maxActive, minIdle or
> > maxWait set.
> >
> > Also, I don't think you're returning the connection to the pool.
> >
> >
> > On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <pljosh.mail@gmail.com
> > >wrote:
> >
> > > Hi,
> > > I am facing something very strange. I am not 100% sure if this is true,
> > > but everything looks like the connection pool actually reconnects to
> > > database, I mean it closes the TCP connection and creates new one, over
> > and
> > > over, instead of reusing connections.
> > >
> > > To make sure it is not an issue with my application, I have created a
> new
> > > project with only one class:
> > >
> > > /**
> > > *
> > > * @author witoldsz
> > > */
> > > @Path("/resource")
> > > @Stateless
> > > public class Endpoint {
> > >
> > > @Resource(name = "jdbc/resource")
> > > private DataSource ds;
> > >
> > > @GET
> > > @Produces(MediaType.TEXT_PLAIN)
> > > public String hello() throws Exception {
> > > try (Connection conn = ds.getConnection()) {
> > > //do nothing, just fetch and close the connection
> > > }
> > > return "Hello!";
> > > }
> > > }
> > >
> > > The JDBC resource is actually an Oracle 11g.
> > >
> > > Now hear this: each time I do a HTTP GET:
> > > http://localhost:8080/app/resource
> > > I can see a new connection being established and the previous one
> closed,
> > > used iptraf to monitor.
> > >
> > > Also, the:
> > > --
> > > SELECT username, sid, serial#, logon_time, machine, port
> > > FROM v$session
> > > WHERE username = 'MY_USER_NAME'
> > > ORDER BY logon_time;
> > > --
> > > result is showing new value in "serial#" column after each HTTP GET.
> > >
> > > What is worth noticing, the already used connection does not get
> > destroyed
> > > immediately after use. It waits in the pool and is destroyed when new
> one
> > > is created. And the new one waits there until yet another one is
> created
> > > and it goes like this over and over.
> > >
> > > I am sure this is the reason my application works really, I mean really
> > > slow, when I am developing from home with remote, over VPN, database.
> > >
> > > My environment:
> > > tomee.xml:
> > >
> > > <Resource id="jdbc/resource" type="DataSource">
> > > JdbcDriver oracle.jdbc.driver.OracleDriver
> > > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > > UserName test
> > > Password test
> > > JtaManaged true
> > > </Resource>
> > >
> > > OS:
> > > Ubuntu 12.10 64 bit,
> > > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > > TomEE 1.5.1 JAX-RS
> > > tested also using:
> > > TomEE 1.6.0-20130226.041154-13-jaxrs
> > >
> > >
> > > I am attaching source code of the webapp (3.6kB), just configure a
> > > "jdbc/resource" datasource in tomee.xml and deploy it to run it on your
> > own.
> > >
> > > Thanks,
> > > Witold Szczerba
> > >
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Witold Szczerba <pl...@gmail.com>.
Well, all the properties you have mentioned have sane defaults, that's why
I did not specified them.
Also, the try (...) statement (called try-with-resources), introduced in
Java7, is calling #close method, so the connection is handled correctly.
Regards,
Witold Szczerba
---
Sent from my mobile phone.
On Feb 28, 2013 1:12 AM, "John D. Ament" <jo...@gmail.com> wrote:
> It seems like you don't have initialSize, maxIdle, maxActive, minIdle or
> maxWait set.
>
> Also, I don't think you're returning the connection to the pool.
>
>
> On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <pljosh.mail@gmail.com
> >wrote:
>
> > Hi,
> > I am facing something very strange. I am not 100% sure if this is true,
> > but everything looks like the connection pool actually reconnects to
> > database, I mean it closes the TCP connection and creates new one, over
> and
> > over, instead of reusing connections.
> >
> > To make sure it is not an issue with my application, I have created a new
> > project with only one class:
> >
> > /**
> > *
> > * @author witoldsz
> > */
> > @Path("/resource")
> > @Stateless
> > public class Endpoint {
> >
> > @Resource(name = "jdbc/resource")
> > private DataSource ds;
> >
> > @GET
> > @Produces(MediaType.TEXT_PLAIN)
> > public String hello() throws Exception {
> > try (Connection conn = ds.getConnection()) {
> > //do nothing, just fetch and close the connection
> > }
> > return "Hello!";
> > }
> > }
> >
> > The JDBC resource is actually an Oracle 11g.
> >
> > Now hear this: each time I do a HTTP GET:
> > http://localhost:8080/app/resource
> > I can see a new connection being established and the previous one closed,
> > used iptraf to monitor.
> >
> > Also, the:
> > --
> > SELECT username, sid, serial#, logon_time, machine, port
> > FROM v$session
> > WHERE username = 'MY_USER_NAME'
> > ORDER BY logon_time;
> > --
> > result is showing new value in "serial#" column after each HTTP GET.
> >
> > What is worth noticing, the already used connection does not get
> destroyed
> > immediately after use. It waits in the pool and is destroyed when new one
> > is created. And the new one waits there until yet another one is created
> > and it goes like this over and over.
> >
> > I am sure this is the reason my application works really, I mean really
> > slow, when I am developing from home with remote, over VPN, database.
> >
> > My environment:
> > tomee.xml:
> >
> > <Resource id="jdbc/resource" type="DataSource">
> > JdbcDriver oracle.jdbc.driver.OracleDriver
> > JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> > UserName test
> > Password test
> > JtaManaged true
> > </Resource>
> >
> > OS:
> > Ubuntu 12.10 64 bit,
> > Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> > TomEE 1.5.1 JAX-RS
> > tested also using:
> > TomEE 1.6.0-20130226.041154-13-jaxrs
> >
> >
> > I am attaching source code of the webapp (3.6kB), just configure a
> > "jdbc/resource" datasource in tomee.xml and deploy it to run it on your
> own.
> >
> > Thanks,
> > Witold Szczerba
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by "John D. Ament" <jo...@gmail.com>.
It seems like you don't have initialSize, maxIdle, maxActive, minIdle or
maxWait set.
Also, I don't think you're returning the connection to the pool.
On Wed, Feb 27, 2013 at 6:37 PM, Witold Szczerba <pl...@gmail.com>wrote:
> Hi,
> I am facing something very strange. I am not 100% sure if this is true,
> but everything looks like the connection pool actually reconnects to
> database, I mean it closes the TCP connection and creates new one, over and
> over, instead of reusing connections.
>
> To make sure it is not an issue with my application, I have created a new
> project with only one class:
>
> /**
> *
> * @author witoldsz
> */
> @Path("/resource")
> @Stateless
> public class Endpoint {
>
> @Resource(name = "jdbc/resource")
> private DataSource ds;
>
> @GET
> @Produces(MediaType.TEXT_PLAIN)
> public String hello() throws Exception {
> try (Connection conn = ds.getConnection()) {
> //do nothing, just fetch and close the connection
> }
> return "Hello!";
> }
> }
>
> The JDBC resource is actually an Oracle 11g.
>
> Now hear this: each time I do a HTTP GET:
> http://localhost:8080/app/resource
> I can see a new connection being established and the previous one closed,
> used iptraf to monitor.
>
> Also, the:
> --
> SELECT username, sid, serial#, logon_time, machine, port
> FROM v$session
> WHERE username = 'MY_USER_NAME'
> ORDER BY logon_time;
> --
> result is showing new value in "serial#" column after each HTTP GET.
>
> What is worth noticing, the already used connection does not get destroyed
> immediately after use. It waits in the pool and is destroyed when new one
> is created. And the new one waits there until yet another one is created
> and it goes like this over and over.
>
> I am sure this is the reason my application works really, I mean really
> slow, when I am developing from home with remote, over VPN, database.
>
> My environment:
> tomee.xml:
>
> <Resource id="jdbc/resource" type="DataSource">
> JdbcDriver oracle.jdbc.driver.OracleDriver
> JdbcUrl jdbc:oracle:thin:@10.169.100.179:1521:orcl
> UserName test
> Password test
> JtaManaged true
> </Resource>
>
> OS:
> Ubuntu 12.10 64 bit,
> Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
> TomEE 1.5.1 JAX-RS
> tested also using:
> TomEE 1.6.0-20130226.041154-13-jaxrs
>
>
> I am attaching source code of the webapp (3.6kB), just configure a
> "jdbc/resource" datasource in tomee.xml and deploy it to run it on your own.
>
> Thanks,
> Witold Szczerba
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Romain Manni-Bucau <rm...@gmail.com>.
check through JMX you use StatementCache interceptor of tomcat jdbc
*Romain Manni-Bucau*
*Twitter: @rmannibucau <https://twitter.com/rmannibucau>*
*Blog: **http://rmannibucau.wordpress.com/*<http://rmannibucau.wordpress.com/>
*LinkedIn: **http://fr.linkedin.com/in/rmannibucau*
*Github: https://github.com/rmannibucau*
2013/2/28 Witold Szczerba <pl...@gmail.com>
> Hi,
> I have just read the JavaDoc of ConnectionState and StatementFinalizer and
> neither of them are talking about the problem I have described.
>
> I do not have problems with connections that I am forgetting to close, I do
> not need any kind of helper to avoid connection leaks. My problem is that I
> am using DataSource correctly and the JDBC Pool of TomEE is misbehaving. It
> does pool connections but it does not reuse them, so entire concept is just
> plain broken.
>
>
> Regards,
> Witold Szczerba
>
>
> On 28 February 2013 07:39, zeeman <ha...@fastmail.us> wrote:
>
> > I use Tomcat pool with Tomee, in resources.xml I have:
> >
> > jdbcInterceptors=ConnectionState;StatementFinalizer
> >
> > I think Tomcat is the default connection pool provider, that latter
> option
> > in interceptor list takes care of closing unneeded connections. I don't
> see
> > that in your config. Give it a shot. Be sure you're using the latest
> > driver.
> >
> >
> > Also, I use below settings to help clean up unneeded or closed
> connections
> > (I included all of my options):
> >
> > JtaManaged true
> > MaxActive 100
> > InitialSize 2
> > MaxIdle 25
> > MinIdle 2
> > suspectTimeout 60
> > maxWait 10000
> > TimeBetweenEvictionRunsMillis 60000
> > minEvictableIdleTimeMillis 60000
> > testOnBorrow true
> > validationInterval 30000
> > removeAbandoned true
> > removeAbandonedTimeout 60
> > logAbandoned true
> > ValidationQuery SELECT 1
> >
> >
> >
> > --
> > View this message in context:
> >
> http://openejb.979440.n4.nabble.com/JDBC-Connection-Pool-reconnects-on-each-connection-usage-tp4661066p4661072.html
> > Sent from the OpenEJB User mailing list archive at Nabble.com.
> >
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Witold Szczerba <pl...@gmail.com>.
Hi,
I have just read the JavaDoc of ConnectionState and StatementFinalizer and
neither of them are talking about the problem I have described.
I do not have problems with connections that I am forgetting to close, I do
not need any kind of helper to avoid connection leaks. My problem is that I
am using DataSource correctly and the JDBC Pool of TomEE is misbehaving. It
does pool connections but it does not reuse them, so entire concept is just
plain broken.
Regards,
Witold Szczerba
On 28 February 2013 07:39, zeeman <ha...@fastmail.us> wrote:
> I use Tomcat pool with Tomee, in resources.xml I have:
>
> jdbcInterceptors=ConnectionState;StatementFinalizer
>
> I think Tomcat is the default connection pool provider, that latter option
> in interceptor list takes care of closing unneeded connections. I don't see
> that in your config. Give it a shot. Be sure you're using the latest
> driver.
>
>
> Also, I use below settings to help clean up unneeded or closed connections
> (I included all of my options):
>
> JtaManaged true
> MaxActive 100
> InitialSize 2
> MaxIdle 25
> MinIdle 2
> suspectTimeout 60
> maxWait 10000
> TimeBetweenEvictionRunsMillis 60000
> minEvictableIdleTimeMillis 60000
> testOnBorrow true
> validationInterval 30000
> removeAbandoned true
> removeAbandonedTimeout 60
> logAbandoned true
> ValidationQuery SELECT 1
>
>
>
> --
> View this message in context:
> http://openejb.979440.n4.nabble.com/JDBC-Connection-Pool-reconnects-on-each-connection-usage-tp4661066p4661072.html
> Sent from the OpenEJB User mailing list archive at Nabble.com.
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by Romain Manni-Bucau <rm...@gmail.com>.
Hi
Connecting to tomee through jmx you ll be able to see the real config (in
openejb mbeans)
Jdbcinterceptor will handle it.
That said close() doesnt mean the tx is closed. Typically if the datasource
is jta it is closed latter to be able to rollback.
Le 28 févr. 2013 07:40, "zeeman" <ha...@fastmail.us> a écrit :
> I use Tomcat pool with Tomee, in resources.xml I have:
>
> jdbcInterceptors=ConnectionState;StatementFinalizer
>
> I think Tomcat is the default connection pool provider, that latter option
> in interceptor list takes care of closing unneeded connections. I don't see
> that in your config. Give it a shot. Be sure you're using the latest
> driver.
>
>
> Also, I use below settings to help clean up unneeded or closed connections
> (I included all of my options):
>
> JtaManaged true
> MaxActive 100
> InitialSize 2
> MaxIdle 25
> MinIdle 2
> suspectTimeout 60
> maxWait 10000
> TimeBetweenEvictionRunsMillis 60000
> minEvictableIdleTimeMillis 60000
> testOnBorrow true
> validationInterval 30000
> removeAbandoned true
> removeAbandonedTimeout 60
> logAbandoned true
> ValidationQuery SELECT 1
>
>
>
> --
> View this message in context:
> http://openejb.979440.n4.nabble.com/JDBC-Connection-Pool-reconnects-on-each-connection-usage-tp4661066p4661072.html
> Sent from the OpenEJB User mailing list archive at Nabble.com.
>
Re: JDBC Connection Pool reconnects on each connection usage
Posted by zeeman <ha...@fastmail.us>.
I use Tomcat pool with Tomee, in resources.xml I have:
jdbcInterceptors=ConnectionState;StatementFinalizer
I think Tomcat is the default connection pool provider, that latter option
in interceptor list takes care of closing unneeded connections. I don't see
that in your config. Give it a shot. Be sure you're using the latest driver.
Also, I use below settings to help clean up unneeded or closed connections
(I included all of my options):
JtaManaged true
MaxActive 100
InitialSize 2
MaxIdle 25
MinIdle 2
suspectTimeout 60
maxWait 10000
TimeBetweenEvictionRunsMillis 60000
minEvictableIdleTimeMillis 60000
testOnBorrow true
validationInterval 30000
removeAbandoned true
removeAbandonedTimeout 60
logAbandoned true
ValidationQuery SELECT 1
--
View this message in context: http://openejb.979440.n4.nabble.com/JDBC-Connection-Pool-reconnects-on-each-connection-usage-tp4661066p4661072.html
Sent from the OpenEJB User mailing list archive at Nabble.com.