Web application jconnector memory Leak

[manhatta25]

Our website has thousands of hits per day. We are using the latest versions of Mysql, Tomcat and Java. We have also been using jconnector 3.0.10. With 3.0.10, memory stayed in reasonable limits. The same happens when using any of 3.0.x drivers. However, when we upgraded to 5, we got a serious memory issue. Memory keeps on rising and never decreases leading to "out of memory". Same happens with any driver after 3.1.

We also use connection pooling. On each website hit, the application gets a connection from the pool, opens many resultsets and statements, then closes them and returns the connection back to the pool.

Now we are stuck with jconnector 3.0.10 since with 3.1.x onwards, memory gets out of limits.

Please advise as this is a long-time problem we have not been able to resolve.

Thanks in advance,

Tomas.

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> -----Original Message-----
> From: manhatta25 [mailto:manhatta25@...]
> Sent: Thursday, August 16, 2007 8:00 AM
> To: java@...
> Subject: Web application jconnector memory Leak
>
>
> Our website has thousands of hits per day. We are using the
> latest versions of Mysql, Tomcat and Java. We have also been
> using jconnector 3.0.10. With 3.0.10, memory stayed in
> reasonable limits. The same happens when using any of 3.0.x
> drivers. However, when we upgraded to 5, we got a serious
> memory issue. Memory keeps on rising and never decreases
> leading to "out of memory". Same happens with any driver after 3.1.
>
> We also use connection pooling. On each website hit, the
> application gets a connection from the pool, opens many
> resultsets and statements, then closes them and returns the
> connection back to the pool.
>
> Now we are stuck with jconnector 3.0.10 since with 3.1.x
> onwards, memory gets out of limits.
>
> Please advise as this is a long-time problem we have not been
> able to resolve.
>
> Thanks in advance,
>
> Tomas.

Tomas,

My guess is that you're leaking statements and/or result sets. Version 3.0 was much more tolerant of this, because it wasn't JDBC-compliant. The JDBC specification requires that drivers keep track of open statements and result sets, and close them when the "parent" object is closed.

Because of this, connections will have lists of open statements, and statements will have lists of open result sets (so that the driver can close them if the application doesn't).

This does have a cost, however, in that the memory used by those instances will not be freed until the "parent" is closed, either implicitly (by the driver), or explicitly (by the application). When a connection pool is in place, this "feature" is even more incidious, as the connection isn't physically closed for quite some time, so statement instances tend to pile up.

If you add "dontTrackOpenResources=true" to your JDBC URL configuration parameters, and the problem goes away, then _somewhere_ your application isn't closing result sets or statements that it creates. The JDBC driver can help tell you where this might be, if you add "useUsageAdvisor=true" to the URL, the driver will track where result sets and statements were created, and then not explicitly closed by the application. You'll have to set your connection pool to have 0 or low idle times so that the physical connection is eventually closed however, to catch statement leaks.

        -Mark
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)

iD8DBQFGxjQ2tvXNTca6JD8RAgf7AJ9Cki2+g0EGcQEm9UFlBv/M5BRyMQCgslK4
FRqXoO/oInc9hJPL9W9O5Zc=
=Q0Sa
-----END PGP SIGNATURE-----


--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/java?unsub=lists@...