RE: High memory usage - Mailing list pgsql-admin

I've continued to work on this problem with limited results so far. I have
lowered the connection re-use setting in our connection pool but this
doesn't seem to have helped. I believe that one particular query ends up
allocating around 80MB of memory and if more than 2 or 3 of the connections
in the pool do this simultaneously then the total memory usage becomes too
high (number of connections * 80 MB). I haven't been able to determine the
exact query yet, I'm still working on it.


I have also discovered another problem with this system and running out of
memory. This one, I believe is a bug in the JDBC driver. Here's the
situation:

The Java program processes a large number (in this case, around 60,000) of
database actions (each consists of a few simple queries and a larger insert
or update), all in a single Java thread. For each of these 60,000 actions
there is a PreparedStatement created. In the PostgreSQL JDBC driver's
implementation of PreparedStatement the following exists during the object
instantiation:


        // We use ThreadLocal for SimpleDateFormat's because they are not
that
        // thread safe, so each calling thread has its own object.
        private ThreadLocal tl_df   = new ThreadLocal(); // setDate()
SimpleDateFormat
        private ThreadLocal tl_tsdf = new ThreadLocal(); // setTimestamp()
SimpleDateFormat


So, you can see that every single PreparedStatement allocates 2 ThreadLocal
objects. The interesting part is that each of these objects persists for the
entire time the thread persists (in our case, until all 60,000 actions are
completed). This is stated in the Sun Javadocs for ThreadLocal:

    Each thread holds an implicit reference to its copy of a
    ThreadLocal as long as the thread is alive and the
    ThreadLocal object is accessible; after a thread goes
    away, all of its copies of ThreadLocal variables are
    subject to garbage collection (unless other references
    to these copies exist).

So, this means that ANY APPLICATION that uses PreparedStatements in a thread
that 1) either does a lot of PreparedStatements or 2) never dies (i.e., a
main thread) will ALWAYS eventually have an out of memory error. Simply put,
this is a MEMORY LEAK. I imagine that the leak is very small, the
ThreadLocal object only contains one member variable, maybe 64 bytes or less
(depending on the VM implementation). So, our 60,000 PreparedStatements of 2
ThreadLocals each times 64 bytes (my wild guess) is 7.5MB.

The good news is that ThreadLocal is ONLY used in PreparedStatemnt and not
in any other parts of the JDBC driver.

I'll work on a patch but if someone has already done this I would be
grateful.



--Rainer

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Thursday, June 14, 2001 10:03 AM
> To: Rainer Mager
> Cc: PostgreSQL Admin
> Subject: Re: [ADMIN] High memory usage
>
> If you can catch the thing while the memory usage is going up, you could
> attach to the backend with gdb and examine debug_query_string to see
> what the current query is.  (Note: I think you need 7.1.1 or later to
> have this variable available.  You should be on 7.1.2 in any case, just
> on general principles.)  Otherwise, consider turning on query logging
> and see if you can correlate log entries with the memory consumption.