Thread: Memory Leak / Prepared Statement
Hi, I noticed several postings a month ago regarding a memory leak brought about by the use of Thread.Local in jdbc2/PreparedStatement.java. I have what seems like a similar problem, but I am not sure. I have applied the patch, but I still have my problem (mine is not really associated with DateFormat usage.) I am running Enhydra Application Server 3.1 on top of Postgres and I have been running an application that tries to match text strings to items in my database. It has been running fine for months until I recently made a modification that uses a lot of "LIKE" statements (i.e. anywhere from 2-200 at a time.) Performance is fine, but memory starts getting eaten up and I eventually hits an OutOfMemoryError. The bigger the prepared statement (i.e. the more LIKEs) the more memory is eaten and the faster I run out of memory. Oddly enough, before I added the LIKE statements, I was running fine and, although I used a lot of prepared statements (the app server did), memory was not being eaten like this. Has anyone had a similar experience and is it related to the Thread.Local problem? Does anyone know if this is addressed in the 1.4.0 beta JDK or is it scheduled to be addressed in an upcoming Postgresql release? Any help would be greatly appreciated. Thanks in advance. John Cook
My guess is that this is unlikely to be the result of the ThreadLocal issues and also I doubt 1.4 will have any effect. This sounds like a memory leak which could be in the driver or in your application code. I also doubt that the use of LIKE is the problem as the JDBC code doesn't parse the SQL so it doesn't know if you are using LIKE or not. To fix this we would either need a reproducable test case that you could submit such that we could reproduce the problem and fix it, or you could use a java memory profiler to track down what objects are being allocated and not released. I personally like OptimizeIt as it has helped me solve quite a few memory leak problems with java code. thanks, --Barry John Cook wrote: > Hi, > > I noticed several postings a month ago regarding a memory leak brought > about by the use of Thread.Local in jdbc2/PreparedStatement.java. I > have what seems like a similar problem, but I am not sure. I have > applied the patch, but I still have my problem (mine is not really > associated with DateFormat usage.) > > I am running Enhydra Application Server 3.1 on top of Postgres and I > have been running an application that tries to match text strings to > items in my database. It has been running fine for months until > I recently made a modification that uses a lot of "LIKE" statements > (i.e. anywhere from 2-200 at a time.) Performance is fine, but memory > starts getting eaten up and I eventually hits an OutOfMemoryError. The > bigger the prepared statement (i.e. the more LIKEs) the more memory is > eaten and the faster I run out of memory. Oddly enough, before I added > the LIKE statements, I was running fine and, although I used a lot of > prepared statements (the app server did), memory was not being eaten > like this. > > Has anyone had a similar experience and is it related to the > Thread.Local problem? Does anyone know if this is addressed in the > 1.4.0 beta JDK or is it scheduled to be addressed in an upcoming > Postgresql release? > > Any help would be greatly appreciated. > > Thanks in advance. > > John Cook > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
Barry, You are right on 2 points. First, LIKE is not the issue, as I modified the code to remove it, but still have the same problem. Also, 1.4 didn't have any impact either. I have done my best to go through my code with a fine tooth comb, but I will do it again. I will test the code an submit it to the group. Thanks for the help. John Barry Lind wrote: > My guess is that this is unlikely to be the result of the ThreadLocal > issues and also I doubt 1.4 will have any effect. This sounds like a > memory leak which could be in the driver or in your application code. I > also doubt that the use of LIKE is the problem as the JDBC code doesn't > parse the SQL so it doesn't know if you are using LIKE or not. > > To fix this we would either need a reproducable test case that you could > submit such that we could reproduce the problem and fix it, or you could > use a java memory profiler to track down what objects are being > allocated and not released. I personally like OptimizeIt as it has > helped me solve quite a few memory leak problems with java code. > > thanks, > --Barry > > John Cook wrote: > > > Hi, > > > > I noticed several postings a month ago regarding a memory leak brought > > about by the use of Thread.Local in jdbc2/PreparedStatement.java. I > > have what seems like a similar problem, but I am not sure. I have > > applied the patch, but I still have my problem (mine is not really > > associated with DateFormat usage.) > > > > I am running Enhydra Application Server 3.1 on top of Postgres and I > > have been running an application that tries to match text strings to > > items in my database. It has been running fine for months until > > I recently made a modification that uses a lot of "LIKE" statements > > (i.e. anywhere from 2-200 at a time.) Performance is fine, but memory > > starts getting eaten up and I eventually hits an OutOfMemoryError. The > > bigger the prepared statement (i.e. the more LIKEs) the more memory is > > eaten and the faster I run out of memory. Oddly enough, before I added > > the LIKE statements, I was running fine and, although I used a lot of > > prepared statements (the app server did), memory was not being eaten > > like this. > > > > Has anyone had a similar experience and is it related to the > > Thread.Local problem? Does anyone know if this is addressed in the > > 1.4.0 beta JDK or is it scheduled to be addressed in an upcoming > > Postgresql release? > > > > Any help would be greatly appreciated. > > > > Thanks in advance. > > > > John Cook > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > >
Barry, I got OptimizeIt configured, and it looks like it is jdbc2/PreparedStatement which is not being garbage collected. All of my PreparedStatement s stay visible in Optimize it and the number of instances never decreases. What other information can I provide to help determine if this is a memory leak or another problem? I am using Postgresql 7.1.2 and the driver that accompanied it. Thanks. John Barry Lind wrote: > My guess is that this is unlikely to be the result of the ThreadLocal > issues and also I doubt 1.4 will have any effect. This sounds like a > memory leak which could be in the driver or in your application code. I > also doubt that the use of LIKE is the problem as the JDBC code doesn't > parse the SQL so it doesn't know if you are using LIKE or not. > > To fix this we would either need a reproducable test case that you could > submit such that we could reproduce the problem and fix it, or you could > use a java memory profiler to track down what objects are being > allocated and not released. I personally like OptimizeIt as it has > helped me solve quite a few memory leak problems with java code. > > thanks, > --Barry > > John Cook wrote: > > > Hi, > > > > I noticed several postings a month ago regarding a memory leak brought > > about by the use of Thread.Local in jdbc2/PreparedStatement.java. I > > have what seems like a similar problem, but I am not sure. I have > > applied the patch, but I still have my problem (mine is not really > > associated with DateFormat usage.) > > > > I am running Enhydra Application Server 3.1 on top of Postgres and I > > have been running an application that tries to match text strings to > > items in my database. It has been running fine for months until > > I recently made a modification that uses a lot of "LIKE" statements > > (i.e. anywhere from 2-200 at a time.) Performance is fine, but memory > > starts getting eaten up and I eventually hits an OutOfMemoryError. The > > bigger the prepared statement (i.e. the more LIKEs) the more memory is > > eaten and the faster I run out of memory. Oddly enough, before I added > > the LIKE statements, I was running fine and, although I used a lot of > > prepared statements (the app server did), memory was not being eaten > > like this. > > > > Has anyone had a similar experience and is it related to the > > Thread.Local problem? Does anyone know if this is addressed in the > > 1.4.0 beta JDK or is it scheduled to be addressed in an upcoming > > Postgresql release? > > > > Any help would be greatly appreciated. > > > > Thanks in advance. > > > > John Cook > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > >
On Fri, 03 Aug 2001 15:47:20 -0400, you wrote: >it looks like it is jdbc2/PreparedStatement which is not being garbage >collected. All of my PreparedStatement stay visible in Optimize it >and the number of instances never decreases. Are you sure your all references to these objects in your application code are gone? It's not unusual to keep PreparedStatements alive for reuse. >What other information can I provide to help determine if this is a >memory leak or another problem? Please provide the source code (preferably simplified as much as possible) that reproduces the problem, and a description of the tables involved and perhaps information about the data that's in the tables. Basically, post everything that we need to see the same thing you're seeing. Regards, René Pijlman
All, Please ignore my previous e-mails as I have found where my problem lies and it is not in the Postgresql driver. Apparently Enhydra uses a prepared statement cache and the size of my prepared statements and the number of statements being allowed into the cache (was at 256. I tuned it back to 64) was contributing to the OutOfMemoryError. Barry - Thanks so much for recommending OptimizeIt. It was key to finding my problem. To all who are working on this project and have helped give my some clues, many thanks. I have been using Postgresql for a while now and am extremely happy with it. Thanks again. John John Cook wrote: > Barry, > > I got OptimizeIt configured, and it looks like it is jdbc2/PreparedStatement > which is not being garbage collected. All of my PreparedStatement s stay > visible in Optimize it and the number of instances never decreases. What > other information can I provide to help determine if this is a memory leak or > another problem? I am using Postgresql 7.1.2 and the driver that accompanied > it. > > Thanks. > > John > > Barry Lind wrote: > > > My guess is that this is unlikely to be the result of the ThreadLocal > > issues and also I doubt 1.4 will have any effect. This sounds like a > > memory leak which could be in the driver or in your application code. I > > also doubt that the use of LIKE is the problem as the JDBC code doesn't > > parse the SQL so it doesn't know if you are using LIKE or not. > > > > To fix this we would either need a reproducable test case that you could > > submit such that we could reproduce the problem and fix it, or you could > > use a java memory profiler to track down what objects are being > > allocated and not released. I personally like OptimizeIt as it has > > helped me solve quite a few memory leak problems with java code. > > > > thanks, > > --Barry > > > > John Cook wrote: > > > > > Hi, > > > > > > I noticed several postings a month ago regarding a memory leak brought > > > about by the use of Thread.Local in jdbc2/PreparedStatement.java. I > > > have what seems like a similar problem, but I am not sure. I have > > > applied the patch, but I still have my problem (mine is not really > > > associated with DateFormat usage.) > > > > > > I am running Enhydra Application Server 3.1 on top of Postgres and I > > > have been running an application that tries to match text strings to > > > items in my database. It has been running fine for months until > > > I recently made a modification that uses a lot of "LIKE" statements > > > (i.e. anywhere from 2-200 at a time.) Performance is fine, but memory > > > starts getting eaten up and I eventually hits an OutOfMemoryError. The > > > bigger the prepared statement (i.e. the more LIKEs) the more memory is > > > eaten and the faster I run out of memory. Oddly enough, before I added > > > the LIKE statements, I was running fine and, although I used a lot of > > > prepared statements (the app server did), memory was not being eaten > > > like this. > > > > > > Has anyone had a similar experience and is it related to the > > > Thread.Local problem? Does anyone know if this is addressed in the > > > 1.4.0 beta JDK or is it scheduled to be addressed in an upcoming > > > Postgresql release? > > > > > > Any help would be greatly appreciated. > > > > > > Thanks in advance. > > > > > > John Cook > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
John, Please pick the driver up from http://jdbc.fastcrypt.com This has been built from the most recent cvs Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of John Cook Sent: August 3, 2001 3:47 PM To: Barry Lind Cc: pgsql-jdbc@postgresql.org Subject: [JDBC] Re: Memory Leak / Prepared Statement Barry, I got OptimizeIt configured, and it looks like it is jdbc2/PreparedStatement which is not being garbage collected. All of my PreparedStatement s stay visible in Optimize it and the number of instances never decreases. What other information can I provide to help determine if this is a memory leak or another problem? I am using Postgresql 7.1.2 and the driver that accompanied it. Thanks. John Barry Lind wrote: > My guess is that this is unlikely to be the result of the ThreadLocal > issues and also I doubt 1.4 will have any effect. This sounds like a > memory leak which could be in the driver or in your application code. > I also doubt that the use of LIKE is the problem as the JDBC code > doesn't parse the SQL so it doesn't know if you are using LIKE or not. > > To fix this we would either need a reproducable test case that you > could submit such that we could reproduce the problem and fix it, or > you could use a java memory profiler to track down what objects are > being allocated and not released. I personally like OptimizeIt as it > has helped me solve quite a few memory leak problems with java code. > > thanks, > --Barry > > John Cook wrote: > > > Hi, > > > > I noticed several postings a month ago regarding a memory leak > > brought about by the use of Thread.Local in > > jdbc2/PreparedStatement.java. I have what seems like a similar > > problem, but I am not sure. I have applied the patch, but I still > > have my problem (mine is not really associated with DateFormat > > usage.) > > > > I am running Enhydra Application Server 3.1 on top of Postgres and I > > have been running an application that tries to match text strings to > > items in my database. It has been running fine for months until I > > recently made a modification that uses a lot of "LIKE" statements > > (i.e. anywhere from 2-200 at a time.) Performance is fine, but > > memory starts getting eaten up and I eventually hits an > > OutOfMemoryError. The bigger the prepared statement (i.e. the more > > LIKEs) the more memory is eaten and the faster I run out of memory. > > Oddly enough, before I added the LIKE statements, I was running fine > > and, although I used a lot of prepared statements (the app server > > did), memory was not being eaten like this. > > > > Has anyone had a similar experience and is it related to the > > Thread.Local problem? Does anyone know if this is addressed in the > > 1.4.0 beta JDK or is it scheduled to be addressed in an upcoming > > Postgresql release? > > > > Any help would be greatly appreciated. > > > > Thanks in advance. > > > > John Cook > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
John, Objects in java don't get garbage collected when other objects hold references to them. So the challenge now is to understand what objects are holding references to these PreparedStatment objects. In OptimizeIt you can look at the dependency graph to determine what objects are holding references to the PreparedStatement objects. From there you should be able to figure out how to fix the problem. You say that you are using Enhydra. It is certainly possible that how you are using Enhydra could be causing it to hold the references. For example are you always ensuring that you are closing your PreparedStatement objects? (ideally in a try/finally block to ensure that you don't forget to close them when exceptions are thrown). I could see how not closing PreparedStatements could easily lead to Enhydra keeping references to the PreparedStatements. thanks, --Barry John Cook wrote: > Barry, > > I got OptimizeIt configured, and it looks like it is jdbc2/PreparedStatement > which is not being garbage collected. All of my PreparedStatement s stay > visible in Optimize it and the number of instances never decreases. What > other information can I provide to help determine if this is a memory leak or > another problem? I am using Postgresql 7.1.2 and the driver that accompanied > it. > > Thanks. > > John > > Barry Lind wrote: > > >>My guess is that this is unlikely to be the result of the ThreadLocal >>issues and also I doubt 1.4 will have any effect. This sounds like a >>memory leak which could be in the driver or in your application code. I >>also doubt that the use of LIKE is the problem as the JDBC code doesn't >>parse the SQL so it doesn't know if you are using LIKE or not. >> >>To fix this we would either need a reproducable test case that you could >>submit such that we could reproduce the problem and fix it, or you could >>use a java memory profiler to track down what objects are being >>allocated and not released. I personally like OptimizeIt as it has >>helped me solve quite a few memory leak problems with java code. >> >>thanks, >>--Barry >> >>John Cook wrote: >> >> >>>Hi, >>> >>>I noticed several postings a month ago regarding a memory leak brought >>>about by the use of Thread.Local in jdbc2/PreparedStatement.java. I >>>have what seems like a similar problem, but I am not sure. I have >>>applied the patch, but I still have my problem (mine is not really >>>associated with DateFormat usage.) >>> >>>I am running Enhydra Application Server 3.1 on top of Postgres and I >>>have been running an application that tries to match text strings to >>>items in my database. It has been running fine for months until >>>I recently made a modification that uses a lot of "LIKE" statements >>>(i.e. anywhere from 2-200 at a time.) Performance is fine, but memory >>>starts getting eaten up and I eventually hits an OutOfMemoryError. The >>>bigger the prepared statement (i.e. the more LIKEs) the more memory is >>>eaten and the faster I run out of memory. Oddly enough, before I added >>>the LIKE statements, I was running fine and, although I used a lot of >>>prepared statements (the app server did), memory was not being eaten >>>like this. >>> >>>Has anyone had a similar experience and is it related to the >>>Thread.Local problem? Does anyone know if this is addressed in the >>>1.4.0 beta JDK or is it scheduled to be addressed in an upcoming >>>Postgresql release? >>> >>>Any help would be greatly appreciated. >>> >>>Thanks in advance. >>> >>>John Cook >>> >>> >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >>> >>> >>> > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
Barry, The PreparedStatement references were being held in a Hashtable cache inside Enhydra. I was never actually using PreparedStatements directly, which was part of what made finding the problem difficult. For those who use Enhydra, there is a parameter called "maxPreparedStatements" in the DatabaseManager. When using very large select statements, setting this parameter too high can cause OutOfMemoryErrors. After adjusting this, the problem was solved. Thanks again for the help. John Barry Lind wrote: > John, > > Objects in java don't get garbage collected when other objects hold > references to them. So the challenge now is to understand what objects > are holding references to these PreparedStatment objects. In OptimizeIt > you can look at the dependency graph to determine what objects are > holding references to the PreparedStatement objects. From there you > should be able to figure out how to fix the problem. > > You say that you are using Enhydra. It is certainly possible that how > you are using Enhydra could be causing it to hold the references. For > example are you always ensuring that you are closing your > PreparedStatement objects? (ideally in a try/finally block to ensure > that you don't forget to close them when exceptions are thrown). I > could see how not closing PreparedStatements could easily lead to > Enhydra keeping references to the PreparedStatements. > > thanks, > --Barry > > John Cook wrote: > > > Barry, > > > > I got OptimizeIt configured, and it looks like it is jdbc2/PreparedStatement > > which is not being garbage collected. All of my PreparedStatement s stay > > visible in Optimize it and the number of instances never decreases. What > > other information can I provide to help determine if this is a memory leak or > > another problem? I am using Postgresql 7.1.2 and the driver that accompanied > > it. > > > > Thanks. > > > > John > > > > Barry Lind wrote: > > > > > >>My guess is that this is unlikely to be the result of the ThreadLocal > >>issues and also I doubt 1.4 will have any effect. This sounds like a > >>memory leak which could be in the driver or in your application code. I > >>also doubt that the use of LIKE is the problem as the JDBC code doesn't > >>parse the SQL so it doesn't know if you are using LIKE or not. > >> > >>To fix this we would either need a reproducable test case that you could > >>submit such that we could reproduce the problem and fix it, or you could > >>use a java memory profiler to track down what objects are being > >>allocated and not released. I personally like OptimizeIt as it has > >>helped me solve quite a few memory leak problems with java code. > >> > >>thanks, > >>--Barry > >> > >>John Cook wrote: > >> > >> > >>>Hi, > >>> > >>>I noticed several postings a month ago regarding a memory leak brought > >>>about by the use of Thread.Local in jdbc2/PreparedStatement.java. I > >>>have what seems like a similar problem, but I am not sure. I have > >>>applied the patch, but I still have my problem (mine is not really > >>>associated with DateFormat usage.) > >>> > >>>I am running Enhydra Application Server 3.1 on top of Postgres and I > >>>have been running an application that tries to match text strings to > >>>items in my database. It has been running fine for months until > >>>I recently made a modification that uses a lot of "LIKE" statements > >>>(i.e. anywhere from 2-200 at a time.) Performance is fine, but memory > >>>starts getting eaten up and I eventually hits an OutOfMemoryError. The > >>>bigger the prepared statement (i.e. the more LIKEs) the more memory is > >>>eaten and the faster I run out of memory. Oddly enough, before I added > >>>the LIKE statements, I was running fine and, although I used a lot of > >>>prepared statements (the app server did), memory was not being eaten > >>>like this. > >>> > >>>Has anyone had a similar experience and is it related to the > >>>Thread.Local problem? Does anyone know if this is addressed in the > >>>1.4.0 beta JDK or is it scheduled to be addressed in an upcoming > >>>Postgresql release? > >>> > >>>Any help would be greatly appreciated. > >>> > >>>Thanks in advance. > >>> > >>>John Cook > >>> > >>> > >>> > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >>> > >>> > >>> > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
* Barry Lind <barry@xythos.com> wrote: | | example are you always ensuring that you are closing your | PreparedStatement objects? (ideally in a try/finally block to ensure | that you don't forget to close them when exceptions are thrown). I ;-) This is why I usually insist on applying the command pattern. Then you only have one place to do cleanup(or other exception handling - waiting for errorcodes...). -- Gunnar Rønning - gunnar@polygnosis.com Senior Consultant, Polygnosis AS, http://www.polygnosis.com/